分析

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

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

導入（この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ユーザーの過去ページ履歴（URL + タイトル）を横持ち出力
  - 対象：page_view（URL/Title）→ 最新CV日までの導線
  - 出力：CV情報 + 過去1〜50回の閲覧履歴（date/time/url/title）
  - 構造：CTE → ROW_NUMBER → MAX(IF(session_number=...)) のまま


  ★★入力★★ の箇所だけ案件ごとに書き換え
========================================================= */


WITH user_sessions AS (
  SELECT
    user_pseudo_id AS user_id,


    /* ★★入力★★ タイムゾーンが必要な案件はここを調整（基本はこのままでOK）
       - event_timestamp は UTC 基準のマイクロ秒
       - TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64)) は UTC時刻になる点に注意
       - JST等に揃えたい場合は TIMESTAMP_MICROS(event_timestamp) を使い、DATETIME(...,"Asia/Tokyo") へ変更（※構造変更になるので、ここでは触らない）
    */
    TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64)) AS event_datetime,
    DATE(TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64))) AS visit_date,


    -- URL
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page_url,
    -- Title
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS landing_page_title,


    traffic_source.name AS traffic_source_name,
    traffic_source.medium AS traffic_source_medium
  FROM
    /* ★★入力★★ ① 対象テーブル（GA4 BigQuery Export）
       例：`project_id.analytics_XXXXXXXX.events_202512*`
       例：`project_id.analytics_XXXXXXXX.events_*`（期間を広くするなら）
    */
    `idemitsu-447809.analytics_328712105.events_202512*`
  WHERE
    event_name = 'page_view'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NOT NULL
),


cv_users AS (
  SELECT DISTINCT
    user_pseudo_id AS user_id,
    DATE(TIMESTAMP_SECONDS(CAST(event_timestamp / 1000000 AS INT64))) AS cv_date,


    -- URL
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS cv_landing_page_url,
    -- Title（CVイベント側に入っていない場合はNULLになり得ます）
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS cv_landing_page_title,


    traffic_source.name AS traffic_source_name,
    traffic_source.medium AS traffic_source_medium
  FROM
    /* ★★入力★★ ① 対象テーブル（上と同じに揃える） */
    `・・・・・・・.analytics_328712105.events_202512*`
  WHERE
    /* ★★入力★★ ② CVイベント名（GA4上のイベント名）
       例：'ASPクリック' / 'purchase' など
    */
    event_name = 'conversion'
),


latest_cv_users AS (
  SELECT
    user_id,
    MAX(cv_date) AS latest_cv_date
  FROM
    cv_users
  GROUP BY
    user_id
),


