GA4のbigquery exportデータをフラットにする

2021/07/01sql bigquery scheduled-query ga

表題の通り

GA4ではbigqueryへのexportを無料で行うことができるが、そのままだと少し扱いづらい

特にevent_paramsがRECORDになっており、あるイベントのページURLやタイトルを取得する際の構文が長くなる

フラット化前後のクエリ例

生のままでは例えば↓のように書く

# 直近30日のページごとPV
select
  date(timestamp_micros(event_timestamp), 'Asia/Tokyo') as date
  -- ここがややこしい
  ,(select string_value from unnest(event_params) where key = 'page_title') as page_title
  ,(select string_value from unnest(event_params) where key = 'page_location') as page_location
  ,count(1) as pv
from
  `analytics_xxxxxxxxx.events_*`
where
  event_name = 'page_view'
and
  _table_suffix between
    format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -30 day))    
  and
    format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))
group by 1,2,3

フラット化したものだと以下のようにかける。行数は変わらないが、unnest構文を覚えておかなくて済む

# 直近30日のページごとPV
select
  date(date)
  ,page_title
  ,page_location
  ,count(1) as pv  
from
  `analytics_xxxxxxxxx.flat_events_*`
where
  event_name = 'page_view'
and
  _table_suffix between
    format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -30 day))    
  and
    format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))
group by 1, 2, 3

スケジュールドクエリの作成

確定データをフラット化したテーブルを定期的に作成する

exportタイミング

https://support.google.com/analytics/answer/9823238?hl=ja#zippy=%2C%E3%81%93%E3%81%AE%E8%A8%98%E4%BA%8B%E3%81%AE%E5%86%85%E5%AE%B9

前日のデータが格納された 1 ファイルが毎日（通常は、レポート作成用に設定したタイムゾーンの午後の早い時間帯に）エクスポートされます。

何やら更新日時が曖昧なので、毎日0時に2日前のテーブルを転送するようにする。最速でやるなら夕方に前日のテーブルを抽出するのだろうがわかりやすい日時にしておく

テーブル、スキーマ情報

https://support.google.com/analytics/answer/7029846?hl=ja

テーブルは2種類ある。intradayは未確定なデータが入っている

これに加えて、1 日の途中で発生したアプリイベントを格納した表もインポートされます。この表には「events_intraday_YYYYMMDD」という形式の名前が付き、イベントが収集されるとデータがリアルタイムで追加されます。

確定データを転写したいのでevents_を用いる

クエリ

user_properties

、

privacy_info

、eコマース

ecommerce

、商品アイテム

items

は抽出していない

クエリは完全に↓のコピペ

https://ex-ture.com/blog/2019/08/07/google-analytics-apps-web-property-export-to-bigquery/

クエリ内容

select
  event_date
  ,event_timestamp
  -- jst timestamp
  ,datetime(timestamp_micros(event_timestamp), 'Asia/Tokyo') as date
  ,event_name
  -- カスタム定義に応じたvalueの追加
  ,(select value.int_value from unnest(event_params) where key = 'engaged_session_event') as engaged_session_event
  ,(select value.string_value from unnest(event_params) where key = 'page_location') as page_location
  ,(select value.string_value from unnest(event_params) where key = 'page_title') as page_title
  ,(select value.int_value from unnest(event_params) where key = 'ga_session_number') as ga_session_number
  ,(select value.string_value from unnest(event_params) where key = 'page_referrer') as page_referrer
  ,(select value.int_value from unnest(event_params) where key = 'ga_session_id') as ga_session_id
  ,(select value.string_value from unnest(event_params) where key = 'medium') as medium
  ,(select value.string_value from unnest(event_params) where key = 'term') AS term
  ,(select value.string_value from unnest(event_params) where key = 'campaign') as campaign
  ,(select value.string_value from unnest(event_params) where key = 'source') as source
  ,(select value.string_value from unnest(event_params) where key = 'session_engaged') as session_engaged
  ,(select value.int_value from unnest(event_params) where key = 'entrances') as entrances
  ,(select value.int_value from unnest(event_params) where key = 'engagement_time_msec') as engagement_time_msec
  ,(select value.int_value from unnest(event_params) where key = 'percent_scrolled') as percent_scrolled
  ,event_previous_timestamp
  ,event_value_in_usd
  ,event_bundle_sequence_id
  ,event_server_timestamp_offset
  ,user_id
  ,user_pseudo_id
  ,user_first_touch_timestamp
  ,user_ltv.revenue
  ,user_ltv.currency
  ,device.category
  ,device.mobile_brand_name
  ,device.mobile_model_name
  ,device.mobile_marketing_name
  ,device.mobile_os_hardware_model
  ,device.operating_system
  ,device.operating_system_version
  ,device.vendor_id
  ,device.advertising_id
  ,device.language
  ,device.is_limited_ad_tracking
  ,device.time_zone_offset_seconds
  ,device.browser
  ,device.browser_version
  ,device.web_info.browser as device_web_info_browser
  ,device.web_info.browser_version as device_web_info_browser_version
  ,device.web_info.hostname
  ,geo.continent
  ,geo.country
  ,geo.region
  ,geo.city
  ,geo.sub_continent
  ,geo.metro
  ,app_info.id
  ,app_info.version
  ,app_info.install_store
  ,app_info.firebase_app_id
  ,app_info.install_source
  ,traffic_source.name
  ,traffic_source.medium as traffic_source_medium
  ,traffic_source.source as traffic_source_source
  ,stream_id
  ,platform
  ,event_dimensions.hostname as event_dimensions_hostname
from `analytics_xxxxxxxxx.events_*`
where
  _table_suffix = format_date('%Y%m%d', date_add(@run_date, interval -2 day))

実行は毎日00:00

destination tableは2日前の日付をつけてwildcard tableとして作成する

flat_events_{run_time-48h|"%Y%m%d"}

テーブル名は任意（ここでは

flat_events_

）

過去データに対してスケジュールドクエリを適用

過去分のテーブルについて適用することもできる

スケジュールドクエリのバックフィル

bigquery consoleより、スケジュールされたクエリ→作成したスケジュールドクエリを選択

https://console.cloud.google.com/bigquery/scheduled-queries

バックフィルのスケジュール構成より日付を選択したOKをクリックすると、

run_dateなどの値が指定された日時で実行されるジョブが作成される（日別に作られる模様）

おまけ

テーブルの復元

作業中にテーブルを上書きしてしまったりして、復元もしたのでついでに残しておく

2日くらいなら戻せるとのこと

https://cloud.google.com/bigquery/docs/managing-tables?hl=ja#undeletetable

10時間前の状態に復元

# replace
bq cp analytics_xxxxxxxxx.events_20210701@-7200000 analytics_xxxxxxxxx.events_20210701

参考

ga4 export bigqueryの方法

https://support.google.com/analytics/answer/9358801

exportしたデータのクエリ

https://analytics-and-intelligence.net/article/2ipnnyjn5s6pcamk5pdy