BigQueryの広告データを監視する｜欠測・ゼロ・急変をGASで毎朝検知する

処理は成功しているのに、値だけが壊れる

ある案件で、BigQueryに集約したYahoo!広告の申告コンバージョンが、8月6日から0のまま並んでいました。費用もクリックも毎日入っています。日次レポートも毎朝、いつもどおり届いていました。それでも気づいたのは6日後です。

スクリプトは正常終了しています。実行ログにエラーはありません。テーブルの行数も減っていません。壊れていたのは値だけです。

BigQueryに代表されるデータ基盤の監視というと、転送が落ちたことを検知する話だと思われがちです。実際に多くの現場で組まれているのも、その種類の監視でしょう。しかし現場で本当に困るのは、転送が成功したまま中身が壊れる場合です。

壊れ方は1種類ではありません。冒頭のように値だけが0になることもあります。行そのものが来ない場合、一部だけ欠ける場合、二重取り込みで倍になる場合もあります。

値が0になる型を先に挙げたのは、いちばん見つけにくいからです。この記事では、これらをまとめて1つの仕組みで点検する方法を扱います。

行は入るのに値が壊れる

筆者が実際に踏んだ事象を3つ挙げます。いずれも複数の媒体データをBigQueryに集約している案件です。日次レポートはGAS（Google Apps Script）で生成しています。

3つに共通するのは、取り込み処理そのものは成功していた点です。GASは正常終了し、実行ログには何も残りません。行数も平常どおりです。APIが200を返しながら中身だけが空、という状態でした。

気づいた経緯もばらばらでした。1件目は月次の集計で数字が合わなかったためです。2件目は管理画面と見比べて分かりました。3件目は週次のグラフが途切れていたためです。

どれも人間の目に依存しています。

問題は、この依存が発見を遅らせることだけではありません。遅れた期間のデータは、そのまま入札やレポートの根拠になります。申告コンバージョンが0のまま自動入札を評価すれば、判断そのものが狂います。

なぜ実行ログの監視では捕まらないのか

データ基盤の監視は、見ている層で3つに分けられます。

1層目と2層目は、多くの現場ですでにあります。GASのエラー通知も、BigQuery Data Transfer Serviceの失敗通知も、設定すれば動きます。行数や最新日付の監視も、スケジュールクエリで組めます。

冒頭の3件は、いずれもこの2層を通り抜けました。行数でも最新日付でも正常だったからです。

見落としの理由は単純です。1層目と2層目は「データが来たか」を見ています。壊れていたのは「来たデータの中身」でした。見ている対象が違えば、どれだけ精度を上げても検知できません。

なお、3層目は2層目の役割も兼ねます。対象日の行が無ければ値も取れないので、行の欠落は「データなし」として同じ経路で鳴ります。行数の監視を別に持たなくても、値の監視だけで取り込みの停止まで拾えるということです。

何をもって異常とするか

判定を4つの状態に分けます。対象日の値を、その直前28日間の中央値と比べる方式です。

平均ではなく中央値を使うのは、広告データは曜日によって大きく上下するからです。土日に費用が落ちる案件で平均を基準にすると、月曜の値が毎週「異常」になります。中央値なら、外れ値1日に引きずられません。

上振れを入れているのは、データ品質の監視と運用の異常検知で、見るテーブルが同じだからです。費用が中央値の3倍になった日も、同じ経路で知らせるほうが手数が減ります。二重取り込みも費用の暴走も、症状は同じ「急に増えた」です。

中央値だけでは足りない理由

この設計には穴があります。停止が長引くと、中央値そのものが0に落ちるのです。

中央値が0になるのは、窓の半分が0で埋まったときです。28日の窓なら、停止からおよそ2週間で到達します。中央値が0になると、「0であること」は異常ではなくなります。異常な値が基準値に取り込まれ、監視が働かなくなります。

そこで、判定の前に「そもそも毎日値が入るはずの指標か」を別の基準でも見ます。窓の中で値が入っている日の割合です。既定では0.7、つまり直近28日のうち7割の日に値があれば、中央値が0でも監視対象とみなします。

