【GA4×BigQuery】CVまでのセッション履歴を“回数×日時×チャネル”で可視化するSQL

導入

広告・SEO・SNSなど複数チャネルを横断する集客では、「ユーザーがCVするまでに何回サイト訪問（セッション）したのか」「それぞれの訪問がいつ・どのチャネル（source/medium/campaign）だったのか」を定量的に把握することが、投資配分やクリエイティブ改善の前提になります。

GA4の標準UIでも“新規/リピーター”や“経路探索”は見られますが、ユーザー単位でCV前セッションのみを抽出し、1回目・2回目…と連番を振って日時とチャネルを横並びに見ることは難しく、施策ごとの“接触設計”を再現的に検証しづらいのが実情です。

本SQLは、まず対象のCVイベントをユーザー×セッション粒度で抽出し、同一ユーザーのすべてのsession_startを時系列で並べて連番化します。

その後、“そのユーザーの最初のCVが起きるまで”のセッションだけを残し、最大30回分までの「訪問日（YYYY-MM-DD）」「訪問時刻（HH:MM:SS）」「source/medium/campaign」「CVセッションか否か」を1行ずつ出力。

これにより、熟考型（訪問回数が多い）か即決型（少ない）か、チャネルの役割（初回はSEO、後半は指名リスティング等）、CV直前のキャンペーンが一望でき、リマーケティングのフリークエンシ設計やラストタッチ偏重の是正など、実務的な意思決定に直結します。

実行結果イメージ

• このユーザーは3回目の訪問（google / cpc / brand_kw）でCV。

• 初回はSEO、2回目はメルマガ、最終訪問は指名リスティングという役割分担。

• 同様のパターンが多数なら、中盤の再訪を促すCRMや、ラストタッチの指名キーワード予算の妥当性を検証可能。

背景・利用シーン

• • フリークエンシ設計の見直し：CVまでの平均訪問回数が多い商品群は、接触回数を前提にした広告設計（シーケンス配信、日数間隔、クリエイティブ段階設計）が有効。

  • チャネルの役割分担：初回はorganic、CV直前はcpcが多い等の傾向が見えれば、**上流（認知）と下流（刈り取り）**でKPIを分け、評価の“すれ違い”を解消。

  • キャンペーン最適化：CVセッションに現れるcampaignを抽出して、貢献が高いクリエイティブ/キーワードを特定。

  • 即決/熟考セグメント：session_orderの最大値でユーザーを二層化し、LPやオファーの出し分けに活用。

抽出結果からできる議論

1. 広告「CVまで平均3.4回の訪問。2回目がemailの比率が高いね。」
   CRM「メールの2通目に比較表リンクを足して、3回目の指名CPCへ橋渡しを強化しよう。」

2. SEO「初回流入の7割がorganic。なのにCVセッションではcpcが優位。」
   アナリスト「上流を担うSEOのKPIは“初回流入数”で評価、CVはラストタッチ偏重にならないよう補正を。」

3. 経営「広告費を減らせるポイントは？」
   マーケ「訪問2回目でのCRM（LINE/メール）転換が効いているので、再来訪を早める施策に配分します。」

4. UX「3回目でCVする人は夜帯アクセスが多い。」
   広告「ナイトタイムの入札係数を引き上げ、同時間帯に“比較→申込”の導線強化を。」

5. PM「is_cv_session=1のキャンペーン群は何？」
   分析「brand_kwとretargeting_saleが中心。予算をそちらへ寄せる判断材料に。」

SQLコードと分解解説

-- ========================
-- 【このSQLでできること】
-- ・CVするまでのユーザーセッション履歴を取得
-- ・セッション回数を付与（最大30回目まで）
-- ・CV発生後のログは非表示
-- ・セッション日時（YYYY-MM-DD、HH:MM:SS）も出力
-- ========================

