【BQ×Looker Studio】広告レポートの数値がズレる「2大落とし穴」を潰した話

はじめに

Looker Studioで広告運用レポートを作っていると、こんな「怪奇現象」に悩まされてしまいました。。。

• CV/MCVが合わない（日付やフィルタを変えると、なぜか数値が増減する）
• 媒体名が噛み合わずJOINが外れる（meta と facebook 問題など）
• ページを増やすたびにSQLがコピペで散らばり、保守不能になる

この記事では、BigQuery上で 「広告×日」粒度のad_detail VIEW を設計し、これらの問題を解決した知見を共有します。

この記事を読むとどうなるか（Benefit）

1. 「数値がズレる/混ざる」原因であるJOINのアンチパターンを回避できる。
2. DataBeat等の媒体データと、ASP（成果）データを 正しく突合するSQL が書けるようになる。
3. 新しいプロダクトや案件に 爆速で横展開できるVIEW設計 が手に入る。

結論（やったことを3行で要約）

1. 媒体（配信側データ）を主語（Left）にしてVIEWを作る。
2. ASP（成果側）は、媒体側に存在するキーだけに限定して集約してからJOINする。
3. 媒体名は MAX() で手抜きせず、行単位で正規化して GROUP BY キーに含める。

---

背景・モチベーション

Looker Studioは便利ですが、カスタムクエリを各グラフやページに埋め込むと、「ロジックの分散」が起きます。
サマリー、月別、日別、キャンペーン別…とページを作るたびに、微妙に異なるSQLが量産されるのは地獄です。

そこで、BigQuery側に「広告×日」粒度で正規化済みの ad_detail VIEW を用意し、Looker Studio側は「それを読むだけ（集計はお任せ）」にする構成を目指しました。

しかし、ここで適当に媒体とASPをJOINすると、「集計レベルの不一致」で痛い目を見ます。

---

ハマりポイント1：ASPをそのまま集約してJOINすると「混ざる」

何が起きる？

ASPのログ（例：asp_daily_logs）は、基本的に「全件の成果ログ」です。
これをそのまま「日付×広告ID」などで集約して媒体データにJOINすると、以下のような事故が起きます。

• 他プロダクトのデータ混入: ID体系が似ている別案件のCVが紛れ込む。
• ゴミデータの結合: テスト用IDや欠損IDが、媒体側の意図しない行と結合される。
• JOIN漏れ: 正規化前の媒体名（fb vs facebook）が一致せず、CVが欠落する。

対処：ASP集約前に「媒体側に存在するキー集合」で絞る

解決策はシンプルです。ASPを集計する前に、「媒体側に存在するキー（Date, AdId, MediaName）」でフィルタリングします。

イメージ:

1. まず媒体側から feed_norm（広告×日×媒体の背骨）を作る。
2. 次にASP側は、feed_norm のキー集合に INNER JOINしてから 集約する。

これにより、「媒体データに存在しない（＝レポートに出すべきでない）謎のCV」を物理的に排除します。

---

ハマりポイント2：媒体名（media_name）を MAX() で作ると事故る

何が起きる？

媒体データの ServiceId（例：facebook_ads, tiktok_ads）から、共通の media_name（meta, tiktok）を作るとき、面倒だからといって集約時にこう書いていませんか？

-- 【アンチパターン】 集約関数の中で変換ロジックを書く
SELECT
  Date,
  AdId,
  CASE
    WHEN MAX(ServiceId) = 'facebook_ads' THEN 'meta'  -- ← これが危険！
    ELSE 'other'
  END AS media_name,
  ...
GROUP BY Date, AdId

これだと、万が一 Date × AdId の中に複数の ServiceId が混在した場合（データの過渡期やバグなど）、MAX がどれを選ぶかによって media_name がランダムに変わる（＝別媒体として集計される） リスクがあります。

対処：行単位で正規化 → GROUP BYキーに含める

横着せず、サブクエリで行ごとに正規化してから、その値を GROUP BY のキーにします。

-- 【正解】 正規化してからGROUP BYする
WITH norm AS (
  SELECT 
    *,
    CASE 
      WHEN ServiceId = 'facebook_ads' THEN 'meta'
      ELSE ServiceId 
    END AS media_name
  FROM SourceTable
)
SELECT
  Date,
  AdId,
  media_name, -- ← ここでGROUP BYする
  SUM(Impressions)
