Google 広告のコンバージョンと GA4 のキーイベントが合わないとき｜管理画面で分けられる差を先に引き、月の数字は計測期間のあとに確定させる

-- 0. 日付の基準が同じかを確かめる
-- GA4 の event_date は、プロパティのタイムゾーンの日付。event_timestamp は UTC のマイクロ秒。
-- 購入のイベントで、event_date が日本時間の日付と UTC の日付のどちらと一致するかを数える。
-- 日本時間と全件一致すれば、Google 広告のアカウントのタイムゾーン（日本時間）と同じ基準で突き合わせられる。
-- Google 広告のアカウントのタイムゾーンが日本時間でなければ、下の 'Asia/Tokyo' もそのタイムゾーンに書き換える。
-- 差し替える所：{EVENTS}・{SUFFIX_FILTER} は kit_01 と同じ（例：`プロジェクト.analytics_123456789.events_*` と _TABLE_SUFFIX BETWEEN '20260501' AND '20261130'）
WITH events AS (
  SELECT * FROM {EVENTS} WHERE {SUFFIX_FILTER}
)
SELECT
  COUNT(*) AS purchase_events,
  COUNTIF(event_date = FORMAT_DATE('%Y%m%d', DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'))) AS same_as_tokyo,
  COUNTIF(event_date = FORMAT_DATE('%Y%m%d', DATE(TIMESTAMP_MICROS(event_timestamp)))) AS same_as_utc
FROM events
WHERE event_name = 'purchase';

-- 1. 購入1件ずつに、結び付く広告のクリック（gclid）とクリックの日を付けた表を作る
-- 差し替える所：
--   {EVENTS}：GA4 の BigQuery エクスポート（例：`プロジェクト.analytics_123456789.events_*`）
--   {SUFFIX_FILTER}：読む日の範囲（例：_TABLE_SUFFIX BETWEEN '20260501' AND '20261130'）。突き合わせる月の 90 日前から読む
--   {CLICKS}：Google 広告の ClickStats 表（BigQuery Data Transfer Service。例：`プロジェクト.google_ads.p_ads_ClickStats_1234567890`。表の名前は自社の転送で確かめる）
--   {OUT}：作る表（例：`プロジェクト.データセット.purchase_click`）
-- collected_traffic_source.gclid は、そのイベントと一緒に収集された Google クリック ID。
-- 購入（purchase）のイベントに入っているとは限らないので、同じ人（user_pseudo_id）の、購入の前 90 日以内に
-- gclid が入っていたイベントのうち最後のものに結び付ける。
-- これは Google 広告の実際の割り当て（アトリビューション モデル）の再現ではない。突き合わせのための決めごと。
--
-- 注文 ID（ecommerce.transaction_id）の扱い：
--   同じ注文 ID の購入は1件にまとめる（GA4 のヘルプ：同じトランザクション ID の購入イベントは重複が除去される。ウェブ ストリームのみ）。アプリ ストリームの購入があれば、stream_id で分けてから決める。
--   注文 ID が空の文字列（''。空白だけのものも含める）の購入：GA4 のヘルプに「transaction_id="" である購入イベントはすべて重複として除去されます」とあるので、既定は「除く」。
--   注文 ID が NULL か '(not set)' の購入：GA4 のヘルプに扱いの記載が見当たらないので、下の設定で選ぶ。
--   既定の「1件ずつ」は、重複除去は同じ注文 ID の購入どうしに働くので、注文 ID の無い購入は画面でもまとめられずに数えられると読んだため（当社の読み）。
--   除いた購入も表には残し、excluded_by_rule = TRUE を付ける（突き合わせの表で「除いた購入」の行に出す）。
--   「1件にまとめる」は、{SUFFIX_FILTER} で読む期間全体で1件になる（月ごとではない。まとめた中で最初の時刻の購入の月に入る）。
DECLARE empty_id_rule STRING DEFAULT '除く';        -- 注文 ID が ''：'除く'（既定。ヘルプの記載どおり）／'1件にまとめる'／'1件ずつ'
DECLARE missing_id_rule STRING DEFAULT '1件ずつ';   -- 注文 ID が NULL・'(not set)'：'1件ずつ'（既定）／'1件にまとめる'／'除く'
-- 設定の値が上の3つのどれでもなければ、ここで止める（書き違えたまま数えないため）
-- NULL も止める（NULL は NOT IN の比べで真にならず、止まらないまま数えてしまうため）
IF IFNULL(empty_id_rule, '') NOT IN ('除く', '1件にまとめる', '1件ずつ') OR IFNULL(missing_id_rule, '') NOT IN ('除く', '1件にまとめる', '1件ずつ') THEN
  SELECT ERROR(CONCAT('kit_01 の設定の値が違います：empty_id_rule=', IFNULL(empty_id_rule, 'NULL'), '、missing_id_rule=', IFNULL(missing_id_rule, 'NULL')));
END IF;

CREATE OR REPLACE TABLE {OUT} AS
WITH events AS (
  SELECT * FROM {EVENTS} WHERE {SUFFIX_FILTER}
),
ad_visits AS (   -- gclid が入っていたイベント（人と gclid ごとに、最初の時刻）
  SELECT user_pseudo_id, collected_traffic_source.gclid AS gclid, MIN(event_timestamp) AS visit_ts
  FROM events
  WHERE collected_traffic_source.gclid IS NOT NULL
  GROUP BY user_pseudo_id, gclid
),
purchase_events AS (   -- 購入のイベント。注文 ID の種類を分け、1件ずつ数えるときの鍵（人ごとの通し番号）を付ける
  SELECT
    user_pseudo_id,
    ecommerce.transaction_id AS transaction_id,
    event_timestamp AS conv_ts,
    PARSE_DATE('%Y%m%d', event_date) AS conv_date,
    CASE
      WHEN ecommerce.transaction_id IS NULL OR TRIM(ecommerce.transaction_id) = '(not set)' THEN 'missing'
      WHEN TRIM(ecommerce.transaction_id) = '' THEN 'empty'   -- 空白だけの注文 ID も空の文字列と同じに扱う（ヘルプの記載は "" だけで、空白だけの扱いは見当たらない）
      ELSE 'id'
    END AS id_kind,
    CONCAT('row:', IFNULL(user_pseudo_id, ''), ':',
           CAST(ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS STRING)) AS row_key
  FROM events
  WHERE event_name = 'purchase'
),
keyed AS (       -- まとめる鍵と、除くかどうかを、上の2つの設定から決める
  SELECT
    * EXCEPT (row_key),
    CASE
      WHEN id_kind = 'id' THEN CONCAT('id:', transaction_id)
      WHEN id_kind = 'empty' AND empty_id_rule = '1件にまとめる' THEN 'empty'
      WHEN id_kind = 'missing' AND missing_id_rule = '1件にまとめる' THEN CONCAT('missing:', IFNULL(transaction_id, 'NULL'))
      ELSE row_key
    END AS order_key,
    (id_kind = 'empty' AND empty_id_rule = '除く') OR (id_kind = 'missing' AND missing_id_rule = '除く') AS excluded_by_rule
  FROM purchase_events
),
purchases AS (   -- 同じ鍵は最初の1件にまとめ、何件あったかを残す
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY order_key) AS events_same_id
  FROM keyed
  WHERE TRUE
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_key ORDER BY conv_ts) = 1
),
linked AS (      -- 購入の前 90 日以内にあった、同じ人の最後の gclid
  SELECT p.*, v.gclid, v.visit_ts
  FROM purchases AS p
  LEFT JOIN ad_visits AS v
    ON v.user_pseudo_id = p.user_pseudo_id
   AND v.visit_ts <= p.conv_ts
   AND v.visit_ts >= p.conv_ts - 90 * 86400 * 1000000
  WHERE TRUE
  QUALIFY ROW_NUMBER() OVER (PARTITION BY p.order_key ORDER BY v.visit_ts DESC) = 1
),
clicks AS (      -- クリックの日とキャンペーン（gclid ごとに1行）
  SELECT click_view_gclid, ANY_VALUE(segments_date) AS click_date, ANY_VALUE(campaign_id) AS campaign_id
  FROM {CLICKS}
  GROUP BY click_view_gclid
)
SELECT
  l.order_key, l.transaction_id, l.id_kind, l.excluded_by_rule, l.user_pseudo_id, l.conv_ts, l.conv_date, l.events_same_id, l.gclid,
  c.click_date, c.campaign_id,
  DATE_DIFF(l.conv_date, c.click_date, DAY) AS days_from_click,           -- クリックの日から購入の日までの日数
  IF(l.gclid IS NULL OR l.excluded_by_rule, NULL,
     ROW_NUMBER() OVER (PARTITION BY l.gclid, l.excluded_by_rule ORDER BY l.conv_ts)) AS nth_for_click   -- 同じクリックからの何回目の購入か（除いた購入は数えない）
