BigQueryで広告のCTR・CVR・CPAをAVG()で集計してはいけない理由

ある日、Yahoo広告の指名キャンペーン（自社名検索）のレポートを作っていました。BigQueryの日次データから、月次の impression_share（インプレッションシェア＝自社広告が表示され得た回数のうち実際に表示された割合）を集計します。

SELECT AVG(impression_share) FROM daily_report
WHERE campaign_name LIKE '%指名%'
  AND date BETWEEN '2026-05-01' AND '2026-05-31'

結果は1桁台でした。これを見て私は「自社名検索の大半を取りこぼし中。入札を大幅に引き上げるべき」と推奨します。

ところが、ユーザーが管理画面を確認したところ、表示は9割超。取りこぼしはほぼなく、入札強化はまったく不要だったのです。BigQueryの集計が、管理画面と9割近くズレていました。

原因は AVG() の使い方を間違えたこと。比率指標は単純平均してはいけない、というSQL集計の基本でした。

結論：比率指標は必ず重み付け平均で集計する

BigQueryから広告データを集計するときの鉄則は3つです。

• impression_share / ctr / cvr / cpa のような比率・率の指標は、必ず重み付け平均（＝各日の規模を反映させた平均）で集計する
• AVG() は使わない。管理画面の値と必ずズレる
• 集計結果は必ず管理画面の同期間表示と突合してから報告する

なぜ AVG() が間違いなのか、数値で示します。

なぜAVGがズレるのか：数値で見る

冒頭の事例を、説明用に単純化した数字で再現します。指名キャンペーンの日次データを想定します。

AVG(impression_share) で集計した場合

(95 × 5 + 0 × 25) / 30 = 15.8%

重み付け平均で集計した場合

SAFE_DIVIDE(SUM(impression_share * impressions), SUM(impressions))

(95 × 100 × 5 + 0 × 0 × 25) / (100 × 5 + 0 × 25)
= 47,500 / 500
= 95%

重み付け平均で出した95%が、管理画面の表示と一致します。

AVG() は日次の単純平均を出すだけで、各日の規模（impressions）を考慮しません。広告が表示されなかった日も大量に表示された日も同じ1票として扱うので、imp=0の日が多いキャンペーンほど大きくズレます。

特にズレが大きいのは次のケース。

• 指名キャンペーン。イベントがある日だけ大量配信、それ以外はゼロになりやすい
• 低予算キャンペーン。日々の配信量が大きく変動する
• 新規キャンペーン。配信開始前のゼロ日が混ざる

比率指標ごとの正しい集計SQL

AVG() を使ってはいけない指標と、正しい集計式の対応表です。これがこの記事の持ち帰りになります。

CTRとCVRに注目してください。AVG(ctr) は使わず、SUM(clicks)/SUM(impressions) で計算する。これがBigQuery上で広告データを扱うときの基本になります。

合計値（impressions / clicks / cost / conversions）なら SUM() で問題ありません。つまずくのは「比率」「率」「シェア」がついた指標だけです。広告データをBigQueryで扱う設計全般については、Claude CodeでBigQueryの広告データを集計・分析する手順も合わせて参考にしてください。

突合プロセスを必ず挟む：報告前のチェックリスト

数値の正しさを担保するために、BigQuery集計の結果をそのまま信用しないルールを置きます。

1. 同じキャンペーン・同じ期間で、広告管理画面の値を1つだけ取得する
2. BigQuery集計の値と突合する
3. 大きく乖離していたら、集計方法（AVG vs 重み付け）を最初に疑う
4. 管理画面と突合できないなら「速報値、管理画面と未突合」と明示してから報告する

冒頭の事例では、このプロセスを飛ばしました。BigQueryから出た1桁台の値をそのまま報告し、ユーザーが管理画面を確認するまで気づかなかったのです。

数値レポートの正解は「BigQueryから出た値」ではなく「管理画面と一致した値」。BigQueryは集計エンジン、管理画面は答え合わせの基準です。順序を間違えると信頼を一気に失います。

アンチパターン3つ

アンチパターン1: 単純平均と重み付け平均の違いを意識せずSQLを書く

統計の基礎知識ではあるものの、SQLで日次データを集計するとき「これは率だ、AVGは使えない」と毎回意識し続けるのは難しい。指標名に share / rate / ctr / cvr / cpa / cpc / roas が含まれていたらAVG禁止、というルールを自分の中に作っておくと事故が減ります。

アンチパターン2: BigQuery結果を管理画面と突合せずに報告する

「クエリは間違っていないはず」という思い込みが、大幅なズレを見逃す原因でした。管理画面の1セルと突合するだけで防げます。3分の手間です。

アンチパターン3: ズレを「BigQuery側の取り込みタイミングのせい」にする

「管理画面の値と微妙に違うのは取り込み遅延でしょう」と片付けると、本当の集計バグを見逃します。乖離が5%以上あったら必ず集計方法を疑う、を徹底してください。

まとめ：率は重み付け、結果は管理画面突合

BigQueryで広告データを集計するときの2大原則は次の2つでした。

1. 比率指標は AVG() を使わない。SUM(指標 × 重み) / SUM(重み) で集計する
2. 集計結果は必ず管理画面の同期間と突合してから報告する

特に指名キャンペーン・低規模キャンペーン・新規キャンペーンではAVGとの差が大きくなります。imp=0の日が混ざるからです。

実害として、私は冒頭の事例で「入札強化を推奨」と誤った提案を出しました。ユーザーが管理画面を見て即座に気づいたから事なきを得ましたが、もし管理画面を見ずにそのまま入札変更していたら、予算配分を大きく間違える事故になっていたはずです。数値レポートの精度は、集計式の正しさと突合プロセスの厳格さの掛け算で決まります。

広告データ基盤の設計でお困りなら

複数媒体（Google Ads / Yahoo広告 / Meta広告）のデータをBigQueryに統合していて、SQLの集計値と管理画面が合わない、指標の定義が媒体ごとにバラバラで横断比較できない、レポートの数値を社内で信用してもらえない。こうした状態に心当たりがあれば、集計式と突合の仕組みから整理する価値があります。

f2t.jpでは、媒体横断のデータ統合設計から、集計の正しさを担保する仕組みづくりまで一貫してお手伝いしています。お問い合わせフォームからお気軽にどうぞ。