【GA4×BigQuery】最新CV当日までのユーザー行動を最大50PV横持ちで抜き出すSQL

2025年8月31日 2025年9月10日

セイさん

Contents

1 導入（このSQLで解決できる課題）
2 実行結果イメージ（抜粋）
3 背景・利用シーン
4 抽出結果からできる議論
5 SQLコード
6 確認すべきポイント
7 まとめ（使いどころ）

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

広告やSEOを運用していると、「コンバージョン（CV）に至るまでに、ユーザーはどんなページをどの順序で辿ったのか」を、個票ベースで確認したい場面が頻繁にあります。

探索レポートや標準レポートでは、“経路”がまとめ表としては見えても、特定ユーザーの直前挙動を横串に時系列で確認するのは難しく、ダッシュボードも一般に“縦持ち”のテーブルが中心のため、CV前後の微細な遷移（FAQを経由したのか、価格表に戻ったのか等）を素早く読み解くのに向いていません。

本SQLは、GA4のevents_* から page_view のタイムスタンプとURLを抽出→最新CV当日までの履歴に限定→古い順で採番→最大50件を列（past_session_1〜50）として横展開 します。

さらに CV時のランディングURL（cv_landing_page）と traffic_source（name/medium） を併記するため、どのチャネル・キャンペーンのCVだったかを踏まえて直前経路を吟味できます。

実務では、申込フォームの直前に踏まれがちなページ特定、即決CVと熟考CVの違い（PV数・経路の深さ）比較、広告クリエイティブごとに“よく通る入口ページ”把握などに直結。改善会議で「どのページを直すか」を合意形成しやすくなるのが最大の効用です。

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

past_session_1〜n が古い順に並ぶページビュー（このSQLでは「セッション」という名だが実体は page_view ベース）。
conversion_* はCV当日の着地とトラフィック情報。**「CV前の閲覧経路」×「CV時の流入」**を一枚で確認できる。

背景・利用シーン

- フォーム改善の着手点特定：直前に /faq や規約ページが頻出→LP/フォーム内に要点を前倒し配置。
- 記事→製品の橋渡し検証：ブログ導線（関連記事/比較表）が適切か、CVセッションでの通過率で把握。
- 広告×LPの整合性評価：キャンペーン名（traffic_source.name）ごとに、入っている入口が設計通りか検証。
- 即決・熟考の二層分析：PV本数や経路深さでセグメントし、CTA/コピーを最適化。

抽出結果からできる議論

広告「cpcのCVは price ページを挟まず直行が多い。」
UX「LPで価格明示を強めた効果っぽい。AB結果の裏取りとして有効ですね。」
SEO「organicのCVは /faq を経由しがち。」
編集「FAQの要点をLPに内包し、FAQリンクはモーダル表示で離脱を抑えよう。」
PM「熟考型（PV10本以上）でも compare ページを必ず通る。」
プロダクト「比較表を製品詳細の上部に常設して、短縮導線も用意したい。」
CS「規約ページがCV直前に頻出、読み込みに時間がかかっているかも。」
エンジニア「非同期化＆キャッシュ設定を見直します。」
経営「即決型の割合は週で上昇してる？」
アナリスト「今週の広告コピー改善が効いた可能性。来週も同コンセプトを拡張します。」

SQLコード

-- 目的：
-- ・GA4生データから「CV当日までに発生した page_view」を、古い順で最大50件まで横持ち出力
-- ・CV時のランディングURLと traffic_source(name/medium) も同時に出す

WITH user_sessions AS (
  -- 1) page_view のタイムスタンプとURLを取得（※実体はセッションではなく "PV"）
  SELECT
    user_pseudo_id AS user_id,
    TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64)) AS event_datetime,   -- 例：UTC
    DATE(TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64))) AS visit_date, -- 日付
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page,
    traffic_source.name   AS traffic_source_name,   -- 参照名（utm_campaign 等と紐付くことが多い）
    traffic_source.medium AS traffic_source_medium  -- 媒体（utm_medium）
  FROM
    `~~~~~~~~~~~.events_202502*`  -- GA4のエクスポートテーブル
  WHERE
    event_name = 'page_view'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NOT NULL
),

