GA4の商品別売上をAIに書かせて検算：UNNESTで4.4倍になる足し方

GA4 のデータを BigQuery に出し、商品別売上の SQL を AI に書かせている EC のマーケター・事業担当の方向けの記事です。GA4 の公開サンプルで、商品別の売上の合計が全体の売上と合うかを検算しました。読み終わると、AI の SQL がどの売上の項目を足したかを見分け、質問文と検算の SQL で確かめられるようになります。

この記事で分かること

• GA4 の BigQuery エクスポートにある 2 つの売上の項目（イベント全体の売上と、商品ごとの売上）の違い
• items の配列を開く（UNNEST）と、なぜイベント全体の売上が何回も足されるのか
• 公開サンプルの 1 か月分で、足し方によって合計がどれだけ変わるか（筆者の計算では最大 4.43 倍）
• AI への質問文に何を書き、どの検算の SQL で確かめればよいか

使うデータ：GA4 の公開サンプルと items の配列

使うのは、Google が公開している GA4 のサンプルデータ（bigquery-public-data.ga4_obfuscated_sample_ecommerce）です。Google Merchandise Store（Google のグッズを売る通販サイト）の 2020 年 11 月 1 日から 2021 年 1 月 31 日までの 3 か月分のイベントが入っています（Google for Developers: ウェブ e コマースのサンプルデータセット）。今回はそのうち 2020 年 12 月の 1 か月分（events_202012*）を使いました。

エクスポートは 1 行が 1 つの イベント（ページの表示や購入など、ユーザーの 1 回の操作の記録）です。購入は purchase という名前のイベントとして 1 行で記録されます。

購入の 1 行の中には、買った商品の一覧が items という 配列（同じ形のデータを 1 つの欄に並べて入れたもの）で入っています。公式のスキーマ（データの項目の一覧）では、items は「商品アイテムごとに繰り返される」項目と説明されています（Google アナリティクス ヘルプ: BigQuery Export スキーマ）。

売上の項目は 2 つある

商品別の売上で迷うのは、売上の項目が 2 か所にあるためです。同じスキーマの説明を、表にまとめました。

項目	場所	意味
purchase_revenue	ecommerce の中	その購入イベント全体の収益
item_revenue	items の中	商品 1 つぶんの収益（価格 × 数量）
quantity	items の中	商品の数量。指定が無ければ 1

正式な項目名は、イベント全体の売上が ecommerce.purchase_revenue、商品ごとの売上が items.item_revenue です。どちらも購入イベントにだけ入り、現地通貨で表されます（Google アナリティクス ヘルプ: BigQuery Export スキーマ）。サンプルのデータには通貨の単位が書かれていないため、この記事でも金額に単位を付けません。

この記事で「商品の行数」と呼ぶのは、items の配列に入っている要素の数です。同じ商品を 3 個買っても、1 つの要素の quantity が 3 になるだけなので、行数は 1 です。

items を開くと 1 行が何行にもなる

商品別に集計するには、items の配列を開いて、商品ごとの行にする必要があります。BigQuery では UNNEST（配列の各要素を 1 行ずつのテーブルにする関数）を使います（Google Cloud: BigQuery の配列の操作）。

FROM テーブル, UNNEST(items) のようにカンマでつなぐと、元の行のほかの列を保ったまま配列を開けます。公式の説明では、配列から作った行は、元のテーブルの 1 行と結合されます（Google Cloud: BigQuery の配列の操作）。元の行の列は、開いた行の数だけ同じ値で繰り返されるということです。

図 1 のとおり、商品 3 つの購入を開くと 3 行になり、イベント全体の売上が 3 行すべてに並びます（金額は説明のための例です）。items を開いた後にイベント全体の売上を足すと、商品の行数の回数だけ同じ売上を足してしまいます。

AI への質問 3 通りと、返ってきた SQL

筆者は AI（Claude Sonnet 5.5）に、商品別の売上のランキングを出す SQL を 3 通りの質問文で頼みました。答えたのは調査を担当した AI 自身で、各質問は 1 回ずつです。

答えた AI は、この記事のテーマ（UNNEST の後の売上の二重計上）を知った上で答えています。そのため、正しい項目を選んだ今回の結果は、何も知らない AI でも同じになるという証拠にはなりません。同じ質問でも、別の AI や別の回では違う項目を選ぶ可能性があります。

