マーケxコード

【GA4×BigQuery】「セッション経路×CV×滞在時間」を1テーブルに展開するSQL

【GA4×BigQuery】「セッション経路×CV×滞在時間」を1テーブルに展開するSQL

1. 導入（このSQLで解決できる課題）

Webサイトの改善では、

「ユーザーがセッション内でどの順序でどのページを辿ったか」

「どこで離脱し、CVは発生したか」

「滞在が短いのは導線か内容か」

を素早く把握することが要です。

ところが、GA4の標準UIでは詳細な“ページ単位の回遊順序”を一覧化するのが難しく、探索レポートで確認しても行単位での出力や集計の柔軟さに限界があります。

さらに、複数セッションを横並びに比較し、「CVが起きるパターン」と「離脱するパターン」を同一指標軸で比較するには、データを1行＝1セッションに整形し、開始/終了ページ、各ステップのURL、滞在時間、CV回数をまとめて持たせる必要があります。

本SQLは、GA4生データ（events_*）から擬似セッションID（user_pseudo_id＋ga_session_id）でセッションを復元し、page_view順に配列化→最大30ステップを列展開します。

併せて開始/終了ページ、ページ数、セッション継続時間（秒）、CVイベント回数を付与。

さらに、レポートを見やすくするため表示時のみドメインを除去（保存値は変えずに見やすさだけ担保）する工夫も備えています。

これにより、

（1）CVセッションに頻出する経路、

（2）離脱直前に閲覧されやすいページ、

（3）滞在が短いボトルネックを、スプレッドシート感覚で横並び比較できます。

実務では、ABテスト前後の経路差分・フォーム直前の摩擦点・記事導線の断絶箇所の特定など、「改善すべきページ」を特定する起点テーブルとして活躍します。

2. 実行結果イメージ（抜粋）

  • page_view_count が同程度でも、session_duration が短いセッションは“早い離脱or直行CV”の可能性。

  • conversion_count が1以上のセッションの start_page → page_path_* → end_page を横断して、CV前の共通経路を抽出。