FROM linked AS l
LEFT JOIN clicks AS c ON c.click_view_gclid = l.gclid;

-- 2. 突き合わせの表：GA4 の購入から、Google 広告が数えない分を順に引き、管理画面の2つの列と比べる数を出す
--    d_by_conv_date は［コンバージョン（コンバージョンの日時別）］と比べる数（購入の日で数えた数）、
--    h_by_click_date は［コンバージョン］と比べる数（クリックの日で数えた数）。check_diff は表の数え方の検算の差（0なら合っている）
-- 差し替える所：
--   {PC}：kit_01 で作った表（例：`プロジェクト.データセット.purchase_click`）
--   {AS_OF}：数えた時点。日本時間の 'YYYY-MM-DD HH:MM:SS'。この時刻より前の購入だけを使う
--            例：'2026-10-01 00:00:00'＝9月末までのデータ。管理画面の数と比べるときは、管理画面の数を控えた日の 0 時にする
--            Google 広告のアカウントのタイムゾーンが日本時間でなければ、下の 'Asia/Tokyo' もそのタイムゾーンに書き換える
--   {WINDOW}：計測期間（クリックスルー コンバージョンの計測期間）の日数。例：30
--   {COUNT_MODE}：カウント方法。'全件' か '初回のみ'（Google 広告のコンバージョン アクションの設定と同じにする）
-- 結果を表に保存するとき（kit_03 の {BRIDGE} に使う）：
--   1) 下の「WITH p AS (」の行の前に、次の1行を足す
--        CREATE OR REPLACE TABLE `プロジェクト.データセット.bridge_20261001` AS
--      （表の名前には {AS_OF} の日付を入れておくと、どの時点の表か分かる）
--   2) いちばん最後の「ORDER BY month;」を「;」にする
--   3) 3行の DECLARE はそのまま残して、全体を1回で実行する
DECLARE as_of TIMESTAMP DEFAULT TIMESTAMP('{AS_OF}', 'Asia/Tokyo');  -- 数えた時点（この時刻より前の購入だけを使う）
DECLARE window_days INT64 DEFAULT {WINDOW};                           -- 計測期間の日数
DECLARE count_mode STRING DEFAULT '{COUNT_MODE}';                     -- カウント方法：'全件' か '初回のみ'

