【BigQuery】特定ページからの“本当に進んでいるページ”を正確に把握

✅ 何をするクエリなのか（概要）

このクエリは、
特定のページを訪れたユーザーが「次にどのページへ遷移したか」を集計し、遷移率を算出するもの
です。

たとえば、以下のような「特定ページの効果測定」や「導線改善検討」に使えます。

✅ どんな時に使うのか（用途・活用シーン）

✅ 活用シーン例

1. キャンペーン・特集ページ分析

   • キャンペーンページや特集ページを見た人が、
     本当に次に商品ページや問い合わせページに遷移しているかを確認。

2. リストページや検索結果ページ分析

   • 車両リストや検索結果ページを見た人が、
     詳細ページにしっかり進んでいるかを確認。

3. 離脱防止・導線改善

   • 期待通りのページに進んでいない、
     離脱が多いページを特定して改善ポイントを探す。

4. 内部リンクやナビゲーション設計の評価

   • 設計通りの流れができているかをユーザー行動からチェック。

✅ クエリの仕組み（どう集計しているのか）

1. 特定のページ（起点ページ）を訪れたユーザーのログを取得

2. その次に閲覧されたページ（遷移先ページ）を特定

3. 起点ページを訪れた「全セッション数（母数）」を集計

4. **各遷移先へのセッション数（成果数）**を集計

5. **遷移率（成果数 ÷ 母数）**を計算

✅ 特に重要なポイント

• 離脱セッションも母数に含めることで、
  実際にどれだけのユーザーが次に進めているかの真の到達率を計算

• リロードや同じページ遷移は除外し、
  意味のあるページ遷移だけを集計

• 次ページが無いセッション（離脱）も全体母数として含めることで、
  「流入→離脱 or 遷移」の両方を評価可能

✅ 出力される結果の意味

trigger_pagetarget_pagetransitioned_sessionstotal_trigger_sessionstransition_rate起点ページURL次ページURLその遷移を行ったユニークセッション数起点ページを訪れた全セッション数（離脱含む）遷移率（%）

✅ まとめ（このクエリの価値）

• ユーザーがどこへ進んだかを定量的に評価できる

• ページの設計やコンテンツ誘導の良し悪しを数字で可視化できる

• 改善ポイントを発見しやすくなる

-- ① イベントデータを取得
WITH base_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
  FROM
    `YOUR_PROJECT_ID.YOUR_DATASET_ID.events_*`  -- ★ 1. ここをプロジェクト・データセットに変更
  WHERE
    event_name = 'page_view'
),

-- ② ページ遷移を取得（次ページ取得）
page_views AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    event_timestamp,
    page_location,
    LEAD(page_location) OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) AS next_page
  FROM
    base_events
),

-- ③ 起点ページを訪れた全セッション（母数取得、離脱含む）
trigger_sessions AS (
  SELECT DISTINCT
    REGEXP_REPLACE(page_location, r'^https?://[^/]+', '') AS trigger_page_path,
    user_pseudo_id,
    ga_session_id
  FROM
    page_views
  WHERE
    page_location LIKE '%YOUR_TRIGGER_PAGE%'  -- ★ 2. ここを分析対象URLの一部に変更（例: %/usedcar/carlist%）
),

-- ④ 有効な遷移（次ページあり・同一ページ遷移除外）
valid_transitions AS (
  SELECT DISTINCT
    REGEXP_REPLACE(page_location, r'^https?://[^/]+', '') AS trigger_page_path,
    user_pseudo_id,
    ga_session_id,
    REGEXP_REPLACE(next_page, r'^https?://[^/]+', '') AS target_page_path
  FROM
    page_views
  WHERE
    page_location LIKE '%YOUR_TRIGGER_PAGE%'  -- ★ 2. ここも同様に変更
    AND next_page IS NOT NULL
    AND page_location != next_page
),

-- ⑤ 遷移先ごとのセッション数集計
transition_counts AS (
  SELECT
    trigger_page_path,
    target_page_path,
    COUNT(DISTINCT ga_session_id) AS transitioned_sessions
  FROM
    valid_transitions
  GROUP BY
    trigger_page_path, target_page_path
)

-- ⑥ 最終出力（母数・遷移率計算）
SELECT
  t.trigger_page_path,
  t.target_page_path,
  t.transitioned_sessions,
  b.total_trigger_sessions,
  SAFE_DIVIDE(t.transitioned_sessions, b.total_trigger_sessions) AS transition_rate
FROM
  transition_counts t
JOIN (
  SELECT trigger_page_path, COUNT(DISTINCT ga_session_id) AS total_trigger_sessions
  FROM trigger_sessions
  GROUP BY trigger_page_path
) b
ON t.trigger_page_path = b.trigger_page_path
ORDER BY t.transitioned_sessions DESC
LIMIT 100

✅ テンプレートの使い方

手順必要な変更ポイント
1YOUR_PROJECT_ID.YOUR_DATASET_ID を 自分のプロジェクト＆データセット名 に変更
2'%YOUR_TRIGGER_PAGE%' を 対象URLの一部（パスやキーワード） に変更（例: '%/usedcar/carlist%'）

✅ 出力される項目の意味

列名説明
trigger_page_path起点ページ（ドメイン省略パス）
target_page_path次ページ（ドメイン省略パス）
transitioned_sessions起点ページから次ページへ遷移したユニークセッション数
total_trigger_sessions起点ページを訪れたユニークセッション数（離脱含む）
transition_rate起点ページを訪れたセッションに対して、その次ページに進んだセッション割合（transitioned_sessions ÷ total_trigger_sessions）