Almost every owner of a subscription business has a theory about why customers leave. "It's the price." "Their cards keep failing." "They never use the new feature."
Most of those theories turn out to be false once you look properly. In the last case I worked on, we wrote down six hypotheses and two survived.
This guide is the method I use when a company asks me to understand its churn. It isn't theory: it's the exact order things have to be done in so you don't land on a false conclusion.
It's written for someone who knows SQL or has someone who writes it. You won't find queries ready to paste —your schema isn't mine— but the patterns and, above all, the traps where this analysis breaks silently.
A warning up front: most of the hypotheses you have about your churn are going to turn out false. That isn't a failure of the analysis, it's the analysis working.
Step 1 · Write your hypotheses before you look at the data
It sounds like a formality. It isn't.
If you open the database without having written down what you expect to find, you'll find exactly what you already believed. The data almost always contains some cut that confirms any hunch, and without a prior record you won't be able to tell a finding from a coincidence you liked.
Write four to eight sentences like:
"I think we lose customers because their payments fail." "I think the ones who don't use feature X leave more." "I think the problem is the price."
Save them with a date. At the end you'll mark each one as confirmed, discarded, or reformulated, and that document is worth more than any chart: it's what stops you from spending three months fixing the wrong problem.
Step 2 · Define "customer" and "cancellation" once
More analyses are ruined at this step than at any other, and in the worst way: no errors, no warnings, just numbers that look perfectly reasonable.
Three questions you have to answer in writing:
What is a customer? Your users table almost always contains sign-ups who never paid. If your database says 2,000 users and 500 pay, any rate calculated over 2,000 is wrong. Find the column that unambiguously marks a paying customer —the creation date in your payment gateway usually works— and use it in absolutely every query.
What is a cancellation? Distinguish the date the customer requested the cancellation from the date they lost access. They can be weeks apart. If you use the second one, your analysis will alert you when there's nothing left to do.
When did you start logging? If your event log started a year ago but your business is three years old, every older customer will show up as "zero usage" and you'll conclude that inactivity is lethal. That's an artifact, not a finding.
Warning sign. If the total number of cancellations changes between one query and the next —166, then 169, then 173— don't let it slide. It means two definitions are coexisting, and your comparisons will drift out of alignment bit by bit until you no longer know which one to believe.
Step 3 · Separate involuntary churn from voluntary churn
They're two different phenomena with opposite solutions, and mixing them makes neither one visible.
- Involuntary: the charge failed, nobody updated the card, the subscription died on its own. Fixed with retries and reminders, not with offers.
- Voluntary: the customer decided to leave. Fixed with product, price, or retention.
The operating rule: if there was a successful charge after the last failure, the customer didn't die from payment. The pattern is this:
WITH last_ok AS (
SELECT customer_id, MAX(date) AS d FROM payments
WHERE status = 'approved' GROUP BY customer_id
),
last_fail AS (
SELECT customer_id, MAX(date) AS d FROM payments
WHERE status <> 'approved' GROUP BY customer_id
)
SELECT
CASE
WHEN lf.d IS NULL THEN 'never failed'
WHEN lo.d IS NOT NULL AND lo.d >= lf.d THEN 'failed and recovered'
WHEN DATEDIFF(c.cancel_date, lf.d) BETWEEN -5 AND 30 THEN 'INVOLUNTARY'
ELSE 'voluntary'
END AS type,
COUNT(*) AS cancellations
FROM customers c
LEFT JOIN last_ok lo ON lo.customer_id = c.id
LEFT JOIN last_fail lf ON lf.customer_id = c.id
WHERE c.cancel_date IS NOT NULL
GROUP BY type;
Be ready for the result to disappoint you. In the last case I analyzed, the expectation was that involuntary churn would be 40% of the total. It was 15%. And 63% turned out to be people with a perfectly valid card who simply stopped needing the product.
One important detail when reading decline codes: not all of them are causes. Codes like "exceeded retry limit" are the death certificate, not the disease — they come preceded by other declines. Counting them as an independent cause means counting the same customer twice.
Step 4 · Build a panel, not a flat table
This is the step that separates an analysis that works from one that misleads, and the one almost nobody gets right.
What most people do: one row per customer, with a churned column set to 0 or 1.
Why it's wrong: it gives you a tiny dataset, it doesn't answer when they leave, and above all it pushes you into the reference-date error.
The error of measuring the past from the future
Suppose you want to know whether inactivity predicts cancellation. You calculate "days since last login" for each customer and cross it with whether they left.
For the one who left, you measure up to their cancellation date. For the one still active, you measure up to today. And that's where everything breaks: the one who canceled used the product until shortly before leaving, so they show up as active. The one who abandoned the product eight months ago but never canceled shows up as quiet and healthy.
The result comes out inverted: more silence, less churn. When an analysis gives you something impossible, this is almost always why.
The correct structure
One row per (customer, weekly cutoff) pair. At each cutoff:
- The universe is the paying customers who were alive on that date.
- The features are calculated using only information prior to the cutoff.
- The target is whether they canceled within the next N days.
WITH RECURSIVE cutoffs AS (
SELECT DATE('2025-11-01') AS cutoff
UNION ALL SELECT cutoff + INTERVAL 7 DAY FROM cutoffs WHERE cutoff < '2026-07-16'
),
base AS (
SELECT c.cutoff, u.id, u.cancel_date
FROM cutoffs c
JOIN customers u
ON u.signup_date IS NOT NULL
AND u.signup_date < c.cutoff -- already existed
AND (u.cancel_date IS NULL OR u.cancel_date >= c.cutoff) -- still alive
)
SELECT
b.cutoff, b.id,
-- every feature with a strict cap: ... AND ev.date < b.cutoff
(b.cancel_date IS NOT NULL
AND b.cancel_date < b.cutoff + INTERVAL 30 DAY) AS churn_30d
FROM base b;
The three conditions in the JOIN are what prevent leakage. The < cutoff cap in every feature subquery is what stops the past from seeing the future.
How to size the windows. You need history backward for the features and forward for the target, and both eat into your range. If your log covers 11 months and you use 90 days on each side, you're left with 6 monthly cutoffs: nothing. With 60 days back and 30 forward, on weekly cutoffs, you get more than 30 snapshots per customer. A 30-day target is also more actionable: "leaving this month" tells you what to do, "leaving this quarter" doesn't.
What you need to know about your sample size. If the panel gives you 400 positive rows, you don't have 400 events. Every customer who leaves is flagged in the 4 weeks before their cancellation, so you have around 90 real events. That drastically limits how many features you can use, and forces the train/test split to be by date, never random — otherwise the same customer ends up on both sides.
Step 5 · Measure lift, not percentages
A number on its own means nothing. "32% of the people who do X leave" is useless until you know how many leave without doing X.
lift = churn rate in the segment ÷ base rate
How to read it, with the thresholds I use:
| Lift | Reading |
|---|---|
| Below 1.2× | Noise. Discard it. |
| 1.2× to 1.5× | Probably tenure bias disguised as signal. |
| 1.5× to 2.5× | Real but weak signal. Useful in combination. |
| Above 2.5× | Actionable on its own. |
About that tenure bias: someone who has been around longer has more transactions, more chance of having failed a payment at some point and more chance of having left. Almost any cumulative feature will show 1.3× for that reason alone. If your signal is in that range, you probably have nothing.
Step 6 · Run a univariate sweep before modeling
Before training anything, materialize the panel into a table and run the same query over every candidate feature:
SELECT my_feature,
COUNT(*) AS rows,
SUM(churn_30d) AS churn,
ROUND(100*SUM(churn_30d)/COUNT(*), 2) AS pct,
ROUND(SUM(churn_30d)/COUNT(*)/0.0387, 2) AS lift -- your base rate
FROM panel GROUP BY 1;
Ten minutes of this tells you whether a model is possible at all. And sometimes the answer is no, which is also a result: it saves you two months.
Features worth testing in a subscription business:
- Actions that signal the end of the need (canceling a service, closing a project, deactivating a resource inside your product)
- Use of the core feature, not the generic login
- Days since last activity, bucketed
- Trend: events in the last 15 days over the last 60
- Tenure, bucketed
- Plan type, monthly versus annual
- Days until the next renewal
- Recent payment failures
Step 7 · Look for interactions, not just features
This is the step with the highest payoff and the one almost everyone skips.
In the last analysis, measured separately:
- Silence longer than 14 days → 1.05×. Noise.
- A certain end-of-need action → 2.33×. Moderate signal.
- The two together → 4.09×.
Silence on its own meant nothing. Conditioned on there being a reason for the silence, it nearly doubled the signal. Silence means nothing until there's a reason for the silence.
Cross your two or three best features in four-cell tables. It's elementary arithmetic and it finds things that a twenty-feature model buries.
Step 8 · Separate the moment of prediction from the moment of action
Brace yourself for an uncomfortable result: the signal tends to get more accurate after your window of influence has closed.
In the case I keep citing, prediction improved the more time passed since the triggering event: 1.89× at 14 days, 3.46× at 45. But the customer abandoned the product at day 17 and only formalized the cancellation at day 46. At 45 days the prediction was excellent and completely useless: the person hadn't logged in for a month.
The underlying cause is that you're predicting an administrative event —the cancellation, whose date is set by your billing cycle— using behavioral signals. Between the two there are weeks of lag that no feature can anticipate.
The practical consequence: use different thresholds for different things.
- Act early, with low precision, when the action costs nothing. An in-product message at the moment of the triggering event doesn't need to be right: it bothers no one.
- Act late, with high precision, when the action is expensive. Human contact, an offer, or a discount do require being fairly sure.
Step 9 · Keep a control group
Your analysis tells you who is at risk. It doesn't tell you who is persuadable. Those are different things: some customers leave regardless, and some were going to stay on their own and you just handed them a discount.
The only way to know the difference is to not intervene with everyone. For two or three months, contact 80% of your alerts and leave 20% untouched. That way you measure the real effect instead of assuming it.
Without a control group you'll see that "40% of those contacted stayed" and you'll have no idea how many would have stayed anyway. That number will lead you to scale a campaign that may do nothing at all.
Checklist of errors
Go through each point before presenting results:
- The universe is paying customers, not registered users
- The cancellation date is the request date, not the end-of-access date
- I know since when I have data and excluded everything before that
- I separated involuntary churn from voluntary churn
- Every feature has a strict cap prior to the cutoff
- The total number of cancellations is the same across all my queries
- I reported lift, not just percentages
- I know how many real events I have, not how many rows
- The validation split is temporal, not random
- No result came out backwards from what's plausible without an explanation
- I marked every hypothesis from step 1 as confirmed, discarded, or reformulated
A result you have to be willing to accept
Sometimes the honest conclusion is that your churn is not predictable with the data you have, and that isn't a defect of the analysis.
If most of your cancellations are customers who paid on time, used what they were supposed to use, and one day stopped needing the product because of an external event you don't log, then no available feature is going to anticipate that moment.
The right answer there isn't a better model. It's two or three simple rules executed well, plus product work so that more than one reason to come back exists.
With fewer than a hundred cancellation events, moreover, a logistic regression with ten features performs practically the same as any sophisticated model, with more interpretability and no overfitting risk. If someone proposes gradient boosting with forty features over ninety events, they're not helping you.
How far this guide goes
With these nine steps you can get to a built panel, tested hypotheses, and one or two actionable rules. That's already more than most companies your size have.
There are four places where this method hits its limit and expert hands are worth it:
Instrumenting what you're not logging. The most valuable feature almost always doesn't exist in your database yet. Deciding what to start logging —and doing it without polluting what you already have— is design work, not query work.
Designing the experiment. A badly built control group is worse than none, because it gives you confidence in a false number. Size, assignment, and stopping criteria all have rules.
Uplift models. Going from "who is at risk" to "who benefits from being contacted" is a different discipline, and it requires having run the experiment first.
Putting it in production. An analysis you run once ages in weeks. Turning it into automatic alerts that land in the right channel is where the work really starts paying off — the same principle I apply when I turn a manual report into automatic sales reports every Monday or a late P&L into a real-time cash flow dashboard.
Frequently asked questions about churn analysis
Do I need a data scientist to do this? For steps 1 through 7, no. You need someone who writes SQL carefully and understands the traps in this article. Steps 8 and 9 —action thresholds and control group design— are where experience really starts to matter.
How many customers do I need for this analysis to make sense? What matters isn't how many customers you have, but how many real cancellations exist in your data window. With fewer than 50 events, stay in steps 1 through 3 and the univariate sweep: it's good for discarding hypotheses, not for building a model.
Does this work if my business isn't SaaS? Yes, as long as there's a recurring relationship with a start date and an end date: memberships, service plans, maintenance contracts, retainers. The vocabulary changes, the method doesn't.
Can I skip the panel and use a one-row-per-customer table? You can, but you'll get the inverted result described in step 4 and you won't notice. That error produces no error message: it produces a reasonable, wrong number.
What if my conclusion is that there's no signal? That's a valid result and it saves you months. The answer there isn't a more sophisticated model: it's instrumenting what you're not logging today and working on the product so more than one reason to come back exists.
Can I automate this so it runs on its own? Yes, and that's where the work starts paying off: the panel recalculates every week and alerts land in the channel your team already works in. Before automating it, it's worth having validated that the signal exists — the same mistake I see in AI pilots that never reach production.
If you made it this far, you already know the problem isn't a lack of data: it's the order in which you look at it. If you want us to go through what yours are telling you —or to build the panel and the alerts so they run on their own— message me on WhatsApp. No cost, no commitment.
This guide is based on a real analysis of a Chilean SaaS platform. The figures cited are from that case; yours will be different, and that's exactly the point.