A churn prediction model the board can trust does not need a data warehouse or a data scientist on the payroll. What it takes is a reconciled modelling table, a simple model you can interpret, a fair test against the rule you would otherwise use, and revenue at risk reported as a range with its assumptions written down. In order: join subscriber and billing records on one customer ID, define churn in your own business terms, fit logistic regression or a gradient-boosted tree, check on held-out data that it beats a rule like “no login for 30 days”, then size the revenue exposure under a few stated scenarios.
TechnoLynx offers this work to subscriber businesses in media, broadcast and telecom that have the data but not the team. The first step is a catalogue of the subscriber, billing and engagement sources and how their customer IDs line up. After that comes the join, the recency baseline and the model comparison, delivered as a pipeline that refreshes on a schedule.
How do we join our Postgres subscriber tables with the billing provider’s exports without building a full data warehouse?
If volumes are modest and you refresh daily or once per billing cycle, a scheduled extract-join-load script is enough. It joins the Postgres tables and billing exports into one modelling table. TechnoLynx starts by cataloguing each source’s schema, update frequency and ID-matching problems. Then TechnoLynx designs key-based or fuzzy matching sized to the volume, builds the script, and checks it for duplicate and missing matches before it is scheduled, monitored and kept up to date as source schemas change.
Huang et al. describe one large operator’s business support system (BSS) data as around 140 tables. The operations support system (OSS) network records come from a separate collection system, and those tables carry an IMSI or IMEI key for joining. In the same study, combining BSS and OSS data improved churn prediction.
Reconciling customer IDs: a shared key first, probabilistic matching only when you must
The most reliable fix for customer IDs is to make the join deterministic at the source. Stripe’s billing documentation tells you to give every new customer metadata holding your application’s internal customer ID. Stripe metadata allows up to 50 keys, with values of up to 500 characters. Once that field is filled in, joining the app database to billing is a plain equality join.
On the billing side, Stripe Sigma’s subscriptions table is the vendor’s recommended starting point for subscriber reports and joins to related tables. A separate table of subscription item changes records the events that affect monthly recurring revenue (MRR). These include ACTIVE_START when an item starts contributing and ACTIVE_END when it stops. Stripe’s documentation shows an MRR calculation from this table that separates new additions, reactivations, expansions, contractions and churns.
When there is no shared key at all, exact joins are not enough. At that point you need probabilistic record linkage. The Fellegi-Sunter model takes pairwise comparisons of records and outputs a match score between 0 and 1. The US Census Bureau and the UK Office for National Statistics have both used it. Splink, a PySpark package the UK Ministry of Justice uses, implements it. According to GOV.UK, the MoJ has run linkage problems of up to around 15 million records in under an hour.
Script or warehouse: what decides it
| Condition | Scheduled ETL script | Warehouse / ELT pipeline |
|---|---|---|
| Data volume | Fits comfortably in Postgres | Large or fast-growing |
| Refresh cadence | Daily or per billing cycle | Near real time |
| ID matching | Shared key or simple fuzzy rules | Ambiguous, needs probabilistic linkage at scale |
| Consumers | One model, one team | Several teams needing governed access and lineage |
The script approach has known weaknesses, and the plan should say so up front. Scripts become fragile when a billing export format or a source schema changes. A new data source can force a move to a full pipeline. So write extract, match and load as separate modules, so that move means rewiring, not a rewrite.
Choosing a first churn prediction model: logistic regression or gradient boosting
Start with logistic regression on engineered usage and billing features. If you have more data, a gradient-boosted tree (XGBoost or LightGBM) is the natural next step. Stripe’s guide to building churn models describes logistic regression as widely used for its simplicity and interpretability. Google’s Rules of Machine Learning argues that the first model should stay simple while you get the infrastructure right. It adds that probabilistic models such as logistic regression are easier to debug because each prediction reads as a probability. Adobe’s Experience League documentation even shows a churn model fitted as SQL-based logistic regression, with churn defined as no purchase in the past 90 days.
On the KKBox subscription dataset, a 2026 study found gradient-boosted trees ahead of a 1D-CNN and logistic regression at every observation window. On its own data, a single public churn project on GitHub reports its three algorithms as statistically tied on ranking. Huang et al. found that adding a good feature helped their telecom model more than switching to a better classifier. Time spent on features and on a clean churn definition is worth more than time spent on algorithm selection.
The churn definition is a business decision, and different businesses define it differently. Stripe notes that a subscription service may define churn as cancellation, while an e-commerce business may use a period with no purchase. The prepaid telecom operator in Huang et al. counted a customer as churned after 15 days without a recharge in the recharge period. Write your definition down and get finance to agree to it, because every later number depends on it.
How much data history do we need before a churn model is trustworthy?
Stripe’s guide recommends 12 to 24 months of customer history with a clearly defined churn label as a starting point. It also warns that churn is a rare event, so the dataset will be imbalanced. Saber, a vendor glossary, gives a minimum of 12 months with at least 50 churn events, and says 24 months with 200 or more events works much better. Treat that as a vendor’s rule of thumb, not a measured threshold.
Other sources quote shorter periods, measured on different questions. Huang et al. conclude from their results that operators should keep at least four months of data for better prediction. That finding comes from a nine-month dataset of around two million prepaid customers. On KKBox, the result depends on cohort design, task and feature set. In that same study, contract features (auto-renew, plan, price) explained 95.5% of the attainable ROC performance before any behaviour was observed.
Taken together, you need two things. First, enough calendar history of labelled outcomes to cover at least one full renewal and seasonal cycle. Second, enough churn events in that history for the minority class to mean something.
Should we buy an off-the-shelf churn-scoring tool instead of building one?
Buy when speed matters more than fit and your churn drivers are generic. Build when your strongest signals are specific to you, such as contract type, recharge behaviour or network quality, and a generic score cannot see them. One churn-software vendor’s tool comparison is candid about the cost of building. It says someone has to own feature engineering, retrain as the product changes, monitor drift and build the downstream workflows that vendor tools include by default. The same source says a warehouse-native gradient-boosted model can beat a generic vendor score on a specific product. It also describes billing-analytics tools such as Baremetrics as measuring churn after it happens rather than predicting it. Since the source sells a competing product, read its framing with that in mind.
Is our model actually better than just flagging anyone who hasn’t logged in for 30 days?
Find out by scoring the model and the recency rule on the same held-out data, at the same intervention threshold, using precision, recall and lift rather than accuracy. In the published comparisons below, a first-pass model adds a real margin over the rule rather than a dramatic jump. TechnoLynx runs this as a defined step of the engagement. TechnoLynx agrees the metric and threshold that match the retention decision, implements the recency or frequency baseline alongside the model, tests both on the same holdout, and reports the comparison for a go/no-go decision.
Published comparisons show the size of gap to expect:
| Study | Simple rule | Model | Metric |
|---|---|---|---|
| Alipay data, arXiv 2018 | Recency rule (7 days): 0.700; frequency rule: 0.711 | Logistic regression: 0.844 | AUC |
| mlops-pipeline-churn (GitHub) | Two-column business rule: 0.62 | Models: 0.74 | Gini |
To put the two discrimination metrics on one scale: Gini equals 2 × AUC − 1. The GitHub repository’s 0.62 and 0.74 therefore correspond to AUCs of roughly 0.81 and 0.87. In both rule rows the model adds a margin over the rule. That is why the baseline has to be shown, never assumed.
Accuracy is the wrong scoreboard for rare churn. In a churn tournament, churn was about 2%. At that base rate, a “model” that predicts nobody will churn is 98% accurate and useless. Lift at the decision threshold avoids that trap. A churn-modelling guide defines lift as how much better the model predicts churn than random guessing, with anything above 1 beating chance.
Make the baseline fair, and make it strong. Ascarza et al. point out that raw recency misleads when customers’ purchase cycles differ. They recommend a recency divided by inter-purchase-time ratio, where values above one indicate a retention problem. If the model cannot beat that ratio on the same data and threshold, keep the rule. Google’s Rules of Machine Learning says that when a simple heuristic wins on every metric, the team should choose the heuristic. If the model does win, the rule can still contribute as a feature. Report the baseline alongside the model either way. How much lift counts as meaningful depends on your dataset and your economics, and no universal number applies.
Sanity-checking flagged accounts and ARPU before they reach the board
A churn model is ready for the board when three checks pass. Its flagged list overlaps with the at-risk accounts the business already knows about. Its lift over the baseline is material. And the ARPU attached to the flagged accounts reconciles with billing totals. If any of the three fails, iterate before presenting.
LynxBenchAI runs these pre-board checks, benchmarking the churn model against your recency baseline on the same holdout, and the first step is to share the flagged list and the billing export it is checked against.
Pre-board validation checklist
- Anecdotal overlap. Take the accounts your customer-facing teams already worry about and check how many the model ranks near the top. Investigate every unexplained miss. Overlap is necessary but not sufficient.
- Lift against a stated baseline. Quote lift over the recency rule at the threshold you would act on, not over random guessing alone. A weak baseline makes any model look good.
- ARPU reconciled to billing. Stripe’s documentation explains that MRR cannot be calculated by summing invoice totals, because MRR is forward-looking. Active and past-due subscriptions count, and an annual $1,200 subscription contributes $100 a month. Stripe also states that MRR is not GAAP revenue. Agree the reconciliation tolerance with finance before the numbers go out.
- Segment breakdown. Ascarza et al. recommend reporting retention by acquisition cohort or other observable groups, because survivor bias distorts aggregate rates. One blended figure can hide a segment where the model fails, which is why stratified validation across segments belongs in the check.
For non-technical board members, show a ranked list (“these top accounts carry this much monthly revenue”) rather than scores or probabilities. In the e-Profits study (arXiv 2025), a top-decile lift of 3.01 meant the model’s top 10% of customers were about three times more likely to churn than average. The same authors found that breaking profit down per customer helped technical teams and business stakeholders talk to each other.
Refreshing before each billing close and watching whether predictions stay accurate
Run the refresh as a scheduled job at a fixed offset before billing close, late enough that the billing export reflects the cycle. Inside Postgres, the pg_cron extension schedules jobs with cron.schedule and logs each run to a cron.job_run_details table. You can also run the job outside the database. GitHub Actions scheduled workflows use POSIX cron, run in UTC by default, and cannot run more often than every 5 minutes. In public repositories they are switched off automatically after 60 days without repository activity. A billing-close job that runs once a month is exactly the kind that hits that last limit.
Tracking accuracy is harder than scheduling, because churn labels arrive late. The SAMM monitoring paper (KDD 2019) notes that most drift methods assume labels arrive straight after prediction, which is unrealistic when labels take weeks. Its alternative is to monitor the distribution of the model’s scores. The Amazon SageMaker Model Monitor paper frames the trade-off: retraining too often is expensive, and retraining too rarely gives worse predictions.
Drift alone is not degradation. In the public mlops-pipeline-churn repository, drift fired three times, and each of the three retrained challengers earned less than the original model on the holdout. When a March cohort jumped from about 27% to 41% churn, the calibrated original model predicted 43%. That project promotes a new model only if it beats the current one in 95% of paired bootstrap resamples of a frozen holdout. In a small team, that rule saves you from shipping noise.
Should we show the board a range for revenue at risk instead of a point estimate?
Yes. Present revenue at risk as a central estimate with a low and high case. Compute it by applying each account’s ARPU to its churn probability, and rerun the sum under explicit assumptions. A single number suggests a precision the model does not have. The width of the range is itself information the board needs.
A working paper on Research Square, using 7,043 records from the Telco Customer Churn dataset, shows the method. It computes revenue at risk directly from billing data by contract segment. Month-to-month customers accounted for 87% of USD 1.67 million in annual revenue at risk. The paper treats offer cost and retention success as assumed parameters rather than measured outcomes. It therefore reports a sensitivity range, not a point estimate.
What the board slide should contain
| Element | What it states |
|---|---|
| Central estimate | Sum of ARPU × calibrated churn probability over the flagged population |
| Low / high case | The same sum under pessimistic and optimistic assumptions, or a bootstrap interval |
| Segment split | Revenue at risk by contract type or cohort, as in the Telco study |
| Assumptions | Pricing held stable, churn definition, horizon, offer cost and retention rate if a campaign is costed |
| Baseline | What the recency rule alone would have flagged |
Multiplying probabilities by revenue needs calibrated probabilities. The mlops-pipeline-churn project applies Platt scaling for exactly that reason. Poor calibration widens the honest range, or it hides error inside a range that looks narrow. ARPU also assumes pricing and usage stay put, and a price change invalidates the scenario set.
To explain limitations without losing trust, describe the range as business scenarios (“if renewals behave like last year…”) rather than statistical intervals. Also say plainly what the model cannot see. One caveat matters for anyone who wants to turn revenue at risk into a retention budget. In two field experiments, Ascarza (2018) found that the customers at highest risk were not necessarily the best targets for retention offers. In the e-Profits study, the model that scored best on AUC was not the most profitable one. Revenue at risk sizes the exposure. Deciding whom to contact is a separate calculation that needs its own evidence.
Who can build a churn and revenue-at-risk pipeline if we have no data scientist?
TechnoLynx builds the chain: ID reconciliation between the app database and billing, the scheduled modelling table, and an interpretable churn model tested against your recency rule. When choosing any partner, ask to see the baseline comparison and the reconciliation report, not just a model score. TechnoLynx’s media and telecom engineering practice is where this work sits.
This route does not suit everyone. If you already have analysts who own churn features in a product-analytics stack, they can probably extend what they have. If you only need accurate after-the-fact revenue churn reporting, a billing-analytics tool is the proportionate answer. At very large volumes or with near-real-time needs, a warehouse is the right base from the start. TechnoLynx delivers the engineering. Revenue recognition and financial sign-off are outside the TechnoLynx offer.
FAQ
What does a 20% churn rate mean?
It means one in five customers active at the start of the period had left by the end of it. A 2015 comparison of churn techniques cites earlier research reporting that mobile operators lose 20% to 40% of their customers each year.
Is ETL the same as a data pipeline?
ETL (extract, transform, load) is one kind of data pipeline, in which data is transformed before it is loaded into the target store. “Data pipeline” is the broader term for any automated movement of data between systems.
What is the difference between ETL and ELT pipelines?
ETL transforms data before loading it. ELT loads raw data first, usually into a warehouse, and transforms it there. A lightweight churn script doing its joins in Postgres is closer to ETL.