filtered_cv_users AS (
  SELECT
    cu.user_id,
    cu.cv_date,
    cu.cv_landing_page_url,
    cu.cv_landing_page_title,
    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 (
  SELECT DISTINCT
    us.user_id,
    us.event_datetime,
    us.visit_date,


    us.landing_page_url,
    us.landing_page_title,


    cu.cv_date,
    cu.cv_landing_page_url,
    cu.cv_landing_page_title,


    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 (
  SELECT
    user_id,
    event_datetime,
    visit_date,
    landing_page_url,
    landing_page_title,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, cv_date
      ORDER BY event_datetime
    ) AS session_number,
    cv_date,
    cv_landing_page_url,
    cv_landing_page_title,
    conversion_traffic_source_name,
    conversion_traffic_source_medium
  FROM
    user_journey
  WHERE
    event_datetime IS NOT NULL
)


SELECT
  user_id,
  cv_date AS conversion_date,


  -- CV地点（URLは保持）
  cv_landing_page_url   AS conversion_landing_page_url,
  cv_landing_page_title AS conversion_landing_page_title,


  conversion_traffic_source_name,
  conversion_traffic_source_medium,


  -- past_session_1
  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_url, NULL))   AS past_session_1_url,
  MAX(IF(session_number = 1, landing_page_title, NULL)) AS past_session_1_title,


  -- past_session_2
  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_url, NULL))   AS past_session_2_url,
  MAX(IF(session_number = 2, landing_page_title, NULL)) AS past_session_2_title,


  -- past_session_3
  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_url, NULL))   AS past_session_3_url,
  MAX(IF(session_number = 3, landing_page_title, NULL)) AS past_session_3_title,


  -- past_session_4
  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_url, NULL))   AS past_session_4_url,
  MAX(IF(session_number = 4, landing_page_title, NULL)) AS past_session_4_title,


  -- past_session_5
  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_url, NULL))   AS past_session_5_url,
  MAX(IF(session_number = 5, landing_page_title, NULL)) AS past_session_5_title,


  -- past_session_6
  MAX(IF(session_number = 6, visit_date, NULL))         AS past_session_6_date,
  MAX(IF(session_number = 6, event_datetime, NULL))     AS past_session_6_time,
  MAX(IF(session_number = 6, landing_page_url, NULL))   AS past_session_6_url,
  MAX(IF(session_number = 6, landing_page_title, NULL)) AS past_session_6_title,


  -- past_session_7
  MAX(IF(session_number = 7, visit_date, NULL))         AS past_session_7_date,
  MAX(IF(session_number = 7, event_datetime, NULL))     AS past_session_7_time,
  MAX(IF(session_number = 7, landing_page_url, NULL))   AS past_session_7_url,
  MAX(IF(session_number = 7, landing_page_title, NULL)) AS past_session_7_title,


  -- past_session_8
  MAX(IF(session_number = 8, visit_date, NULL))         AS past_session_8_date,
  MAX(IF(session_number = 8, event_datetime, NULL))     AS past_session_8_time,
  MAX(IF(session_number = 8, landing_page_url, NULL))   AS past_session_8_url,
  MAX(IF(session_number = 8, landing_page_title, NULL)) AS past_session_8_title,


  -- past_session_9
  MAX(IF(session_number = 9, visit_date, NULL))         AS past_session_9_date,
  MAX(IF(session_number = 9, event_datetime, NULL))     AS past_session_9_time,
  MAX(IF(session_number = 9, landing_page_url, NULL))   AS past_session_9_url,
  MAX(IF(session_number = 9, landing_page_title, NULL)) AS past_session_9_title,


  -- past_session_10
  MAX(IF(session_number = 10, visit_date, NULL))         AS past_session_10_date,
  MAX(IF(session_number = 10, event_datetime, NULL))     AS past_session_10_time,
  MAX(IF(session_number = 10, landing_page_url, NULL))   AS past_session_10_url,
  MAX(IF(session_number = 10, landing_page_title, NULL)) AS past_session_10_title,


  -- past_session_11
  MAX(IF(session_number = 11, visit_date, NULL))         AS past_session_11_date,
  MAX(IF(session_number = 11, event_datetime, NULL))     AS past_session_11_time,
  MAX(IF(session_number = 11, landing_page_url, NULL))   AS past_session_11_url,
  MAX(IF(session_number = 11, landing_page_title, NULL)) AS past_session_11_title,


  -- past_session_12
  MAX(IF(session_number = 12, visit_date, NULL))         AS past_session_12_date,
  MAX(IF(session_number = 12, event_datetime, NULL))     AS past_session_12_time,
  MAX(IF(session_number = 12, landing_page_url, NULL))   AS past_session_12_url,
  MAX(IF(session_number = 12, landing_page_title, NULL)) AS past_session_12_title,


  -- past_session_13
  MAX(IF(session_number = 13, visit_date, NULL))         AS past_session_13_date,
  MAX(IF(session_number = 13, event_datetime, NULL))     AS past_session_13_time,
  MAX(IF(session_number = 13, landing_page_url, NULL))   AS past_session_13_url,
  MAX(IF(session_number = 13, landing_page_title, NULL)) AS past_session_13_title,


  -- past_session_14
  MAX(IF(session_number = 14, visit_date, NULL))         AS past_session_14_date,
  MAX(IF(session_number = 14, event_datetime, NULL))     AS past_session_14_time,
  MAX(IF(session_number = 14, landing_page_url, NULL))   AS past_session_14_url,
  MAX(IF(session_number = 14, landing_page_title, NULL)) AS past_session_14_title,


  -- past_session_15
  MAX(IF(session_number = 15, visit_date, NULL))         AS past_session_15_date,
  MAX(IF(session_number = 15, event_datetime, NULL))     AS past_session_15_time,
  MAX(IF(session_number = 15, landing_page_url, NULL))   AS past_session_15_url,
  MAX(IF(session_number = 15, landing_page_title, NULL)) AS past_session_15_title,


  -- past_session_16
  MAX(IF(session_number = 16, visit_date, NULL))         AS past_session_16_date,
  MAX(IF(session_number = 16, event_datetime, NULL))     AS past_session_16_time,
  MAX(IF(session_number = 16, landing_page_url, NULL))   AS past_session_16_url,
  MAX(IF(session_number = 16, landing_page_title, NULL)) AS past_session_16_title,


  -- past_session_17
  MAX(IF(session_number = 17, visit_date, NULL))         AS past_session_17_date,
  MAX(IF(session_number = 17, event_datetime, NULL))     AS past_session_17_time,
  MAX(IF(session_number = 17, landing_page_url, NULL))   AS past_session_17_url,
  MAX(IF(session_number = 17, landing_page_title, NULL)) AS past_session_17_title,


  -- past_session_18
  MAX(IF(session_number = 18, visit_date, NULL))         AS past_session_18_date,
  MAX(IF(session_number = 18, event_datetime, NULL))     AS past_session_18_time,
  MAX(IF(session_number = 18, landing_page_url, NULL))   AS past_session_18_url,
  MAX(IF(session_number = 18, landing_page_title, NULL)) AS past_session_18_title,


  -- past_session_19
  MAX(IF(session_number = 19, visit_date, NULL))         AS past_session_19_date,
  MAX(IF(session_number = 19, event_datetime, NULL))     AS past_session_19_time,
  MAX(IF(session_number = 19, landing_page_url, NULL))   AS past_session_19_url,
  MAX(IF(session_number = 19, landing_page_title, NULL)) AS past_session_19_title,


  -- past_session_20
  MAX(IF(session_number = 20, visit_date, NULL))         AS past_session_20_date,
  MAX(IF(session_number = 20, event_datetime, NULL))     AS past_session_20_time,
  MAX(IF(session_number = 20, landing_page_url, NULL))   AS past_session_20_url,
  MAX(IF(session_number = 20, landing_page_title, NULL)) AS past_session_20_title,


  -- past_session_21
  MAX(IF(session_number = 21, visit_date, NULL))         AS past_session_21_date,
  MAX(IF(session_number = 21, event_datetime, NULL))     AS past_session_21_time,
  MAX(IF(session_number = 21, landing_page_url, NULL))   AS past_session_21_url,
  MAX(IF(session_number = 21, landing_page_title, NULL)) AS past_session_21_title,


  -- past_session_22
  MAX(IF(session_number = 22, visit_date, NULL))         AS past_session_22_date,
  MAX(IF(session_number = 22, event_datetime, NULL))     AS past_session_22_time,
  MAX(IF(session_number = 22, landing_page_url, NULL))   AS past_session_22_url,
  MAX(IF(session_number = 22, landing_page_title, NULL)) AS past_session_22_title,


  -- past_session_23
  MAX(IF(session_number = 23, visit_date, NULL))         AS past_session_23_date,
  MAX(IF(session_number = 23, event_datetime, NULL))     AS past_session_23_time,
  MAX(IF(session_number = 23, landing_page_url, NULL))   AS past_session_23_url,
  MAX(IF(session_number = 23, landing_page_title, NULL)) AS past_session_23_title,


  -- past_session_24
  MAX(IF(session_number = 24, visit_date, NULL))         AS past_session_24_date,
  MAX(IF(session_number = 24, event_datetime, NULL))     AS past_session_24_time,
  MAX(IF(session_number = 24, landing_page_url, NULL))   AS past_session_24_url,
  MAX(IF(session_number = 24, landing_page_title, NULL)) AS past_session_24_title,


  -- past_session_25
  MAX(IF(session_number = 25, visit_date, NULL))         AS past_session_25_date,
  MAX(IF(session_number = 25, event_datetime, NULL))     AS past_session_25_time,
  MAX(IF(session_number = 25, landing_page_url, NULL))   AS past_session_25_url,
  MAX(IF(session_number = 25, landing_page_title, NULL)) AS past_session_25_title,


  -- past_session_26
  MAX(IF(session_number = 26, visit_date, NULL))         AS past_session_26_date,
  MAX(IF(session_number = 26, event_datetime, NULL))     AS past_session_26_time,
  MAX(IF(session_number = 26, landing_page_url, NULL))   AS past_session_26_url,
  MAX(IF(session_number = 26, landing_page_title, NULL)) AS past_session_26_title,


  -- past_session_27
  MAX(IF(session_number = 27, visit_date, NULL))         AS past_session_27_date,
  MAX(IF(session_number = 27, event_datetime, NULL))     AS past_session_27_time,
  MAX(IF(session_number = 27, landing_page_url, NULL))   AS past_session_27_url,
  MAX(IF(session_number = 27, landing_page_title, NULL)) AS past_session_27_title,


  -- past_session_28
  MAX(IF(session_number = 28, visit_date, NULL))         AS past_session_28_date,
  MAX(IF(session_number = 28, event_datetime, NULL))     AS past_session_28_time,
  MAX(IF(session_number = 28, landing_page_url, NULL))   AS past_session_28_url,
  MAX(IF(session_number = 28, landing_page_title, NULL)) AS past_session_28_title,


  -- past_session_29
  MAX(IF(session_number = 29, visit_date, NULL))         AS past_session_29_date,
  MAX(IF(session_number = 29, event_datetime, NULL))     AS past_session_29_time,
  MAX(IF(session_number = 29, landing_page_url, NULL))   AS past_session_29_url,
  MAX(IF(session_number = 29, landing_page_title, NULL)) AS past_session_29_title,


  -- past_session_30
  MAX(IF(session_number = 30, visit_date, NULL))         AS past_session_30_date,
  MAX(IF(session_number = 30, event_datetime, NULL))     AS past_session_30_time,
  MAX(IF(session_number = 30, landing_page_url, NULL))   AS past_session_30_url,
  MAX(IF(session_number = 30, landing_page_title, NULL)) AS past_session_30_title,


  -- past_session_31
  MAX(IF(session_number = 31, visit_date, NULL))         AS past_session_31_date,
  MAX(IF(session_number = 31, event_datetime, NULL))     AS past_session_31_time,
  MAX(IF(session_number = 31, landing_page_url, NULL))   AS past_session_31_url,
  MAX(IF(session_number = 31, landing_page_title, NULL)) AS past_session_31_title,


  -- past_session_32
  MAX(IF(session_number = 32, visit_date, NULL))         AS past_session_32_date,
  MAX(IF(session_number = 32, event_datetime, NULL))     AS past_session_32_time,
  MAX(IF(session_number = 32, landing_page_url, NULL))   AS past_session_32_url,
  MAX(IF(session_number = 32, landing_page_title, NULL)) AS past_session_32_title,


  -- past_session_33
  MAX(IF(session_number = 33, visit_date, NULL))         AS past_session_33_date,
  MAX(IF(session_number = 33, event_datetime, NULL))     AS past_session_33_time,
  MAX(IF(session_number = 33, landing_page_url, NULL))   AS past_session_33_url,
  MAX(IF(session_number = 33, landing_page_title, NULL)) AS past_session_33_title,


  -- past_session_34
  MAX(IF(session_number = 34, visit_date, NULL))         AS past_session_34_date,
  MAX(IF(session_number = 34, event_datetime, NULL))     AS past_session_34_time,
  MAX(IF(session_number = 34, landing_page_url, NULL))   AS past_session_34_url,
  MAX(IF(session_number = 34, landing_page_title, NULL)) AS past_session_34_title,


  -- past_session_35
  MAX(IF(session_number = 35, visit_date, NULL))         AS past_session_35_date,
  MAX(IF(session_number = 35, event_datetime, NULL))     AS past_session_35_time,
  MAX(IF(session_number = 35, landing_page_url, NULL))   AS past_session_35_url,
  MAX(IF(session_number = 35, landing_page_title, NULL)) AS past_session_35_title,


  -- past_session_36
  MAX(IF(session_number = 36, visit_date, NULL))         AS past_session_36_date,
  MAX(IF(session_number = 36, event_datetime, NULL))     AS past_session_36_time,
  MAX(IF(session_number = 36, landing_page_url, NULL))   AS past_session_36_url,
  MAX(IF(session_number = 36, landing_page_title, NULL)) AS past_session_36_title,


  -- past_session_37
  MAX(IF(session_number = 37, visit_date, NULL))         AS past_session_37_date,
  MAX(IF(session_number = 37, event_datetime, NULL))     AS past_session_37_time,
  MAX(IF(session_number = 37, landing_page_url, NULL))   AS past_session_37_url,
  MAX(IF(session_number = 37, landing_page_title, NULL)) AS past_session_37_title,


  -- past_session_38
  MAX(IF(session_number = 38, visit_date, NULL))         AS past_session_38_date,
  MAX(IF(session_number = 38, event_datetime, NULL))     AS past_session_38_time,
  MAX(IF(session_number = 38, landing_page_url, NULL))   AS past_session_38_url,
  MAX(IF(session_number = 38, landing_page_title, NULL)) AS past_session_38_title,


  -- past_session_39
  MAX(IF(session_number = 39, visit_date, NULL))         AS past_session_39_date,
  MAX(IF(session_number = 39, event_datetime, NULL))     AS past_session_39_time,
  MAX(IF(session_number = 39, landing_page_url, NULL))   AS past_session_39_url,
  MAX(IF(session_number = 39, landing_page_title, NULL)) AS past_session_39_title,


  -- past_session_40
  MAX(IF(session_number = 40, visit_date, NULL))         AS past_session_40_date,
  MAX(IF(session_number = 40, event_datetime, NULL))     AS past_session_40_time,
  MAX(IF(session_number = 40, landing_page_url, NULL))   AS past_session_40_url,
  MAX(IF(session_number = 40, landing_page_title, NULL)) AS past_session_40_title,


  -- past_session_41
  MAX(IF(session_number = 41, visit_date, NULL))         AS past_session_41_date,
  MAX(IF(session_number = 41, event_datetime, NULL))     AS past_session_41_time,
  MAX(IF(session_number = 41, landing_page_url, NULL))   AS past_session_41_url,
  MAX(IF(session_number = 41, landing_page_title, NULL)) AS past_session_41_title,


  -- past_session_42
  MAX(IF(session_number = 42, visit_date, NULL))         AS past_session_42_date,
  MAX(IF(session_number = 42, event_datetime, NULL))     AS past_session_42_time,
  MAX(IF(session_number = 42, landing_page_url, NULL))   AS past_session_42_url,
  MAX(IF(session_number = 42, landing_page_title, NULL)) AS past_session_42_title,


  -- past_session_43
  MAX(IF(session_number = 43, visit_date, NULL))         AS past_session_43_date,
  MAX(IF(session_number = 43, event_datetime, NULL))     AS past_session_43_time,
  MAX(IF(session_number = 43, landing_page_url, NULL))   AS past_session_43_url,
  MAX(IF(session_number = 43, landing_page_title, NULL)) AS past_session_43_title,


  -- past_session_44
  MAX(IF(session_number = 44, visit_date, NULL))         AS past_session_44_date,
  MAX(IF(session_number = 44, event_datetime, NULL))     AS past_session_44_time,
  MAX(IF(session_number = 44, landing_page_url, NULL))   AS past_session_44_url,
  MAX(IF(session_number = 44, landing_page_title, NULL)) AS past_session_44_title,


  -- past_session_45
  MAX(IF(session_number = 45, visit_date, NULL))         AS past_session_45_date,
  MAX(IF(session_number = 45, event_datetime, NULL))     AS past_session_45_time,
  MAX(IF(session_number = 45, landing_page_url, NULL))   AS past_session_45_url,
  MAX(IF(session_number = 45, landing_page_title, NULL)) AS past_session_45_title,


  -- past_session_46
  MAX(IF(session_number = 46, visit_date, NULL))         AS past_session_46_date,
  MAX(IF(session_number = 46, event_datetime, NULL))     AS past_session_46_time,
  MAX(IF(session_number = 46, landing_page_url, NULL))   AS past_session_46_url,
  MAX(IF(session_number = 46, landing_page_title, NULL)) AS past_session_46_title,


  -- past_session_47
  MAX(IF(session_number = 47, visit_date, NULL))         AS past_session_47_date,
  MAX(IF(session_number = 47, event_datetime, NULL))     AS past_session_47_time,
  MAX(IF(session_number = 47, landing_page_url, NULL))   AS past_session_47_url,
  MAX(IF(session_number = 47, landing_page_title, NULL)) AS past_session_47_title,


  -- past_session_48
  MAX(IF(session_number = 48, visit_date, NULL))         AS past_session_48_date,
  MAX(IF(session_number = 48, event_datetime, NULL))     AS past_session_48_time,
  MAX(IF(session_number = 48, landing_page_url, NULL))   AS past_session_48_url,
  MAX(IF(session_number = 48, landing_page_title, NULL)) AS past_session_48_title,


  -- past_session_49
  MAX(IF(session_number = 49, visit_date, NULL))         AS past_session_49_date,
  MAX(IF(session_number = 49, event_datetime, NULL))     AS past_session_49_time,
  MAX(IF(session_number = 49, landing_page_url, NULL))   AS past_session_49_url,
  MAX(IF(session_number = 49, landing_page_title, NULL)) AS past_session_49_title,


  -- past_session_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_url, NULL))   AS past_session_50_url,
  MAX(IF(session_number = 50, landing_page_title, NULL)) AS past_session_50_title