ただし件数の少ない指標では、中央値より先にこの割合が落ちます。前述のYahoo!の申告コンバージョンは8日連続で0になった時点で、28日中20日ぶんの約0.71でした。あと1日で下限を割る、という状態です。

// 「動いているはずの系列」かを3つの条件のいずれかで判定する
var baseline = [];            // 対象日の前 windowDays 日ぶんの値
var median = ANOM_median_(baseline);
var nonZeroRate = nonZero / baseline.length;

var watched = (median >= opts.minMedian)      // 中央値が立っている通常の指標
  || (nonZeroRate >= opts.zeroRate)           // 件数は少ないがほぼ毎日値が入る指標
  || !!(known && known[stateKey]);            // すでに異常として記録済みの系列
if (!watched) continue;

そこで効くのが3つ目の条件です。停止が長引けば、中央値も値が入っている日の割合も、いずれは下限を割ります。監視の対象から外れれば、復旧しても気づけません。

一度検知した系列は、解消するまで見続ける必要があります。そのために検知済みの状態を保存します。

誤報を止める設計

値の監視は、作ることよりも誤報を減らすことに手間がかかります。筆者の経験では、毎朝鳴る通知は2週間ほどで読み飛ばされるようになりました。

誤報の発生源は、だいたい4つに絞れます。

立ち上げ直後の母数不足。 データが7日ぶんしかない系列で中央値を取っても意味がありません。母数がしきい値未満なら判定そのものを行わない、という条件を先に置きます。

もともとほぼ0の指標。 出稿していないキャンペーンや、年に数件しか発生しないコンバージョンです。中央値が一定未満なら上下振れの判定はしない、で切り分けられます。

件数の少ない指標の単発ゼロ。 1日1〜2件のコンバージョンでは、0の日が普通にあります。何日続いたら通知するかのしきい値を2にすると、単発の0が消えます。実データで判定を再生したところ、誤報として残っていた2件はどちらもこの型でした。

指標ごとの振れ幅の違い。 費用の20%減とコンバージョンの20%減は、意味の重さが違います。同じしきい値でまとめると、費用に合わせればコンバージョンで誤報が続き、コンバージョンに合わせれば費用の異常を見逃します。

しきい値の一覧と単位

設定値は単位を取り違えると誤設定します。全項目の意味と既定値を先に置きます。

指標ごとの上書きは、設定ファイルに書きます。

metricOptions: {
  // コンバージョンは日々の振れが大きく、Yahoo!は1日1〜2件なので0の日が普通にある。
  // 上下振れでは鳴らさず、2日続けて止まったときだけ鳴らす。
  conversions: { dropPct: 0.99, spikePct: 99, minMedian: 0.5, minStreak: 2 },
  // 費用は絞りすぎと使いすぎの両方を早く知りたいので敏感に。
  cost: { dropPct: 0.5, spikePct: 1.0 },
},

コンバージョンの下振れ判定を0.99にするのは、事実上その判定を無効にする書き方です。件数が少ない指標では、下振れの判定は誤報しか生みません。見たいのは「止まったかどうか」だけなので、ゼロと連続日数で判定します。

この設定には副作用があります。連続日数のしきい値を2にした指標は、停止の翌朝には鳴りません。最速でも2日後です。単発のゼロを許容する設計を選んだ以上、この遅れは避けられません。

GASからBigQueryに問い合わせる

実装はGASに置きました。Cloud Runやワークフローでも組めます。ただし日次レポートのGASがすでに動いているなら、同じプロジェクトに関数を1つ足すほうが手数は増えません。

既存の日次処理には手を入れず、独立したトリガーで動かします。筆者は同じ判定ロジックを5つの案件に展開しており、以下はその共通コードです。

BigQueryへの問い合わせには、GASの拡張サービスであるBigQuery Serviceを使います。エディタのサービス一覧から有効化すると、BigQuery.Jobs.query が呼べるようになります。

