GA4 の BigQuery のログを URL 毎に集計

GA4 の BigQuery のログを URL 毎に集計する方法のメモ。

from で unnest(event_params) を指定する場合

select
  event_date as date
  , p.value.string_value as url
  , count(*) as pv
from
  `{ga_dataset}.events_*`
  , unnest(event_params) as p
where
  _table_suffix = '{YYYYmmdd}'
  and event_name = 'page_view'
  and p.key = 'page_location'
group by
  date
  , url
;

from で unnest を指定しない場合

select
  event_date as date
  , (select p.value.string_value from unnest(event_params) p where p.key = 'page_location') as url
  , count(*) as pv
from
  `{ga_dataset}.events_*`
where
  _table_suffix = '{YYYYmmdd}'
  and event_name = 'page_view'
group by
  date
  , url
;

event_params からのデータ取得関数を作成した場合

関数定義

event_params はデータ構造が複雑なため、引数は any type としています。

create or replace function
  {dataset}.get_event_params_value (
    event_params any type
    , key string
  )
returns struct<>
as (
 (select
    p.value
  from
    unnest(event_params) as p
  where
    p.key = key
  )
);

ログ集計 SQL

select
  event_date as date
  , {dataset}.get_event_params_value(event_params, 'page_location').string_value as url
  , count(*) as pv
from
  `{ga_dataset}.events_*`
where
  _table_suffix = '{YYYYmmdd}'
  and event_name = 'page_view'
  and p.key = 'page_location'
group by
  date
  , url
;