Retention Radar — Customer Health Command Center

A runnable implementation of the data structure described in churn-prediction-data-structure.md: the seven-table weekly account panel, the churn_prediction_wide analytical view, a churn-risk model for churn_next_30d, and a dashboard that turns the model's output into the prioritised GUARDIAN worklist.

The upstream systems in the spec (Legacy DB, DR. ROI, Intercom, HubSpot, Stripe, SENTINEL/GUARDIAN/RESCUE/ECHO/ALPHA) don't exist here, so the app ships with a synthetic panel generator that stands in for all of them. Everything else — schema, feature assembly, leakage policy, training, evaluation, scoring — is the real thing and swaps onto real sources by replacing one module.

Quick start

pip install -r requirements.txt
python -m churn_app.cli all          # schema + data + model + scores (~20s)
streamlit run churn_app/dashboard/app.py

all is idempotent and destructive: it recreates the schema from scratch each time. The dashboard lets operators switch between scored snapshots, filter the portfolio by plan, region, lifecycle phase, and risk band, and export a GUARDIAN queue whose ranks are recalculated for the visible portfolio.

What the pipeline produces

▸ Generating synthetic panel
  accounts:        600 (269 churned)
  snapshots:       23,744
  labelled rows:   22,089 (1,184 positive = 5.36%)
  date range:      2024-11-04 → 2026-04-27

▸ Training churn_next_30d model
  model:      xgboost.XGBClassifier + isotonic calibration
  evaluation: 5-fold out-of-fold over 21,727 rows
  features:   146
  base rate:  5.42%
  ROC AUC:    0.8239   PR AUC: 0.2012   Brier: 0.0463
  calibration: predicted 0.0618 vs observed 0.0542 (1.14x, ECE 0.0087)
  P@25       0.400  (lift 7.38x)  [folds 0.12–0.40]
  P@50       0.340  (lift 6.27x)  [folds 0.16–0.32]
  P@100      0.270  (lift 4.98x)  [folds 0.20–0.30]

Read P@50 = 0.340 as: if GUARDIAN works the 50 highest-priority accounts this week, about 17 of them were genuinely about to churn — 6.3× better than working 50 accounts at random.

The bracketed range is the spread across the five folds, and it is not decoration. Fifty accounts at a 5% base rate is a small sample, so a single holdout would have reported anywhere from 0.18 to 0.38 depending on which accounts happened to land in it. The pooled out-of-fold number is the estimate; the range is its honest uncertainty. Run --split time and you get one number with all of that variance still in it.

Runs are reproducible: the same --seed gives bit-identical output, including account ids.

Commands

Command	What it does
python -m churn_app.cli all	Everything below, in order
python -m churn_app.cli init	Create the schema and seed the catalogs (destructive)
python -m churn_app.cli generate --accounts 600 --weeks 78	Generate the synthetic panel
python -m churn_app.cli train --split group|time --folds 5	Train, evaluate, write reports
python -m churn_app.cli score --snapshot YYYY-MM-DD	Score a snapshot into churn_scores
python -m churn_app.cli queue --top 25	Print the GUARDIAN worklist, ranked by expected value
python -m churn_app.cli status	Row counts and current model summary

A Makefile wraps the same targets (make all, make dashboard, make clean).

Tests

make test        # or: python -m pytest tests/ -q

67 tests, ~25s, against their own temp database — they never touch artifacts/. They assert the invariants that everything downstream depends on:

• Labels — churn_next_30d agrees with churn_date in both directions, no snapshots survive past cancellation, censoring is confined to the final 30 days, and every feature table has exactly one row per master row (the wide view is a left join, so a missing row would become silent NULLs).
• Leakage — no forbidden column reaches the model matrix. The forbidden list is transcribed literally from the spec rather than imported from catalog.py; a test that reads its expectations out of the module under test passes even when someone deletes an exclusion.
• Scoring — untrained lifecycle phases are skipped, the queue is ordered by expected value, and value-ranking can never capture less than probability ranking at any K.
• Calibration — predicted probabilities track the observed base rate, calibration beats raw output on both ECE and Brier, and ranking quality survives it. Includes a synthetic perfectly-calibrated input as a control.
• Determinism — the same seed reproduces identical account ids, not merely identical row counts.

The suite is mutation-checked. Reintroducing any of these makes it fail: the uuid4 bug, the probability-ranking bug, the unfiltered-scoring bug, a leakage-exclusion removal, a calibrator that passes raw scores through, or risk bands that ignore the base rate.

Layout

churn_app/
  config.py              paths, plan catalog, risk bands, tunables
  cli.py                 command line entry point
  db/
    schema.sql           the 7 tables + wide view + catalogs, SQLite dialect
    database.py          connections, schema init, catalog seeding
  data/generator.py      synthetic panel — stands in for the upstream systems
  features/
    catalog.py           identifiers, leakage lists, categorical policy
    build.py             wide view → numeric model matrix
  models/
    train.py             grouped/time split, fit, evaluate, persist
    evaluate.py          global metrics, Precision@K, per-segment metrics
    predict.py           snapshot scoring, risk bands, GUARDIAN queue
  dashboard/app.py       Streamlit UI