function ANOM_query_(cfg, sql) {
  var request = {
    query: sql,
    useLegacySql: false,
    timeoutMs: 120000,
    maxResults: 20000,
  };
  if (cfg.bqLocation) request.location = cfg.bqLocation;

  var res = BigQuery.Jobs.query(request, cfg.bqProjectId);

  // タイムアウトで返ってきた場合はジョブの完了を待って取り直す
  var waited = 0;
  while (!res.jobComplete && waited < 120000) {
    Utilities.sleep(2000);
    waited += 2000;
    res = BigQuery.Jobs.getQueryResults(cfg.bqProjectId, res.jobReference.jobId,
      cfg.bqLocation ? { location: cfg.bqLocation } : {});
  }
  if (!res.jobComplete) throw new Error('クエリがタイムアウトしました');

  // 以下、res.rows を date / series / metric / value の配列に変換する
}

jobComplete の待ち直しは省略できません。数十万行のテーブルでは、初回のレスポンスが未完了で返ることがあります。そのまま res.rows を読むと、行が0件でも例外にならず、全系列が「データなし」として通知されます。監視の仕組み自体が誤報の発生源になるため、この待ち直しは省略しないでください。

SQLの返し方を1つに決める

監視対象を増やすたびに判定コードを書き換えるのは続きません。そこで、SQLが返す形を4列に固定しました。

• date：対象の日付
• series：系列名（媒体、アカウント、SNSのプロフィールなど）
• metric：指標名
• value：値

判定ロジックはこの4列しか知りません。何を監視するかは、SQLとしきい値だけで決まります。新しい媒体を足すときも、既存のコードには触れずに定義を1つ追加するだけで済みます。

広告テーブルは指標が列方向に並んでいるので、UNPIVOT で縦持ちに変換します。ここで INCLUDE NULLS を付ける点が重要です。付けないとNULLの行が消え、データなしの検知ができません。

SELECT date, series, metric, value FROM (
  SELECT
    date,
    ad_platform AS series,
    CAST(SUM(cost) AS FLOAT64) AS cost,
    CAST(SUM(clicks) AS FLOAT64) AS clicks,
    CAST(SUM(impressions) AS FLOAT64) AS impressions,
    CAST(SUM(total_conversions) AS FLOAT64) AS conversions
  FROM `project.ad_data.dashboard_all_platforms`
  WHERE date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 40 DAY)
  GROUP BY 1, 2
) UNPIVOT INCLUDE NULLS (value FOR metric IN (cost, clicks, impressions, conversions))

取得期間を40日にしているのは、28日の窓に加えて着弾の遅れぶんの余裕を持たせるためです。CAST を挟むのは、整数と浮動小数点数が混ざるとUNPIVOTが型エラーで落ちるからです。

参照先を媒体横断のビューにしている点にも理由があります。媒体ごとの生テーブルを個別に監視すると、ビューの張り替え漏れを検知できません。日次レポートが実際に読んでいるビューを監視すれば、取り込みの停止とビューの不整合を同じ経路で拾えます。

監視定義はプロジェクトごとに分ける

判定ロジックは全案件で共通、監視対象の定義は案件ごとに違います。ファイルを分けておくと、しきい値の調整でロジックを触らずに済みます。

var ANOM_CONFIG = {
  projectLabel: '案件名',
  bqProjectId: 'your-project-id',
  bqLocation: 'asia-northeast1',

  defaults: {
    lagDays: 1,        // 対象日 = 今日 - lagDays。データの着弾の遅れに合わせる
    windowDays: 28,    // 中央値を取る期間（対象日は含めない）
    renotifyDays: 3,   // 継続中の異常を再通知する間隔
  },

  checks: [
    {
      key: 'ads_meta',
      name: '広告実績（Meta）',
      sql: '（前掲のSQL。ad_platform = meta に絞る）',
      options: { lagDays: 1 },   // 前日ぶんが入っている
      metricOptions: {
        conversions: { dropPct: 0.99, spikePct: 99, minMedian: 0.5, minStreak: 2 },
        cost: { dropPct: 0.5, spikePct: 1.0 },
      },
    },
    {
      key: 'ads_yahoo',
      name: '広告実績（Yahoo!）',
      sql: '（前掲のSQL。ad_platform = yahoo に絞る）',
      options: { lagDays: 2 },   // 前々日ぶんが最新
      metricOptions: {
        conversions: { dropPct: 0.99, spikePct: 99, minMedian: 0.5, minStreak: 2 },
        cost: { dropPct: 0.5, spikePct: 1.0 },
      },
    },
  ],
};