cv_users AS (
  -- 2) CVイベントの発生ユーザーとCV日を抽出（同月）
  SELECT DISTINCT
    user_pseudo_id AS user_id,
    DATE(TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64))) AS cv_date,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS cv_landing_page,
    traffic_source.name   AS traffic_source_name,
    traffic_source.medium AS traffic_source_medium
  FROM
    `~~~~~~~~~~~.events_202502*`
  WHERE
    event_name = 'コンバージョン'  -- ★集計対象のCVイベント名に置換
),

latest_cv_users AS (
  -- 3) ユーザーごとに最新のCV日を残す
  SELECT user_id, MAX(cv_date) AS latest_cv_date
  FROM cv_users
  GROUP BY user_id
),

filtered_cv_users AS (
  -- 4) 最新CV日の詳細行だけに絞り込む
  SELECT
    cu.user_id,
    cu.cv_date,
    cu.cv_landing_page,
    cu.traffic_source_name,
    cu.traffic_source_medium
  FROM cv_users cu
  JOIN latest_cv_users lcu
    ON cu.user_id = lcu.user_id AND cu.cv_date = lcu.latest_cv_date
),

user_journey AS (
  -- 5) CV当日までの page_view を結合して時系列表に（同一ユーザー、CV日以前）
  SELECT DISTINCT
    us.user_id,
    us.event_datetime,
    us.visit_date,
    us.landing_page,
    cu.cv_date,
    cu.cv_landing_page,
    cu.traffic_source_name  AS conversion_traffic_source_name,
    cu.traffic_source_medium AS conversion_traffic_source_medium
  FROM user_sessions us
  JOIN filtered_cv_users cu
    ON TRIM(us.user_id) = TRIM(cu.user_id)
  WHERE us.visit_date <= cu.cv_date
),

ranked_sessions AS (
  -- 6) ユーザー×CV日単位で古い順に連番（1,2,3…）
  SELECT
    user_id,
    event_datetime,
    visit_date,
    landing_page,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, cv_date
      ORDER BY event_datetime
    ) AS session_number,                -- ※実際は「PV番号」
    cv_date,
    cv_landing_page,
    conversion_traffic_source_name,
    conversion_traffic_source_medium
  FROM user_journey
  WHERE event_datetime IS NOT NULL
)

-- 7) 横持ち（過去1～50PVを列化）
SELECT
  user_id,
  cv_date AS conversion_date,
  cv_landing_page AS conversion_landing_page,
  conversion_traffic_source_name,
  conversion_traffic_source_medium,

  -- 1～10（以降は同様に拡張）
  MAX(IF(session_number = 1,  visit_date,     NULL)) AS past_session_1_date,
  MAX(IF(session_number = 1,  event_datetime, NULL)) AS past_session_1_time,
  MAX(IF(session_number = 1,  landing_page,   NULL)) AS past_session_1_landing_page,

  MAX(IF(session_number = 2,  visit_date,     NULL)) AS past_session_2_date,
  MAX(IF(session_number = 2,  event_datetime, NULL)) AS past_session_2_time,
  MAX(IF(session_number = 2,  landing_page,   NULL)) AS past_session_2_landing_page,

  MAX(IF(session_number = 3,  visit_date,     NULL)) AS past_session_3_date,
  MAX(IF(session_number = 3,  event_datetime, NULL)) AS past_session_3_time,
  MAX(IF(session_number = 3,  landing_page,   NULL)) AS past_session_3_landing_page,

  MAX(IF(session_number = 4,  visit_date,     NULL)) AS past_session_4_date,
  MAX(IF(session_number = 4,  event_datetime, NULL)) AS past_session_4_time,
  MAX(IF(session_number = 4,  landing_page,   NULL)) AS past_session_4_landing_page,

  MAX(IF(session_number = 5,  visit_date,     NULL)) AS past_session_5_date,
  MAX(IF(session_number = 5,  event_datetime, NULL)) AS past_session_5_time,
  MAX(IF(session_number = 5,  landing_page,   NULL)) AS past_session_5_landing_page,

  -- …中略：past_session_6〜50 を同様に列追加…

  MAX(IF(session_number = 50, visit_date,     NULL)) AS past_session_50_date,
  MAX(IF(session_number = 50, event_datetime, NULL)) AS past_session_50_time,
  MAX(IF(session_number = 50, landing_page,   NULL)) AS past_session_50_landing_page

