impressions集計がcontent_id経由だけでpayload.adId経由を辿らず常に0件だった

結論

イベントの帰属経路がFKで2系統に分かれていて互いに排他な構造のとき、集計クエリが片方の経路しか辿っていなければ、もう片方の経路でしか記録されない種類のイベントは常に0件のまま拾えなくなる。 広告主向け効果レポートのimpressions/taps集計は「広告主→契約→契約コンテンツ→コンテンツ→イベントのcontent_id」という連鎖だけを辿っていた。ところが実際に配信される広告の反応イベントはcontent_idを持たず、広告idをpayloadのadIdに持つ別経路で記録される構造だったため、この連鎖では広告の反応が構造的に1件も拾えなかった。直し方は、広告id経由の帰属をもう1本の集計として足し、2つの経路の結果を合算すること。2つの経路のイベント集合は互いに排他（一方はcontent_idを持ちpayload.adIdを持たない、他方は逆）なので、合算しても二重計上にはならない。

症状

広告主向けの効果レポートAPIは、集計月のimpressions（表示回数）とtaps（タップ回数）をイベントテーブルから集計して返す。元のクエリは次のような結合で、広告主から契約・契約コンテンツ・コンテンツを経由してイベントのcontent_idへ辿り着く形だった。

// advertiser-metrics.ts（修正前。06221f4e時点、aggregateTotals）
const rows = await db
  .select({
    impressions: distinctOfType("view"),
    taps: distinctOfType("tap"),
    asks: distinctOfType("ask"),
  })
  .from(advertisers)
  .leftJoin(contracts, contractActiveInMonth(advertiserId, monthStart, nextMonthStart))
  .leftJoin(contractContents, eq(contractContents.contractId, contracts.id))
  .leftJoin(contents, eq(contents.id, contractContents.contentId))
  .leftJoin(events, reactionInScope(monthStart, nextMonthStart))
  .where(eq(advertisers.id, advertiserId));

const row = rows[0] ?? { impressions: 0, taps: 0, asks: 0 };

return {
  impressions: row.impressions,
  taps: row.taps,
  asks: row.asks,
  dwellSeconds,
  presence,
};

このクエリはエラーを返さない。契約が無い、コンテンツに紐づくイベントが無いといった場合、leftJoinの結果は単に空になり、impressions: 0という一見正当な値が返る。だがこの0は「反応が無かった」のではなく、「この経路からは何も辿れなかった」という別の意味を持っていた。

原因

配信現場のサイネージ端末が送信する広告の反応イベントは、content_idというカラムには値を入れない。これはイベント投入処理のコメントに明記されている。

// event-ingest.ts（113-119行目）
if (raw.adId != null) {
  // 表示中の広告 (effective_ads_per_class.ad_id) の uuid。広告主の到達数集計 (F07 ユーザーストーリー)
  // 用。events.content_id は contents への FK なので広告 id は載せられず、payload に持つ。
  if (typeof raw.adId !== "string" || !UUID_RE.test(raw.adId)) {
    return { ok: false, message: "adId が不正です。" };
  }
  payload.adId = raw.adId;
}

content_idはcontentsテーブルへの外部キーであり、広告（adsテーブルの行）はそもそもcontentsではない。そのため広告idはcontent_idには入らず、payload.adIdというJSON内の値として記録される。つまりイベントの帰属先を示す経路は、実際には2系統に分かれていた。

• コンテンツ経路: events.content_id → contents → contract_contents → contracts → advertisers
• 広告経路: events.payload->>'adId' → ads.id → ads.advertiser_id

集計クエリは前者のコンテンツ経路だけを辿るものとして書かれており、後者の広告経路は辿っていなかった。サイネージが実際に配信する広告の反応はすべて広告経路側に記録されるため、コンテンツ経路だけを辿るこのクエリでは、広告主のimpressions/tapsは構造的に0件のまま、何件配信しても増えることがなかった。

この2つの経路は互いに排他である。コンテンツ経路のイベントはcontent_idを持ちpayload.adIdは持たない。広告経路のイベントはpayload.adIdを持ちcontent_idは持たない（同じイベント投入処理の中で、どちらか一方の形でしか書き込まれないため）。この排他性が、後述する合算方法の安全性の根拠になる。

直し方

広告id経由の帰属を辿る集計関数aggregateAdReactionsを新設し、既存のコンテンツ経路の結果と合算した。結合条件そのものは、社内の運用ダッシュボードの集計がすでに同じ形で使っており動作確認済みだったため、新規に設計せずそのまま転用した。

// advertiser-metrics.ts（修正後。e420233f時点）
const [dwellSeconds, presence, adRow] = await Promise.all([
  sumDwellSeconds(db, advertiserId, monthStart, nextMonthStart),
  countPresence(db, targetSchoolIds, monthStart, nextMonthStart),
  aggregateAdReactions(db, advertiserId, monthStart, nextMonthStart),
]);

// コンテンツ経路 + 広告経路の合算 (排他なので二重計上しない、`aggregateAdReactions` 参照)。
return {
  impressions: row.impressions + adRow.impressions,
  taps: row.taps + adRow.taps,
  asks: row.asks + adRow.asks,
  dwellSeconds,
  presence,
};

aggregateAdReactions本体は、eventsをadsへads.id::text = events.payload->>'adId'で直接結合し、その広告主に属する広告への反応だけを数える。

// advertiser-metrics.ts（修正後。e420233f時点）
async function aggregateAdReactions(
  db: Selectable,
  advertiserId: string,
  monthStart: ReturnType<typeof sql>,
  nextMonthStart: ReturnType<typeof sql>,
): Promise<{ impressions: number; taps: number; asks: number }> {
  const rows = await db
    .select({
      impressions: distinctOfType("view"),
      taps: distinctOfType("tap"),
      asks: distinctOfType("ask"),
    })
    .from(events)
    .innerJoin(ads, adJoinOn)
    .where(
      and(
        eq(ads.advertiserId, advertiserId),
        gte(events.occurredAt, monthStart),
        lt(events.occurredAt, nextMonthStart),
        inArray(events.type, [...REACTION_EVENT_TYPES]),
      ),
    );
  return rows[0] ?? { impressions: 0, taps: 0, asks: 0 };
}

学校別の内訳（by_school）側にも同じ考え方でaggregateAdReactionsBySchoolを追加し、コンテンツ経路の内訳と広告経路の内訳を学校idで合流させている。これを入れずにtotalsだけ直すと、合計値とby_schoolの内訳合計が食い違う状態になるため、両方を同じ構造で直す必要があった。

まとめ

「集計クエリが0件を返す」ことと「その対象の反応が実際に0件だった」ことは同じではない。イベントの帰属先がFKで複数経路に分かれている設計では、新しい経路を追加したときに、既存の集計クエリがその新しい経路を辿るように更新されているかを別に確認する必要がある。更新を忘れた経路は、エラーも警告も出さずに静かに0件を返し続ける。直すときは、既存の経路と新しい経路のイベント集合が本当に排他かどうかを先に確認してから合算する。排他性の確認を飛ばして単純に件数を足すと、設計次第では二重計上の別の不整合を持ち込むことになる。