上の例でチェックを2つに分けているのは、着弾のタイミングが媒体で1日ずれるからです。Metaは前日ぶんが入っているのに、Yahoo!は前々日ぶんが最新、という状態が普通にあります。同じ lagDays でまとめると、Yahoo!側が毎日「データなし」で鳴り続けます。

通知を読まれるものにする

判定ができたら、次は伝え方です。異常が出るたびに1通ずつ送ると、障害の日に10通届きます。1日1通のダイジェストにまとめ、異常がゼロの日は何も送りません。

送る内容は3つに分けます。

• 新しく起きた異常：即通知
• 続いている異常：3日ごとに再通知
• 直った異常：1度だけ復旧を通知

この振り分けには、前回の判定結果を覚えておく必要があります。GASなら PropertiesService のスクリプトプロパティにJSONで持たせれば足ります。系列と指標をキーにして、状態と発生日、最後に通知した日を保存する形です。保存するのは異常が出ている系列だけで、復旧すればキーごと消えるため、系列数が増えても肥大化しません。

if (!was || was.status !== f.status) {
  // 初めて出た異常、または状態が変わった異常は即通知する
  newIssues.push(f);
  next[f.key] = { status: f.status, since: f.date, lastNotified: today, name: ANOM_shortName_(f) };
} else {
  var days = ANOM_diffDays_(was.lastNotified || was.since, today);
  if (days >= renotify) {
    // 続いている異常は renotifyDays 日ごとにだけ再通知する
    ongoing.push(f);
    next[f.key] = { status: f.status, since: was.since, lastNotified: today, name: was.name };
  } else {
    next[f.key] = was;
  }
}

復旧の通知を入れているのは、確認の手間を減らすためです。復旧を知らせない監視だと、直ったかどうかを人が毎朝確かめることになります。それでは元の状態と変わりません。

本文には、判断に必要な数字だけを並べます。実際の通知はこの形です。

【案件名】データ異常 新規2 ／ 継続1（2026-08-12）

■ 新しく起きた異常
  ・広告実績 ／ yahoo ／ conversions  ゼロ（6日連続）
      2026-08-11 の値 0  ／ 直近の中央値 2
      直近28日のうち 23日は値が入っていました

「直近28日のうち23日は値が入っていました」の一行が、確認の起点になります。毎日出ている指標が止まったのか、元から出ない日がある指標なのかを、通知の時点で切り分けられます。この一行がないと、結局BigQueryを開いて過去を見ることになります。

もう1つ、通知に必ず含めるものがあります。判定そのものが失敗した項目です。クエリのエラーや権限の不足で判定できなかったとき、黙って何も送らないと、監視が止まっていることに気づけません。異常が0件でも「点検できなかった項目がある」場合は送ります。

監視しないものを決める

監視は増やすほど良くなるわけではありません。実データで判定を再生した結果、外した項目が2つあります。

売上（コンバージョン価値）。 コンバージョンが止まれば連動して0になります。同じ事象で2通届くだけでした。原因が同じなら、通知は1つで足ります。

アクセストークンの期限切れ。 これは値の監視で扱う対象ではないと判断しました。トークンが切れればデータが止まるので、値の監視で必ず捕まります。ただし捕まるのは止まった後です。期限そのものは、データの監視とは別に管理するほうが適しています。

どちらも外したのは、通知が増えるほど1通あたりが読まれにくくなるからです。通知の総量が、気づくまでの速さを左右します。

有効化の前に過去データで確かめる

しきい値を勘で決めて本番のトリガーに載せると、翌朝から誤報が続きます。有効化の前に2段階の確認を挟みます。

