Capstone: Churn Prediction, then a Recommender

A user is a person with a locale, a time zone and a role inside an account, and the account is the thing that cancels — so the unit the model predicts is not the unit the table describes, and every user-level feature has to be aggregated to the account with a choice of how (the admin's activity? the median seat's? the most active?). Time zones make "day" ambiguous: a login at 23:30 in Sydney and one at 08:30 in Berlin are on different calendar days depending on which clock you use, and a "logins per day" feature computed in UTC in the warehouse and in local time in the product disagree by a whole day at the edges. Users are also deleted on request, and a deleted user takes their events with them, which makes the history of churned accounts systematically thinner than the history of live ones.

The subscriptions table has status, plan, term, renews_at and cancelled_at, and it is the source of the label — which makes it the most dangerous table in the system. cancelled_at must never be a feature, obviously, but neither must anything derived from it: status, which reads "cancelling" for weeks before the effective date; renews_at, which is nulled at cancellation; the row's updated_at, which moves when the cancellation is recorded. The table is also mutable and snapshotless — it shows the current state, not the state as of any past date — so building honest training rows means reconstructing history from an audit log or a daily snapshot, and if neither exists the dataset cannot be built correctly from this table at all.

Events are the richest signal and the least trustworthy. They arrive late — a mobile client batches them and uploads hours or days later — so a 7-day login count computed on Monday and again on Thursday for the same week gives different answers, and the training pipeline, which runs later, always sees more complete data than serving did. They are duplicated by retries, so raw counts overstate activity unpredictably. Their schema changes with every release: an event is renamed, a property becomes optional, a platform stops firing it — and a feature that silently drops to zero for iOS users looks exactly like iOS users disengaging.

A payment is not one row; it is an attempt, a retry, a retry, a success or a final failure, spread over up to two weeks of dunning. A naive "failed payments in 90 days" counts retries as failures and makes every account with a flaky card look like it is churning. Involuntary churn — the card expires and the account lapses — is recorded as a cancellation by the billing system and is a different problem with a different fix (a dunning email, not a CSM call), so it must be separated from the label or the model learns to predict card expiry. Payments also settle in the provider's time, which is not the product's time zone or the warehouse's.

Support tickets look like the ideal early-warning signal, and some are. But a large fraction of tickets from accounts that cancel are opened *because* the account is cancelling — "how do I export my data", "please close my account", a billing dispute during offboarding — which makes "has open ticket" one of the strongest features in any naive model and one of the most leaked. Ticket timestamps are when the ticket was created, but the category and resolution fields are edited afterwards, so a point-in-time join on created_at still pulls in the future through the other columns. The honest version uses only tickets created before the snapshot date, with the category as it was at creation, and then checks whether the feature still predicts anything at a 7-day horizon.

Emails sent, offers shown, calls made. This is the intervention, and once the model is live it is the intervention the model caused: an account flagged as at-risk gets a call, which changes whether it churns, which changes the label that ends up in next quarter's training set. Used as a feature, "received a retention offer" becomes the strongest predictor of the previous model's score. Left out, the intervention's effect is silently attributed to the account's own features. It has to be recorded on every training row as the treatment it is, and a random holdout of flagged accounts that receive nothing is the only population whose labels still mean what the label definition says.