最も一般的なチャーン指標は比率です。直近7日間の利用量を、その前の7日間で割る。低下の検知には有効ですが、より深刻なケース — 毎月支払っているのに一度も使っていないアカウント — には完全に盲目です。本記事では、この指標をPostgreSQLで実装する際の3つの落とし穴と、最初の集計結果がほぼ必ず誤りになる理由を扱います。
問題はゼロ除算ではない
直感的な説明は「比率がゼロ除算になる」というものですが、違います。イベントを一度も生成していないアカウントは、イベントテーブルに行を1件も生成しません — GROUP BYにすら到達しないのです。行が存在しない以上、失敗する除算も存在しません。COALESCEでも救えません。存在しない行にデフォルト値は適用できないからです。
-- Errado: o FROM parte de events. Uma conta que nunca gerou
-- evento nao produz nenhuma linha aqui, entao ela nao chega
-- no GROUP BY. Nao e uma razao 0/0 -- e uma conta ausente.
SELECT
e.account_id,
count(*) FILTER (
WHERE e.created_at >= now() - interval '7 days'
)::numeric
/ nullif(count(*) FILTER (
WHERE e.created_at >= now() - interval '14 days'
AND e.created_at < now() - interval '7 days'
), 0) AS razao_uso
FROM events e
GROUP BY e.account_id;欠陥は算術ではなくFROMにあります。クエリがeventsから始まる限り、その母集団は「すでに何かをしたアカウント」です。何もしなかったアカウントを見るには、FROMがsubscriptions — 誰が支払っているかを知っているテーブル — から始まり、そこからイベントを探しに行く必要があります。
LEFT JOINではなくアンチジョイン
FROMを反転させると、問いは「イベントに対応する行が1件もないサブスクリプションはどれか」になります。これは最大のテーブルに対するアンチジョインであり、間違った書き方が2つ広く使われています。1つ目はNOT INです。
-- Pior ainda: se algum account_id retornado for NULL, o
-- predicado inteiro vira UNKNOWN e a query devolve ZERO linhas.
-- Nao da erro. So responde "nao ha contas inativas".
SELECT s.account_id, s.mrr_cents
FROM subscriptions s
WHERE s.status = 'active'
AND s.account_id NOT IN (
SELECT e.account_id FROM events e
WHERE e.type IN ('report_created', 'integration_connected')
);account_idがNULL許容で、いずれかの行がNULLを返すと、比較はすべてのアカウントでUNKNOWNとなり、結果は空集合になります。クエリは失敗もせず、警告も出さず、ただ「問題ありません」と答えます。
2つ目はLEFT JOIN ... WHERE e.id IS NULLです。これは動作します。PostgreSQLのプランナはこのパターンを認識し、アンチジョインとして実行します。問題は性能ではなく脆さです。誰かがeに対するフィルタをONではなくWHEREに追加した日、LEFT JOINは静かにINNER JOINとなり、クエリは探していたはずの行をちょうど捨て始めます。NOT EXISTSは意図を明示し、この失敗モードを持ちません。
-- O FROM parte de subscriptions: toda conta que paga entra,
-- tenha evento ou nao. NOT EXISTS declara a intencao (anti-join)
-- e o predicado correlacionado fica onde pertence.
SELECT s.account_id, s.mrr_cents
FROM subscriptions s
WHERE s.status = 'active'
AND NOT EXISTS (
SELECT 1
FROM events e
WHERE e.account_id = s.account_id
AND e.type IN ('report_created', 'integration_connected')
);
-- Indice parcial: eventos relevantes sao uma fracao do total,
-- entao o indice fica pequeno e e exatamente o que o anti-join
-- sonda. CONCURRENTLY para nao travar a escrita em producao.
CREATE INDEX CONCURRENTLY idx_events_relevantes
ON events (account_id)
WHERE type IN ('report_created', 'integration_connected');存在しないバックフィル
ここが、指標が初日から嘘をつく原因となる落とし穴です。first_meaningful_event_at IS NULLには、カラムが区別できない2つの意味があります。「このアカウントは一度も有効化されなかった」と「契約時点でそのイベントを計測していなかった」です。report_createdの送出を3月に開始したなら、3月より前のアカウントはすべて非アクティブに見えます — 数字が壊滅的になるのは現実のせいではなく、バグのせいです。
-- Guarda contra o falso positivo: so conta assinaturas que
-- comecaram depois que a instrumentacao existia. Antes disso,
-- ausencia de evento nao significa ausencia de uso.
SELECT sum(s.mrr_cents) AS mrr_sem_uso
FROM subscriptions s
WHERE s.status = 'active'
AND s.started_at >= timestamp '2026-03-01' -- instrumentacao
AND NOT EXISTS (
SELECT 1
FROM events e
WHERE e.account_id = s.account_id
AND e.type IN ('report_created', 'integration_connected')
);遡って直す方法はありません。送出されなかったイベントは再構成できず、書き込まれなかったデータを回復するクエリも存在しません。できるのは境界を明示することだけです。この指標は、計測開始後に始まったサブスクリプションにのみ妥当します。範囲は狭まりますが、数字は意味を持ち始めます。
カラムをどこに置くか
このアンチジョインを、読み込みのたびに更新されるダッシュボードでオンデマンドに走らせるのは高くつきます。代替は導出カラムを保持することです — アカウント上のfirst_meaningful_event_atを、最初の意味あるイベントが到着したときに一度だけ書き込みます。
-- Write-once: o WHERE ... IS NULL torna a escrita idempotente
-- e segura sob concorrencia. Nao precisa ler antes, nao precisa
-- transacao, e o segundo evento nao sobrescreve o primeiro.
UPDATE accounts
SET first_meaningful_event_at = $2
WHERE id = $1
AND first_meaningful_event_at IS NULL;重い仕事をしているのはWHERE ... IS NULLです。書き込みを冪等にし、事前の読み取りを不要にし、並行実行下でも安全にします。同時に届いた2つのイベントは同じ行を奪い合い、NULLを見つけるのは最初の1つだけだからです。以降、指標はeventsに触れることなくaccountsの走査になります。代償は導出カラムにつきものの、いつものものです。「意味のある利用」の定義が変われば、再計算が必要になります。
何をもって「意味のある利用」とするか
どんなクエリでも解決できないのが、この定義です。ログインは有効化ではありません — そこで何をすればいいか分からないことを再確認しに来ただけです。意味のある利用とは、そのアカウントが購入した価値を生み出す行為を指します。
- レポートを開いたのではなく、レポートが生成されたこと
- トークンを保存したのではなく、連携が接続されデータが流れていること
- 招待を送ったのではなく、2人目のユーザーが招待を受け入れたこと
- オンボーディングのサンプルではなく、顧客自身がレコードを作成したこと
どのイベントをこのリストに入れるかはプロダクトの判断です。それらが確実に送出され、適切にインデックスされ、データベースを落とさずに計算できるようにするのはエンジニアリングの判断です。指標が誠実になるのは、その両方が下されたとき — しかも事後ではなく事前に下されたときだけです。
関連記事
本記事は実装についてのものです。プロダクト側の論点 — なぜ「忘れられたアカウント」は「低下したアカウント」より深刻で、なぜ優先順位で先に来るべきなのか — はPaga e nunca usou é pior que uso caiuとThe most uncomfortable number in your customer baseにあります。