BigQueryで広告データを分析するSQL実践｜基本クエリから媒体横断分析まで

なぜBigQueryで広告データを分析するのか

広告媒体の管理画面にも集計機能はあります。しかし管理画面でできる分析には限界があります。

まず、媒体横断の比較ができません。Google広告とMeta広告の日別CPAを一つのグラフで並べたい場合、CSVをエクスポートしてスプレッドシートに貼り付ける手順が必要です。媒体数やキャンペーン数が増えると、この手順だけで相当の時間を取られます。

次に、管理画面の集計軸は固定されています。「広告グループ別の週次推移を前週比で見たい」「コンバージョン種別ごとのROASを月次で集計したい」といった独自の切り口は、管理画面の標準機能では対応しきれません。

BigQueryを使えば、SQLひとつでこれらの分析が可能になります。テラバイト級のデータでも数秒で結果が返り、毎月1TBまでの無料枠もあるため、広告データの分析基盤としてコストパフォーマンスに優れています。

運用メモ BigQueryの無料枠（毎月1TBのクエリ、10GBのストレージ）は、広告データの日次サマリーであれば数年分をカバーできる量です。まずは無料枠の範囲で始めて、データ量が増えたタイミングで料金プランを検討するのが現実的です。

広告データをBigQueryに取り込む方法

分析の前提として、各媒体のデータをBigQueryに格納する必要があります。取り込み方法は大きく4つに分かれます。

それぞれの方法の特徴を比較します。

Google広告とGA4は公式連携があるため、すぐに始められます。Meta広告やLINEヤフー広告は、API連携のスクリプトを自前で開発するか、troccoのようなETLツールを導入する形になります。

運用メモ Google広告のData Transfer Serviceは、初回設定時にバックフィル（過去データの取り込み）が可能です。最大で過去の全期間をさかのぼれるため、分析の開始時点で十分なデータ量を確保できます。設定はBigQueryの管理画面から「データ転送」→「転送を作成」で進めます。

広告分析でよく使うSQL基本構文

BigQueryではGoogleの標準SQLを使います。広告データの分析で頻出する構文を整理します。

データの取得と絞り込み

最も基本的な操作は、テーブルからデータを取得し、条件で絞り込むことです。

-- 直近7日間のGoogle広告データを取得する
SELECT
  segments_date AS date,                -- 日付
  campaign_name,                        -- キャンペーン名
  metrics_impressions AS impressions,   -- 表示回数
  metrics_clicks AS clicks,             -- クリック数
  metrics_cost_micros / 1e6 AS cost,    -- 広告費（マイクロ単位→円に変換）
  metrics_conversions AS conversions    -- コンバージョン数
FROM
  `your_project.google_ads.p_ads_CampaignStats_1234567890`
WHERE
  segments_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY
  segments_date DESC, campaign_name

WHERE 句で期間を絞ることで、スキャン対象のデータ量を減らし、クエリの費用を抑えられます。パーティション分割テーブルでは特に効果的です。

集計とグループ化

GROUP BY で集計軸を指定し、SUM や AVG で数値を集約します。

-- キャンペーン別の月間パフォーマンスを集計する
SELECT
  campaign_name,
  SUM(metrics_impressions) AS impressions,
  SUM(metrics_clicks) AS clicks,
  SUM(metrics_cost_micros) / 1e6 AS cost,
  SUM(metrics_conversions) AS conversions,
  SAFE_DIVIDE(SUM(metrics_clicks), SUM(metrics_impressions)) AS ctr,
  SAFE_DIVIDE(SUM(metrics_cost_micros) / 1e6, SUM(metrics_conversions)) AS cpa
FROM
  `your_project.google_ads.p_ads_CampaignStats_1234567890`
WHERE
  segments_date BETWEEN '2026-06-01' AND '2026-06-30'
GROUP BY
  campaign_name
HAVING
  SUM(metrics_conversions) > 0  -- CV発生のあるキャンペーンのみ
ORDER BY
  cost DESC

SAFE_DIVIDE はBigQuery固有の関数です。分母がゼロの場合でもエラーにならず、NULLを返します。CTRやCPAの計算では必ず使いましょう。

日付関数

期間の集計に欠かせない日付関数をまとめます。

CASE WHENによる分類

キャンペーン名やコンバージョンの種類に応じて、データを任意のカテゴリに分類できます。

