Finding 1 from step 3
Viewing dropped sharply about [n] days before most cancellations
The drop was a far earlier signal than failed payments.
So what: the retention team gets [n] days to act instead of none.
A streaming platform now sees churn coming [x] weeks earlier
A national subscription video service counted churn three different ways. We agreed one definition per plan, found what happens before customers leave, and gave the retention team a weekly list to act on.
Before: three teams, three churn rates
Billing4.1%
Marketing6.8%
Product9.2%
Same customers, same month. Illustrative figures.
After: one number, agreed
[x]%
of cancellations now flagged in advance, within [n] weeks of kickoff.
- Client
- National subscription video platform
- Industry
- Media & streaming
- Region
- [Region]
- Timeline
- [n] weeks
- Services
- Churn analytics, Segmentation
- Engagement
- Project, [n] weeks
[x]%of churn flagged before it happened, within [n] weeks
[n]behaviour segments used by the retention team
1churn definition per plan, replacing three
[x] hrsof manual reporting removed each month
The short version
Problem
Three teams reported three churn rates, and cancellations were only seen after they happened.
What we did
Agreed one churn rule per plan, mapped the behaviour before cancellation, and built a weekly at-risk list.
Result
[x]% of cancellations flagged in advance, and retention offers aimed at the right [n] segments.
Everyone measured churn, nobody agreed on it
Billing counted a customer as gone the day a payment failed. Marketing waited 30 days. Product counted anyone inactive for two weeks. Each number was defensible, so meetings were spent arguing about which was right instead of what to do.
Revenue at riskEvery lost subscriber costs [x] months of revenue to replace.
Wasted offersWin-back discounts went to people who would have stayed anyway.
Slow reactionBy the time churn showed in reports, the customer had already left.
What we had to work with
The data existed, but it lived in three systems that had never been joined, and one of them had no customer ID the others could match.
Billing records[n] months of renewals, failed payments and plan changes.
Viewing logsDaily sessions and minutes watched per subscriber.
CRM and campaignsPlan, tenure, region and past offers.
ConstraintNo shared customer ID across two systems until week 2.
Four steps, one decision that mattered
- Agreed the ruleWorkshops with billing, marketing and product to set one churn rule per plan.
Week 1 Led to finding 2 - Joined the dataMatched billing, viewing and CRM records into one subscriber table.
Weeks 2 to 3 - Found the signalsCompared the last 60 days of customers who left with those who stayed.
Weeks 3 to 4 Led to finding 1 - Shipped the listA weekly at-risk list with the reason for each customer, plus a retention dashboard.
Weeks 5 to 6
The key decision
We set a different inactivity window for each plan instead of one rule for everyone, because monthly and annual subscribers behave differently before they leave.
What changed
Before
- Three churn rates in three reports
- Churn noticed after cancellation
- One offer sent to every lapsing customer
After
- One documented rule per plan
- At-risk customers flagged every Monday
- Offers matched to [n] segments
Finding 2 from step 1
Promotional plans churned [x] times faster than full-price plans
Customers who joined on a discount left soon after the price reverted.
So what: offer design changed, not just who received offers.
"[One or two sentences from the client about the result, in their own words. The most specific number wins.]"
[Name][Role], [national streaming platform]
Approved for publishingFacing a similar problem?Tell us what's going on. We'll say honestly whether we can help, and how.
Everything stayed with the client
Retention dashboard (anonymised screenshot)
Weekly at-risk list
Churn definition documentOne rule per plan, signed off by each team.
Subscriber data modelOne joined table, refreshed daily.
At-risk listRanked customers with the reason for each.
Retention dashboardCohorts, churn by plan and saves over time.
The engagement, and what it would take for you
EngagementProjectQuoted from a written scope
Timeline[n] weeksFrom first call to handover
Client time~1 hr / weekOne owner answering questions
Price rangefrom $6,000Estimate your project
Services used
What the client did next
| Recommendation | Expected effect | Confidence |
|---|---|---|
| Contact at-risk subscribers when viewing drops | [x]% more saves at the same offer cost | High |
| Redesign the promotional plan's exit price | Lower churn after the discount ends | Medium |
| Review the churn rule each quarter | Definitions stay true as plans change | High |
What this project can't tell you
- The at-risk list predicts who is likely to leave, not whether a given offer will keep them. That needs a test.
- Results cover [n] months; seasonal effects around major releases need a longer window.
- Figures are rounded and anonymised with the client's approval.
For the analysts in the room
Buyers can skip this. Technical reviewers usually want to see it.
Definitionschurn rule, grace window, at-risk
Churn: a subscriber whose paid access has ended and who has not renewed within the plan's grace window. Grace window: days allowed after expiry before a subscriber counts as churned; set per plan.
Methodwhy this approach, not another
A rule per plan came first because the teams had to agree on it. Prediction on top of the rule is only useful once the definition is stable.
Data and toolssources, volumes, stack
Billing, viewing logs and CRM joined in
SQL; segments built in Python; dashboard in Power BI.Check the workcode sample
The core of the definition: a subscriber is churned once the plan's grace window passes without a renewal.
-- Mark churn once a plan's grace window passes without renewal
SELECT s.subscriber_id, s.plan_code, s.end_date,
s.end_date + g.grace_days AS churn_date
FROM subscriptions s
JOIN plan_grace g ON g.plan_code = s.plan_code
WHERE NOT EXISTS (
SELECT 1 FROM subscriptions r
WHERE r.subscriber_id = s.subscriber_id
AND r.start_date BETWEEN s.end_date AND s.end_date + g.grace_days
);Questions readers ask about this project
Could this work with our data?
If you have 12 months of customer, billing and usage history, very likely. The Data Health Check tells you for certain in two weeks.
How much of our team's time did it take?
About an hour a week from one owner, plus two short workshops in the first week to agree the churn rule.
Can we speak to the client?
On request, and only with their permission. Ask on your call and we will check with them.
What would a similar project cost us?
Projects start at $6,000 and are fixed from a written scope. The pricing page has an estimator for your situation.
Counting churn three different ways?
Most subscription businesses we talk to have the same problem. In 30 minutes we can tell you whether your data can support a weekly at-risk list, and what it would take.
- Teams report different churn numbers
- Cancellations are noticed after they happen
- Offers go to everyone, not the people at risk
On the call we will
- Review how you define churn today
- Check which data you already have
- Outline a first step and its cost