差分

このページの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:bigquery|一つ上へ]]
+===== ネスト構造を持つレコードの対応 =====
+レコードにネスト構造がある場合、単純にselectしてもエラーとなる。
+その場合は<wrap hi>UNNEST関数</wrap>を使用することで解消できる。
+例えば、GA4ではvalue.string_valueの中にevent_paramsレコードがあり、
+その中にさらにレコードが存在するという構造になっている。
+このうち、event_paramsのkeyがpage_locationとなっているものを取得したいとすると、
+<code sql>
+(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
+</code>
+とする必要がある。
+===== GA4 から Tableau への取り込み =====
+<code sql>
+WITH source_data AS (
+SELECT
+  event_timestamp, -- アクセス日時
+  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS event_date, -- アクセス日時
+  event_name, -- イベント名
+  device.category as device_category, -- デバイスカテゴリ
+  device.operating_system as device_operating_system, -- デバイスOS
+  device.web_info.browser as device_web_info_browser, -- ブラウザ
+  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location, -- ページURL
+  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title, -- ページタイトル
+  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS inflow_campaign, -- 流入キャンペーン
+  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 = 'ga_session_id')
+    ,user_pseudo_id
+  ) AS session_id, -- ユーザーIDとセッションIDの結合したものを一意なセッションIDとして定義
+FROM `xxxxxx.analytics_1111111111.events_20231111`
+WHERE event_name IN ('session_start', 'page_view') -- セッション開始イベント、べーじビューに絞る
+)
+-- ベースとなるセッションID毎のセッション情報。セッション開始（流入）イベントの属性情報
+,base_session_start_data AS (
+SELECT
+  session_id,
+  event_date,
+  device_category,
+  device_operating_system,
+  inflow_campaign,
+  inflow_source,
+  inflow_medium,
+  page_location, -- セッション開始時のページURL = ランディングページURL
+  REGEXP_EXTRACT(page_location, '//[*/]+([^?#]+)') AS page_location_path, -- パラメータを除いたランディングページURL(パス)
+  SPLIT(REGEXP_EXTRACT(page_location, '//[*/]+([^?#]+)'), '/')[SAFE_ORDINAL(2)] AS page_location_path_1, -- ランディングページURL(パス)の第1ディレクトリ
+  SPLIT(REGEXP_EXTRACT(page_location, '//[*/]+([^?#]+)'), '/')[SAFE_ORDINAL(3)] AS page_location_path_1, -- ランディングページURL(パス)の第2ディレクトリ
+  SPLIT(REGEXP_EXTRACT(page_location, '//[*/]+([^?#]+)'), '/')[SAFE_ORDINAL(4)] AS page_location_path_1, -- ランディングページURL(パス)の第3ディレクトリ
+  page_title
+FROM source_data
+WHERE event_name = 'session_start'
+)
+-- セッションID毎のPV数
+,page_view_count_session_data AS (
+SELECT
+  session_id,
+  count(*) as pv_count
+FROM source_data
+WHERE event_name = 'session_start'
+)
+-- CV毎のセッションIDを抽出
+,conversion_session_data AS (
+SELECT
+  session_id
+FROM source_data
+WHERE event_name IN ('in_app_purchase', 'purchase')
+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, -- 当ページ1ページのみアクセスしたセッションの総数（直帰数）
+  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
+</code>
+===== Tips =====
+==== PV数取得 ====
+<code sql>
+SELECT
+  COUNT(user_pseudo_id) AS PV
+FROM `project_name.data_set.events_20230101`
+WHERE event_name = 'page_view'
+</code>