Google・Yahoo!・Microsoft の検索広告データを BigQuery に集約して 1 枚にする — 共通スキーマと日次同期の設計手順

なぜ媒体別のレポート画面のままでは 1 枚にならないのか？

管理画面を 3 つ開いて数字を手で写す運用が破綻するのは、作業時間よりも定義がそろわないためだ。同じ「コンバージョン」という列名でも、媒体によって計上する日が違い、参照する期間の窓が違い、通貨と時間帯の基準も違う。手で足した合計は、足した人にしか再現できない。集約の目的は見た目を 1 枚にすることではなく、同じ定義で並べた数字を毎朝同じ手順で再現できる状態を作ることにある。以下は当社が約半年間、複数媒体で広告を運用する事業者向けに実装・運用したダッシュボード基盤の構成を、案件が特定されない形に一般化したものである匿名化した支援案件の実装ログ（対象範囲・算定方法は本文注記）。観測期間は約半年。個別の媒体費・アカウント構成は非公開^[1]。

経営側の要点は 3 つに絞れる。手集計の合計は足した人にしか再現できず監査に耐えないこと、集約の費用は保管より媒体仕様の変更に追随する保守の時間で決まること、取得経路を切り替えるときは新旧の突合基準を先に決めておくこと。以降は実装担当向けの手順で、コードは手順 3 に閉じた。

手順 1 — 共通スキーマの最小列を決める

媒体ごとのレポート項目をすべて取り込もうとすると、媒体固有の列が増え続けてスキーマが安定しない。まず全媒体に共通して存在する最小列だけを決め、そこに入らないものは後回しにする。

粒度は「日付 × 媒体 × アカウント × キャンペーン」で 1 行とし、この 4 列の組で一意になる複合キーを持つ。キャンペーン ID は媒体をまたぐと一意ではない。広告グループなど下位の切り口は同じテーブルに混ぜず、別テーブルにする。分析用 9 列に加えて、口座通貨・口座の時間帯・元データの更新時刻・取り込み時刻の 4 列を運用メタ列として必須にする。通貨コードがなければ通貨の異なる費用を同じ列で合計できず、取り込み時刻がなければ再取得でどの世代のデータかを判別できない。テーブルは日付で分割しておくと、クエリが読む範囲が日付で絞られ、費用が想定外に膨らむのを防げるBigQuery 公式ドキュメント「Introduction to partitioned tables」^[2]。

手順 2 — 取得経路は媒体ごとに選ぶ。直 API と ETL SaaS のどちらを使うか？

取得経路は本稿では、媒体が公開している公式 API を自分で叩く方式と、複数媒体をまとめて取ってくる ETL SaaS を経由する方式の 2 つに絞る（ファイル連携や BigQuery Data Transfer Service など他の経路もある）。どちらかに統一する必要はなく、媒体ごとに違ってよい。

当社は公式 API のライブラリが整っている媒体を自前実装、残りを ETL SaaS 経由という分担で始め、運用が安定した後に一部を直 API へ切り替えた。最初から全媒体を自前実装すると、集約の設計が正しいかを検証する前に、媒体ごとの認証と仕様差の対応で時間を使い切る。 判断の目安は、取得したい粒度が SaaS の提供列に収まるか、1〜2 日の遅れが意思決定に影響するか、媒体側の仕様変更に自分で追随し続けられるか、の 3 点に加えて、再取得できる遡及期間と障害時の再送のしやすさである。

手順 3 — 日次バッチを冪等にする

広告データは確定しない。当日に取得した値は、コンバージョンの後追い計上や無効クリックの調整で数日かけて増えることがある。この性質を無視して日次で追記していくと、テーブルには「取得した日の値」が積み上がり、あとから見た数字が管理画面と合わなくなる。対処は、毎日、直近の窓を取り直して置き換えることである。当該案件では直近 14 日を窓にしたが、窓は媒体のアトリビューション設定と実測した確定遅延から決める。クリック後 30 日の窓で成果を数える設定なら 14 日では取りこぼすため、日次の窓とは別に 30 日・90 日といった低頻度のバックフィルを持つ。同じ日に何度実行しても結果が変わらないため、失敗した日の再実行も、遡っての修正も同じ処理で済む。

置き換えは「削除してから入れる」ではなく、先にステージング表へ読み込み、件数・重複・期間を検証してから本表へ反映する。削除が成功した直後に読み込みが失敗すると、対象期間が空になるためである。反映は MERGE か、日付パーティション単位の置き換えで行うBigQuery 公式ドキュメント「MERGE statement」^[3]。書き込みは 1 行ずつ送らず、まとめて読み込ませる方式を使う。従来型のストリーミング挿入は追加費用がかかり、直後の更新に制約が出ることがあるため、この用途ではバッチロードを使うBigQuery 公式ドキュメント「Batch loading data」「Streaming data into BigQuery」^[4]。