FROM ranked_sessions
WHERE session_number <= 50
GROUP BY
  user_id, cv_date, cv_landing_page,
  conversion_traffic_source_name, conversion_traffic_source_medium
ORDER BY
  cv_date ASC;

確認すべきポイント

“当日まで”の定義（重要）
現状は WHERE us.visit_date <= cu.cv_date のため、CV当日の“CVより後のPV”も含まれる可能性があります。厳密に 「CV時刻以前まで」 にしたい場合は、CVの 日時（cv_datetime） を取得し、
- user_journey で us.event_datetime <= cv_datetime に変更してください。
  例：cv_users に TIMESTAMP_MICROS(event_timestamp) の最大値を保持し、JOINして比較。
タイムゾーン
本SQLは TIMESTAMP_SECONDS(event_timestamp/1e6) を使用（UTC想定）。**レポート基準TZ（例：Asia/Tokyo）**に合わせる場合、AT TIME ZONE ではなく BigQueryでは DATETIME(TIMESTAMP, "Asia/Tokyo")/TIMESTAMP変換や DATE(TIMESTAMP, "Asia/Tokyo") を使ってください。
命名の整合性
CTE user_sessions 内の landing_page は実体は「そのPVの page_location」です。紛らわしい場合は page_url 等に名称変更推奨。
traffic_source のスコープ
ここではイベント行の traffic_source.* を参照。セッション起点での流入情報を安定取得したい場合は、session_start の行から FIRST_VALUE(... IGNORE NULLS) でセッションスコープに揃える実装が堅牢です。
CVイベントの指定
event_name = 'コンバージョン' を対象イベント名（例：purchase、generate_lead）に置換。複数CVをまとめたい場合は IN (...) で列挙、あるいは CVマッピング表をJOINすると管理が楽です。
列数（最大50PV）と可搬性
横持ちは可読性が高い反面、スキーマが大きくなります。配列のまま出して可視化側で展開、または Nグラム化して頻出経路ランキングへ派生する選択肢も検討ください。
パフォーマンス
期間が長いと events_* は巨大化。まずは 対象月だけで検証→月跨ぎ対応、必要なら **page_view 抽出の中間テーブル（日次）**を用意すると安定します。

まとめ（使いどころ）

- 最新CV当日までのユーザー行動を、古い順に最大50PV横持ちで一望化。
- CV時のチャネル（name/medium）とURL を同梱し、直前経路×流入のクロスで原因仮説を素早く立てられる。
- フォーム改善・LP設計・記事導線・広告整合性の議論を、個票の実データで具体化できる。

この記事を書いた人

セイさん

これまで広告代理店で、大手から中小企業まで幅広いクライアントを支援し、広告運用・データ分析・戦略立案に携わってきました。現在はマーケター／コンサルタントとして活動し、「数字では測れない顧客心理をどう捉え、事業の成長につなげるか」をテーマに、企業のマーケティング支援を行っています。【専門領域】データ分析（GA4、Looker Studio）広告運用（リスティング・SNS・ディスプレイ）採用マーケティングランディングページ改善【主な実績】 FC美容業界：採用応募数を前年比 131%改善管理会社：LP改善＋MA導入で資料請求数を前年比 173%達成不動産管理ツール：CPAを 20,000円 → 9,000円に改善（4ヶ月） ECサイト：売上前年比 156%達成自動車メーカー：新車訴求でCV数 173%、CPA 214% 達成その他、美容師採用、クレジットカード広告、歯科医院・注文住宅PRなど多数