WITH p AS (
  SELECT
    *,
    NOT excluded_by_rule AND gclid IS NOT NULL AS from_ad,                    -- 広告のクリックからの訪問が前にある購入
    NOT excluded_by_rule AND click_date IS NOT NULL
      AND days_from_click < window_days AS in_window,                          -- 計測期間の内（3月1日のクリック・30日間→3月30日までに合わせた）
    (count_mode = '全件' OR nth_for_click = 1) AS counted                      -- カウント方法を当てたとき数えるか
  FROM {PC}
  WHERE conv_ts < UNIX_MICROS(as_of)
),
by_conv AS (     -- 購入の日の月で数える
  SELECT
    DATE_TRUNC(conv_date, MONTH) AS m,
    SUM(events_same_id) AS a0_purchase_events,                                 -- GA4 の purchase のイベント数
    SUM(IF(excluded_by_rule, events_same_id, 0)) AS a_excluded,                -- kit_01 の設定で除いた購入（注文 ID が '' など）
    COUNTIF(NOT excluded_by_rule) AS a_orders,                                 -- 注文 ID ごとに1件にした数
    COUNTIF(NOT excluded_by_rule AND id_kind <> 'id') AS a_no_order_id,        -- うち注文 ID の無い購入（NULL・'(not set)' など）
    COUNTIF(from_ad) AS b_from_ad,
    COUNTIF(from_ad AND click_date IS NULL) AS b_click_not_found,              -- gclid がクリックの表に無い
    COUNTIF(from_ad AND click_date IS NOT NULL AND NOT in_window) AS b_outside_window,
    COUNTIF(in_window) AS c_in_window,
    COUNTIF(in_window AND counted) AS d_by_conv_date,                         -- 購入の日で数えた数（［コンバージョン（コンバージョンの日時別）］と比べる）
    COUNTIF(in_window AND counted AND DATE_TRUNC(click_date, MONTH) < DATE_TRUNC(conv_date, MONTH)) AS e_clicked_in_earlier_month
  FROM p
  GROUP BY m
),
by_click AS (    -- クリックの日の月で数える
  SELECT
    DATE_TRUNC(click_date, MONTH) AS m,
    COUNTIF(in_window AND counted) AS h_by_click_date,                        -- クリックの日で数えた数（［コンバージョン］と比べる）
    COUNTIF(in_window AND counted AND DATE_TRUNC(conv_date, MONTH) > DATE_TRUNC(click_date, MONTH)) AS f_bought_in_later_month
  FROM p
  WHERE click_date IS NOT NULL
  GROUP BY m
)
SELECT
  m AS month,
  IFNULL(a0_purchase_events, 0) AS a0_purchase_events,
  IFNULL(a0_purchase_events, 0) - IFNULL(a_excluded, 0) - IFNULL(a_orders, 0) AS same_order_id_dup,
  IFNULL(a_excluded, 0) AS excluded_by_rule,
  IFNULL(a_orders, 0) AS a_orders,
  IFNULL(a_no_order_id, 0) AS no_order_id,
  IFNULL(a_orders, 0) - IFNULL(b_from_ad, 0) AS not_from_ad,
  IFNULL(b_from_ad, 0) AS b_from_ad,
  IFNULL(b_click_not_found, 0) AS click_not_found,
  IFNULL(b_outside_window, 0) AS outside_window,
  IFNULL(c_in_window, 0) AS c_in_window,
  IFNULL(c_in_window, 0) - IFNULL(d_by_conv_date, 0) AS second_or_later,
  IFNULL(d_by_conv_date, 0) AS d_by_conv_date,
  IFNULL(e_clicked_in_earlier_month, 0) AS e_clicked_in_earlier_month,
  IFNULL(f_bought_in_later_month, 0) AS f_bought_in_later_month,
  IFNULL(d_by_conv_date, 0) - IFNULL(e_clicked_in_earlier_month, 0) + IFNULL(f_bought_in_later_month, 0) AS g_bridge,
  IFNULL(h_by_click_date, 0) AS h_by_click_date,
  IFNULL(d_by_conv_date, 0) - IFNULL(e_clicked_in_earlier_month, 0) + IFNULL(f_bought_in_later_month, 0) - IFNULL(h_by_click_date, 0) AS check_diff   -- 検算の差（0なら表の数え方は合っている）
