database:bigquery:ga4
差分
このページの2つのバージョン間の差分を表示します。
両方とも前のリビジョン前のリビジョン次のリビジョン | 前のリビジョン | ||
database:bigquery:ga4 [2023/02/08 16:01] – 削除 - 外部編集 (Unknown date) 127.0.0.1 | database:bigquery:ga4 [2023/08/08 16:36] (現在) – mikoto | ||
---|---|---|---|
行 1: | 行 1: | ||
+ | ====== BigQuery による GA4 データの処理について ====== | ||
+ | [[database: | ||
+ | ===== ネスト構造を持つレコードの対応 ===== | ||
+ | レコードにネスト構造がある場合、単純にselectしてもエラーとなる。 | ||
+ | その場合は< | ||
+ | 例えば、GA4ではvalue.string_valueの中にevent_paramsレコードがあり、 | ||
+ | その中にさらにレコードが存在するという構造になっている。 | ||
+ | このうち、event_paramsのkeyがpage_locationとなっているものを取得したいとすると、 | ||
+ | <code sql> | ||
+ | (SELECT value.string_value FROM UNNEST(event_params) WHERE key = ' | ||
+ | </ | ||
+ | とする必要がある。 | ||
+ | ===== GA4 から Tableau への取り込み ===== | ||
+ | <code sql> | ||
+ | WITH source_data AS ( | ||
+ | SELECT | ||
+ | event_timestamp, | ||
+ | DATE(timestamp_micros(event_timestamp), | ||
+ | event_name, -- イベント名 | ||
+ | device.category as device_category, | ||
+ | device.operating_system as device_operating_system, | ||
+ | device.web_info.browser as device_web_info_browser, | ||
+ | |||
+ | (SELECT value.string_value FROM UNNEST(event_params) WHERE key = ' | ||
+ | (SELECT value.string_value FROM UNNEST(event_params) WHERE key = ' | ||
+ | |||
+ | (SELECT value.string_value FROM UNNEST(event_params) WHERE key = ' | ||
+ | traffic_source.source as inflow_source, | ||
+ | traffic_source.medium as inflow_medium, | ||
+ | |||
+ | user_pseudo_id as user_id, -- ユーザーID | ||
+ | CONCAT( | ||
+ | (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ' | ||
+ | , | ||
+ | ) AS session_id, -- ユーザーIDとセッションIDの結合したものを一意なセッションIDとして定義 | ||
+ | |||
+ | FROM `xxxxxx.analytics_1111111111.events_20231111` | ||
+ | WHERE event_name IN (' | ||
+ | ) | ||
+ | |||
+ | -- ベースとなるセッションID毎のセッション情報。セッション開始(流入)イベントの属性情報 | ||
+ | , | ||
+ | SELECT | ||
+ | session_id, | ||
+ | event_date, | ||
+ | device_category, | ||
+ | device_operating_system, | ||
+ | inflow_campaign, | ||
+ | inflow_source, | ||
+ | inflow_medium, | ||
+ | page_location, | ||
+ | REGEXP_EXTRACT(page_location, | ||
+ | SPLIT(REGEXP_EXTRACT(page_location, | ||
+ | SPLIT(REGEXP_EXTRACT(page_location, | ||
+ | SPLIT(REGEXP_EXTRACT(page_location, | ||
+ | page_title | ||
+ | FROM source_data | ||
+ | WHERE event_name = ' | ||
+ | ) | ||
+ | |||
+ | -- セッションID毎のPV数 | ||
+ | , | ||
+ | SELECT | ||
+ | session_id, | ||
+ | count(*) as pv_count | ||
+ | FROM source_data | ||
+ | WHERE event_name = ' | ||
+ | ) | ||
+ | |||
+ | -- CV毎のセッションIDを抽出 | ||
+ | , | ||
+ | SELECT | ||
+ | session_id | ||
+ | FROM source_data | ||
+ | WHERE event_name IN (' | ||
+ | GROUP BY session_id | ||
+ | ) | ||
+ | |||
+ | -- ベースとなるセッション情報に、セッション毎のPV数とCV数を結合し、流入属性情報xランディングページURLごとに集計 | ||
+ | SELECT | ||
+ | -- 流入属性情報 | ||
+ | base.event_date, | ||
+ | base.device_category, | ||
+ | base.inflow_campaign, | ||
+ | base.inflow_source, | ||
+ | base.inflow_medium, | ||
+ | -- ランディングページ | ||
+ | base.page_location, | ||
+ | base.page_location_host, | ||
+ | base.page_location_path, | ||
+ | base.page_location_path_1, | ||
+ | base.page_location_path_2, | ||
+ | base.page_location_path_3, | ||
+ | base.page_title, | ||
+ | | ||
+ | -- 上記組み合わせ毎に集計 | ||
+ | COUNT(*) AS entry_page_session_cnt, | ||
+ | SUM(CASE WHEN pv.pv_count = 1 THEN 1 ELSE 0 END) AS bounce_session_cnt, | ||
+ | SUM(CASE WHEN cv.session_id IS NOT NULL THEN 1 ELSE 0 END) AS conversion_session_cnt -- コンバージョンしたセッションの総数(コンバージョン数) | ||
+ | |||
+ | FROM base_session_start_date AS base | ||
+ | LEFT OUTER JOIN page_view_count_session_data AS pv on base.session_id = pv.session_id | ||
+ | LEFT OUTER JOIN conversion_session_data AS cv on base.session_id = cv.session_id | ||
+ | GROUP BY | ||
+ | event_date, | ||
+ | device_category, | ||
+ | inflow_campaign, | ||
+ | inflow_source, | ||
+ | inflow_medium, | ||
+ | page_location, | ||
+ | page_location_host, | ||
+ | page_location_path, | ||
+ | page_location_path_1, | ||
+ | page_location_path_2, | ||
+ | page_location_path_3, | ||
+ | page_title | ||
+ | </ | ||
+ | |||
+ | ===== Tips ===== | ||
+ | ==== PV数取得 ==== | ||
+ | <code sql> | ||
+ | SELECT | ||
+ | COUNT(user_pseudo_id) AS PV | ||
+ | FROM `project_name.data_set.events_20230101` | ||
+ | WHERE event_name = ' | ||
+ | </ |