🎯BigQueryでGoogle広告×GA4データを結合してキーワード別の「真のROAS」を計算する

はじめに

Google広告の管理画面を開くと、キャンペーンごとにROASが表示されます。「このキャンペーンはROAS 800%、めちゃくちゃ優秀だ」と判断して予算を増やしたくなる、あの数字です。でも、その数字を鵜呑みにして予算配分を決めるのは、けっこう危ういんです。

理由はシンプルで、管理画面のROASは「クリックベース・媒体の自己申告」だからです。Google広告のコンバージョン値は、広告がクリックされてから一定期間（デフォルトで30日など）以内に発生したコンバージョンを、Googleが自分のルールで広告に紐づけて集計したものです。同じ購入を Google広告も Meta広告も「自分の成果」としてカウントしていることは珍しくありませんし、計測タグの設定次第では送信される売上金額がズレていることもあります。

具体例で考えてみます。あるECショップで、Google広告の管理画面では「ブランド名キーワード」のキャンペーンがROAS 1,200%と出ていたとします。一見すると最高の投資先です。ところが、ブランド名で検索する人の多くは、もともと購入を決めていてGoogleで店名を打っただけ。広告がなくても自然検索から買っていた可能性が高い、というのはよくある話です。一方で、商品の悩みを表す「一般キーワード」のキャンペーンは管理画面ROASが低く見えても、新規顧客を連れてきている――こういう構造は、管理画面の数字を眺めているだけでは見えてきません。

そこでこの記事では、GA4の実際の購入イベント（できれば購入金額付き）と広告費データをBigQuery上で結合し、キャンペーン／キーワード単位で「自分のデータに基づくROAS」を計算する方法を、実際のSQLとともに解説します。完璧なアトリビューションを目指すというより、まず「管理画面とは別の物差し」を一本持つことがゴールです。

前提：必要な2つのデータソース

真のROASを計算するには、最低限「売上」と「広告費」の2つがBigQueryに乗っている必要があります。

データ	中身	BigQueryへの入れ方
GA4の購入イベント	purchase イベント、売上金額、流入元（utm/gclid）	GA4のBigQueryエクスポート（無料・標準機能）
Google広告の費用	日付×キャンペーン×キーワード別の費用・クリック数	Data Transfer Service もしくは手動取り込み

GA4側は、管理画面の「BigQueryのリンク」設定をオンにすれば、events_YYYYMMDD という日次テーブルが自動で書き出されます。ここに purchase イベントと購入金額、そして流入元の情報が入ってきます。

広告費側は、Google広告のData Transfer Serviceを使うと、費用やクリック数を自動でBigQueryに同期できます。設定手順は別記事のGoogle広告の費用データをBigQueryに自動取り込みする手順にまとめているので、まだの方はそちらを先に進めてください。この記事では、広告費が ads_cost のような形でテーブル化されている前提で進めます。

💡 補足

GA4のBigQueryエクスポートは、過去にさかのぼってのデータ取り込みはできません。「リンクを設定した日以降」のデータだけが蓄積されます。ROAS分析をやると決めたら、まずエクスポートだけでも先にオンにしておくのがおすすめです。

設計：どのキーで結合するか

ここが一番のキモであり、落とし穴も多いところです。GA4の購入データと広告費データを「何で突き合わせるか」を決めます。

結合キーの考え方

理想は gclid（Google Click ID）単位での結合ですが、GA4のBigQueryエクスポートには gclid がそのまま入っていないことが多く、現実的には 「日付 × キャンペーン名」 あるいは 「日付 × キャンペーン × キーワード（検索語句）」 での集計レベルの結合が扱いやすいです。

GA4側では、購入イベントに紐づく流入元を、イベント単位の collected_traffic_source RECORD の manual_source / manual_medium / manual_campaign_name / manual_term（Google広告クリックなら gclid も）から取り出します。広告費側は、Google広告のキャンペーン名・キーワードがそのまま入っています。この両者の「キャンペーン名」を揃えるのがポイントです。

💡 補足

GA4のBigQueryエクスポートのスキーマはバージョンによって列名が異なる場合があります。collected_traffic_source をはじめ、実際の列名は最新の公式ドキュメント（BigQuery Export schema）で確認してください。

よくある落とし穴

⚠️ 注意

キャンペーン名は「表記揺れ」で結合が壊れます。GA4側のutmパラメータに入る campaign の値と、広告費テーブル側のキャンペーン名が、全角/半角・大文字小文字・スペースの有無で一致しないと、JOINで売上か費用のどちらかがNULLになり、ROASが正しく出ません。

実務でつまずきやすいポイントを挙げておきます。

