🔗BigQueryでEC受注データ×GA4データを結合して正確な売上帰属分析をする

はじめに：GA4の売上とカート/受注システムの売上が合わない／流入元に紐づかない

ECサイトを運営していると、こんな違和感にぶつかることがあります。

• GA4の「収益」と、受注システム（カートASPや基幹システム）の売上金額が合わない
• 受注データには正確な売上が載っているのに、その注文が「どの流入元から来たのか」が分からない
• 広告の費用対効果を出したいのに、頼れる売上データが2系統あって、どちらを信じればいいか判断できない

これらは多くの中小EC・個人事業主が抱える共通の悩みです。原因はシンプルで、「正確な売上を持つデータ」と「正確な流入経路を持つデータ」が別々のシステムに分かれているからです。

この記事では、BigQuery上でEC受注データとGA4データを結合し、「どのチャネルが、いくらの売上を生んだか」を正確に出す方法を解説します。SQLのサンプルも載せるので、自社データに当てはめながら読み進めてみてください。

なお、GA4からBigQueryへのエクスポート設定がまだの場合は、先にGA4管理画面の「BigQueryのリンク」を済ませておく必要があります。エクスポートされた events_YYYYMMDD テーブルが揃っていることを前提に話を進めます。

なぜ受注データ（正）とGA4（行動）を結合するのか

そもそも、なぜ片方だけではダメなのでしょうか。それぞれの得意・不得意を整理すると分かりやすくなります。

データソース	得意なこと	苦手なこと
受注データ（カート／基幹）	正確な売上金額、キャンセル・返品の反映、顧客情報	流入元・広告・行動の情報を持たない
GA4	流入チャネル、広告、ページ遷移、デバイス	売上金額がズレやすい（計測漏れ・二重計上・税送料の扱い）

つまり、金額の「正」は受注データ、流入経路の「正」はGA4という役割分担になっています。GA4の売上をそのまま信じると、計測漏れや二重計上のぶんだけ実態とズレます。一方で受注データだけでは「この注文がどこから来たか」が永遠に分かりません。

両者を結合すると、「受注データの正確な金額」に「GA4の流入経路」を貼り付けることができます。これで初めて、信頼できる売上ベースのチャネル別アトリビューションが出せるようになります。

結合の基本方針

結合の方針はこうです。

1. 金額は受注データを正とする（GA4の purchase イベントの金額は使わない、あるいは検証用にとどめる）
2. 流入経路はGA4を正とする（受注データには流入元が無いため）
3. 両者を transaction_id / order_id で1対1に突合する

このとき、GA4側で purchase イベントに正しい注文番号（受注データの主キーと一致するID）を渡しておくことが、すべての前提になります。ここがズレていると後の分析がすべて崩れるので、計測実装の段階で最も注意したいポイントです。

結合キーの設計（transaction_id / order_id でGA4 purchaseと受注を突合）

GA4の purchase イベントには、ecommerce.transaction_id というパラメータがあります。EC計測を正しく実装していれば、ここに受注システムの注文番号がそのまま入っているはずです。これを結合キーにします。

注意したいのは、GA4のBigQueryエクスポートでは transaction_id がイベントパラメータの中にネストされて格納されている点です。event_params の配列から取り出す必要があります。

GA4側：purchaseイベントから注文番号と流入元を取り出す

まずGA4のエクスポートテーブルから、注文ごとに「流入チャネル」を1行にまとめておきます。