3. 背景・利用シーン

  • フォーム離脱対策：/form/input → /form/confirm への遷移が少ないセッション群を抽出し、入力エラーやUXの摩擦を特定。

  • 記事導線の強化：/blog/* → /product/* へのブリッジに穴がないか、よく通るルートと通らないルートを比較。

  • LP設計の検証：/lp/* でのファーストビュー修正前後で、page_path_2〜3 の偏りが変化したかを確認。

  • チャネル別の回遊差：organic と cpc で、同じCVでも経路が違う（＝訴求の最適化対象が異なる）ことを見極める。

抽出結果からできる議論

  1. PM「CVセッションの7割が /blog/a → /product/abc → /form/input を通ってるね。」
    UX「/product/abc の関連リンクを増やして、他プロダクトでも同様の流れを再現しよう。」

  2. 広告「CPCは page_path_2 で /faq に流れがち。」
    編集「LP側にFAQ要点を前倒し表示して、離脱リンクを減らしてみます。」

  3. 分析「session_duration が短いCVも多い。直行CVが成立している可能性。」
    マーケ「“即決型”訴求クリエイティブは増やして良さそう。回遊前提の訴求は別設計に。」

  4. SEO「/blog/b からCVセッションは少数だが、page_view_count が多い。」
    編集「内部リンクの終着が弱い。/product/ へのCTA位置を再設計しよう。」

  5. CS「/terms が end_page に頻出、ここで離脱？」
    法務/UX「モーダル表示に切り替え、コンテキストを維持して申込動線を分断しないよう変更。」

4. SQLコード（コメント付き・分解）

-- ① 表示時にドメインを隠す（レポート見やすさ向上）
WITH domain_to_remove AS (
  SELECT 'https://www.example.com' AS domain  -- ★非表示にしたいドメインを入れる
),

-- ② セッション内の page_view を時系列で採番
session_data AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
    -- ★簡易セッションID（user_pseudo_id + ga_session_id）
    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,

    -- 完全URL（page_location）
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,

    -- セッション起点の参照元/媒体を event_params から取得（※環境により traffic_source.* を使う方が一般的）
    (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_*`
  WHERE
    _table_suffix BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- ★対象期間
    AND event_name = '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）
    ANY_VALUE(landing_source) AS landing_source,
    ANY_VALUE(landing_medium) AS landing_medium
  FROM session_data
  GROUP BY session_id
),

-- ④ CVイベントをセッション単位でカウント
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_*`
  WHERE
    _table_suffix BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'
    AND event_name = 'コンバージョンイベント名'  -- ★例：'purchase'
  GROUP BY event_date, session_id
)

-- ⑤ 最終出力：ドメイン除去＋30ステップ展開
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,

  -- 経路を30歩まで展開（不足分はNULL）
  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;

 

確認すべきポイント

  1. 期間指定
    _table_suffix BETWEEN 'YYYYMMDD' AND 'YYYYMMDD' を必ず実データのパーティションに合わせる。日跨ぎやタイムゾーン差異に注意。

  2. 参照元・媒体の取得元
    本SQLは event_params['source'/'medium'] を参照。**推奨は traffic_source.*（session_start 基準）**です。運用に合わせて切替えを検討。

  3. URLの正規化
    表示時の REPLACE(d.domain) のみで、クエリ除去や末尾スラッシュ統一は未実装。必要に応じて

    • REGEXP_EXTRACT(page_location, r'^https?://[^/]+([^\\?]+)')（クエリ除去）

    • 末尾スラッシュ統一（REGEXP_REPLACE）
      を追加してJOIN/比較の安定性を高める。

  4. CVイベントの重複
    COUNT(event_name) はセッション内の発火回数。CVを“1セッション1回”にしたい場合は COUNT(DISTINCT event_timestamp) ではなく、MIN(1)ロジック（例：MAX(1)に置換）などで二重カウント抑制を。

  5. 最大30ステップ制限
    深い経路のサイトでは30超のセッションが切り捨てられる。SAFE_OFFSETでNULLになるが、必要なら列数を増やすか、配列のまま出力＋可視化側（Looker Studio/BI）で展開。

  6. パフォーマンス

    • まず必要列のみSELECT（本SQLは絞り込み済み）。

    • 長期期間を扱う際は日付分割でUNIONやサンプル検証→本番の段階実行を。

    • events_* は巨大化しやすいので、事前にpage_viewだけを日次テーブルに落とす設計も効果的。

  7. セッション同定
    擬似ID user_pseudo_id + ga_session_id を使用。クロスデバイス/Consentの影響でセッションつなぎに限界がある点は前提知識として共有。

6. まとめ（使いどころ）

  • 1行＝1セッションに「開始/終了・経路（最大30）・PV数・滞在秒・CV回数」を統合し、改善候補の“目に見える化”を実現。

  • CVセッションの共通経路や離脱直前ページを横並びで比較でき、LP/フォーム/記事導線の具体的な改修ポイントを即提示可能。

  • 参照元・媒体の差で経路がどう変わるかを俯瞰でき、チャネル別の役割分担や訴求の最適化にも直結。

この記事を書いた人
セイさん
セイさん
これまで広告代理店で、大手から中小企業まで幅広いクライアントを支援し、広告運用・データ分析・戦略立案に携わってきました。現在はマーケター／コンサルタントとして活動し、「数字では測れない顧客心理をどう捉え、事業の成長につなげるか」 をテーマに、企業のマーケティング支援を行っています。 【専門領域】 データ分析（GA4、Looker Studio） 広告運用（リスティング・SNS・ディスプレイ） 採用マーケティング ランディングページ改善 【主な実績】 FC美容業界：採用応募数を前年比 131%改善 管理会社：LP改善＋MA導入で資料請求数を前年比 173%達成 不動産管理ツール：CPAを 20,000円 → 9,000円 に改善（4ヶ月） ECサイト：売上前年比 156%達成 自動車メーカー：新車訴求でCV数 173%、CPA 214% 達成 その他、美容師採用、クレジットカード広告、歯科医院・注文住宅PRなど多数
最近書いた記事
コラム
「セールスを不要にする」- ドラッカーと映画から学ぶマーケティングの本質
バズは不要！YouTubeで売上を作るダイレクトレスポンス型マーケティング
マーケx戦略
バズは不要！YouTubeで売上を作るダイレクトレスポンス型マーケティング
【GA4×BigQuery】特定ページからの“本当に進んでいるページ”を正確に把握
マーケxコード
【GA4×BigQuery】特定ページからの“本当に進んでいるページ”を正確に把握
【GA4×BigQuery】GA4アトリビューション（ファースト/ラスト/中間）をSQLで再現する
マーケxコード
【GA4×BigQuery】GA4アトリビューション（ファースト/ラスト/中間）をSQLで再現する
おすすめの記事