質問 1：売上の項目を書かない

GA4 の BigQuery エクスポート（bigquery-public-data.ga4_obfuscated_sample_ecommerce）で、
2020年12月の商品別の売上ランキングを出す BigQuery の SQL を書いてください。

返ってきた SQL は次のとおりです。

SELECT item_name, SUM(item_revenue) AS revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`, UNNEST(items)
WHERE event_name = 'purchase'
GROUP BY item_name
ORDER BY revenue DESC
LIMIT 3

この回の AI は、items の中の item_revenue を選びました。ただし、なぜその項目を選んだのかは答えの中で説明されていません。

質問 2：商品ごとの売上の項目を指定する

GA4 の BigQuery エクスポートで、2020年12月の商品別の売上ランキングを出す SQL を書いてください。
売上は、items の配列の中の商品ごとの売上の項目（item_revenue）を使ってください。
イベント全体の売上（ecommerce.purchase_revenue）は足さないでください。

返ってきた SQL は、質問 1 の答えと同じでした。

質問 3：検算の SQL も頼む

GA4 の BigQuery エクスポートで、2020年12月の商品別の売上ランキングを出す SQL を書いてください。
売上は items の中の item_revenue を使い、ランキングの SQL に加えて、
商品別の合計が、全体の売上（ecommerce.purchase_revenue の合計）と一致するかを確かめる SQL も書いてください。

ランキングの SQL は質問 1 の答えと同じで、加えて次の検算の SQL が返ってきました。

WITH item_sales AS (
  SELECT item_name, SUM(item_revenue) AS item_total
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`, UNNEST(items)
  WHERE event_name = 'purchase'
  GROUP BY item_name
), total AS (
  SELECT SUM(ecommerce.purchase_revenue) AS event_total
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`
  WHERE event_name = 'purchase'
)
SELECT (SELECT SUM(item_total) FROM item_sales) AS sum_items,
       event_total,
       event_total - (SELECT SUM(item_total) FROM item_sales) AS diff
FROM total

3 通りの答えのまとめ

質問	足した項目	検算の SQL
1. 項目を書かない	item_revenue	なし
2. 項目を指定	item_revenue	なし
3. 検算も頼む	item_revenue	あり

今回は 3 通りとも正しい項目を選び、二重に足す SQL は返ってきませんでした。ただし、二重計上の問題を知っている AI の 1 回ずつの答えなので、AI に任せれば安全だという根拠にはなりません。そこで比較のために、UNNEST の後に ecommerce.purchase_revenue を足す誤った SQL を筆者が自分で作り、同じデータで実行しました。この記事の 4.43 倍は、この誤った SQL を実行した結果で、AI の答えから出た数字ではありません。

検算：全体の売上と、商品別の合計は一致するか

筆者は BigQuery で、次の 3 通りの足し方を実行しました。1 つ目は items を開かずに数える全体の売上、2 つ目と 3 つ目は items を開いた後の合計です。

-- 1. 全体の売上（items を開かない）
SELECT COUNT(*) AS purchase_events,
  SUM(ecommerce.purchase_revenue) AS rev,
  SUM(ARRAY_LENGTH(items)) AS item_rows
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`
WHERE event_name = 'purchase';

-- 2 と 3. items を開いた後に、2 つの項目をそれぞれ足す
SELECT SUM(ecommerce.purchase_revenue) AS double_rev,  -- 誤った足し方（比較用）
  SUM(i.item_revenue) AS item_rev                       -- 商品ごとの売上
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`, UNNEST(items) AS i
WHERE event_name = 'purchase';

1 つ目の結果は、purchase イベントが 2,434 件、売上の合計が 160,555、items を開いた後の行数が 6,869 行でした。

図 2 の値を表にしました。全体に対する比は、筆者が各合計を 160,555 で割って計算しました。

足し方	合計	全体に対する比
全体の売上（開かずに足す）	160,555	1
商品ごとの売上を足す	160,498	0.9996
開いた後にイベントの売上を足す	711,567	4.43

商品ごとの売上を足した合計は、全体の売上より 57 小さくなりました。全体の 0.04% で、AI が書いた検算の SQL でも diff=57 と同じ値が出ました。商品ごとの売上を足しても、このサンプルでは全体の売上とぴったり一致せず、57 のずれが残りました。

ずれの中身も調べました。売上が空でない購入 2,340 件のうち 406 件（筆者の計算で 17.35%）で、イベント全体の売上と、商品ごとの売上の合計が一致しませんでした。差の絶対値の平均は、筆者の計算で約 1.1（459 ÷ 406）でした。ただし、差が出る原因（丸め・割引・税など）は公式の説明では確認できませんでした。

一方、UNNEST の後にイベント全体の売上を足した合計は 711,567 で、全体の 4.43 倍でした。余分に足された額は 711,567 − 160,555 = 551,012 で、本来の売上の 3 倍以上です。

商品 1 つで見ると何倍になるか

ランキングの 1 位でも比べました。Google Crewneck Sweatshirt Navy（紺のスウェット）は、商品ごとの売上を足すと 5,665、イベント全体の売上を足すと 14,319 でした。筆者の計算で約 2.53 倍です。

全体の倍率（4.43 倍）と、この商品の倍率（2.53 倍）は同じではありません。商品ごとの倍率は、その商品がどんな購入（ほかに何をいくつ一緒に買ったか）に入っていたかで変わると考えられます。商品別の誤った合計は一律の倍率では膨らまないため、ランキングの順位まで入れ替わるおそれがあります。

ずれの理由：1 回の購入に商品が複数あると、何が何回足されるか

二重に足される回数は、1 回の購入に入っている商品の行数で決まります。筆者は items の要素数ごとに購入件数を数えました。

図 3 の値を表にしました。割合は、筆者が購入件数 2,434 件で割って計算しました。

商品の行数	購入件数	割合	開いた時に売上が足される回数
1 行	920	37.8%	1 回（ずれない）
2 行	559	23.0%	2 回
3 行	338	13.9%	3 回
4 行以上	617	25.3%	4 回以上
合計	2,434	100%

2 行以上の購入は 559 + 338 + 617 = 1,514 件で、全体の 62.2% です。これらの購入では、UNNEST の後にイベント全体の売上が 2 回以上足されます。4 行以上のグループは、筆者が全体の行数から計算すると平均で約 6.19 行（3,817 行 ÷ 617 件）でした。

4.43 倍は「売上で重みを付けた行数の平均」

1 回の購入あたりの行数を単純に平均すると、6,869 行 ÷ 2,434 件 = 2.82 行です。それなのに誤った合計が 2.82 倍ではなく 4.43 倍になったのは、足される回数が購入ごとに違うからです。

UNNEST の後にイベント全体の売上を足すと、それぞれの購入の売上が、その購入の行数の回数だけ足されます。式にすると、誤った合計は「購入ごとの売上 × 行数」の総和です。これを全体の売上 160,555 で割った 4.43 は、売上の大きい購入ほど重く数えた、行数の平均にあたります。

誤った合計 = Σ（購入ごとの売上 × その購入の行数）= 711,567
全体の売上 = Σ（購入ごとの売上）                    = 160,555
711,567 ÷ 160,555 = 4.43  ← 売上で重みを付けた行数の平均
6,869 ÷ 2,434     = 2.82  ← 重みを付けない行数の平均

重みを付けた平均が単純な平均より大きいので、商品の行数が多い購入ほど売上も大きい傾向があると考えられます。ただし、行数のグループ別の売上は今回集計しておらず、確認できていません。

揃う質問文の書き方と、検算の SQL のひな形

今回の AI は、項目を書かない質問 1 でも item_revenue を選びました。とはいえ 1 回だけの結果なので、毎回そうなるとは言えません。項目名と検算の依頼を、最初から質問文に書いておくと安全です。

質問 3 の文をもとに、次の型にしました。

GA4 の BigQuery エクスポートで、＜期間＞の商品別の売上ランキングを出す SQL を書いてください。
・売上は items の中の item_revenue を足してください。
・items を UNNEST した後に ecommerce.purchase_revenue を足さないでください（商品の数だけ重複するため）。
・商品別の合計と、UNNEST しないで足した ecommerce.purchase_revenue の合計を並べ、差も出す SQL を付けてください。

AI が書いた SQL は、次の検算の SQL で確かめます。全体の売上（開かない）・商品ごとの売上の合計・開いた後のイベントの売上の合計の 3 つを 1 回で並べます。

WITH purchases AS (
  SELECT ecommerce.purchase_revenue AS event_rev, items
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`
  WHERE event_name = 'purchase'
)
SELECT
  (SELECT SUM(event_rev) FROM purchases) AS event_total,          -- 全体の売上
  (SELECT SUM(i.item_revenue) FROM purchases, UNNEST(items) AS i) AS item_total,  -- 商品ごとの売上
  (SELECT SUM(event_rev) FROM purchases, UNNEST(items) AS i) AS unnest_event_total  -- 二重に足した場合