-- GA4: purchaseイベントごとに transaction_id と流入元を抽出
WITH ga4_purchase AS (
  SELECT
    -- event_params から transaction_id を取り出す
    (SELECT ep.value.string_value
       FROM UNNEST(event_params) ep
      WHERE ep.key = 'transaction_id') AS transaction_id,
    traffic_source.source  AS source,
    traffic_source.medium  AS medium,
    -- セッション単位の流入チャネルを使いたい場合は session_traffic_source_last_click を参照
    TIMESTAMP_MICROS(event_timestamp) AS purchase_ts
  FROM `your_project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630'
    AND event_name = 'purchase'
)
SELECT * FROM ga4_purchase
WHERE transaction_id IS NOT NULL

traffic_source はユーザー獲得時（ファーストタッチ）の流入元です。「購入したセッションの流入元」を見たい場合は、GA4エクスポートの新しめのフィールドである session_traffic_source_last_click を使うとラストクリック基準で取得できます。アトリビューションのモデルとして何を採用したいかで使い分けてください。

受注データ側：正確な売上を持つテーブルを用意する

受注データはCSVやデータ転送でBigQueryに取り込んでおきます。最低限、注文番号・確定売上金額・注文日時・ステータスがあれば十分です。

-- 受注: 有効な注文だけを正の売上として抽出
WITH orders AS (
  SELECT
    order_id,                         -- GA4の transaction_id と一致する注文番号
    net_revenue,                      -- 税送料を除いた確定売上など、定義をそろえた金額
    ordered_at,                       -- 注文確定日時（タイムゾーンに注意）
    status
  FROM `your_project.ec.orders`
  WHERE status NOT IN ('cancelled', 'returned')   -- キャンセル・返品は除外
    AND DATE(ordered_at) BETWEEN '2026-06-01' AND '2026-06-30'
)
SELECT * FROM orders

結合：受注を正、GA4を流入情報として貼り付ける

あとは注文番号で突合します。受注データを LEFT JOIN の左側に置くのがポイントです。こうすると「受注はあるがGA4に紐づかなかった注文（計測漏れ）」も漏れなく把握できます。

-- 受注（正）に GA4 の流入情報を結合する
SELECT
  o.order_id,
  o.net_revenue,
  o.ordered_at,
  -- 流入が取れなかった注文は (direct)/(none) として扱う
  IFNULL(g.source, '(direct)') AS source,
  IFNULL(g.medium, '(none)')   AS medium
FROM orders o
LEFT JOIN ga4_purchase g
  ON o.order_id = g.transaction_id

GA4側に重複（同じ transaction_id が複数行）がある場合は、結合前に1注文1行へ集約しておくと安全です。後述しますが、purchase イベントの二重計上はよくあるトラブルです。

結合後にチャネル別の正確な売上帰属を出す

結合できれば、あとは集計するだけです。受注データの正確な金額を、GA4の流入チャネルでグルーピングします。

WITH joined AS (
  SELECT
    o.order_id,
    o.net_revenue,
    g.transaction_id             AS matched_transaction_id,  -- 結合できなければ NULL
    IFNULL(g.source, '(direct)') AS source,
    IFNULL(g.medium, '(none)')   AS medium
  FROM orders o
  LEFT JOIN (
    -- transaction_id ごとに1行へ集約（重複対策）
    SELECT transaction_id, ANY_VALUE(source) AS source, ANY_VALUE(medium) AS medium
    FROM ga4_purchase
    GROUP BY transaction_id
  ) g
  ON o.order_id = g.transaction_id
)
SELECT
  source,
  medium,
  COUNT(*)              AS orders,
  SUM(net_revenue)      AS revenue,
  ROUND(AVG(net_revenue)) AS avg_order_value,
  -- GA4に結合できなかった注文の比率（計測の健全性チェック）
  ROUND(COUNTIF(matched_transaction_id IS NULL) / COUNT(*) * 100, 1) AS unmatched_pct
FROM joined
GROUP BY source, medium
ORDER BY revenue DESC

これで出てくる revenue は、GA4の収益ではなく受注データの確定売上です。つまり「Google広告経由の確定売上は◯円」「自然検索経由は◯円」という、経理上の数字と整合する売上帰属が手に入ります。

unmatched_pct（GA4に紐づかなかった注文の割合）も一緒に出しておくと便利です。この比率が高いほど計測実装に問題があるサインなので、分析の信頼度を測る指標として常に確認しておきましょう。

広告の費用と突き合わせて真のROASを出したい場合は、こちらの記事も参考にしてください。

• BigQueryでGoogle広告×GA4を結合してキーワード別の真のROASを出す

注意：transaction_id の付与漏れ・重複・タイムゾーン

結合分析でつまずく原因は、ほぼこの3つに集約されます。先に潰しておきましょう。

1. transaction_id の付与漏れ サンクスページの計測タグに注文番号が渡っていないと、transaction_id が空のまま purchase が飛びます。空のIDは受注データと結合できず、すべて (direct) に流れ込みます。計測実装後は、必ずGA4のDebugViewやBigQueryで「transaction_id が NULL の purchase」がどれくらいあるか確認してください。

2. transaction_id の重複・二重計上 ユーザーがサンクスページをリロードしたり、ブラウザバックで戻ったりすると、同じ transaction_id で purchase が複数回飛ぶことがあります。集約せずに JOIN すると売上が二重三重に膨らむので、結合前に transaction_id 単位で1行へ集約しておくのが安全です。

3. タイムゾーンのズレ GA4エクスポートの event_timestamp はUTCのマイクロ秒です。一方、受注データの日時はJST（日本時間）で記録されていることが多く、そのまま日付で範囲を切ると最大9時間ぶんの境界がズレます。月初・月末の集計が合わない原因はたいていこれです。比較の際は DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') のように、片方をJSTへそろえてから日付を扱いましょう。

このあたりの「データが合わない」原因を切り分ける感覚は、ページ単位の行動分析でも共通します。離脱の分析については以下も合わせてどうぞ。

• GA4×BigQueryで商品ページの離脱率を正しく測る

まとめ

EC受注データとGA4を結合する分析のポイントを振り返ります。

• GA4の売上と受注システムの売上が合わないのは、金額の「正」と流入の「正」が別システムに分かれているから
• 金額は受注データを正、流入経路はGA4を正として、transaction_id / order_id で突合する
• 受注データを LEFT JOIN の左側に置けば、計測漏れの注文も取りこぼさない
• 結合後はチャネル別に集計するだけで、経理と整合する正確な売上帰属が出せる
• つまずきの原因はほぼ「付与漏れ・重複・タイムゾーン」の3つなので、先に潰しておく

「2つの売上が合わない」というモヤモヤは、結合キーをきちんと設計するだけで解消できます。まずは自社の purchase イベントに注文番号が正しく入っているか、その確認から始めてみてください。