FROM by_conv
FULL JOIN by_click USING (m)
ORDER BY month;

-- 3. Google 広告の管理画面の数と並べ、数え方で説明のつかない差を出す
--    unexplained_diff_* は説明のつかない差。0になるとは限らない（推定・アトリビューションなど、数え方の外の理由で出る）
--    kit_02 の check_diff（表の数え方の検算の差。0なら合っている）とは別のもの
-- 差し替える所：
--   {UI}：管理画面の数を月ごとに書き写した表。列は month DATE（月の1日）、conversions FLOAT64（［コンバージョン］列）、
--         conversions_by_conv_time FLOAT64（［コンバージョン（コンバージョンの日時別）］列）、taken_on DATE（控えた日＝書き写した日）
--         作り方の例（数字は控えた値に替える）：
--           CREATE OR REPLACE TABLE `プロジェクト.データセット.ads_ui_20261001`
--             (month DATE, conversions FLOAT64, conversions_by_conv_time FLOAT64, taken_on DATE);
--           INSERT INTO `プロジェクト.データセット.ads_ui_20261001` VALUES
--             (DATE '2026-09-01', 191, 225, DATE '2026-10-01');
--   {BRIDGE}：kit_02 の結果を保存した表（kit_02 の先頭のコメントの手順）。
--             kit_02 の {AS_OF} は、控えた日（taken_on）の 0 時にそろえる。ずれると、クリックの日の差に「まだ数えていない購入の分」が混ざる
SELECT
  u.month,
  u.taken_on,
  u.conversions_by_conv_time,
  b.d_by_conv_date,
  ROUND(u.conversions_by_conv_time - b.d_by_conv_date, 2) AS unexplained_diff_by_conv_time,   -- 購入の日どうしの説明のつかない差（調べる分）
  u.conversions,
  b.h_by_click_date,
  ROUND(u.conversions - b.h_by_click_date, 2) AS unexplained_diff_by_click_date               -- クリックの日どうしの説明のつかない差