処理の骨格は次の十数行に収まる。

# 日次同期（1 媒体分）
for media in media_list:
    rows = normalise(fetch(media, since=today-W, until=today-1))  # W = 再取得の窓
    if len(rows) == 0:
        log_run(media, "empty"); continue           # 空なら本表に触らない
    load(staging[media], rows)                      # まず一時表へまとめ書き込み
    check(staging[media], key=KEY, window=W)        # 件数・重複・期間を検証
    merge(main, staging[media], on=KEY, window=W)   # 検証後に本表へ反映（MERGE）
    log_run(media, "ok", n=len(rows))               # 実行結果を別テーブルへ

要点は 4 つ。取得がゼロ件のときに本表に触らない（媒体側の一時的な失敗で既存データが消える）。本表への反映は検証後に 1 回で行う（途中失敗で期間が空にならない）。媒体ごとに失敗を隔離する（1 媒体の障害で全媒体が止まらない）。実行結果を別テーブルに記録する（監視はこのログがないと成立しない）。

手順 4 — 媒体で定義が違う指標をどう吸収するか？

同じ列名で並べた瞬間に、定義の差は見えなくなる。集約する前に、次の 4 点を媒体ごとに確認して記録しておく。

とくに参照期間の窓は見落としやすい。検討期間の長い商材で窓が短く設定されていると、その媒体だけ成果が構造的に少なく出る。媒体間の効率を横並びで比べる前に、窓の設定を突き合わせる。

集約したデータをサイト側の計測と突き合わせる場合は、キャンペーンを一致させる識別子が媒体ごとに違う点も効く。当社の実測では、自動タグ付けが有効な媒体は数値 ID で照合できた一方、手動でパラメータを付与している媒体は、キャンペーン名を変更した時点で照合が切れた。名称ではなく ID で突き合わせる設計にしておくとよい。

手順 5 — 鮮度と欠損をどう監視するか？

同期が止まったことに気づく経路は、実行ログを見る方法とデータの最終日を見る方法の 2 つがある。前者だけでは足りない。ジョブが成功で終わっていても、媒体側が空の結果を返していればデータは増えていない。当社は一度、更新されない原因を障害探しの方向だけで調べて実行ログばかりを追ったが、実際には実行環境が移行で止まっていた媒体と、配信を停止していて正常にデータが無い媒体とが混在していた。「ジョブが走ったか」と「データの最終日はいつか」は別の問いで、後者を見ないと正常な運用理由を障害として扱う。

監視項目は 4 つで足りる。媒体ごとのデータ最終日と当日との差、直近 7 日の日次行数の急変、実行ログの連続失敗、再取得の窓の外で値が変わっていないかの確認。いずれもダッシュボードとは別系統で通知する。画面の中に警告を出す設計にすると、開かない日は気づけない。

経路を切り替えるとき、新旧の突合で何を見るか？

経路を替えるときは、新旧を並行させて突き合わせてから切り替える。当該案件では切替前に 7 日ぶんを両経路で同じ時刻に取得し、日次の費用の乖離が ±1% 以内で、かつ絶対額の差も説明可能な範囲にとどまることを暫定の受入基準とした匿名化した支援案件の実装ログ^[1]。費用は条件をそろえれば原則一致を目指すべきで、±1% は恒常的な許容値ではなく、差異理由を説明する対象を足切りするための当該移行時の内規である。

観測されたことが 2 つある。1 つは、費用の合計が完全に一致していても行数は一致しないことがある点だ。SaaS 経由の行に、同一の日付・キャンペーン・広告グループの組み合わせが重複して入っているケースがあり、直 API 経由では重複が解消された。合計だけを見ていると重複に気づけない。もう 1 つは、コンバージョン数が新経路のほうが数件多く出た点で、計上の遅れである可能性が高いが、取得時刻・列定義・参照期間をそろえた後に判定する。費用は一致を求め、コンバージョンは方向と差の大きさを見ると基準を分けておくとよい。

切替後も旧経路は一定期間残す。止めた後に不足が判明すると、その期間のデータは取り直せないことがある。

どこまでやらなくてよいか — 集約しない方がよい場合