artifacts/               gitignored — churn.db, models/, reports/

Design decisions worth knowing

SQLite, not Postgres. The spec's DDL is Postgres. db/schema.sql translates it (UUID→TEXT, DECIMAL→REAL, BOOLEAN→INTEGER, DATE→ISO TEXT) so the whole thing runs with no server. Everything the app does is plain SQL through one module — db/database.py is the only file that changes to point at Postgres.

churn_prediction_wide lists its columns explicitly. The spec's view uses i.*, u.*, ..., which would emit account_id and snapshot_date seven times. The columns are enumerated instead.

Splitting is grouped by account_id, and evaluation is out-of-fold. Weekly snapshots of one account are near-duplicates; a random row split lets an account's own future leak into its training rows and inflates every metric. On top of that, a single grouped holdout is too noisy at this base rate to report — so train runs a 5-fold GroupKFold, gives every labelled row exactly one out-of-fold prediction, and reports the pooled metrics plus per-fold spread. The persisted model is then refit on all labelled rows; the folds exist only to estimate how it will behave. --split time trains on early snapshots and tests on later ones — more production-realistic, but a single split with the variance that implies.

Reproducibility is enforced, including ids. The generator draws UUIDs from the seeded RNG rather than uuid.uuid4(). That is not cosmetic: account ids determine the grouped fold assignment, so uuid4 made every run split differently and quietly moved Precision@50 by more than 3× at a fixed seed.

Leakage policy is explicit, not inferred. features/catalog.py encodes the spec's exclusion list. LEAKAGE_HARD (dropped always) covers churn_date, churn_cause_echo, rescue_last_outcome, is_tbc_at_snapshot, and cancel_at_period_end. LEAKAGE_CAUTIOUS (dropped by default, restored with --include-cautious) covers the ECHO/GUARDIAN/RESCUE features the spec flags as "possible leakage depending on horizon". Adding a schema column requires classifying it here deliberately — nothing is auto-included.

Labels are right-censored. A snapshot in the final 30 days of the panel has no observable 30-day outcome, so churn_next_30d is NULL rather than 0. Training uses labelled rows only.

Precision@K is the headline metric, not accuracy. At a ~5% base rate a model that predicts "no churn" for everyone scores 95% accurate and is worthless. The team can only work a fixed number of accounts per week, so what matters is the hit rate at the top of the ranking.

The queue is ranked by expected value, not by probability. Priority is churn_risk_score × mrr_usd, because a retention slot spent on a small account that is nearly certain to leave protects far less revenue than one spent on a large account that is merely likely to. On the default dataset, re-ranking the same 50 slots this way raises expected MRR covered from $13,945 to $18,365/mo (+31.7%) and changes 22 of the 50 accounts. churn_scores.rank_by_probability keeps the old ordering alongside so the difference stays auditable, and cli queue prints both.

Probabilities are isotonically calibrated, and that is load-bearing. scale_pos_weight (~17× at this base rate) buys ranking quality but destroys the probability scale: raw output over-predicted by 2.47×, with accounts scored 0.82 actually churning 24% of the time. Ranking alone is invariant to that distortion, which is why it went unnoticed — but ranking on p × MRR is not, and neither is a dashboard reporting expected loss in dollars. The uncalibrated model claimed $62,336/mo at risk against a true figure near $27,000.

The calibration map is learned from cross-fitted inner out-of-fold predictions, so the base model still trains on 100% of the data. Holding out a calibration slice instead cost 0.013 ROC AUC for no benefit — isotonic regression is monotone, so it cannot change the ranking either way. Net effect: Brier 0.0797 → 0.0463 (−42%), calibration ratio 2.47× → 1.14×, ROC AUC essentially unchanged (0.8263 → 0.8239). --no-calibration reproduces the old behaviour.

Risk bands are multiples of the base rate, not absolute thresholds. This follows directly from calibration: the old cutoffs (CRITICAL ≥ 0.60) were tuned against inflated scores, and once probabilities were corrected the top band became unreachable — the first calibrated scoring run produced 0 CRITICAL and 1 HIGH out of 335 accounts. Bands are now MEDIUM at ≥2× base rate, HIGH at ≥4×, CRITICAL at ≥8×, which is what "critical" should mean anyway.

Scoring is restricted to the phases the model was trained on. The spec's training query excludes Onboarding accounts, so score excludes them too and reports how many it skipped. Scoring a population the model never saw produces a confident-looking number with nothing behind it — an Onboarding account has no usage history for the behavioural features to compare against.

XGBoost is optional. train.py uses it when installed and falls back to scikit-learn's HistGradientBoostingClassifier otherwise. Both handle NaN natively, so no imputation step is needed and missingness stays informative — a NULL attainment means DON DEAL never captured a KPI, which is itself signal.

What the evaluation segments show