FROM
  ranked_sessions
WHERE
  /* ★★入力★★ ③ 何セッションまで出すか（上の列定義と合わせる）
     - 50列まで用意しているので、基本は <= 50 のままでOK
     - 10で十分なら <= 10 にしてOK（列は残っててもNULLになるだけ）
  */
  session_number <= 50
GROUP BY
  user_id, cv_date, cv_landing_page_url, cv_landing_page_title,
  conversion_traffic_source_name, conversion_traffic_source_medium
ORDER BY
  cv_date ASC;

 

確認すべきポイント

  1. “当日まで”の定義（重要）
    現状は 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して比較。

  2. タイムゾーン
    本SQLは TIMESTAMP_SECONDS(event_timestamp/1e6) を使用（UTC想定）。**レポート基準TZ（例：Asia/Tokyo）**に合わせる場合、AT TIME ZONE ではなく BigQueryでは DATETIME(TIMESTAMP, "Asia/Tokyo")/TIMESTAMP変換や DATE(TIMESTAMP, "Asia/Tokyo") を使ってください。

  3. 命名の整合性
    CTE user_sessions 内の landing_page は実体は「そのPVの page_location」です。紛らわしい場合は page_url 等に名称変更推奨。

  4. traffic_source のスコープ
    ここではイベント行の traffic_source.* を参照。セッション起点での流入情報を安定取得したい場合は、session_start の行から FIRST_VALUE(... IGNORE NULLS) でセッションスコープに揃える実装が堅牢です。

  5. CVイベントの指定
    event_name = 'コンバージョン' を対象イベント名（例：purchase、generate_lead）に置換。複数CVをまとめたい場合は IN (...) で列挙、あるいは CVマッピング表をJOINすると管理が楽です。

  6. 列数（最大50PV）と可搬性
    横持ちは可読性が高い反面、スキーマが大きくなります。配列のまま出して可視化側で展開、または Nグラム化して頻出経路ランキングへ派生する選択肢も検討ください。

  7. パフォーマンス
    期間が長いと 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など多数
最近書いた記事
なぜ今、広告代理店離れが進むのか—顧客が本当に求める「価値」の心理学
その他
なぜ今、広告代理店離れが進むのか—顧客が本当に求める「価値」の心理学
保存される広告の心理メカニズム──捨てられない情報設計の秘密
マーケティング施策
保存される広告の心理メカニズム──捨てられない情報設計の秘密
【CVRが7.2%→13.1%へ】成果が出ない理由は「数字の見すぎ」
その他
【CVRが7.2%→13.1%へ】成果が出ない理由は「数字の見すぎ」
意外性がマーケティングを変える──常識を覆す訴求が顧客を動かす心理メカニズム
マーケティング施策
意外性がマーケティングを変える──常識を覆す訴求が顧客を動かす心理メカニズム
おすすめの記事