例外は 2 つある（加えて、Yahoo!広告のディスプレイ広告は API 系統が別で取得粒度も異なるため、本稿の集約対象には含めていない）。レポート API の提供が限定的な媒体は、取得できる粒度が管理画面より粗い、または遡及取得に制約があるため、集約に無理に載せず管理画面の値を注記する形にしたほうが早い。出稿が 1 媒体に集中していて金額も小さいアカウントも、集約の設計と保守にかかる時間が得られる示唆を上回る。

費用面では、日付で分割したテーブルと日付で絞ったクエリを守れば、保管とクエリの費用そのものは大きくならず、当該案件の構成では月額 10 ドル未満の水準に収まっている（無料枠に依存しうる。データ量に依存するため他の構成へ一般化できる値ではない）。増えるのは費用ではなく、媒体仕様の変更に追随する保守の時間である。

次に読む診断チェック — 集約基盤を作る前に 6 項目を確認する

着手前に次の 6 項目を確認すると、作り直しのほとんどは避けられる。

1. 共通スキーマの最小列を決め、媒体固有の列を後回しにする合意ができているか。
2. 媒体ごとの取得経路を、鮮度・粒度・保守負担の 3 点で選び分けたか。
3. 日次バッチは同じ日に何度実行しても結果が変わらないか。再取得の窓を何日にするか決めたか。
4. コンバージョンの計上日・参照期間の窓・通貨・時間帯を媒体ごとに記録したか。
5. データの最終日を、実行ログとは別に監視しているか。
6. 経路を切り替える際の突合基準を、切り替える前に決めたか。

1 つでも未確定なら、画面設計より先にそこを埋めたほうがよい。mixednuts では、こうした広告データ基盤の設計と実装を AI・データ基盤の実装支援 として提供している。

FAQ

Q. まず BI ツールを選ぶべきか、先にデータ基盤を作るべきか？ A. 先に共通スキーマとデータの置き場を決める。表示ツールは後から差し替えられるが、定義の異なるデータを取り込んだ後の作り直しは全期間の再取得を伴う。

Q. 全媒体を公式 API で自前実装したほうがよいか？ A. 媒体側の仕様変更に追随し続けられるかで決める。追随の担当を置けないなら、ETL SaaS 経由のほうが総保守時間は短い。当社は SaaS 経由で先に 1 枚を成立させ、運用が安定した媒体から順に直 API へ切り替えた。

Q. 日次バッチで何日ぶんを取り直せばよいか？ A. 媒体のアトリビューション設定と、実測した確定遅延より長く取る。当該案件では直近 14 日を日次の窓にし、それより長い参照期間の成果は 30 日・90 日の低頻度バックフィルで拾った。窓を短くすると遅れて計上された成果を取りこぼし、長くすると毎日の処理量が増える。

Q. 媒体側の数字とダッシュボードの数字が合わないときは何から見るか？ A. ①比較している期間と時間帯の基準 ②コンバージョンの計上日と参照期間の窓 ③取得の窓の外に古い値が残っていないか ④重複行の有無、の順で確認する。合計だけでなく主キー単位の行数も見る。

Q. ダッシュボードが更新されていないことに、どうすれば早く気づけるか？ A. 媒体ごとのデータ最終日を毎日チェックし、当日との差が想定を超えたら通知する。実行ログの成功・失敗だけでは、空の結果を返して成功したケースを検出できない。

参考文献 / Sources

匿名化した支援案件の実装ログ（対象範囲・算定方法は本文注記）:

• 広告データ集約基盤の実装・運用ログ^[1] — 複数媒体で広告を運用する事業者向けの日次ダッシュボード基盤。観測期間は約半年。突合の暫定基準・再取得窓は本文に記載のとおり。個別の媒体費・アカウント構成は非公開

BigQuery（保管・書き込み・費用）:

• Introduction to partitioned tables^[2] — 日付分割とクエリが読む範囲の制御
• MERGE statement^[3] — ステージング表から本表へ反映する構文
• Batch loading data^[4] — まとめ書き込み（無料のロードジョブ）
• Streaming data into BigQuery — 従来型ストリーミング挿入の費用と制約
• Control costs in BigQuery — クエリ費用の上限設定と分割の効果

媒体のレポート API:

• Google Ads API — Reporting overview — レポート取得の基本構造
• Google Ads API — Date ranges — 日付範囲の指定
• Microsoft Advertising — Reports guide — レポート種別と取得方法、項目の定義
• LINEヤフー 検索広告 API リファレンス — レポート定義と取得仕様

関連記事:

• 予算ペーシングは「変更履歴」で監査する — 消化率の段差を原因候補に絞る 4 手順