GA4×BigQueryでUA指標の直帰率を計測するSQLクエリ

/*
サイト全体の直帰率を集計（UA指標）
*/

with predata as (
  select
    event_timestamp,
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id
  from
    `ga4-bigquery-pj-392206.analytics_342165491.events_*`
  where
    _table_suffix between '20240401' and '20240430'
    and event_name = 'page_view'
),
predata2 as (
  select
    case
      -- セッションIDごとにグループ化し、イベントが発生した時刻順にデータを並べ、最初に発生したページビューを1とする
      when row_number() over(partition by session_id order by event_timestamp) = 1 then 1 
      -- それ以外（セッション内で最初のページビューではない場合）は0とする
      else 0
    end as entrance,
    case
      -- セッション内のページビューイベントの総数が1であれば1とする（直帰したとカウント）
      when count(1) over(partition by session_id) = 1 then 1 
      -- それ以外（セッション内のページビューイベントの総数が1以上である場合）は0とする（直帰していないとカウント）
      else 0
    end as bounce
  from predata
)
select
    sum(entrance) as landing,
    sum(bounce) as bounce,
    -- 直帰率の計算（直帰数 / 流入回数 * 100）
    case
      -- ゼロ除算のエラーを防ぐ
      when sum(entrance) > 0 then round((sum(bounce) / sum(entrance)) * 100, 2)
      else 0
    end as bounce_rate
from
    predata2
;

/*
ページごとの流入回数、直帰数、直帰率を集計
*/

with predata as (
  select
    event_timestamp,
    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
  from
    `ga4-bigquery-pj-392206.analytics_342165491.events_*`
  where
    _table_suffix between '20240401' and '20240430'
    and event_name = 'page_view'
),
predata2 as (
  select
    page_location,
    case
      when row_number() over(partition by session_id order by event_timestamp) = 1 then 1 
      else 0
    end as entrance,
    case
      when count(1) over(partition by session_id) = 1 then 1 
      else 0
    end as bounce
  from predata
)
select
    page_location,
    sum(entrance) as landing,
    sum(bounce) as bounce,
    case
      when sum(entrance) > 0 then round((sum(bounce) / sum(entrance)) * 100, 2)
      else 0
    end as bounce_rate
from
    predata2
group by
    page_location
order by
    landing desc
;