[GA4+BigQuery] イベントごとにばらすクエリ

イベントごとにばらすためのクエリを具体的に書く

連携されたデータを使いやすく整理するにおいて最初に「イベントごとにテーブルを分けることを強くお勧めする」と書いたので、具体的なクエリも紹介する。

前日以前のpage_viewイベント

ひとまずこのクエリをコピーし、プロジェクトとデータセットの名前を変えて実行すれば前日以前のpage_viewイベントが入ったテーブルが作れる。

--前日以前のpage_viewイベント
select
datetime(timestamp_micros(event_timestamp), 'Asia/Tokyo') as event_datetime
,user_pseudo_id
,device.category as device_category
,device.web_info.browser as device_web_info_browser
,traffic_source.source as traffic_source_source
,(select value.int_value from unnest(event_params) params where params.key = ' ga_session_id') as ga_session_id
,(select value.string_value from unnest(event_params) params where params.key = 'page_location') as page_location
from  `xxxxxxxxxx.xxxxxxxxxx.events_*`
where event_name='page_view'
and _table_suffix <= format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))

クエリの詳細

クエリを書いただけだと応用できないので、詳しく説明も書いてみよう

日付

一旦timestampにしてからdatetimeに変換。その際にタイムゾーンを日本時間にしている

--日本時間のdatetime型に変換
datetime(timestamp_micros(event_timestamp), 'Asia/Tokyo') as event_datetime

timestamp型にしたければ変換不要

--timestamp型に変換
,timestamp_micros(event_timestamp) as event_datetime

日付も作れるし、date_truncやextractを使って年月を作ることも可能

--日本時間のdate型に変換
,date(timestamp_micros(event_timestamp), 'Asia/Tokyo') as event_datetime

構造体型

.(ドット）で繋げばアクセスできる

-- 構造体型だけの場合
,device.category
,device.web_info.browser
,traffic_source.source

event_params,user_propatiesのように配列の中に構造体型（struct）型が入れ子になっているならunnestする。取りたいパラメータに合わせたkeyとデータ型を選ぶ

-- 構造体型（struct）型が入れ子になっているならunnest
,(select value.int_value from unnest(event_params) params where params.key = 'ga_session_id') as ga_session_id
,(select value.string_value from unnest(event_params) params where params.key = 'page_location') as page_location

from

期間の絞り込みは_table_suffixで行うのでワイルドカードで全期間指定。プロジェクトとデータセットの名前は自分の環境に併せて変更すること

--全期間指定
from  `xxxxxxxxxx.xxxxxxxxxx.events_*`

イベント名

イベント名を指定

--全期間指定
where event_name='page_view'

期間

whereで期間を指定する。指定しないと全期間読み込まれる。_table_suffixの日付はyyyymmddの文字列型で指定。

-- 実行日の前日以前（日本時間）
and _table_suffix <= format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))

特定の日付を取るならそのまま書く

-- 特定の日付の場合はyyyymmdd
and _table_suffix = '20220101'

GA4+BigQueryのまとめ