-- キャンペーン名から施策タイプを分類する
SELECT
  CASE
    WHEN campaign_name LIKE '%brand%' THEN 'ブランド'
    WHEN campaign_name LIKE '%generic%' THEN '一般'
    WHEN campaign_name LIKE '%retargeting%' THEN 'リターゲティング'
    ELSE 'その他'
  END AS campaign_type,
  SUM(metrics_impressions) AS impressions,
  SUM(metrics_clicks) AS clicks,
  SUM(metrics_cost_micros) / 1e6 AS cost,
  SUM(metrics_conversions) AS conversions
FROM
  `your_project.google_ads.p_ads_CampaignStats_1234567890`
WHERE
  segments_date BETWEEN '2026-06-01' AND '2026-06-30'
GROUP BY
  campaign_type
ORDER BY
  cost DESC

キャンペーンの命名規則を統一しておくと、この手法の活用幅が大きく広がります。

運用メモ 命名規則の一例として {媒体}_{施策タイプ}_{ターゲット}_{開始月} の形式があります。例えば META_retargeting_cart_202606 のような命名です。この規則を守ることで、REGEXP_EXTRACT による属性の自動抽出も可能になります。

実践クエリ1：日次パフォーマンスレポート

日別の主要指標を一覧で確認するレポートクエリです。広告費、CV数、CPA、ROASを日次で並べます。

-- 日次パフォーマンスレポート
SELECT
  date,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(cost) AS cost,
  SUM(conversions) AS conversions,
  SUM(conversion_value) AS conversion_value,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SAFE_DIVIDE(SUM(cost), SUM(clicks)) AS cpc,
  SAFE_DIVIDE(SUM(cost), SUM(conversions)) AS cpa,
  SAFE_DIVIDE(SUM(conversion_value), SUM(cost)) AS roas
FROM
  `your_project.ad_data.mart_all_platforms`
WHERE
  date BETWEEN '2026-06-01' AND '2026-06-18'
ORDER BY
  date DESC

出力イメージは以下のとおりです。

このクエリの前提として、各媒体のrawテーブルを統合した mart_all_platforms ビューが存在する想定です。統合ビューの作り方は後述の「媒体横断パフォーマンス比較」で説明します。

実践クエリ2：週次・月次のトレンド分析

週次集計と前週比

DATE_TRUNC で日付を週の開始日に丸めると、週次の集計ができます。さらに LAG 関数で前週のデータを参照し、変化率を算出します。

-- 週次パフォーマンスと前週比
WITH weekly AS (
  SELECT
    DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
    SUM(cost) AS cost,
    SUM(conversions) AS conversions,
    SAFE_DIVIDE(SUM(cost), SUM(conversions)) AS cpa
  FROM
    `your_project.ad_data.mart_all_platforms`
  WHERE
    date >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK)
  GROUP BY
    week_start
)
SELECT
  week_start,
  cost,
  conversions,
  cpa,
  LAG(cost) OVER (ORDER BY week_start) AS prev_week_cost,
  LAG(conversions) OVER (ORDER BY week_start) AS prev_week_cv,
  SAFE_DIVIDE(
    cost - LAG(cost) OVER (ORDER BY week_start),
    LAG(cost) OVER (ORDER BY week_start)
  ) AS cost_change_rate,
  SAFE_DIVIDE(
    conversions - LAG(conversions) OVER (ORDER BY week_start),
    LAG(conversions) OVER (ORDER BY week_start)
  ) AS cv_change_rate
FROM
  weekly
ORDER BY
  week_start DESC

WEEK(MONDAY) を指定すると、月曜始まりで週を区切ります。広告運用では月曜始まりが一般的ですが、業種によっては日曜始まりの場合もあります。

月次集計と前月比

同じ要領で月次集計も作れます。

-- 月次パフォーマンスと前月比
WITH monthly AS (
  SELECT
    DATE_TRUNC(date, MONTH) AS month_start,
    SUM(cost) AS cost,
    SUM(conversions) AS conversions,
    SAFE_DIVIDE(SUM(cost), SUM(conversions)) AS cpa,
    SAFE_DIVIDE(SUM(conversion_value), SUM(cost)) AS roas
  FROM
    `your_project.ad_data.mart_all_platforms`
  WHERE
    date >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
  GROUP BY
    month_start
)
SELECT
  FORMAT_DATE('%Y-%m', month_start) AS month,
  cost,
  conversions,
  cpa,
  roas,
  LAG(cost) OVER (ORDER BY month_start) AS prev_month_cost,
  SAFE_DIVIDE(
    cost - LAG(cost) OVER (ORDER BY month_start),
    LAG(cost) OVER (ORDER BY month_start)
  ) AS cost_mom,
  SAFE_DIVIDE(
    conversions - LAG(conversions) OVER (ORDER BY month_start),
    LAG(conversions) OVER (ORDER BY month_start)
  ) AS cv_mom
