BigQueryの集計が現場と合わない？ ステータスフィルタの定義ミスを見つける方法

「キャンセルを除外」と「予約済だけ抽出」は同じ結果になる。そう思っていた時期がありました。実際には結果がずれます。そしてその差は、数ヶ月間ずっと気づかれないまま報告書に載り続けます。

SQLエラーも出ない、数字もそれっぽい。だから誰も検算しない。これがいちばんやっかいなパターンです。

結論

ステータス列（＝予約済・キャンセルなど業務上の状態を表す列）でフィルタするときは、除外条件（!=）ではなく完全一致（=）で書く。除外条件は中間ステータスを巻き込んで件数を水増しします。現場の数字と食い違う原因の大半はここにあります。

実話：月5件だけ、ずっと多かった

ある案件で、月ごとのカウンセリング件数を集計していました。WHERE句は status != 'キャンセル' と書いていた。

月80件前後。集計を始めた当初は違和感がありませんでした。

ところが現場の管理者は月75件くらいと認識していた。しかも「月70件を超えたのは年に2回だけ」と言う。こちらの集計では70件超が年5回ある。5件の差は、誤差にしては毎回同じ方向にずれていました。

原因を探ると、ステータス列にはこんな値が入っていた（数値はダミーに置き換えた、ある月の構成イメージ）。

status != 'キャンセル' は、キャンセル12件を除外する。だが「未対応」「保留」「重複」の計5件も残してしまう。現場が数えている「実施されたカウンセリング」は予約済の75件なのに、SQL上は80件になる。

この差は月ごとに変動します。中間ステータスが少ない月は誤差が小さく、多い月は大きくなる。一定の差ではないので「何かの固定バグ」とは認識されにくく、数ヶ月そのまま報告されていました。

同じ「集計の罠」でも、月単位の急な増減を集計ミスと疑う視点は経営判断の文脈でも効きます。直近月の成約が激減したらまず集計の遅延を疑う話は別記事にまとめました。

正しいSQLの書き方

-- ❌ 除外条件（中間ステータスを巻き込む）
SELECT
  FORMAT_DATE('%Y-%m', DATE(created_at)) AS month,
  COUNT(*) AS cnt
FROM `project.dataset.bookings`
WHERE status != 'cancelled'
GROUP BY month

-- ✅ 完全一致（数えたいものだけを明示する）
SELECT
  FORMAT_DATE('%Y-%m', DATE(created_at)) AS month,
  COUNT(*) AS cnt
FROM `project.dataset.bookings`
WHERE status = 'confirmed'
GROUP BY month

!= 'cancelled' は「キャンセル以外の全部」を意味する。この「全部」の中身が想定どおりかは、ステータスの値を全部知っていなければ判断できません。一方 = 'confirmed' は「予約済だけ」を意味する。曖昧さがない。

NOT IN（＝列挙した値のどれにも当てはまらない、で絞る除外指定）で複数除外するパターンも同じ問題を抱えています。

-- ❌ 除外リストの漏れリスク
WHERE status NOT IN ('cancelled', 'duplicate')
-- 「保留」や「未対応」を書き忘れたら巻き込む

-- ✅ 数えたいものを列挙する
WHERE status IN ('confirmed', 'completed')

除外リストは「新しいステータスが追加されたときに漏れる」という時限爆弾を抱えています。半年後に「仮予約」ステータスが追加されたとき、除外リストに追記しなければ自動的にカウント対象へ混入する。完全一致なら、追記しない限りカウント対象に入りません。

食い違いを見つけるための総当たり検証

「正しい定義はどれか」がわからないときは、定義の候補を横に並べて12ヶ月分を一気に出す。

