created: 2022-09-10T09:21:28.156Z

GA4 (Google Analytics 4) から BigQuery に連携されたデータを簡単にクエリするためのビュー定義

Google Analytics 4 から BigQuery 連携が簡単になった。コンソールでちょこちょこやるだけで GA4 のデータが毎日 BigQuery に連携されるようになっている。

今回は GA4 のデータを SQL でクエリしやすいように加工したビューをメモっておく。

連携されるデータの構造がややこしかったのでフラットにする
カラムがたくさんあってよく使うやつだけに絞ったテーブルが欲しかった

定義

CREATE OR REPLACE VIEW tmp.v AS (
  SELECT
    _TABLE_SUFFIX TABLE_SUFFIX,
    event_name,
    timestamp_micros(event_timestamp) event_timestamp,
    user_pseudo_id,
    -- event_params
    bqutil.fn.get_value("ga_session_id", event_params).int_value ga_session_id,
    bqutil.fn.get_value("ga_session_number", event_params).int_value ga_session_number,
    bqutil.fn.get_value("page_title", event_params).string_value page_title,
    bqutil.fn.get_value("page_location", event_params).string_value page_location,
    bqutil.fn.get_value("page_referrer", event_params).string_value page_referrer,
    bqutil.fn.get_value("ignore_referrer", event_params).string_value ignore_referrer,
    -- traffic_source
    traffic_source.name traffic_source_name,
    traffic_source.medium traffic_source_medium,
    traffic_source.source traffic_source_source,
    -- device
    device.category device_category,
    device.mobile_model_name device_mobile_model_name,
    device.operating_system_version device_operating_system_version,
    device.web_info.browser device_web_info_browser,
    device.web_info.browser_version device_web_info_browser_version
  FROM
    `proj-123456.analytics_987654321.events_*`
  WHERE
    event_name = "page_view"
);

TABLE_SUFFIX = "20220101" とかやるとちゃんとスキャン範囲を絞ってくれる

GA4 (Google Analytics 4) から BigQuery に連携されたデータを簡単にクエリするためのビュー定義

定義

参考