FROM
  monthly
ORDER BY
  month_start DESC

FORMAT_DATE('%Y-%m', month_start) で「2026-06」のような表示用ラベルを生成しています。レポートに月名を表示したい場合に便利です。

運用メモ 前月比の数値だけでは判断を誤ることがあります。「前月が5連休を含む月だった」「セール期間が前月と今月でずれている」など、カレンダー要因の影響を考慮してください。異常値を見つけたら、まずカレンダーを確認する習慣をつけましょう。

実践クエリ3：媒体横断パフォーマンス比較

複数媒体のデータを UNION ALL で統合し、媒体別のパフォーマンスを比較します。

統合ビューの作成

まず、各媒体のrawテーブルをカラム名を揃えて統合するビューを作成します。

-- 媒体横断の統合ビューを作成する
CREATE OR REPLACE VIEW `your_project.ad_data.mart_all_platforms` AS

-- Google広告
SELECT
  segments_date AS date,
  'Google' AS platform,
  campaign_name,
  ad_group_name,
  metrics_impressions AS impressions,
  metrics_clicks AS clicks,
  metrics_cost_micros / 1e6 AS cost,
  metrics_conversions AS conversions,
  metrics_conversions_value AS conversion_value
FROM
  `your_project.google_ads.p_ads_AdGroupStats_1234567890`

UNION ALL

-- Meta広告
SELECT
  date_start AS date,
  'Meta' AS platform,
  campaign_name,
  adset_name AS ad_group_name,
  CAST(impressions AS INT64) AS impressions,
  CAST(clicks AS INT64) AS clicks,
  CAST(spend AS FLOAT64) AS cost,
  CAST(conversions AS FLOAT64) AS conversions,
  CAST(conversion_value AS FLOAT64) AS conversion_value
FROM
  `your_project.meta_ads.raw_meta_daily`

UNION ALL

-- LINEヤフー広告
SELECT
  day AS date,
  'Yahoo' AS platform,
  campaignName AS campaign_name,
  adGroupName AS ad_group_name,
  imps AS impressions,
  clicks,
  cost,
  conversions,
  conversionValue AS conversion_value
FROM
  `your_project.yahoo_ads.raw_yahoo_daily`

各媒体のカラム名を AS で統一するのがポイントです。カラム名さえ揃えれば、UNION ALL で一つのテーブルのように扱えます。

以下は、統合ビューのデータモデルを図示したものです。

媒体別の集計

統合ビューができれば、媒体横断の比較は簡単です。

-- 媒体別の月間パフォーマンス比較
SELECT
  platform,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(cost) AS cost,
  SUM(conversions) AS conversions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SAFE_DIVIDE(SUM(cost), SUM(conversions)) AS cpa,
  SAFE_DIVIDE(SUM(conversion_value), SUM(cost)) AS roas,
  SAFE_DIVIDE(SUM(cost), (
    SELECT SUM(cost) FROM `your_project.ad_data.mart_all_platforms`
    WHERE date BETWEEN '2026-06-01' AND '2026-06-30'
  )) AS cost_share
FROM
  `your_project.ad_data.mart_all_platforms`
WHERE
  date BETWEEN '2026-06-01' AND '2026-06-30'
GROUP BY
  platform
ORDER BY
  cost DESC

cost_share で各媒体の広告費構成比を算出しています。媒体間の予算配分の妥当性を確認する際に有用な指標です。

実践クエリ4：キャンペーン別・広告グループ別のドリルダウン

広告アカウントの階層構造（キャンペーン → 広告グループ → 広告）に沿って、集計の粒度を変える方法です。

キャンペーン別の集計

-- 特定媒体のキャンペーン別パフォーマンス
SELECT
  campaign_name,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(cost) AS cost,
  SUM(conversions) AS conversions,
  SAFE_DIVIDE(SUM(cost), SUM(conversions)) AS cpa,
  SAFE_DIVIDE(SUM(conversion_value), SUM(cost)) AS roas
FROM
  `your_project.ad_data.mart_all_platforms`
WHERE
  date BETWEEN '2026-06-01' AND '2026-06-30'
  AND platform = 'Google'
GROUP BY
  campaign_name
