BigQueryで広告データを集約する方法｜複数媒体の一元管理とSQL活用の基礎

なぜBigQueryで広告データを集約するのか

複数の広告媒体を運用していると、それぞれの管理画面でデータを確認する必要があります。Google広告はGoogle広告の管理画面、Meta広告はMeta Business Suiteと、媒体ごとにレポートが分散している状態です。

この状態では、媒体横断でのパフォーマンス比較ができません。また、ローデータに直接アクセスできないため、独自の集計軸で分析するにはCSVエクスポートとスプレッドシート加工が必要になります。媒体数やキャンペーン数が増えるほど、レポートの作成にかかる時間も増えていきます。

BigQueryにデータを集約すれば、SQLひとつで複数媒体のデータを横断的に分析できます。以下の図は、データの流れの全体像です。

管理画面のエクスポートに依存しない仕組みを一度構築すれば、日々のレポート業務を大幅に効率化できます。

BigQueryの基本概念

BigQueryはGoogleが提供するフルマネージドのデータウェアハウスです。サーバーの構築や運用管理は不要で、SQLを書くだけでデータの分析ができます。

階層構造

BigQueryのデータは3つの階層で管理されます。

• プロジェクト：Google Cloud上の管理単位。課金やアクセス権限はプロジェクト単位で設定します
• データセット：テーブルをまとめるフォルダのような存在。媒体別やドメイン別に分けるのが一般的です
• テーブル：実際のデータが格納される場所。CSVやスプレッドシートの「シート」に相当します

例えば、my-project.ad_data.google_ads_daily は「my-projectプロジェクトの、ad_dataデータセットにある、google_ads_dailyテーブル」を意味します。

料金体系

BigQueryの料金は、ストレージとクエリの2つで構成されます。

項目	料金	備考
ストレージ（アクティブ）	約$0.02/GB/月	90日未編集データはさらに半額
クエリ（オンデマンド）	$6.25/TB	スキャンしたデータ量に応じて課金
無料枠（ストレージ）	10GB/月	広告データなら数年分に相当
無料枠（クエリ）	1TB/月	日常的な分析には十分な量

広告運用のデータ量であれば、無料枠の範囲内で十分に実用的な分析が可能です。月間数千万インプレッション規模のアカウントでも、日別のサマリーデータなら数十MB程度に収まります。

データの取り込み方法

媒体ごとにデータの取り込み方法が異なります。以下の表で比較します。

媒体	取り込み方法	難易度	特徴
Google広告	BigQuery Data Transfer Service	低	公式連携。管理画面から設定するだけ
GA4	BigQuery Export	低	公式連携。GA4の管理画面から有効化
Meta広告	API経由（GAS / Python / ETLツール）	中〜高	Marketing APIでデータを取得しBQに格納
LINEヤフー広告	API経由（GAS / Python）	中〜高	レポートAPIでデータを取得しBQに格納
その他	CSVエクスポート → BQアップロード	低	小規模向け。手動のため自動化されない

Google広告とGA4は公式の連携機能があるため、設定だけで日次のデータ転送が自動化されます。Meta広告やLINEヤフー広告はAPIを利用したスクリプト開発が必要ですが、一度構築すれば自動的にデータが蓄積されていきます。

ETL/データ連携ツールを使えば、スクリプトを書かずにMeta広告やLINEヤフー広告のデータ取り込みも自動化できます。日本で導入実績の多いツールとしては、trocco（国産・日本語サポート充実）、Fivetran（グローバル標準・広告コネクタが豊富）、Google Apps Script（無料・小規模向け）の3つが代表的です。troccoは日本の広告媒体のコネクタが充実しており、日本市場向けの広告データ連携では第一選択になるケースが多いです。ただし、有料プランが必要なケースもあるため、データ量と予算に応じて選定してください。

テーブル設計の考え方

BigQueryにデータを取り込む際のテーブル設計は、2層構成が実用的です。

Layer 1（rawテーブル） は、各媒体から取り込んだデータをそのまま格納するテーブルです。媒体固有のカラムも含めて、加工せずに保存します。データの原本としての役割を持ちます。

Layer 2（統合ビュー / マートテーブル） は、rawテーブルのデータを共通のカラム名に変換し、UNION ALLで統合したものです。日常の分析やダッシュボードでは、この統合ビューを参照します。