FROM norm
GROUP BY Date, AdId, media_name

---

実装コード（完全版）

実際に運用しているVIEWの構成（ボイラープレート）です。
これをコピーして、テーブル名やカラム名を差し替えればすぐに動きます。

/* BigQuery VIEW Definition: ad_detail
  Description: 媒体データ(feed)を主として、ASP成果(cv)を付与する
*/

WITH feed_norm AS (
  -- 1. 媒体側：Date × AdId × media_name を行単位で正規化して集約
  SELECT
    Date,
    CAST(AdId AS STRING) AS AdId,
    media_name,
    SUM(Impressions) AS Impressions,
    SUM(Clicks) AS Clicks,
    SUM(Cost) AS Cost,
    -- 文字列系は MAX で代表値を取得
    MAX(Headline) AS Headline
  FROM (
    SELECT
      *,
      -- ここで媒体名を正規化（行レベル）
      CASE
        WHEN ServiceId IN ('facebook_ads', 'instagram_ads') THEN 'meta'
        WHEN ServiceId = 'tiktok_ads' THEN 'tiktok'
        WHEN ServiceId = 'google_ads' THEN 'google'
        WHEN ServiceId IN ('yahoo_display', 'yahoo_search') THEN 'yahoo'
        ELSE ServiceId
      END AS media_name
    FROM `project.dataset.media_feed_table` -- ★ここを自身のテーブルに変更
    WHERE Date >= DATE('2023-01-01') -- パーティションプルーニング用（必要に応じて）
  )
  GROUP BY Date, AdId, media_name
),

asp_aggregated AS (
  -- 2. ASP側：媒体側に存在するキー（Date, AdId, media_name）に限定して集約
  SELECT
    a.Date,
    a.AdId,
    a.media_name,
    SUM(a.cv_count)  AS cv_count,
    SUM(a.mcv_count) AS mcv_count
  FROM (
    SELECT
      stat_date AS Date,
      CAST(external_ad_id AS STRING) AS AdId,
      -- ASP側も同様に媒体名を正規化してキーを合わせる
      CASE
        WHEN media_code IN ('fb', 'ig') THEN 'meta'
        WHEN media_code = 'tt' THEN 'tiktok'
        ELSE media_code
      END AS media_name,
      cv_count,
      mcv_count
    FROM `project.dataset.asp_daily_logs` -- ★ここを自身のテーブルに変更
    WHERE stat_date >= DATE('2023-01-01')
  ) a
  INNER JOIN (
    -- ★ここが重要：媒体側に存在するキーのみに絞る（誤結合防止）
    SELECT DISTINCT Date, AdId, media_name
    FROM feed_norm
  ) k
    ON a.Date = k.Date
   AND a.AdId = k.AdId
   AND a.media_name = k.media_name
  GROUP BY a.Date, a.AdId, a.media_name
)

-- 3. 最終結合：媒体データをLeftとしてASPを付与
SELECT
  f.Date,
  f.media_name,
  f.AdId,
  f.Headline,
  f.Impressions,
  f.Clicks,
  f.Cost,
  -- ASPデータがない場合は0埋め
  COALESCE(a.cv_count, 0) AS cv_count,
  COALESCE(a.mcv_count, 0) AS mcv_count
FROM feed_norm f
LEFT JOIN asp_aggregated a
  ON f.Date = a.Date
 AND f.AdId = a.AdId
 AND f.media_name = a.media_name

---

結果・成果

この設計を導入し、7つのUnit（案件/プロダクト）へ横展開しました。

• データの信頼性向上: 「ASPと媒体で数値が合わない」という問い合わせが激減。
• 開発スピード向上: 新しい案件が来ても、FROM 句のテーブルを差し替えるだけでVIEWが完成。
• Looker Studioの管理コスト減: 全ページで同じVIEWを参照するため、定義変更（例：媒体名のグルーピング変更）が1箇所の修正で完了。

まとめ

広告レポートのデータ基盤は、適当に作ると「運用」で死にます。
特に 「媒体起点で骨格を作り、成果データはそこに紐付けに行く（存在しない骨格には紐付けない）」 という考え方は、ズレないレポートを作るための鉄則です。

同じように「Looker Studioの数値が信用できない」と言われて胃が痛い方の助けになれば幸いです！

役に立ったらLGTM お願いします！

---