• 自動タグ設定（gclid）と手動utmの混在：Google広告で自動タグ設定（auto-tagging）が有効だと、流入は collected_traffic_source.gclid で記録され、manual_campaign_name などの手動utm項目は空になりがちです。手動utmと混ざると名寄せが必要になります。
• 日付のタイムゾーン：GA4のイベント時刻はUTCで記録されることが多く、広告費はアカウントのタイムゾーン基準です。日付をキーにするなら、どちらかに揃える必要があります。
• 通貨：GA4の購入金額は通貨が混在しうるので、単一通貨のショップ以外では換算を考えます。

まずは「日付 × キャンペーン」の粗い粒度で結合し、数字の桁が合うことを確認してから、キーワード粒度に細かくしていくのが安全です。

実SQL：3ステップで真のROASを出す

ここからは実際のクエリです。プロジェクト名・データセット名・テーブル名はご自身の環境に置き換えてください。

① GA4の購入をキャンペーン／キーワード単位で集計

GA4のエクスポートは1イベント1行で、売上は event_params や items の中にネストされ、流入元はイベント単位の collected_traffic_source というRECORDに入っています。UNNEST で売上を展開し、流入元は collected_traffic_source.manual_* から取り出して、purchase イベントの売上をキャンペーン×キーワード単位に集計します。

-- GA4: purchaseイベントを流入元別に集計
WITH ga4_revenue AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS date,
    -- 流入元（utm相当）は collected_traffic_source RECORD から取り出す
    collected_traffic_source.manual_source        AS utm_source,
    collected_traffic_source.manual_medium        AS utm_medium,
    collected_traffic_source.manual_campaign_name AS utm_campaign,
    -- term（検索語句）。実装によっては入らないことがある
    collected_traffic_source.manual_term          AS utm_term,
    -- Google広告クリックは gclid も同RECORDに入る
    collected_traffic_source.gclid                AS gclid,
    -- 購入金額（イベント単位の合計売上）
    (SELECT value.double_value FROM UNNEST(event_params)
       WHERE key = 'value')    AS purchase_value
  FROM
    `your_project.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20260501' AND '20260531'
    AND event_name = 'purchase'
)
SELECT
  date,
  utm_campaign,
  utm_term,
  COUNT(*)                       AS purchases,
  ROUND(SUM(purchase_value), 0)  AS revenue
FROM ga4_revenue
-- Google広告経由の購入だけに絞る（自動タグ設定だと medium は cpc になる）
WHERE utm_medium = 'cpc'
GROUP BY date, utm_campaign, utm_term

💡 補足

売上金額は purchase イベントの value パラメータに入っているのが標準ですが、実装によっては items 配列の中の item_revenue を合計しないと正しい売上にならないケースもあります。商品単位で見たいときは UNNEST(items) を使って SUM(item.item_revenue) で集計してください。

items を展開する場合は、こんな形になります。

-- 商品配列(items)から売上を積み上げる場合
SELECT
  PARSE_DATE('%Y%m%d', event_date) AS date,
  collected_traffic_source.manual_campaign_name AS utm_campaign,
  SUM(item.item_revenue)     AS revenue
FROM
  `your_project.analytics_123456789.events_*`,
  UNNEST(items) AS item
WHERE
  _TABLE_SUFFIX BETWEEN '20260501' AND '20260531'
  AND event_name = 'purchase'
GROUP BY date, utm_campaign

② 広告費をキャンペーン／キーワード単位で集計

次に広告費です。Data Transfer Serviceで取り込んだテーブルは、費用がマイクロ単位（実費用 × 1,000,000）で入っていることが多いので、/ 1e6 で円に戻します。テーブル構造は取り込み方法によって変わるので、列名はご自身の環境に合わせてください。

-- Google広告: 日付×キャンペーン×キーワードで費用を集計
WITH ads_cost AS (
  SELECT
    _DATA_DATE                       AS date,
    campaign_name                    AS utm_campaign,
    criteria                         AS utm_term,   -- 検索キーワード
    SUM(metrics_cost_micros) / 1e6   AS cost,
    SUM(metrics_clicks)              AS clicks
  FROM
    `your_project.google_ads.KeywordBasicStats_123456789`
  WHERE
    _DATA_DATE BETWEEN '2026-05-01' AND '2026-05-31'
  GROUP BY date, campaign_name, criteria
)
SELECT * FROM ads_cost

③ 結合して真のROASを算出

最後に、①の売上と②の費用を「日付 × キャンペーン × キーワード」で結合します。費用はあるのに売上が紐づかない行（NULL）も見たいので、広告費側を基準にした LEFT JOIN にします。

-- 真のROAS = GA4実売上 / 広告費
WITH ga4_revenue AS (
  -- （①のクエリと同じ：purchaseをキャンペーン×キーワードに集計）
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS date,
    collected_traffic_source.manual_campaign_name AS utm_campaign,
    collected_traffic_source.manual_term          AS utm_term,
    SUM((SELECT value.double_value FROM UNNEST(event_params)
           WHERE key = 'value')) AS revenue
  FROM `your_project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260501' AND '20260531'
    AND event_name = 'purchase'
  GROUP BY date, utm_campaign, utm_term
),
ads_cost AS (
  -- （②のクエリと同じ：費用を集計）
  SELECT
    _DATA_DATE                     AS date,
    campaign_name                  AS utm_campaign,
    criteria                       AS utm_term,
    SUM(metrics_cost_micros) / 1e6 AS cost
  FROM `your_project.google_ads.KeywordBasicStats_123456789`
  WHERE _DATA_DATE BETWEEN '2026-05-01' AND '2026-05-31'
  GROUP BY date, campaign_name, criteria
)
SELECT
  a.utm_campaign,
  a.utm_term,
  ROUND(SUM(a.cost), 0)                 AS total_cost,
  ROUND(SUM(g.revenue), 0)              AS total_revenue,
  -- 真のROAS（%表記）。費用ゼロ割りを防ぐ
  ROUND(SAFE_DIVIDE(SUM(g.revenue), SUM(a.cost)) * 100, 0) AS true_roas_pct
