【Big quert】アトリビューション再現SQL

2025年8月31日 2025年8月31日

セイさん

Contents

1 ✅ このSQLは【GA4アトリビューション分析】をするためのものです
2 📖 SQLをパートごとに説明
3 ① session_list：まず全セッションをリストアップする
4 ② cv_sessions：コンバージョン（CV）だけを取り出す
5 ③ session_with_cv_flag：セッションに「CVしたかどうか」のフラグを付ける
6 ④ attribution_points：ファーストタッチとラストタッチを決定する
7 ⑤ 最終出力：ファースト・ラスト単位で集計して出力
8 🧠 まとめるとこのSQLは…
9 ✅ そしてこのSQLの強みは…

✅ このSQLは【GA4アトリビューション分析】をするためのものです

つまり、

どのチャネルで最初に流入したか（ファーストタッチ）
どのチャネルで最終的にCVしたか（ラストタッチ）
中間でどれくらい接触したか（中間タッチ回数） をすべてBigQuery単体で再現・可視化するために組まれています！
→1以下の場合、ほとんどのユーザーが、ファースト→ラストの間に、追加でほぼ接触していない＝「ファーストタッチした後、すぐにCVしているユーザーが多い」

📖 SQLをパートごとに説明

① session_list：まず全セッションをリストアップする

ここではGA4のsession_startイベントだけを取り出し、

参照元（source）
メディア（medium）
キャンペーン（campaign）
イベント発生時刻（event_timestamp）を引っ張ってきます。

さらに、ユーザー単位でイベント順に並べて、
「このセッションは何番目か？（session_order）」を付けています。

🔵 要するに
👉「ユーザーのセッション履歴を時系列順に整理する」ステップ！

② cv_sessions：コンバージョン（CV）だけを取り出す

GA4のデータから、

CVイベント（ここでは'GA4_6'）
その発生セッション（ga_session_id）
を抽出しています。

もし1ユーザーに複数CVがあった場合は、最初のCVだけを記録します。

🔵 要するに
👉「どのセッションがCVだったか特定する」ステップ！

③ session_with_cv_flag：セッションに「CVしたかどうか」のフラグを付ける

①で作ったセッションリストに、②で特定したCVセッションを結合して、

CVだったら is_cv_session = 1
そうじゃなければ is_cv_session = 0 というフラグを付けます。

🔵 要するに
👉「どのセッションでCVが起きたかを明示する」ステップ！

④ attribution_points：ファーストタッチとラストタッチを決定する

ここで重要！

ユーザー単位で

最初（session_order=1）のsource/medium/campaign → ファーストタッチ
is_cv_session=1のsource/medium/campaign → ラストタッチ をそれぞれFIRST_VALUEで取得します。

さらに、

ファーストとラストの間に何回タッチがあったか（mid_touch_count）もカウントしています。

🔵 要するに
👉「ユーザーごとに、最初と最後と中間接触の情報をまとめる」ステップ！

⑤ 最終出力：ファースト・ラスト単位で集計して出力

最後に、

ファーストタッチ時のsource/medium/campaign
ラストタッチ時のsource/medium/campaign ごとに
CVユーザー数（user_count）
中間タッチ平均回数（avg_mid_touch_points）を集計して並べています！

🔵 要するに
👉「ファースト・ラスト別の成果を一覧にする」ステップ！

🧠 まとめるとこのSQLは…

ステップ役割session_listユーザーのセッション履歴を時系列で並べるcv_sessionsCVしたセッションを特定するsession_with_cv_flagセッションにCVフラグを付けるattribution_pointsファーストタッチ・ラストタッチ・中間タッチ回数を定義する最終SELECTファーストとラスト別にCV件数・中間タッチ回数を集計する

✅ そしてこのSQLの強みは…

ファーストとラストでチャネルのズレ（例：広告で流入→自然検索でCV）も見える！
リードタイムが長い商材でも、中間タッチ回数を見て検討期間の長さを推測できる！
GA4レポートよりもカスタマイズ自由に見られる！

-- ==========================
-- 【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,
    e.traffic_source.medium AS medium,
    e.traffic_source.name AS campaign,
    e.event_timestamp,
    ROW_NUMBER() OVER (PARTITION BY e.user_pseudo_id ORDER BY e.event_timestamp ASC) AS session_order
  FROM
    -- ★【入力1】対象テーブルを指定
    `idemitsu-447809.analytics_328712105.events_202504*` e
  WHERE
    e.event_name = 'session_start'
    AND _TABLE_SUFFIX BETWEEN '01' AND '30'  -- ★【入力2】対象期間を指定（例：4月なら01～30）
),

-- ② CVセッションを特定
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
    -- ★【入力3】CVイベント名を指定
    e.event_name = 'GA4_6'
    AND _TABLE_SUFFIX BETWEEN '01' AND '30'  -- ★【入力2】対象期間を指定
  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
),

-- ④ ファーストタッチ・ラストタッチ定義
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,

    -- 中間タッチ回数
    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
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
;

この記事を書いた人

セイさん