====== 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>