AI の答えの合計は、ランキングの SQL から全商品の売上を足して出します。ランキングの SQL に LIMIT がある場合は、外してから全商品の売上を合計します。item_total が event_total とほぼ同じで、AI の答えの合計もそれに近ければ、商品ごとの売上を足しています。AI の答えの合計が unnest_event_total に近ければ、イベント全体の売上を何回も足しています。

図 4 の順番で進めると、合計の比べ方だけで足し方の誤りに気づけます。商品別の合計が全体の売上の何倍にもなったら、足した項目が item_revenue かどうかを最初に確かめてください。

同じ「合計が全体と合うか」の考え方は、ほかの集計でも使えます。セッション数や購入率の SQL を検算した例は AI に書かせた GA4 の BigQuery SQL を検算した記事、人数の合計がずれた例は GA4 の流入元別集計を AI に書かせて検算した記事にまとめています。

つまずきやすい点：匿名化されたサンプルの欠けた値

公開サンプルは 難読化（元のデータが分からないように値を置き換えたり伏せたりする加工）されています。一部の項目には <Other>・NULL・空の文字列などの代わりの値が入り、データの内部の整合性が少し損なわれている可能性があると、公式に注意書きがあります（Google for Developers: ウェブ e コマースのサンプルデータセット）。

今回のデータでも、次の欠けた値や重複が見つかりました。