WITH
-- ① CVイベントを抽出
cv_events AS (
  SELECT
    user_pseudo_id,
    (SELECT ep.value.int_value 
     FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') AS ga_session_id,
    MIN(event_timestamp) AS cv_timestamp  -- CV発生時刻（マイクロ秒）
  FROM
    `★データセット名をここに入力★.events_202504*`  -- ★対象テーブルを指定
  WHERE
    event_name = '★CVイベント名をここに入力★'        -- ★CVイベント名を指定（例：purchase）
  GROUP BY
    user_pseudo_id, ga_session_id
),

-- ② CVユーザーのセッション履歴取得
user_sessions AS (
  SELECT
    e.user_pseudo_id,
    (SELECT ep.value.int_value 
     FROM UNNEST(e.event_params) ep WHERE ep.key = 'ga_session_id') AS ga_session_id,
    e.event_timestamp,
    e.traffic_source.source   AS source,
    e.traffic_source.medium   AS medium,
    e.traffic_source.name     AS campaign
  FROM
    `★データセット名をここに入力★.events_202504*` e
  WHERE
    e.event_name = 'session_start'   -- セッション開始イベント
    AND e.user_pseudo_id IN (SELECT DISTINCT user_pseudo_id FROM cv_events)
),

-- ③ セッションに順番付与＆CV判定
cv_sessions_with_rank AS (
  SELECT
    us.*,
    CASE WHEN cv.ga_session_id IS NOT NULL THEN 1 ELSE 0 END AS is_cv_session,
    cv.cv_timestamp,
    ROW_NUMBER() OVER (
      PARTITION BY us.user_pseudo_id ORDER BY us.event_timestamp
    ) AS session_order
  FROM user_sessions us
  LEFT JOIN cv_events cv
    ON us.user_pseudo_id = cv.user_pseudo_id
   AND us.ga_session_id  = cv.ga_session_id
),

-- ④ 1ユーザーごとの最初のCVセッション時刻
first_cv_per_user AS (
  SELECT
    user_pseudo_id,
    MIN(event_timestamp) AS first_cv_event_timestamp
  FROM cv_sessions_with_rank
  WHERE is_cv_session = 1
  GROUP BY user_pseudo_id
)

-- ⑤ 最終出力
SELECT
  csr.user_pseudo_id,
  csr.session_order,
  FORMAT_TIMESTAMP('%F', TIMESTAMP_MICROS(csr.event_timestamp)) AS session_date, -- 日付
  FORMAT_TIMESTAMP('%T', TIMESTAMP_MICROS(csr.event_timestamp)) AS session_time, -- 時刻
  csr.source,
  csr.medium,
  csr.campaign,
  csr.is_cv_session
FROM cv_sessions_with_rank csr
LEFT JOIN first_cv_per_user fcv
  ON csr.user_pseudo_id = fcv.user_pseudo_id
WHERE
  (fcv.first_cv_event_timestamp IS NULL OR csr.event_timestamp <= fcv.first_cv_event_timestamp) -- CV以降は除外
  AND csr.session_order <= 30  -- ★最大30回目まで表示
ORDER BY
  csr.user_pseudo_id, csr.session_order;

確認すべきポイント

1. CVの定義
   event_name = '★CVイベント名★' を必ず実環境のイベント名に置換。複数CVを対象にする場合は IN (…)。

2. タイムゾーン
   TIMESTAMP_MICROS はUTC基準。Japan基準で「日付」を切りたいなら FORMAT_TIMESTAMP('%F', TIMESTAMP_MICROS(...), 'Asia/Tokyo') を使用。

3. ユーザー識別の限界
   user_pseudo_id はデバイス／同意設定の影響を受け、真のユーザー横断は保証されない点を共有。

4. チャネル情報のスコープ
   ここでは session_start 行の traffic_source.* を参照しているため、セッション起点チャネルとして概ね妥当。UTM実装の揺れがある場合は整備・補正を。

5. CV以降の除外ロジック
   first_cv_event_timestamp 以降を除外。同日に複数CVがあり得る設計では、対象CVの粒度（最初/最後/特定タイプ）を明確化。

6. 上限30回
   長期検討商材で30回を超えるケースは切り捨て。必要に応じて上限を拡張。

7. パフォーマンス
   フィルタで対象月のテーブルに絞っており比較的軽量。期間跨ぎはワイルドカード範囲を広げるか、日付パーティションを活用。

まとめ

• • ユーザー単位×時系列で、CVまでの訪問回数とチャネルを再現。

  • “初回はSEO→メール→指名CPCでCV”のような役割分担を可視化でき、配分・クリエイティブ・CRMの打ち手に直結。

  • 即決/熟考の二層セグメントを作り、LPやオファーの出し分けへ展開可能。