共通カラムとして date、platform、campaign_name、impressions、clicks、cost、conversions を定義しておくと、媒体横断の集計がシンプルになります。

よく使うSQLパターン

BigQueryに広告データを集約したら、SQLで分析を行います。ここでは実務で頻出するクエリパターンを紹介します。

日別の媒体横断サマリー

各媒体のrawテーブルをUNION ALLで結合し、日別のサマリーを出力します。

SELECT
  date,
  platform,
  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
  `project.ad_data.mart_all_platforms`
WHERE
  date BETWEEN '2026-06-01' AND '2026-06-17'
GROUP BY
  date, platform
ORDER BY
  date DESC, platform

SAFE_DIVIDE を使うことで、ゼロ除算エラーを回避できます。コンバージョンがゼロの日でもエラーにならず、NULLが返ります。

前週比の算出（LAG関数）

時系列の比較には、ウィンドウ関数の LAG が便利です。

SELECT
  date,
  cost,
  conversions,
  LAG(cost, 7) OVER (ORDER BY date) AS cost_prev_week,
  LAG(conversions, 7) OVER (ORDER BY date) AS cv_prev_week,
  SAFE_DIVIDE(
    cost - LAG(cost, 7) OVER (ORDER BY date),
    LAG(cost, 7) OVER (ORDER BY date)
  ) AS cost_change_rate
FROM
  `project.ad_data.mart_daily_summary`
ORDER BY
  date DESC

7日前のデータを LAG(cost, 7) で取得し、変化率を計算しています。前月比なら LAG(cost, 30) のように日数を変更してください。

キャンペーン名からのパターン抽出

キャンペーン名に命名規則を設けている場合、正規表現で属性を抽出できます。

SELECT
  campaign_name,
  REGEXP_EXTRACT(campaign_name, r'^([A-Z]+)_') AS media_code,
  REGEXP_EXTRACT(campaign_name, r'_([a-z]+)_\d{6}') AS campaign_type,
  SUM(cost) AS cost,
  SUM(conversions) AS conversions
FROM
  `project.ad_data.mart_all_platforms`
GROUP BY
  campaign_name, media_code, campaign_type

例えば META_retargeting_202606 という命名規則なら、META や retargeting をカラムとして抽出できます。命名規則をあらかじめ統一しておくと、分析の自由度が大きく上がります。

運用のポイント

BigQueryのデータ基盤を安定して運用するために、いくつかのポイントを押さえておきましょう。

日次の自動取得

Google広告のData Transfer Serviceやスクリプトによるデータ取得は、日次で自動実行されるように設定します。手動実行に依存すると、データの欠損が起きやすくなります。GASのトリガー機能やCloud Schedulerを活用してください。

パーティション設定

テーブルに日付パーティションを設定しておくと、特定の期間だけをスキャンするクエリで課金額を抑えられます。WHERE date BETWEEN ... の条件を指定すれば、該当パーティションだけがスキャンされます。

料金の管理

BigQueryのオンデマンド課金は、クエリがスキャンするデータ量に比例します。不用意に SELECT * を実行すると、意図せず大量のデータをスキャンしてしまう場合があります。以下の対策が有効です。

• SELECT * ではなく、必要なカラムだけを指定する
• クエリ実行前に「ドライラン」でスキャン量を確認する
• プロジェクト単位で日次/月次のクエリ上限を設定する

無料枠の活用

BigQueryの無料枠は、毎月10GBのストレージと1TBのクエリ処理です。広告データの日別サマリーであれば、複数媒体を数年分格納しても10GBには余裕があります。クエリも、日常的な分析であれば1TBを超えることはまずありません。

まずは無料枠の範囲で始めてみて、データ量や利用頻度が増えたタイミングで料金体系を見直すのが現実的です。

まとめ

BigQueryによる広告データの集約は、媒体横断のレポート業務を効率化するための基盤です。一度仕組みを構築すれば、毎日のデータ蓄積は自動化でき、SQLで自由に分析できるようになります。

最初のステップとしては、Google広告のData Transfer Serviceを設定してBigQueryに慣れるのがおすすめです。その後、GA4のBigQuery Export、Meta広告やLINEヤフー広告のAPI連携と、段階的にデータソースを増やしていくとスムーズです。