一つ上へ

レコードにネスト構造がある場合、単純にselectしてもエラーとなる。 その場合はUNNEST関数を使用することで解消できる。 例えば、GA4ではvalue.string_valueの中にevent_paramsレコードがあり、 その中にさらにレコードが存在するという構造になっている。 このうち、event_paramsのkeyがpage_locationとなっているものを取得したいとすると、

(SELECT VALUE.string_value FROM UNNEST(event_params) WHERE KEY = 'page_location') AS page_location

とする必要がある。

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

SELECT
  COUNT(user_pseudo_id) AS PV
FROM `project_name.data_set.events_20230101`
WHERE event_name = 'page_view'