FROM {UI} AS u
LEFT JOIN {BRIDGE} AS b USING (month)
ORDER BY u.month;

-- 4. 月の数字を確定させる日を決める表：月末から n 日後までのデータで数えると、その月の数字はどう変わるか
-- 購入の日で数えた数（by_conv_date、［コンバージョン（コンバージョンの日時別）］と比べる数）と、
-- クリックの日で数えた数（by_click_date、［コンバージョン］と比べる数）を、同じ n で並べる。
-- 差し替える所：{PC}＝kit_01 で作った表、{WINDOW}・{COUNT_MODE}＝kit_02 と同じ、
--   {M0}・{M1}＝見る月の最初と最後（月の1日。例：2026-06-01 と 2026-09-01）、{NMAX}＝月末から何日後まで見るか（例：60）
--   Google 広告のアカウントのタイムゾーンが日本時間でなければ、下の 'Asia/Tokyo' もそのタイムゾーンに書き換える
-- 読み方：クリックの日の数が、ある n から先で変わらなくなる。計測期間が W 日なら、増えうるのは n＝W−1 まで（月の最後の日のクリックの計測期間の最後の日）
DECLARE window_days INT64 DEFAULT {WINDOW};
DECLARE count_mode STRING DEFAULT '{COUNT_MODE}';

WITH p AS (
  SELECT
    *,
    NOT excluded_by_rule AND click_date IS NOT NULL AND days_from_click < window_days AS in_window,   -- kit_01 の設定で除いた購入は数えない
    (count_mode = '全件' OR nth_for_click = 1) AS counted
  FROM {PC}
),
grid AS (
  SELECT m, n, UNIX_MICROS(TIMESTAMP(DATE_ADD(LAST_DAY(m), INTERVAL n + 1 DAY), 'Asia/Tokyo')) AS as_of_micros
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE '{M0}', DATE '{M1}', INTERVAL 1 MONTH)) AS m,
       UNNEST(GENERATE_ARRAY(0, {NMAX})) AS n                -- n＝月末から何日後までのデータか（0＝月末まで）
)
SELECT
  g.m AS month,
  g.n AS days_after_month_end,
  COUNTIF(p.in_window AND p.counted AND DATE_TRUNC(p.conv_date, MONTH) = g.m AND p.conv_ts < g.as_of_micros) AS by_conv_date,
  COUNTIF(p.in_window AND p.counted AND DATE_TRUNC(p.click_date, MONTH) = g.m AND p.conv_ts < g.as_of_micros) AS by_click_date
FROM grid AS g
CROSS JOIN p
GROUP BY month, days_after_month_end
ORDER BY month, days_after_month_end;

3. の SQL の unexplained_diff_by_conv_time・unexplained_diff_by_click_date は、表の数と管理画面の数の差で、0になるとは限りません。