BigQuery × Looker Studioで広告媒体横断ROASダッシュボードを構築する全手順

はじめに：媒体ごとに管理画面を開く非効率／横断で見たい

複数の広告媒体を回していると、Google広告の管理画面を開いて、次にMeta広告マネージャを開いて、Yahoo!広告も確認して……という作業を、月末や週次のたびに繰り返すことになります。中小ECや個人事業主のように、運用も分析も自分一人でやっているケースだと、この「画面を行ったり来たりする時間」がそのまま分析の遅れに直結します。

しかも厄介なのが、各媒体の管理画面に出てくる「コンバージョン」や「売上」が、媒体ごとのアトリビューション基準で水増しされがちだという点です。媒体Aと媒体Bでそれぞれ「うちのおかげで10万円売れた」と主張されると、合算すると実売上を超えてしまう、という珍事もよく起きます。

そこで本記事では、各媒体の「費用」だけをBigQuery（以下BQ）に集約し、売上はGA4の実データ一本に統一して結合する、という設計でROASダッシュボードを組みます。費用は媒体側、売上はGA4側、という役割分担にすることで、横断でも筋の通った数字が出せるようになります。

ROAS（Return On Advertising Spend）は「広告経由の売上 ÷ 広告費用」で計算する指標です。本記事では売上をGA4の実測値、費用を各媒体の実費として扱います。

全体設計

構成はシンプルに、次の3層で考えます。

費用レイヤー：各媒体（Google広告・Meta・Yahoo!など）の日次費用をBQの媒体別テーブルに集約する
統合レイヤー：媒体別費用をUNIONで縦に積み、さらにGA4の実売上を日付・媒体で結合した統合テーブルを作る
可視化レイヤー：統合テーブルをLooker Studioに接続し、媒体別ROASや期間比較を表示する

[Google広告] ┐
[Meta]       ├─→ BQ: 媒体別費用テーブル ─┐
[Yahoo!]     ┘                          ├─→ 統合テーブル ─→ Looker Studio
[GA4 → BQ Export] ─→ 日次売上集計 ──────┘

費用データをBQに入れる方法はいくつかあります。

Google広告 / GA4：BigQuery Data Transfer ServiceやGA4のBigQueryエクスポートを使うと、定期的に自動でBQへ取り込めます
MetaやYahoo!：公式の直接エクスポートが乏しいため、各媒体のレポートをCSVでエクスポートしてBQにロードするか、データ連携ツール（ETL系SaaS）を使うのが現実的です

どの経路を使うかは予算と運用体制しだいですが、まずは手動CSVロードでも十分にダッシュボードは作れます。最初から自動化に凝りすぎないのがコツです。

費用テーブルのスキーマを揃える

媒体ごとにカラム名がバラバラだと結合で苦労するので、BQに入れる時点でスキーマを統一しておきます。最低限、次の4つがあれば横断分析が成立します。

カラム	型	説明
`date`	DATE	計上日
`channel`	STRING	媒体名（google / meta / yahoo など）
`campaign`	STRING	キャンペーン名
`cost`	NUMERIC	その日の費用（円・税抜で統一）

媒体名（channel）は、後でGA4側の値と突き合わせることを見越して、表記ゆれが出ないように決め打ちしておきます。ここを最初に揃えておくと、後工程の名寄せがぐっと楽になります。

統合テーブルを作るSQL例

ここからが本記事の中心です。媒体別の費用テーブルをUNIONで縦に積み、GA4の日次売上を結合して、1つの統合テーブルにまとめます。

まずは費用側を縦に積みます。媒体ごとにテーブルが分かれている前提です。

-- 各媒体の費用を UNION ALL で1本にまとめる
WITH cost_unified AS (
  SELECT date, 'google' AS channel, campaign, cost
  FROM `myproject.ads.google_cost`
  UNION ALL
  SELECT date, 'meta' AS channel, campaign, cost
  FROM `myproject.ads.meta_cost`
  UNION ALL
  SELECT date, 'yahoo' AS channel, campaign, cost
  FROM `myproject.ads.yahoo_cost`
)
SELECT date, channel, SUM(cost) AS cost
FROM cost_unified
GROUP BY date, channel

次に、GA4のBigQueryエクスポート（events_*テーブル）から、媒体別・日次の購入金額を集計します。GA4側の媒体名はtraffic_sourceや各イベントのsource / mediumパラメータに入っているので、これを自前のchannel表記に寄せます。

-- GA4 events から日次・媒体別の購入金額を集計
WITH ga4_revenue AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS date,
    CASE
      WHEN traffic_source.source = 'google' AND traffic_source.medium = 'cpc' THEN 'google'
      WHEN traffic_source.source IN ('facebook', 'instagram', 'meta') THEN 'meta'
      WHEN traffic_source.source = 'yahoo'  THEN 'yahoo'
      ELSE 'other'
    END AS channel,
    SUM(ecommerce.purchase_revenue) AS revenue
  FROM `myproject.analytics_123456789.events_*`
  WHERE event_name = 'purchase'
    AND _TABLE_SUFFIX BETWEEN '20260601' AND '20260630'
  GROUP BY date, channel
)
SELECT * FROM ga4_revenue

最後に、費用と売上をdate × channelで外部結合し、ROASを計算した統合テーブルを作ります。費用だけある日・売上だけある日の両方を取りこぼさないようFULL OUTER JOINにしておくのが安全です。