ORDER BY
  cost DESC
LIMIT 20

広告グループ別のドリルダウン

キャンペーンを特定したうえで、広告グループ単位に掘り下げます。

-- 特定キャンペーン内の広告グループ別パフォーマンス
SELECT
  ad_group_name,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(cost) AS cost,
  SUM(conversions) AS conversions,
  SAFE_DIVIDE(SUM(cost), SUM(conversions)) AS cpa,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr
FROM
  `your_project.ad_data.mart_all_platforms`
WHERE
  date BETWEEN '2026-06-01' AND '2026-06-30'
  AND platform = 'Google'
  AND campaign_name = 'Google_search_brand_202606'
GROUP BY
  ad_group_name
ORDER BY
  cost DESC

ROLLUPで階層集計を一発で出す

ROLLUP を使うと、キャンペーン別・広告グループ別の小計と全体合計を1回のクエリで出力できます。

-- ROLLUP で階層ごとの小計と合計を同時に集計する
SELECT
  COALESCE(campaign_name, '== 合計 ==') AS campaign_name,
  COALESCE(ad_group_name, '== 小計 ==') AS ad_group_name,
  SUM(cost) AS cost,
  SUM(conversions) AS conversions,
  SAFE_DIVIDE(SUM(cost), SUM(conversions)) AS cpa
FROM
  `your_project.ad_data.mart_all_platforms`
WHERE
  date BETWEEN '2026-06-01' AND '2026-06-30'
  AND platform = 'Google'
GROUP BY
  ROLLUP(campaign_name, ad_group_name)
ORDER BY
  campaign_name NULLS LAST,
  ad_group_name NULLS LAST

ROLLUP は集計の階層ごとに小計行を自動生成します。COALESCE で NULL を表示用のラベルに置き換えると、レポートとして見やすくなります。

運用メモ ドリルダウン分析では、まずキャンペーン単位で全体のCV・CPA分布を把握し、異常値のあるキャンペーンだけを広告グループ単位に掘り下げるのが効率的です。最初からすべてのキャンペーンを広告グループ単位で展開すると、データ量が膨大になり視認性が下がります。

パフォーマンスとクエリ費用の注意点

BigQueryは強力な分析基盤ですが、使い方を誤るとクエリ費用が膨らむことがあります。以下のポイントを押さえておきましょう。

パーティション分割テーブル

テーブルに日付パーティションを設定すると、WHERE 句で期間を指定したときに該当パーティションだけがスキャンされます。結果として、スキャン量の削減とクエリ費用の節約につながります。

-- パーティション分割テーブルの作成例
CREATE TABLE `your_project.ad_data.raw_google_ads`
(
  date DATE,
  campaign_name STRING,
  impressions INT64,
  clicks INT64,
  cost FLOAT64,
  conversions FLOAT64
)
PARTITION BY date
OPTIONS (
  partition_expiration_days = 730  -- 2年間保持
);

クエリ費用の見積もり

クエリ実行前にドライランでスキャン量を確認できます。

-- BigQueryコンソールで「ドライラン」ボタンを押すか、
-- bqコマンドで --dry_run フラグを使う
-- 例：bq query --dry_run --use_legacy_sql=false 'SELECT ...'

BigQueryコンソールでは、クエリを入力するとエディタの右上にスキャン予定のデータ量が表示されます。この表示を確認してから実行する習慣をつけてください。

ビューの活用

よく使うクエリはビューとして保存しておくと、毎回SQLを書く手間が省けます。

ビューはデータを保持せず、参照時にSQLが実行されるだけです。ストレージの追加費用はかかりません。ただし、ビューを参照するたびにスキャンが発生するため、大量のデータを何度も参照する場合はマートテーブル（実体化したテーブル）を検討してください。

運用メモ BigQueryのオンデマンド課金は$6.25/TBです。広告データの日次サマリーは1日あたり数KB程度なので、数年分をスキャンしても数十MBにしかなりません。ただし、GA4のイベントテーブルなど行数の多いテーブルを SELECT * で読むと、1回のクエリで数GBに達することがあります。不要なカラムは必ず除外してください。

まとめ

BigQueryでの広告データ分析は、管理画面の限界を超えた柔軟な集計を可能にします。この記事で紹介した手法を整理します。

最初の一歩として、Google広告のData Transfer ServiceでBigQueryにデータを取り込み、日次レポートのクエリを動かしてみてください。一度動く仕組みができれば、週次・月次の集計、媒体横断の分析と段階的に拡張していけます。