FROM ads_cost a
LEFT JOIN ga4_revenue g
  ON  a.date         = g.date
  AND a.utm_campaign = g.utm_campaign
  AND a.utm_term     = g.utm_term
GROUP BY a.utm_campaign, a.utm_term
ORDER BY total_cost DESC

出力イメージはこんな形です（数字はすべて説明用の例です）。

utm_campaign	utm_term	total_cost	total_revenue	true_roas_pct
一般-悩み系	〇〇 おすすめ	80,000	520,000	650
ブランド名	△△ 公式	30,000	360,000	1,200
一般-比較系	〇〇 比較	60,000	90,000	150

管理画面では横並びに見えていたキャンペーンも、自社の実売上で割り直すと差がはっきり出てきます。true_roas_pct が極端に低い行は、費用は出ているのにGA4側で売上が紐づいていない（=計測漏れ、または本当に成果が薄い）候補として、優先的に調べる価値があります。

アトリビューションの注意：これは「完璧な真実」ではない

ここで計算したROASは、あくまで 「GA4のアトリビューションを物差しにした数字」 です。万能の正解ではないことは、はっきり意識しておいてください。

• GA4の流入元は、デフォルトではセッション単位の判定で、複数チャネルをまたいだ購入では「最後に効いたチャネル」寄りに評価されがちです。
• この記事のクエリは、購入が発生したセッションの流入元で単純に紐づける、いわばラストクリックに近い見方です。広告クリックは別日で、購入はオーガニック再訪で起きた、というケースは取りこぼします。
• 逆に言えば、管理画面の「クリックベースの自己申告ROAS」とは別の角度からの数字なので、2つを並べて見ること自体に価値があります。

💡 補足

数字の桁が大きくズレるとき（GA4の売上が管理画面の半分しかない等）は、ROASの優劣を語る前に「計測そのもの」を疑ってください。GA4側のコンバージョン設定漏れ、utmの名寄せミス、タイムゾーンのズレが定番の原因です。

可視化まで一気にやりたい場合は、ここで作ったテーブルをそのままLooker Studioにつなぐと運用が楽になります。複数ソースのブレンディングはLooker StudioでGA4と広告データをブレンドする方法に、ROAS/CPAをグラフ上で計算する方法はLooker Studioのカスタム指標でROAS・CPAを出すにまとめています。

まとめ

管理画面のROASは便利ですが、「クリックベース・媒体の自己申告」という性質上、特にブランド名キーワードなどで過大評価が起きやすい数字です。自社のGA4実売上と広告費をBigQueryで結合すれば、もう一本の物差しを持てます。

要点を整理します。

ポイント	内容
データ準備	GA4 BigQueryエクスポート＋広告費の自動取り込み
結合キー	まず「日付×キャンペーン」、慣れたら「×キーワード」
最大の落とし穴	キャンペーン名の表記揺れ・タイムゾーン・通貨
解釈	これはラストクリック寄りの「別物差し」であり万能解ではない

最初は「日付×キャンペーン」の粗い粒度で、GA4売上と管理画面の桁が合うかを確認するところから始めるのがおすすめです。そこさえクリアできれば、キーワード粒度に落として「どの検索語句が本当に売上を生んでいるか」を自分の手で確かめられるようになります。同じ考え方はMeta広告にも応用できるので、クリエイティブ別に見たい方はMeta広告×GA4でクリエイティブ別ROASを出す方法もどうぞ。