-- 費用と売上を結合して ROAS を持つ統合テーブルを作成
CREATE OR REPLACE TABLE `myproject.mart.cross_channel_roas` AS
WITH cost AS (
  SELECT date, channel, SUM(cost) AS cost
  FROM `myproject.mart.cost_unified`
  GROUP BY date, channel
),
revenue AS (
  SELECT date, channel, SUM(revenue) AS revenue
  FROM `myproject.mart.ga4_revenue`
  GROUP BY date, channel
)
SELECT
  COALESCE(c.date, r.date)       AS date,
  COALESCE(c.channel, r.channel) AS channel,
  IFNULL(c.cost, 0)              AS cost,
  IFNULL(r.revenue, 0)           AS revenue,
  SAFE_DIVIDE(IFNULL(r.revenue, 0), NULLIF(c.cost, 0)) AS roas
FROM cost AS c
FULL OUTER JOIN revenue AS r
  ON c.date = r.date
 AND c.channel = r.channel

SAFE_DIVIDEとNULLIFを組み合わせることで、費用ゼロの日にROASがエラーや無限大にならず、すっきりNULLになります。Looker Studio側での表示も安定するので、この書き方をおすすめします。

なお、このSQLは日次スケジュールクエリ（Scheduled Queries）として登録しておくと、毎朝自動で統合テーブルを更新できます。手動運用に疲れてきたら、ここから自動化を進めるとよいでしょう。

Looker Studio接続とダッシュボード構成

統合テーブルcross_channel_roasができたら、可視化はあっという間です。

Looker Studioで新しいレポートを作成し、データソースとして「BigQuery」を選びます
該当プロジェクト → データセット → cross_channel_roasテーブルを指定して接続します
roasやcostがうまく集計されない場合は、フィールドの集計方法（Sum / Averageなど）を確認します

接続画面やメニューの名称はLooker Studioのアップデートで変わることがあります。細かいUIは最新の画面の表示に従ってください。

ダッシュボードの構成は、次の3ブロックを基本形にすると見やすくまとまります。

1. 媒体別ROASの一覧

channelを行に、cost / revenue / roasを列に置いた表（テーブル）を中心に据えます。ROASは降順ソートにしておくと、効率の良い媒体・悪い媒体がひと目で並びます。

ここで注意したいのが、ROASを「表全体の合計行」で出すときの計算です。媒体ごとのROASを単純平均すると実態とずれるので、合計売上 ÷ 合計費用で再計算される設定（合計値ベース）になっているかを確認しておきましょう。

2. 期間比較

「今月 vs 先月」「今週 vs 先週」を比べられるよう、日付範囲コントロールと「比較期間」の設定を入れます。スコアカード（Scorecard）に前期間比の増減を表示しておくと、費用が伸びたのにROASが落ちている媒体などにすぐ気づけます。

3. フィルタ

channelとcampaignのプルダウンフィルタを上部に配置します。普段は全媒体で俯瞰し、気になったらキャンペーン単位まで掘る、という流れが作れます。日付グラフ（時系列）でROASの推移を重ねると、施策変更の効果も追いやすくなります。

これで「複数の管理画面を開かなくても、1画面で媒体横断のROASが見える」状態が完成します。

注意：媒体ごとの費用定義・名寄せ・更新頻度とコスト

ダッシュボードは作って終わりではありません。横断分析ならではの落とし穴がいくつかあります。

費用の定義を揃える：媒体によって「費用」が税込・税抜で違ったり、プラットフォーム手数料が含まれたり含まれなかったりします。BQに入れる前に、どの定義で統一するかを必ず決めておきましょう。ここがズレると、媒体間のROAS比較そのものが意味を失います。

名寄せ（チャネルマッピング）：GA4側のsource / mediumは、UTMパラメータの付け方しだいで簡単に表記ゆれを起こします。facebook / fb / meta、yahoo / yahoo_ssなどが混在しがちです。CASE式のマッピングは「育てるもの」と考え、otherに落ちた値を定期的に確認して、ルールを足していくのが現実的です。なお、ここで使うtraffic_source.source / traffic_source.mediumは「そのユーザーを最初に連れてきた参照元（ユーザー単位の最初の流入元）」を表す値です。購入イベント時点の流入元とは一致しないことがあるので、ここでも完璧なアトリビューションは目指さず、媒体横断の傾向を掴む割り切りで使いましょう。

更新頻度とコスト：GA4のBigQueryエクスポートやスケジュールクエリは便利ですが、BQはスキャン量に応じて課金されます。events_*へのクエリは必ず_TABLE_SUFFIXで日付を絞り、SELECT *を避けてください。統合テーブルをマート化しておけば、Looker Studio側のスキャン量も抑えられます。連携ツール（ETL系SaaS）を使う場合は、その月額費用が浮いてくるコストにも目を向けておきましょう。

これらは地味ですが、数字の信頼性を左右する土台です。最初に決めておくほど、後の運用が楽になります。

まとめ

媒体横断のROASダッシュボードは、次の3ステップで作れます。

各媒体の費用だけをスキーマを揃えてBQに集約する
費用をUNIONで積み、GA4の実売上をdate × channelでFULL OUTER JOINして統合テーブルを作る
統合テーブルをLooker Studioに接続し、媒体別ROAS・期間比較・フィルタの3ブロックで可視化する

ポイントは「費用は媒体側、売上はGA4側」という役割分担を最初に決めることと、名寄せルールを育て続けることです。ここさえ押さえれば、毎週の管理画面巡回から解放され、数字を見る時間を施策の改善に回せるようになります。

関連する手順は、こちらの記事もあわせてどうぞ。