【言語分析】BigqueryxSQL:コンバージョンに至るまでのセッション別経路分析

2025年8月31日 2025年8月31日

セイさん

Contents

1 全体の目的
2 1. domain_to_remove サブクエリ
3 2. session_data サブクエリ
4 3. session_paths サブクエリ
5 4. entry_complete サブクエリ
6 5. 最終結果の集計
7

全体の目的

このクエリの主な目的は、GA4のイベントデータを用いて、各ユーザーセッション内での行動を詳細に把握することです。特定のドメイン名を除去し、訪問ページのパスやセッションの長さ、コンバージョンが発生したかどうかを一つのレポートにまとめています。

1. domain_to_remove サブクエリ

非表示にするドメイン名を指定します。例では 'https://www.example.com' というドメインが指定されています。後のクエリでページパスからこのドメイン名が除去されます。
目的: ページパスに含まれるドメイン名を削除して、相対的なパスのみを表示する。

2. session_data サブクエリ

GA4のイベントデータを使用して、セッションごとにページビュー情報を抽出します。
user_pseudo_id と ga_session_id を結合して一意のセッションID (session_id) を作成し、各ページビューのタイムスタンプ (event_timestamp) も記録します。
主な列:
- event_date: セッションが発生した日付。
- session_id: セッションを一意に識別するためのID。
- page_location: ページのURL。
- page_view_rank: セッション内のページビューの順序（ページビューの最初から最後までを特定するため）。
目的: ユーザーのセッションごとのページビューの順序とタイムスタンプを収集する。

3. session_paths サブクエリ

session_data を基に、各セッションの最初のページ (start_page) と最後のページ (end_page)、およびセッション全体のページパス (page_path_array) を集計します。
セッションの開始時間 (session_start) と終了時間 (session_end) を計算し、セッション内でのページビューの総数 (page_view_count) もカウントします。
目的: 各セッションにおけるユーザーの行動パスを集計し、セッション時間やページビューの数を計算する。

4. entry_complete サブクエリ

conversion イベントを基にして、セッションごとのコンバージョン数をカウントします。セッションID (session_id) とイベント日 (event_date) を使用して集計します。
目的: コンバージョンイベントが発生したセッションとその回数を取得する。

5. 最終結果の集計

session_paths テーブルを基に、セッションごとの行動パスやページビューの情報を表示します。また、entry_complete テーブルとの結合により、そのセッションで発生したコンバージョン数 (conversion_count) も追加されます。
ページパスに含まれるドメイン名を domain_to_remove テーブルから取得して削除します。
主な列:
- start_page: セッション内で最初に訪れたページ（ドメイン名を除去）。
- end_page: セッション内で最後に訪れたページ（ドメイン名を除去）。
- page_view_count: セッション内のページビューの総数。
- session_duration: セッションの持続時間（秒単位）。
- conversion_count: そのセッションで発生したコンバージョン数。
- page_path_1, page_path_2, ...: セッション内で訪れた各ページのパス（最大30ページ分、ドメイン名を除去）。
目的: 各セッションの詳細な行動パスやコンバージョン数を分析し、訪問したページのドメイン名を削除した状態で結果を表示する。