見つかったこと	件数
売上が空の購入イベント	94 件
取引 ID が空か (not set) の購入	94 件
商品の売上が NULL の行	94 行
同じ取引 ID の購入が複数ある ID	28 件
全体と商品の合計が合わない購入	406 件

売上が空の 94 件では、items の中の item_revenue も NULL で、商品名は (not set) でした。SUM は NULL を飛ばすため合計は変わりませんが、件数や平均を出す時は、分母に入るかどうかで結果が変わります。

transaction_id（取引 ID。1 回の注文を表す番号）が同じ購入イベントが複数ある ID も 28 件ありました。同じ注文が 2 回記録されていれば、全体の売上そのものが多めに出ている可能性があります。重複を除いた売上の合計は、今回は計算できていません。

もう 1 つの注意は、サンプルの商品の行数の分布が、実際の EC サイトとは違う可能性があることです。自社のデータで誤った足し方をした時の倍率は 4.43 倍とは限らないため、毎回、全体の売上との比を自分で計算してください。

AI に SQL を見直させる時の質問文は、AI に GA4 の SQL をレビューさせるプロンプトを試した記事も参考になります。

まとめ

• 商品別の売上は items の中の item_revenue を足します。UNNEST の後に ecommerce.purchase_revenue を足すと、このサンプルの 2020 年 12 月分では合計が 4.43 倍（711,567）になりました。
• 倍率は、商品が 2 行以上ある購入（62.2%）で同じ売上が行数の回数だけ足されることから生まれます。4.43 は、売上で重みを付けた行数の平均です。
• 今回の AI は 3 通りとも item_revenue を選びましたが、二重計上の問題を知った AI の 1 回ずつの答えです。また、正しい足し方でも全体と 57（0.04%）ずれました。質問文に項目名と検算の依頼を書き、全体の売上との差の大きさで判断します。

次にやること

商品別の売上をどう見せるかの参考には、ダッシュボードの一覧で、このサイトが公開データから作ったダッシュボードの例をご覧ください。AI が書いた商品別の SQL が正しい項目を足しているかを第三者の目で確かめたい場合は、BigQuery SQL レビューパックもご利用いただけます。

参考にした一次情報

• Google アナリティクス ヘルプ: BigQuery Export スキーマ（2026-11-14 確認。items・ecommerce.purchase_revenue・items.item_revenue・items.quantity・ecommerce.total_item_quantity）
• Google for Developers: Google アナリティクス 4 の BigQuery サンプル データセット（ウェブ e コマース）（2026-11-14 確認。期間、難読化と代わりの値）
• Google Cloud: BigQuery の配列の操作（2026-11-14 確認。UNNEST、相関結合、カンマでの結合）