Customer Scores (churn_score and ces_score)

Use this guide for any question that involves the customer scores: "who is at risk of cancelling", "how engaged is this customer", "what is the churn risk of the annual base", or any chart built on FCT_CUSTOMER_SCORES.

The table

• ANALYTICS_DB.ANALYTICS.FCT_CUSTOMER_SCORES, one row per active subscriber (stripe_cust is the key), rebuilt in full on every dbt run.
• It has no history. Every row carries today's score_date; yesterday's scores are gone. Do not use today's score to explain something that happened in the past — a customer who cancelled last month has already left the table, and one whose device went dark three weeks ago has a low score today because it went dark.
• plan_type is monthly, annual, or free. Paid rows require at least 35 days since first_paid_start, a first_paid_start on or after 2025-07-01 (the training-cohort start; earlier payers are not scored, so a query over "the annual base" will not include them), and exclude subscribers who are past due or have already requested cancellation. Free rows require a live device and 35 days since the later of subscription start and first device online.

The four scores

All four are calibrated probabilities that the customer requests cancellation (the Stripe canceled_at request, not the date service ends) within a fixed window after the score date. None is rescaled or normalized.

Column	Horizon	Populated for	Typical monthly-paid average
churn_score	next 56 days	monthly, annual	≈ 3.5–3.8%
monthly_churn_score	next 28 days	monthly, annual	≈ 1.8–2.0%
ces_score	next 56 days	monthly, annual, free	≈ 3.5–3.8%
monthly_ces_score	next 28 days	monthly, annual, free	≈ 1.8–2.0%

• churn_score and ces_score share eight of nine input features (churn_score adds the share of recent calls that are can-to-can) and rank paid customers almost identically. Use ces_score when free subscribers must be included; use churn_score when the question is specifically Party Line cancellation.
• The 28-day and 56-day columns order customers the same way. Choose by the question: 28-day for outreach and "this month" language, 56-day for reporting and cohort comparisons.
• Lower is more engaged. A score of 0.05 reads as "a 5% chance of a cancellation request in the window".

Who the numbers are true for

• Both models are trained on monthly paying subscribers at least 35 days into their subscription, because that is the population with a real, frequent cancellation decision.
• Annual subscribers receive the same formulas. Read their scores as "the risk a monthly subscriber with this behaviour would carry", an engagement index, not a literal renewal probability. Annual cancellations cluster at renewal dates and need a renewal-window model.
• Free subscribers have no cancellation event to learn from. ces_score for them is an engagement index on the same scale; going dark is their churn event.
• Averages drift between retrains as the base ages or the season changes; the model is recalibrated at each retrain rather than on every run.
• Training tenure spans 35 to 368 days. For customers beyond that (mostly annual subscribers as the base ages) the tenure effect is extrapolated, so treat their scores as indicative.

Default rules

Question	Default
Rank customers for a retention outreach list	monthly_churn_score for paid, monthly_ces_score if free devices are in scope; take the top decile
Report engagement or risk across the whole base	average ces_score by plan_type, never a blended average across plan types
Compare two cohorts' risk	average churn_score (56-day) with the count of customers next to it
Explain why a customer scores high	read the feature columns on the same row (r28_active_days, pct_weeks_healthy, log_r28_max_call_sec, tenure_days, ever_had_ticket, r28_vm_backlog_5plus, r28_can_call_pct)

What not to do

• Do not compare monthly_churn_score to the month-over-month subscription churn rate. That rate counts subscription terminations among everyone paying at the start of a month, including customers in their first weeks. The score predicts cancellation requests over the following 28 days for customers already 35+ days in. The two measure different things and the headline churn rate will read higher.
• Do not average scores across plan_type values, and do not read an annual or free customer's score as a literal probability of leaving.
• Do not build "score history" by saving the table daily unless a proper history model is added; as of September 2026 none exists.
• Do not treat the raw feature columns as inputs you can re-scale: pct_weeks_healthy is a 0–1 fraction, and the models are trained on exactly the columns as exposed.

Feature columns on the row

Column	Meaning
tenure_days	days from cohort_start (paid start, or free activation) to score_date
w1_active_days	days with a successful call in the first 7 days (0–7); exposed for drill-down, not used by the current models
pct_weeks_healthy	share of weeks from week 2 to today with 2+ successful calls (0–1)
num_devices	Tin Cans on the account; exposed, not used by the current models
r28_active_days	distinct days with a successful call in the trailing 28 days
r28_calls_per_active_day	calls per active day in the trailing 28 days (0 if none)
log_r28_max_call_sec	log(1 + longest answered call, seconds) in the trailing 28 days
r28_vm_backlog_5plus	1 if 5+ unchecked voicemails in the trailing 28 days
ever_had_ticket	1 if the customer has ever filed a Zendesk ticket (a risk signal in the forward models)
r28_can_call_pct	can-to-can dependency index for the trailing 28 days: greatest(0, outgoing successes − all-direction external successes) / outgoing successes. Because the two counts are on different bases it floors at 0 for most customers and is high only for households whose outgoing calls are almost entirely can-to-can — read it as an indicator, not a clean share. Null for free
ever_ext_call	1 if the customer has ever completed an external call; exposed, not used by the current models

Method, briefly

Both models are forward-labelled logistic regressions: each customer is scored at many past reference dates using only data before that date, and the label is whether they requested cancellation in the following 56 (or 28) days. Validation is out-of-time on the most recent reference dates; out-of-time AUC is about 0.67–0.70 for both models and predicted risk matches observed risk decile by decile. The full method and history are in the header of dbt/models/analytics/fct_customer_scores.sql; the retraining pipeline and its runbook are in ml/customer_scores/ in the analytics repo; the September 2026 changelog entries record how and why the design changed.