1つ目は、通知を出さずに判定結果だけをログに出す実行です。当日のデータに対して現在の設定でどう判定されるかを見て、明らかな誤報がないかを確認します。しきい値の調整は、この出力を見ながら行います。

2つ目は、過去データでの再生です。対象日を1日ずつ過去にずらしながら判定を走らせ、実際の障害日に鳴るか、平常日に鳴らないかを見ます。筆者の環境では14日ぶんを再生し、次の結果になりました。

• Yahoo!のコンバージョン停止を8月8日に検知（人が実際に気づいたのは8月12日）
• 他の4系列は発報ゼロ、つまり誤報なし

4日早く気づけることが、この仕組みの効果です。逆に言えば、この再生をやらずに導入すると、価値があるかどうかもわからないまま通知だけが増えます。

ここまで確認してから、日次のトリガーを作ります。データの着弾が早朝に終わる想定なら、10時台に置くと余裕があります。既存の日次レポートとは別のトリガーにしておくと、監視を止めたいときにレポート側へ影響しません。

function ANOM_setupTrigger() {
  ScriptApp.newTrigger('ANOM_run')
    .timeBased()
    .atHour(10)      // 10時台に実行される。分は指定できない
    .everyDays(1)
    .create();
}

運用してわかったこと

5つのプロジェクトに同じ仕組みを展開して、設計時に想定していなかった点がいくつか出ました。

GASプロジェクトのタイムゾーンは信用しない。 ある案件のプロジェクトは、タイムゾーンが America/New_York のままでした。日付の扱いは常に Asia/Tokyo を明示して計算する、と決めておくと、この種の事故が起きません。BigQuery側の CURRENT_DATE にもタイムゾーンを渡します。

判定ロジックと監視定義は、ファイルを分けても正本を1つにする。 同じロジックを5つのプロジェクトに置くと、直すたびに5箇所へ配布することになります。正本のディレクトリを1つ決め、そこからコピーして配る運用にしないと、案件ごとに少しずつ違う判定が残ります。

関数名の接頭辞を揃える。 既存の日次レポートと同じプロジェクトに同居させるので、グローバル名前空間の衝突が現実的なリスクです。監視側の識別子をすべて共通の接頭辞で始めると、既存コードを読まずに追加できます。

まとめ

データ基盤の監視は、処理が落ちたことを知る層と、値が壊れたことを知る層で、必要な仕組みが違います。前者だけを組んでいる現場は多いのですが、実際に困るのは後者です。

値の監視は、判定そのものより設定の調整に時間がかかります。中央値との比較という骨組みは共通でも、どの指標をどのしきい値で見るかは案件ごとに違います。過去データで再生しながら、異常の日に通知が出て、平常の日には出ない状態へ近づけていきます。

冒頭の6日は、この仕組みがあれば2日に縮みます。短縮した4日は、誤った数字で判断せずに済む日数です。まずは1媒体の1指標だけを28日ぶん再生してみると、自分の環境でどれだけ縮むかが見えます。

共通 広げる

BigQueryで広告データを集約する方法｜複数媒体の一元管理とSQL活用の基礎Google広告・Meta広告・LINEヤフー広告などのデータをBigQueryに集約し、SQLで横断分析する方法を解説します。

共通 仕組み化する

Google Apps Scriptで広告運用を自動化する｜GASの基本からレポート・アラートの実装までGoogle Apps Script（GAS）を使って広告運用の定型業務を自動化する方法を解説。スプレッドシート連携、レポート自動生成、Slack通知の実装例を紹介します。

関連記事

• BigQueryで広告データを集約する方法｜複数媒体の一元管理とSQL活用の基礎
• Google広告のデータをBigQueryに自動転送する｜Data Transfer Serviceの設定ガイド
• Google Apps Scriptで広告運用を自動化する｜GASの基本からレポート・アラートの実装まで
• AIエージェントに広告レポートを書かせる｜BigQuery×Claude Codeで日次・週次レポートを自動化する