GA4 のデータを BigQuery で見始め、月次で売上の増減を報告しているマーケターの方向けの記事です。「どの国・端末・流入元が売上を減らしたのか」を AI に探させ、その答えを自分の SQL で検算する手順が分かります。読み終わると、上司に「なぜ落ちたの？」と聞かれた時に、AI の答えをそのまま伝えてよいかを自分で判断できるようになります。
この記事で分かること
- GA4 公開サンプルの購入売上は、2020 年 12 月から 2021 年 1 月に 103,205 減りました（減少率 64.28%）。その減少を、端末・国・流入元のどこが生んだかを数字で分けます
- BigQuery の AI 関数
AI.KEY_DRIVERSが挙げた上位の区分は、寄与度分解の SQL の答えと比べられた 4 区分で、差分の大きさがすべて一致しました（国 × 端末の掛け合わせ区分の 1 件は比べていません） - ただし AI の「上位 3 件」をそのまま足すと 146.63% になります。報告の前に確かめるべき点を、計算式つきで整理します
ざっくり言うと：AI の答えは「合っていた」が「そのまま読むと誤解する」
結論から書きます。売上の減少に最も効いた区分は、端末なら desktop（63.93%）、国なら United States（47.61%）、流入元なら google（27.61%）でした。BigQuery の AI.KEY_DRIVERS（売上などの指標を大きく動かした区分を自動で探す BigQuery の関数）も、同じ区分を同じ大きさで挙げました。
一方で、AI の出力は端末・国・流入元が 1 つの順位表に混ざって並びます。そのため「desktop が 64%、US が 48%、mobile が 35%」と並べて読むと、合計が 100% を超えてしまいます。この「足せない数字」を見抜けるかどうかが、AI の答えを報告に使えるかの分かれ目です。
図 1 は、この記事で行う作業の流れです。
なぜ AI の答えを検算する必要があるのか
AI に「なぜ売上が落ちたか」を聞くと、もっともらしい要因が文章で返ってきます。しかし、その要因が本当に減少の大部分を説明しているかは、文章だけでは分かりません。
検算に使うのは 寄与度分解（全体の増減を、区分ごとの増減に分けて、それぞれが全体の何割を占めるかを出す方法）です。式は「区分の差分 ÷ 全体の差分」の 1 本だけなので、AI がどんな答えを出しても同じ物差しで比べられます。
筆者はこのサイトの運用で、AI に書かせた SQL を、公式の定義に沿って書いた検算用の SQL と比べる記録を取ってきました。その記録でも「公式の定義で作った数字を正解とし、AI の答えをそれと比べる」という方針を取っています。今回はその方針を、売上減少の要因探しに当てはめました。
データ：GA4 公開サンプルと、難読化の注意
使ったのは BigQuery の公開データセット bigquery-public-data.ga4_obfuscated_sample_ecommerce です。Google Merchandise Store（Google のグッズを売る公式ストア）の GA4 データを、2020 年 11 月 1 日から 2021 年 1 月 31 日までの 3 か月分、難読化（個人や実際の数字を特定できないよう値を加工すること）して公開しています（Google for Developers）。
このデータは無料の BigQuery サンドボックス（クレジットカード登録なしで使える制限付きの環境）で試せるため、読者の方も同じ結果を出せます。ただし公式ページには、難読化のために <Other>・NULL・空文字などの置き換え値が入り、データの内部の整合性が少し崩れる場合があると書かれています（同ページ）。また、GA4 のデモアカウントの画面に出る数字とは比べられません。
売上には ecommerce.purchase_revenue を使います。これは購入イベントにだけ入る、購入による収益を現地通貨の標準単位で表す列です（GA4 BigQuery Export スキーマ）。このサンプルでは、米ドル換算の purchase_revenue_in_usd と全行で値が一致したことを、調査担当の実行で確認しています。
手順 1：区分別 × 月別の売上表を SQL で作る
まず 12 月と 1 月の購入売上を、国（geo.country）・端末（device.category）・流入元（traffic_source.source）ごとに集計します。次の SQL は国別の例です。端末や流入元は、geo.country の部分を入れ替えます。
-- 国別 × 月別の購入売上（NULL の売上は 0 として合計）
SELECT
FORMAT_DATE('%Y-%m', PARSE_DATE('%Y%m%d', event_date)) AS month,
geo.country AS segment,
SUM(COALESCE(ecommerce.purchase_revenue, 0)) AS revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20210131'
AND event_name = 'purchase'
GROUP BY month, segment
ORDER BY month, revenue DESC;調査担当がこの集計を実行した時の dry run（実行せずに処理量だけを見積もる機能）の処理バイト数は 154,504,635 バイト（約 147 MiB）でした。BigQuery のオンデマンド料金は毎月 1 TiB まで無料なので、この集計は無料枠に十分収まります（BigQuery の料金）。
全体の合計は次のとおりです。
| 月 | 購入売上 | purchase イベント数 | 売上が NULL の購入 |
|---|---|---|---|
| 2020 年 12 月 | 160,555 | 2,434 件 | 94 件 |
| 2021 年 1 月 | 57,350 | 1,204 件 | 300 件 |
| 差 | −103,205（−64.28%） | −1,230 件 | +206 件 |
出典: 調査担当の bq 実行結果（減少率は筆者が 12 月の値から計算）
手順 2：AI.KEY_DRIVERS に要因を探させる
次に、AI に要因を探させます。今回「AI の答え」として使ったのは、BigQuery の AI.KEY_DRIVERS です。公式ドキュメントでは、集計できる指標に統計的に意味のある変化を起こした区分を特定する関数と説明されており、比べる対象の例として「2 つの月の売上データ」が挙げられています（AI.KEY_DRIVERS の公式ドキュメント）。
注意したいのは、AI.KEY_DRIVERS はチャット AI のような、文章を作る AI（LLM）ではない点です。公式ドキュメントに書かれた式で差分などを計算する統計の関数なので、同じデータからは同じ数字が返ります。答えを作り出すわけではないため、この記事の検算で確かめられるのは「数字の意味を取り違えていないか」です。
実行した SQL は次のとおりです。1 行が purchase イベント 1 件で、is_jan という BOOL 列（TRUE か FALSE だけが入る列）で、1 月の行を注目側、12 月の行を基準側に分けています。
-- AI.KEY_DRIVERS：1 月（注目側）が 12 月（基準側）からどう変わったか
SELECT *
FROM AI.KEY_DRIVERS(
(
SELECT
event_date LIKE '202101%' AS is_jan, -- 1 月なら TRUE
COALESCE(geo.country, '(null)') AS country, -- 区分の NULL は文字に置き換える
COALESCE(device.category, '(null)') AS device,
COALESCE(traffic_source.source, '(null)') AS source,
IFNULL(ecommerce.purchase_revenue, 0) AS rev -- 売上の NULL は 0 として合計
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20210131'
AND event_name = 'purchase'
AND (event_date LIKE '202012%' OR event_date LIKE '202101%')
),
metric_col => 'rev',
dimension_cols => ['country', 'device', 'source'],
interest_label_col => 'is_jan',
top_k => 10
);区分の列を COALESCE で置き換えているのには理由があります。公式ドキュメントには、区分の列に NULL が入った行は除かれると書かれています（同ドキュメント）。置き換えないと、その行の売上が全体の合計から抜け、手順 1 の合計と合わなくなります。
渡した条件をまとめると次のとおりです。
| 設定 | 値 | 意味 |
|---|---|---|
| 指標の列 | rev（購入売上） | 増減を調べる数字 |
| 区分の列 | country・device・source | 要因の候補にする区分 |
| 注目側 / 基準側 | 1 月 / 12 月 | 1 月が 12 月からどう変わったかを見る |
| top_k | 10 | 上位 10 件まで返す |
公式ドキュメントは、注目側と基準側の行数をおおよそそろえると結果が偏りにくいと勧めています（同ドキュメント）。今回の入力は 12 月 2,434 行・1 月 1,204 行で、1 月が約半分です。この勧めは満たしていないことを前提に、結果を読んでください。
この実行は無料枠の中で終わり、課金対象は 155,189,248 バイトでした。執筆時に同じ SQL を実行し直し、下の表と同じ値が返ることも確かめました。返ってきた上位 5 件（全体を表す all の行を除く）は次のとおりです。
| AI の順位 | 区分 | difference（1 月 − 12 月） | contribution |
|---|---|---|---|
| 1 | device = desktop | −65,978 | 65,978 |
| 2 | country = United States | −49,140 | 49,140 |
| 3 | device = mobile | −36,219 | 36,219 |
| 4 | country = United States かつ device = desktop | −30,604 | 30,604 |
| 5 | source = google | −28,492 | 28,492 |
出典: 調査担当の bq 実行結果
チャット AI に表を渡す方法との違い
ChatGPT や Claude のようなチャット AI に表を貼り付けて「減少に最も寄与した区分を上位 3 つ、寄与率つきで答えて」と聞く方法もあります。今回の記録では、この方法は実行していません。そのため、チャット AI がどう答えるかの結果は、この記事には載せていません。
試す場合は、次のような質問文が使えます。どちらの方法でも、この後の手順 3 の検算はそのまま使えます。
次の表は、ある EC サイトの 12 月と 1 月の購入売上を、端末・国・流入元ごとに集計したものです。
全体の売上は 12 月から 1 月に減りました。
減少に最も寄与した区分を上位 3 つ挙げ、それぞれの寄与率（区分の差分 ÷ 全体の差分）を % で答えてください。
端末・国・流入元は別々に答え、計算に使った差分の値も書いてください。
（ここに表を貼る）AI.KEY_DRIVERS との大きな違いは、計算をどこでするかです。AI.KEY_DRIVERS は BigQuery の中でデータ全体から計算するので、表を渡す時の書き写しの誤りが起きません。チャット AI は貼った表の範囲でしか答えられず、計算の過程も文章で返ってくるため、検算がより重要になると考えられます。
手順 3：寄与度分解 SQL で正解を作って検算する
正解は、区分ごとに「1 月 − 12 月」の差分を出し、それを全体の差分で割って作ります。図 2 が式の組み立てです。
SQL では次のように書けます。手順 1 の結果を monthly として受け取る形です。
-- 寄与度分解：区分の差分 ÷ 全体の差分
WITH monthly AS (
-- 手順 1 の集計結果（month, segment, revenue）
SELECT ...
),
diff AS (
SELECT
segment,
SUM(IF(month = '2021-01', revenue, 0))
- SUM(IF(month = '2020-12', revenue, 0)) AS segment_diff
FROM monthly
GROUP BY segment
)
SELECT
segment,
segment_diff,
ROUND(100 * segment_diff / SUM(segment_diff) OVER (), 2) AS contribution_pct,
RANK() OVER (ORDER BY segment_diff) AS drop_rank
FROM diff
ORDER BY drop_rank;検算 1：区分の合計は全体の差分に戻るか
寄与度分解が正しければ、同じ種類の区分の差分を全部足すと全体の差分に戻ります。筆者が調査担当の結果から足し算したところ、端末も流入元もぴったり −103,205 に戻りました。
| 区分の種類 | 区分 | 差分 | 寄与率 |
|---|---|---|---|
| 端末 | desktop | −65,978 | 63.93% |
| 端末 | mobile | −36,219 | 35.09% |
| 端末 | tablet | −1,008 | 0.98% |
| 端末 | 合計 | −103,205 | 100% |
| 流入元 | −28,492 | 27.61% | |
| 流入元 | (direct) | −23,949 | 23.21% |
| 流入元 | <Other> | −22,014 | 21.33% |
| 流入元 | (data deleted) | −15,934 | 15.44% |
| 流入元 | shop.googlemerchandisestore.com | −12,816 | 12.42% |
| 流入元 | 合計 | −103,205 | 100% |
出典: 調査担当の bq 実行結果（合計は筆者が計算）
図 3 は、流入元別の寄与率を大きい順に並べたものです。本来は 12 月の合計から区分ごとに差分を積み上げて 1 月に至る滝図（ウォーターフォール図）が向いていますが、ここでは同じ内容を寄与率の横棒で示しています。
国は 12 月に 85 種類、1 月に 78 種類あり、上位 5 か国だけを載せます。上位 5 か国の寄与率の合計は 73.38% で、残りの 26.62% はそれ以外の国の合計です（筆者が 100% から引いて計算）。
| 国 | 差分 | 寄与率 |
|---|---|---|
| United States | −49,140 | 47.61% |
| India | −13,338 | 12.92% |
| Canada | −7,376 | 7.15% |
| United Kingdom | 不明（寄与率のみ確認） | 3.43% |
| Germany | 不明（寄与率のみ確認） | 2.27% |
| Romania（増加した例） | +249 | −0.24% |
出典: 調査担当の bq 実行結果
検算 2：AI の答えと正解は一致したか
手順 2 の AI の答えと、手順 3 の正解を並べたのが図 4 です。区分が 1 つだけの行（desktop・United States・mobile・google）は、AI の contribution と正解の差分の絶対値が完全に一致しました。4 位の掛け合わせ区分は、手順 3 で分解していないため比べていません。
| AI の順位 | 区分 | 正解の差分 | 正解の寄与率 | AI の contribution | 判定 |
|---|---|---|---|---|---|
| 1 | 端末 desktop | −65,978 | 63.93% | 65,978 | 一致 |
| 2 | 国 United States | −49,140 | 47.61% | 49,140 | 一致 |
| 3 | 端末 mobile | −36,219 | 35.09% | 36,219 | 一致 |
| 4 | United States かつ desktop | 分解していない | 29.65%（筆者が換算） | 30,604 | 比較対象なし |
| 5 | 流入元 google | −28,492 | 27.61% | 28,492 | 一致 |
一致するのは偶然ではなく、当然の結果です。公式ドキュメントで、contribution は difference の絶対値（ABS(difference)）と定義されているからです（AI.KEY_DRIVERS の公式ドキュメント）。
そのため、この検算で確かめたのは「AI の計算が正しいか」ではありません。確かめたのは contribution の意味で、これは「割合」ではなく「差分の大きさ」です。寄与率として報告したい時は、自分で全体の差分で割る必要があります。チャット AI の答えは式で決まるわけではないので、この一致を理由に同じように信頼することはできません。
検算 3：「想定外に落ちた」の数字を手計算で再現する
AI.KEY_DRIVERS は、もう 1 つ unexpected_difference という数字も返します。これは、その区分の実際の値と、他の区分の変化率から求めた期待値との差です（同ドキュメント）。単純な差分とは別物なので、手計算で中身を確かめました。
12 月と 1 月の金額が両方分かっている United States で計算します。
US 以外の 12 月 = 160,555 − 71,958 = 88,597
US 以外の 1 月 = 57,350 − 22,818 = 34,532
US 以外の倍率 = 34,532 ÷ 88,597 = 0.389765
US の 1 月の期待値 = 71,958 × 0.389765 = 28,046.7
unexpected_difference = 22,818 − 28,046.7 = −5,228.70AI の出力は −5,228.70 で、小数 2 桁まで一致しました。ちなみに「全体の倍率（57,350 ÷ 160,555）」で期待値を出すと差は −2,885.3 になり、一致しません。期待値は「その区分を除いた残り」の変化率で計算されていることが、手計算で確かめられました。
| 区分 | difference | unexpected_difference | 読み方 |
|---|---|---|---|
| device = desktop | −65,978 | −8,034.69 | 全体の減り方より大きく落ちた |
| country = United States | −49,140 | −5,228.70 | 全体の減り方より大きく落ちた |
| device = mobile | −36,219 | +4,231.56 | 減ったが、全体ほどは落ちていない |
| source = google | −28,492 | +1,842.16 | 減ったが、全体ほどは落ちていない |
出典: 調査担当の bq 実行結果（4 行とも筆者が同じ式の手計算で再現）
AI.KEY_DRIVERS の出力には、区分ごとの 12 月と 1 月の金額（metric_reference と metric_interest）も入っています。その金額を使い、desktop・mobile・google も US と同じ式で計算したところ、4 行とも小数 2 桁まで一致しました。
US の減少率は −68.29% で、全体の −64.28% より大きく落ちています（筆者が計算）。一方 mobile と google は、差分では上位でも「想定外に落ちた」わけではありません。
結果：区分の種類ごとに寄与率の上位を見る
図 5 は、端末・国・流入元の寄与率の上位を 1 枚に並べたものです。青い棒の端末と流入元は、それぞれ全部足すと 100% になります。灰色の棒の国は上位 5 件だけなので、足すと 73.38% です。
この図から分かるのは、どの物差しで切っても「一番大きい区分」は変わるということです。端末で切れば desktop が約 3 分の 2 を占め、流入元で切れば上位 3 つがほぼ横並びになります。報告では「どの種類で切ったか」を必ず添える必要があります。
つまずきやすい点
種類の違う区分の寄与率は足せない
AI.KEY_DRIVERS は端末・国・流入元を 1 つの順位表に混ぜて返します。上位 3 件の寄与率を足すと 63.93% + 47.61% + 35.09% = 146.63% になり、100% を超えます（筆者が計算）。同じ売上を端末と国の両方で数えているためです。
さらに 4 位の「United States かつ desktop」は、1 位と 2 位の両方に含まれる部分です。「上位 3 つで減少の 146% を説明できた」とは言えないので、報告では種類ごとに分けて書きます。
増えた区分の寄与率はマイナスになる
寄与率は足すと 100% になりますが、売上が増えた区分は寄与率がマイナスになります。たとえば Romania は +249 増えたため、寄与率は −0.24% です。マイナスの区分は「減少を打ち消した側」と読みます。
区分を掛け合わせると件数が小さくなる
国 × 端末のように区分を掛け合わせると、1 つの区分に入る購入の件数が小さくなります。件数が少ない区分の差分は、少しの変動で大きく動くと考えられます。
そのため、自分で SQL を書く時は、区分ごとの購入の件数も一緒に出しておくと安全です。
一方、AI.KEY_DRIVERS の足切りは件数ではなく売上の割合で決まります。出力の apriori_support は、その区分の売上が全体の売上に占める割合（12 月と 1 月の大きいほう）です（同ドキュメント）。top_k を指定しない場合は、この割合が min_apriori_support（既定 0.1）より小さい区分が出力から外れます。今回は top_k=10 を指定したので、この既定値は当てはまりません。
売上は purchase_revenue を使い、NULL の扱いを決める
売上には、標準単位で入っている ecommerce.purchase_revenue を使います（GA4 BigQuery Export スキーマ）。注意したいのは、このサンプルでは売上が NULL の購入イベントがあることです。
| 月 | NULL の割合 | 1 件あたり売上（NULL を 0 として含む） | 1 件あたり売上（NULL を除く） |
|---|---|---|---|
| 2020 年 12 月 | 3.86% | 65.96 | 68.61 |
| 2021 年 1 月 | 24.92% | 47.63 | 63.44 |
出典: 筆者計算（元データ: 調査担当の bq 実行結果の件数と売上）
1 月は購入の 4 件に 1 件で売上が NULL でした。今回は NULL を 0 として合計したので、減少の一部は難読化による欠損の可能性があります。このため、ここで出した金額の大きさは事業上の事実ではなく、手順の練習用の数字として扱うのが安全と考えられます。
流入元は「購入した時の流入元」ではない
traffic_source.source は、ユーザーを最初に獲得した流入元を表す列で、その後に別の広告から来ても値は変わりません（GA4 BigQuery Export スキーマ）。「google からの購入が減った」ではなく「google で最初に獲得したユーザーの購入が減った」と読みます。
また、流入元の <Other> と (data deleted) は難読化の置き換え値で、合わせて減少の 36.77% を占めます（筆者が計算）。中身が分からない区分が上位に来た時は、AI の答えが正しくても、それを要因として報告することはできません。
自分のデータで同じ検算をする手順
- 手順 1 の SQL で、比べたい 2 か月の売上を区分ごとに集計する（先に dry run で処理量を確かめる）
AI.KEY_DRIVERSかチャット AI に、要因の上位を挙げさせる- 手順 3 の寄与度分解 SQL で、区分ごとの差分と寄与率を出す
- 区分の種類ごとに、差分の合計が全体の差分に戻るかを確かめる
- AI の答えと正解を、区分・差分・寄与率の 3 列で並べて比べる
- NULL の件数、置き換え値の区分、掛け合わせ区分の件数を確かめてから報告する
まとめ
AI.KEY_DRIVERSが挙げた区分のうち、比べられた 4 区分の差分は寄与度分解の SQL とすべて一致しました（掛け合わせ区分の 1 件は比べていません）。contribution は差分の絶対値なので、寄与率は自分で全体の差分で割って出します- AI の上位は種類の違う区分が混ざっており、足すと 146.63% になります。報告では端末・国・流入元ごとに分けて書きます
- 公開サンプルでは 1 月の購入の約 4 分の 1 で売上が NULL でした。金額の大きさより、検算の手順を持ち帰るのが安全です
次の一歩
月次レポートの数字をどう見せるかを整えたい方は、ダッシュボードのページで、GA4 や BigQuery のデータを見える形にする方法を確認できます。寄与度分解の SQL や集計の正しさに不安がある場合は、BigQuery SQL レビューパックも用意しています。
参考にした一次情報
いずれも 2026-11-09 に内容を確認しました。