churn_evaluation_segments is evaluated on the out-of-fold predictions on every training run (artifacts/reports/segment_metrics.csv):

Segment	n	Base rate	ROC AUC	P@50	Lift@50
tier_high	3,755	6.3%	0.821	0.24	3.82×
tier_medium	10,451	5.4%	0.809	0.34	6.28×
phase_ramp	474	1.3%	0.912	0.08	6.32×
phase_established	21,253	5.5%	0.822	0.38	6.89×
causa_known	3,199	15.4%	0.676	0.28	1.82×
zero_activity	948	24.7%	0.582	0.30	1.22×
high_attainment	9,023	1.2%	0.634	0.00	0.00×

Two segments carry the story. zero_activity has a 24.7% base rate but an AUC of 0.582 and lift of only 1.22× — those accounts are already obvious from a SQL WHERE clause, and the model adds almost nothing on top of the rule. Ranking effort there is close to wasted.

high_attainment is the segment the spec calls out for "surprise churn": customers hitting their KPI who leave anyway. At a 1.2% base rate the model is near-useless here — AUC 0.634 and zero hits in its top 50. This is the one segment where the model genuinely fails, and it is worth being clear about why: in the simulator these churns are driven by health shocks that the behavioural features barely register before the fact, which mirrors the real problem — a churn caused by a lost champion, a budget cut, or a competitor does not show up in usage until it is too late. The fix is not a better learner on the same features; it is different features (Objective 4, inferring cause before ECHO classifies it) or a different target (uplift). Tuning the current model against this segment would be fitting noise.

Synthetic data — how it works

Each account carries a latent weekly health series (random walk plus a drift; 28% of accounts get a negative drift, plus occasional shocks). Every observable feature is emitted as a noisy function of that health, and the churn hazard is 0.0012 + 0.075·(1−health)⁴, so cancellations cluster where health has decayed.

Two features are derived rather than drawn independently, because the spec's catalog names them as top predictors and the generator has to honour that:

• days_since_last_activity tracks the actual consecutive-zero-usage streak.
• credits_pct_of_baseline_7d compares to the account's frozen early-tenure baseline (spec: weeks 3–8). A rolling baseline would drift downward with a dying account and mask the very decay the feature exists to expose.

The model has to work through the noise, which is why PR AUC sits at ~0.20 rather than at an implausible 0.9.

Swapping in real data

Replace data/generator.py with loaders that populate the same seven tables from the real sources, on the spec's cadence (usage/billing/support/sentinel daily, ROI + guardian/rescue/echo weekly on Wednesday, alpha weekly on Friday, identity monthly). Nothing downstream changes: features/, models/, and dashboard/ read only from churn_prediction_wide and churn_scores.

Label backfill on cancellation — the spec's event trigger — is the one piece to implement for real: when an account cancels, set churn_next_30d = true on every snapshot within 30 days before churn_date. The generator does this inline.

Not built yet

The spec lists four modelling objectives. Objective 1 (weekly scoring to prioritise GUARDIAN) is implemented end to end. The other three are open:

• Objective 2 — time-to-churn via Cox PH / survival forest. days_to_churn is already populated in churn_prediction_master; needs lifelines or scikit-survival.
• Objective 3 — partially done. The model's own probabilities are now isotonically calibrated (see above), but calibrating ALPHA's θ per cell (cause × phase × tier) is not. alpha_p_hat_current is currently used as a meta-feature and is consistently among the top two inputs by gain, alongside sentinel_alerts_30d.
• Objective 4 — multi-class prediction of churn_cause_echo from behavioural features only, to act on probable cause before ECHO classifies it.

Risk explanations in the queue are rule-based (predict.RISK_DRIVERS), not SHAP. They're operator-facing talking points drawn from the catalog's high-importance features; per-account attribution would need shap.

Two further directions from the churn literature, neither implemented:

Profit-driven model selection (EMPC). AUC treats false positives and false negatives symmetrically and ignores the economics of a retention campaign; selecting models on it leads to measurably suboptimal profit. The Expected Maximum Profit measure for Customer Churn (Verbraken, Verbeke & Baesens) folds in offer cost, retention success probability, and CLV, and yields the profit-maximising fraction of customers to target — which would derive the queue size K instead of it being a slider. Ranking by expected value, above, is the cheap half of this idea; EMPC is the principled version. Python: empulse.

Uplift modelling instead of propensity. The stronger claim in the literature is that ranking by churn probability is the wrong objective entirely — you should model uplift, not churn. Propensity ranking spends effort on lost causes (leaving regardless) and on sleeping dogs, customers an intervention actively pushes out; the target should be persuadables. The schema is unusually well set up for this — the Squad tables already record treatments and responses (guardian_interventions_count, guardian_responded_last, guardian_conversion_rate, rescue_last_outcome). The blocker is the simulator, not the model: generator.py makes GUARDIAN activity correlate with risk but changes no outcome, so uplift would be measuring nothing. Implementing it means first giving the generator real treatment effects, including negative ones.