WITH monthly AS (
  SELECT
    FORMAT_DATE('%Y-%m', DATE(created_at)) AS month,
    status
  FROM `project.dataset.bookings`
  WHERE created_at >= '2025-01-01'
)
SELECT
  month,
  -- 定義A: キャンセル除外
  COUNTIF(status != 'cancelled') AS def_a,
  -- 定義B: 予約済のみ
  COUNTIF(status = 'confirmed') AS def_b,
  -- 定義C: 予約済 + 完了
  COUNTIF(status IN ('confirmed', 'completed')) AS def_c,
  -- 定義D: キャンセルと重複を除外
  COUNTIF(status NOT IN ('cancelled', 'duplicate')) AS def_d,
  -- 定義E: 全件
  COUNT(*) AS def_e
FROM monthly
GROUP BY month
ORDER BY month

COUNTIF（＝条件に合う行だけを数える集計関数）で定義を横並びにすれば、現場の数字と12ヶ月分を一度に突き合わせられます。全月で一致する定義が正解。1ヶ月でもずれる定義は不採用。

この検証では「定義B（予約済のみ）」が全月一致しました。「定義A（キャンセル除外）」は数ヶ月分でずれがあり、差分の月を個別に見ると、未対応・保留・重複のいずれかが件数に混入していた。

カウント前チェックリスト

ステータス列を集計する前に、この5つを確認すれば今回のミスはほぼ防げます。

• フィルタを書く前に GROUP BY status でステータス値の全量を確認したか
• WHERE句を除外条件（!=、NOT IN）ではなく完全一致（=、IN）で書いたか
• 「数えたいもの」を列挙したか（「数えたくないもの」を消す書き方になっていないか）
• 現場の認識値と複数月で突き合わせたか（1ヶ月だけの一致で安心していないか）
• 新しいステータスが追加されたら集計に勝手に混入しないか（除外リストの漏れリスクが無いか）

アンチパターン

「とりあえず除外」で書き始める

集計を書くとき、「キャンセルを除外すればいい」と最初に浮かぶ。この直感は正しそうに見えますが、「キャンセル以外はすべてカウント対象」という暗黙の仮定が含まれています。業務データのステータスは思ったより多い。除外ではなく、数えたいものを明示する。

食い違いを「タイミングの差」で片づける

現場と数字が合わないとき、「集計のタイミングが違うから」と説明しがちです。実際にタイミング差が原因のこともある。ただし「毎月同じ方向にずれている」なら、タイミングではなく定義の問題を疑ったほうがいい。タイミング差はランダムにぶれる。片方向のずれは定義ミスのサインです。

ステータス値の全量を確認しない

-- フィルタを書く前に、まずこれを実行する
SELECT status, COUNT(*) AS cnt
FROM `project.dataset.bookings`
GROUP BY status
ORDER BY cnt DESC

WHERE句を書く前に、対象テーブルのステータス列にどんな値が入っているかを確認する。「予約済」「キャンセル」の2種類だと思い込んでいたら5種類あった、というのが今回の落とし穴でした。

まとめ

ステータスフィルタで集計が狂う原因はシンプルです。除外条件が想定外の値を巻き込んでいる、これに尽きます。対策も3つだけ。

1. != 'cancelled' ではなく = 'confirmed' で書く
2. フィルタを書く前に GROUP BY status で値の全量を確認する
3. 現場の数字と12ヶ月分突き合わせて、全月一致する定義を採用する

エラーが出ない。結果もそれっぽい。だから気づかない。数字のずれは発覚までの期間が長いほど信用を削ります。集計定義は「除外」ではなく「明示」で書く。それだけで防げる事故があります。

報告した件数が、現場の感覚とずれていませんか

KPIの定義ミスは、気づいたときには数ヶ月分の報告が狂っています。「集計担当はいるが定義の検算までは手が回らない」「BigQueryに集計はあるが正しいか自信がない」という状態なら、設計段階で定義を固めて検算の仕組みを入れておくほうが安全です。f2t.jpではBigQueryの集計設計からKPIの定義策定、検算の自動化まで対応しています。お問い合わせフォームからご相談ください。