BigQueryでコホート成約率を出すSQL、「直近月だけ低い」を正しく扱う実装

成約率を月次で出すと、直近月だけ異常に低くなる。問い合わせから成約までに時間がかかるビジネスでは必ずぶつかる現象だ。なぜ起きるかという考え方と、それを見て経営判断をどう変えるべきかは、概念編として直近月の成約激減はほぼ集計ミスという話で書いた。本記事はその実装編で、BigQueryで「成熟成約率」を正しく出すクエリと、書いていてハマったポイントに絞る。

対象はSQLを自分で書くエンジニアやデータ分析担当。COUNTIF や TIMESTAMP_DIFF を普段から触っている人を想定している。

成熟成約率の定義をSQLに落とす

成熟成約率（＝十分な時間が経った後の最終的な成約率）とは、問い合わせから一定日数が経過した母集団だけで出す成約率のこと。「まだ検討中」の人を分母から除外して、実力値を見る指標になる。

ここでは閾値を30日にする。問い合わせから30日以上経った人だけを母数にして、そのうち成約した割合を出す。閾値は事業によって変わる。検討期間が長い商材なら60日や90日でもいい。自社の「問い合わせから成約までの中央値」を一度出して、それを目安にするのがいちばん確実だ。

SELECT
  FORMAT_DATE('%Y-%m', DATE(contact_date)) AS cohort_month,
  COUNT(*) AS total_inquiries,
  COUNTIF(
    TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), contact_date, DAY) >= 30
  ) AS matured_count,
  COUNTIF(
    TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), contact_date, DAY) >= 30
    AND contract_date IS NOT NULL
  ) AS contracted_matured,
  SAFE_DIVIDE(
    COUNTIF(
      TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), contact_date, DAY) >= 30
      AND contract_date IS NOT NULL
    ),
    COUNTIF(
      TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), contact_date, DAY) >= 30
    )
  ) AS matured_conversion_rate
FROM `project.dataset.inquiries`
WHERE contact_date >= '2025-11-01'
GROUP BY cohort_month
ORDER BY cohort_month

出力はこうなる（数値はサンプル）。

4月は matured が3人しかいない。母数が少なすぎるので、ここで出る成約率は信頼できない。レポート上では「未確定」と扱うべき数字だ。

「未確定」をレポートに反映するロジック

matured_count が一定数以下の月は、成約率を表示しても意味がない。SQLの段階でフラグを付ける。

CASE
  WHEN COUNTIF(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), contact_date, DAY) >= 30) < 10
  THEN NULL
  ELSE SAFE_DIVIDE(...)
END AS matured_conversion_rate_display

NULLの月は、Looker StudioやMetabaseのほうで「未確定」と表示させる。数字が出ていないのは「悪い」のではなく「まだわからない」という意味だと、閲覧者に伝わるようにしておく。閾値の10件はあくまで目安。月の問い合わせ母数が少ない事業なら、もっと下げていい。

セグメント別に割る

全体平均だけ見ていても改善点は出ない。カテゴリ別にGROUP BYを足して、どのセグメントが効いているかを分解する。

SELECT
  FORMAT_DATE('%Y-%m', DATE(contact_date)) AS cohort_month,
  category,
  COUNTIF(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), contact_date, DAY) >= 30) AS matured,
  SAFE_DIVIDE(
    COUNTIF(
      TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), contact_date, DAY) >= 30
      AND contract_date IS NOT NULL
    ),
    COUNTIF(
      TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), contact_date, DAY) >= 30
    )
  ) AS matured_rate
FROM `project.dataset.inquiries`
WHERE contact_date >= '2025-11-01'
GROUP BY cohort_month, category
ORDER BY cohort_month, category

ある案件でこれを回したら、カテゴリによって成約率が倍近く違った。片方が6割弱、もう片方が3割弱。全体平均だけ追っていたら絶対に気づかなかった差だ。

実装でハマった3つ

ひとつ目は、成約日のカラムが「成約日」ではなく「カウンセリング日」だったケース。カウンセリング日にはキャンセル分も混ざるので、そのまま使うと成約数が過大になる。ステータスが「予約済」や「成約」のレコードだけをフィルタしてから数える必要があった。

ふたつ目は TIMESTAMP_DIFF の引数順（＝end と start を渡す順番のこと）。BigQueryの TIMESTAMP_DIFF は (end, start, unit) の順で、start と end を逆に書くと負の日数が返る。慣れていてもうっかり書き間違える。符号がマイナスになっていたら、まずここを疑う。

みっつ目は AVG で成約率を出してしまう罠。月別の成約率をさらに四半期で平均するとき、AVG(matured_rate) を使うと母数の大小を無視した単純平均になる。母数5件で成約率60%の月と、母数50件で成約率30%の月を AVG で平均すると45%になるが、実態は33%前後。集計の集計は AVG ではなく SUM ベースで分子と分母を積み上げてから割る。

ここまでを設計フェーズで確認するための持ち帰りリストにしておく。クエリを書く前にこの4つを潰しておくと手戻りが減る。

• 成約を表すカラムはどれか（「成約日」か「カウンセリング日」か、ステータス列で絞る必要があるか）
• 経過日数の閾値は何日が妥当か（問い合わせから成約までの中央値を先に算出する）
• 母数が少ない月をどう表示するか（NULL化の閾値を何件に置くか）
• 二次集計は SUM ベースで積み上げているか（AVG で単純平均にしていないか）

まとめ

成熟成約率をBigQueryで出すこと自体は難しくない。COUNTIF と TIMESTAMP_DIFF と SAFE_DIVIDE を組み合わせるだけで形になる。難所はSQL以前にある。「どのカラムが本当の成約を表すか」「経過日数の閾値は何日か」「母数の少ない月をどう見せるか」。この3点を設計で先に決めてからクエリを書くと、後から数字が合わない事故が減る。

データ基盤やKPI集計のSQLでつまずいているなら

成約率やLTVのようにラグのある指標は、クエリの巧拙より集計設計でつまずく。「直近月が毎回低く出てレポートが信用されない」「カラムの意味が曖昧で正しい母数が組めない」といった状態に心当たりがあれば、設計の段階から整理できる。f2t.jpではBigQueryを軸にしたKPI集計基盤の設計を支援している。相談は https://f2t.jp/contact からどうぞ。