GA4×BigQueryで前後パス分析を実現するSQLクエリ

/*
前後パス分析（特定ページの前後1Pを表示）
*/

with prep1 as (
  select
    concat(user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id,
    (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
    event_timestamp
  from
   `ga4-bigquery-pj-392206.analytics_342165491.events_*`
  where
    event_name = 'page_view'
    and _table_suffix between '20240401' and '20240430'
),

prep2 as (
  select
    event_timestamp,
    session_id,
    lag(page_location) over(partition by session_id order by event_timestamp) as prev_page,
    page_location,
    lead(page_location) over(partition by session_id order by event_timestamp) as next_page
  from
    prep1
)

select
  ifnull(prev_page, '( landing )') as prev_page,
  page_location,
  ifnull(next_page, '( exit )') as next_page,
  count(distinct session_id) as session_count
from
  prep2
where
  page_location = 'https://sgs-prog.com/swell-cafe-homepage-develop1/'
group by
  prev_page,
  page_location,
  next_page
having
  page_location not in (prev_page, next_page)
order by
  session_count desc
;