WITH domain_to_remove AS (
  SELECT 'https://www.example.com' AS domain  -- 非表示にするドメイン名を入力する箇所。例: 'https://www.example.com'
),
session_data AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
    CONCAT(CAST(user_pseudo_id AS STRING), "_", CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS landing_source,  -- 参照元を取得
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS landing_medium,  -- メディアを取得
    event_timestamp,
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, CONCAT(CAST(user_pseudo_id AS STRING), "_", CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) ORDER BY event_timestamp) AS page_view_rank
  FROM
    `プロジェクト名.データセット名.events_*`  -- GA4のイベントデータセット名を記入。例: `your-project.analytics_your-dataset.events_*`
  WHERE
    _table_suffix BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- クエリする期間のテーブルサフィックスを設定する箇所。YYYYMMDD形式、開始日-終了日
    AND event_name = 'page_view'  -- 分析対象のイベント名。基本的に'page_view'
),
session_paths AS (
  SELECT
    DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS session_date,
    session_id,
    ARRAY_AGG(page_location ORDER BY page_view_rank) AS page_path_array,
    ARRAY_AGG(page_location ORDER BY page_view_rank LIMIT 1)[OFFSET(0)] AS start_page,
    ARRAY_AGG(page_location ORDER BY page_view_rank DESC LIMIT 1)[OFFSET(0)] AS end_page,
    MIN(event_timestamp) AS session_start,
    MAX(event_timestamp) AS session_end,
    COUNT(*) AS page_view_count,
    ANY_VALUE(landing_source) AS landing_source, 
    ANY_VALUE(landing_medium) AS landing_medium  
  FROM
    session_data
  GROUP BY
    session_id
),
entry_complete AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
    CONCAT(CAST(user_pseudo_id AS STRING), "_", CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_id,
    COUNT(event_name) AS conversion_count
  FROM
    `プロジェクト名.データセット名.events_*`  -- GA4のデータセット名を記入。例: `your-project.analytics_your-dataset.events_*`
  WHERE
    _table_suffix BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- コンバージョンイベントが発生した期間のテーブルサフィックスを設定、開始日-終了日
    AND event_name = 'コンバージョンイベント名'  -- コンバージョンイベントの名前を指定する箇所。例: 'purchase'
  GROUP BY
    event_date, session_id
)
SELECT
  sp.session_date AS event_date,
  sp.session_id,
  sp.landing_source,  -- 参照元
  sp.landing_medium,  -- メディア
  REPLACE(sp.start_page, d.domain, '') AS start_page,
  REPLACE(sp.end_page, d.domain, '') AS end_page,
  sp.page_view_count,
  TIMESTAMP_DIFF(TIMESTAMP_MICROS(sp.session_end), TIMESTAMP_MICROS(sp.session_start), SECOND) AS session_duration,
  COALESCE(ec.conversion_count, 0) AS conversion_count,
  REPLACE(sp.page_path_array[OFFSET(0)], d.domain, '') AS page_path_1,
  REPLACE(sp.page_path_array[SAFE_OFFSET(1)], d.domain, '') AS page_path_2,
  REPLACE(sp.page_path_array[SAFE_OFFSET(2)], d.domain, '') AS page_path_3,
  REPLACE(sp.page_path_array[SAFE_OFFSET(3)], d.domain, '') AS page_path_4,
  REPLACE(sp.page_path_array[SAFE_OFFSET(4)], d.domain, '') AS page_path_5,
  REPLACE(sp.page_path_array[SAFE_OFFSET(5)], d.domain, '') AS page_path_6,
  REPLACE(sp.page_path_array[SAFE_OFFSET(6)], d.domain, '') AS page_path_7,
  REPLACE(sp.page_path_array[SAFE_OFFSET(7)], d.domain, '') AS page_path_8,
  REPLACE(sp.page_path_array[SAFE_OFFSET(8)], d.domain, '') AS page_path_9,
  REPLACE(sp.page_path_array[SAFE_OFFSET(9)], d.domain, '') AS page_path_10,
  REPLACE(sp.page_path_array[SAFE_OFFSET(10)], d.domain, '') AS page_path_11,
  REPLACE(sp.page_path_array[SAFE_OFFSET(11)], d.domain, '') AS page_path_12,
  REPLACE(sp.page_path_array[SAFE_OFFSET(12)], d.domain, '') AS page_path_13,
  REPLACE(sp.page_path_array[SAFE_OFFSET(13)], d.domain, '') AS page_path_14,
  REPLACE(sp.page_path_array[SAFE_OFFSET(14)], d.domain, '') AS page_path_15,
  REPLACE(sp.page_path_array[SAFE_OFFSET(15)], d.domain, '') AS page_path_16,
  REPLACE(sp.page_path_array[SAFE_OFFSET(16)], d.domain, '') AS page_path_17,
  REPLACE(sp.page_path_array[SAFE_OFFSET(17)], d.domain, '') AS page_path_18,
  REPLACE(sp.page_path_array[SAFE_OFFSET(18)], d.domain, '') AS page_path_19,
  REPLACE(sp.page_path_array[SAFE_OFFSET(19)], d.domain, '') AS page_path_20,
  REPLACE(sp.page_path_array[SAFE_OFFSET(20)], d.domain, '') AS page_path_21,
  REPLACE(sp.page_path_array[SAFE_OFFSET(21)], d.domain, '') AS page_path_22,
  REPLACE(sp.page_path_array[SAFE_OFFSET(22)], d.domain, '') AS page_path_23,
  REPLACE(sp.page_path_array[SAFE_OFFSET(23)], d.domain, '') AS page_path_24,
  REPLACE(sp.page_path_array[SAFE_OFFSET(24)], d.domain, '') AS page_path_25,
  REPLACE(sp.page_path_array[SAFE_OFFSET(25)], d.domain, '') AS page_path_26,
  REPLACE(sp.page_path_array[SAFE_OFFSET(26)], d.domain, '') AS page_path_27,
  REPLACE(sp.page_path_array[SAFE_OFFSET(27)], d.domain, '') AS page_path_28,
  REPLACE(sp.page_path_array[SAFE_OFFSET(28)], d.domain, '') AS page_path_29,
  REPLACE(sp.page_path_array[SAFE_OFFSET(29)], d.domain, '') AS page_path_30
FROM
  session_paths sp
LEFT JOIN
  entry_complete ec
ON
  sp.session_id = ec.session_id,
domain_to_remove d
ORDER BY
  sp.session_date, sp.session_id;

この記事を書いた人

セイさん