【GA4×BigQuery】GA4アトリビューション（ファースト/ラスト/中間）をSQLで再現する

導入

広告・SEO・CRMが入り乱れる運用では、「初回の接触（ファーストタッチ）」「コンバージョンを起こした接触（ラストタッチ）」「その間に何回接触（中間タッチ）したのか」を、チャネル（source/medium）やキャンペーン（campaign）の粒度で把握することが、予算配分とKPI設計の土台になります。

一方、GA4のUIはアトリビューションモデルの切替や比較は可能ですが、“自社定義のロジック”でファースト/ラスト/中間タッチを明示列として出し、さらに集計軸を自由に組み合わせることは難しい場面が多いです。

本SQLは、session_start 行だけを対象にユーザーの全セッションを時系列に並べ、指定したCVイベントが発生したセッションをラストタッチとしてフラグ化。

ウィンドウ関数でユーザーごとに最初のセッション（ファースト）とCVが起きたセッション（ラスト）の source/medium/campaign を抽出し、その間にある非CVセッション数を“中間タッチ”として数えます。

最後に、「ファースト×ラスト」の組み合わせごとにユーザー数と中間タッチ平均を集計。

これにより、たとえば「ファーストはSEO・ラストは指名CPCが主流か」「メール→指名CPCの二段構えが機能しているか」「中間タッチが多い導線は熟考を要する商材か」などを定量的に判断できます。

評価のすれ違い（上流と下流）をなくし、役割分担に基づくKPIや予算の再配分に直結させることが目的です。

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

背景・利用シーン（実務での使いどころ）

• • 予算配分の見直し：ファーストを多く獲得するチャネル（認知）と、ラストを多く担うチャネル（刈り取り）で評価KPIを分離し、配分を最適化。

  • キャンペーン評価：first_campaign と last_campaign の組み合わせを見て、連携の勝ち筋（例：教育系→指名獲得）を特定。

  • 熟考度の把握：avg_mid_touch_points が大きい組み合わせは検討期間が長い＝CRM/比較コンテンツ強化の優先候補。

  • クロスチャネルの整合：メール/ディスプレイ/オーガニック/指名CPCのシーケンス最適化に活用。

抽出結果からできる議論

• 広告「ラストの大半がbrand_kw。ファーストはSEOが最多。」
  SEO「上流のSEOは“初回流入KPI”で評価し、CVはラストだけで判断しない運用に変えましょう。」

• CRM「メールをファーストに持つユーザーの中間タッチが少ない。」
  UX「メール→LPの情報量が足り、比較ページを挟まず即CVしている可能性。良い意味での“短縮導線”か検証を。」

• 経営「中間が多い組み合わせは費用対効果が悪化？」
  アナリスト「高単価商材ほど中間が増える傾向。LTV連動で評価軸を分けるべきです。」

• SEO「“referral→organic→CV”が目立つ。」
  BizDev「提携サイトの誘導文言を磨けば、さらに初回流入を伸ばせます。」

• 広告「“display→brand_cpc”の導線は機能。」
  クリエイティブ「上流バナーに“検索ワード想起”要素を追加してラストの指名CPCへ橋渡し強化を。」

SQLコード（テンプレ）と分解解説

-- ==========================
-- 【GA4アトリビューション再現SQL（ファースト・中間・ラスト＋参照元・キャンペーン版）】
-- テンプレート版
-- ==========================

-- ① セッションリストを作成
WITH session_list 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.traffic_source.source AS source,     -- 参照元 (utm_source 等に相当)
    e.traffic_source.medium AS medium,     -- 媒体 (utm_medium)
    e.traffic_source.name AS campaign,     -- キャンペーン (utm_campaign)
    e.event_timestamp,
    ROW_NUMBER() OVER (PARTITION BY e.user_pseudo_id ORDER BY e.event_timestamp ASC) AS session_order
  FROM
    -- ★【入力1】対象テーブルを指定（例：`project.dataset.events_202504*`）
    `idemitsu-447809.analytics_328712105.events_202504*` e
  WHERE
    e.event_name = 'session_start'         -- GA4のセッション開始イベントのみ
    AND _TABLE_SUFFIX BETWEEN '01' AND '30' -- ★【入力2】対象期間を指定（例：4月なら01～30）
),

-- ② CVセッションを特定（CVイベントが発生したセッションIDを取得）
cv_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,
    MIN(e.event_timestamp) AS cv_event_timestamp
  FROM
    -- ★【入力1】対象テーブルを指定（①と同じ）
    `idemitsu-447809.analytics_328712105.events_202504*` e
  WHERE
    e.event_name = 'conversion'                 -- ★【入力3】CVイベント名を指定（例：purchase, generate_lead 等）
    AND _TABLE_SUFFIX BETWEEN '01' AND '30'
  GROUP BY
    user_pseudo_id, ga_session_id
),

