【Big query】アトリビュージョン計測用

① 目的

「CV（コンバージョン）するまでにユーザーが辿ったセッション（訪問）履歴を、順番に可視化する」

しかも、

• CVした後の行動は除外

• セッションごとに「何回目か」もわかる

• さらにセッションの「日付」と「時間」も出せる

つまり、
CVに至るまでのカスタマージャーニー（行動履歴）を精密に追える仕組みです。

② 具体的にやっている処理の流れ

ステップ内容1まず、指定したCVイベント（例：purchaseやlead）が発生したユーザーだけをリストアップする2そのユーザーの、全セッション開始（session_start）イベントを時系列順に取得する3各セッションに**何回目の訪問か（1回目、2回目…）**を付番する4CVが起きたセッション日時を特定し、それ以降のログは除外する5それぞれのセッションについて、**日付（YYYY-MM-DD）と時間（HH:MM:SS）**も見れる形にする

③ 出力されるデータイメージ

最終的に、こんな形でスプレットシートでデータが出ます。

✨このBigQueryでできること・活かせること

活用例説明カスタマージャーニー分析CVに至るまでにどんなチャネルやキャンペーンを経由したか把握できる流入施策の間接効果評価広告が「すぐCVしなくても、後のCVに繋がっていたか」を評価できるファネル改善何回目の訪問でCVするケースが多いか？
を分析して施策を最適化できるリターゲティング戦略設計2〜5回目の流入が多いチャネルをリターゲ広告対象にするなど購買までの平均日数、時間帯分析何日かけてCVするか、どの時間帯のセッションが効果的か分析できる

✅このBigQueryはどんな場面に役立つか？

• ECサイト、SaaS、申込型サイトなど、CVまで時間がかかる商材の分析

• 広告間接効果まで含めた総合マーケティング評価

• リード育成（リードナーチャリング）施策の結果確認

• **オムニチャネル（複数チャネル併用）**分析

などにすぐ使える実戦級ツールです。

-- ========================
-- 【この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に設定しているイベント名
  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,  -- セッション日付（YYYY-MM-DD）
  FORMAT_TIMESTAMP('%T', TIMESTAMP_MICROS(csr.event_timestamp)) AS session_time,  -- セッション時刻（HH:MM:SS）
  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
;