-- ③ セッションにCVフラグを付与
session_with_cv_flag AS (
  SELECT
    sl.*,
    CASE WHEN cv.ga_session_id IS NOT NULL THEN 1 ELSE 0 END AS is_cv_session
  FROM session_list sl
  LEFT JOIN cv_sessions cv
    ON sl.user_pseudo_id = cv.user_pseudo_id
   AND sl.ga_session_id = cv.ga_session_id
),

-- ④ ファーストタッチ・ラストタッチ定義
--   ・ファースト：ユーザーの最初のセッションの source/medium/campaign
--   ・ラスト：CVが起きたセッションの source/medium/campaign
--   ・中間タッチ回数：初回以外でCV以外のセッション数
attribution_points AS (
  SELECT
    user_pseudo_id,

    -- ファーストタッチ（最初のセッション）
    FIRST_VALUE(source)  OVER (PARTITION BY user_pseudo_id ORDER BY session_order ASC) AS first_source,
    FIRST_VALUE(medium)  OVER (PARTITION BY user_pseudo_id ORDER BY session_order ASC) AS first_medium,
    FIRST_VALUE(campaign)OVER (PARTITION BY user_pseudo_id ORDER BY session_order ASC) AS first_campaign,

    -- ラストタッチ（CVが発生したセッション）
    FIRST_VALUE(source)  OVER (PARTITION BY user_pseudo_id ORDER BY CASE WHEN is_cv_session = 1 THEN session_order ELSE NULL END ASC) AS last_source,
    FIRST_VALUE(medium)  OVER (PARTITION BY user_pseudo_id ORDER BY CASE WHEN is_cv_session = 1 THEN session_order ELSE NULL END ASC) AS last_medium,
    FIRST_VALUE(campaign)OVER (PARTITION BY user_pseudo_id ORDER BY CASE WHEN is_cv_session = 1 THEN session_order ELSE NULL END ASC) AS last_campaign,

    -- 中間タッチ回数（CV以外セッションのカウント ※初回を除く）
    COUNTIF(is_cv_session = 0 AND session_order > 1) OVER (PARTITION BY user_pseudo_id) AS mid_touch_count
  FROM
    session_with_cv_flag
)

-- ⑤ ファースト・ラスト・キャンペーン別に集計
SELECT
  first_source, first_medium, first_campaign,
  last_source,  last_medium,  last_campaign,
  COUNT(DISTINCT user_pseudo_id) AS user_count,  -- 当該組合せに属するユーザー数
  AVG(mid_touch_count) AS avg_mid_touch_points   -- CVまでの平均中間タッチ数
FROM
  attribution_points
WHERE
  first_medium IS NOT NULL
  AND last_medium  IS NOT NULL
GROUP BY
  first_source, first_medium, first_campaign,
  last_source,  last_medium,  last_campaign
ORDER BY
  user_count DESC;

確認すべきポイント

1. 1. CVイベントの設定
      e.event_name = 'conversion' は環境に合わせて置換（例：purchase / generate_lead）。複数CVを対象にする場合は IN (...)。

   2. 期間・テーブル指定
      _TABLE_SUFFIX の範囲と対象テーブルをセッション側・CV側で一致させる。月跨ぎならワイルドカードを広げるか日付パーティションで指定。

   3. ラストタッチの定義
      現状は**“CVが存在すれば最初に見つかったCVセッション”をラストとして取得。複数CVがあるユーザーは初回CVをラスト扱い**になる点に注意。

      • 直近CVをラストにしたい → MAX(session_order) を使う設計へ変更。

      • CV前のみのセッションに限定したい → session_order <= MIN(cv_session_order) の条件を追加。

   4. セッション粒度
      session_start ベースのため、**セッション内のページ内遷移（流入変化なし）**は集計に影響しない。起点チャネルの評価として妥当。

   5. ユーザー同定の限界
      user_pseudo_id はデバイス・同意の影響を受ける。クロスデバイス/ログイン前後は完全同一ユーザーとは限らない。

   6. mid_touch_count の解釈
      数が多い＝悪ではない。高関与商材や検討長期で自然に増える。CVR・LTVと併読する前提で運用。

   7. ブランド/ノンブランドの切り分け
      必要なら campaign / source を正規化して brand / nonbrand 列を付与し、集計で比較できるようにする。

まとめ

• • ファースト×ラストを source/medium/campaign の粒度で把握し、中間タッチの平均も同時に可視化。

  • **上流（初回獲得）と下流（刈り取り）**の役割を分け、KPI・予算配分の根拠をデータで提示。

  • 複数CVや直近CVなどのバリエーションにも拡張しやすい構造。

  • ダッシュボードに載せれば、定例で運用インサイトを更新できる“使い回し可能”なテンプレSQL。