Why Subscription Churn Matters More Than You Think
Your monthly numbers:
- New subscribers: 50
- Canceled subscribers: 30
- Net growth: +20 subscribers
You think: "We're growing! +20 is good!"
The reality:
- You have 200 active subscribers
- 30 cancellations = 15% monthly churn
- At 15% monthly churn, you lose 84% of customers within a year
- You need 168 new subscribers/month just to stay flat
The math that kills subscription businesses:
```
Month 1: 200 subscribers × (1 - 0.15) = 170 remain
Month 2: 170 × (1 - 0.15) = 145 remain
Month 3: 145 × (1 - 0.15) = 123 remain
...
Month 12: 32 remain (84% churned)
```
Every 1% of monthly churn costs you 11% of annual customers.
This guide teaches you to calculate, track, and predict churn before it kills your business.
The 5 Critical Subscription Metrics
1. Monthly Churn Rate
Formula:
```
Monthly Churn Rate = (Customers Lost in Month) / (Customers at Start of Month) × 100
```
Example:
- Jan 1: 200 active subscribers
- Jan 31: 30 cancellations
- Churn rate: 30 / 200 × 100 = 15%
Benchmarks:
- <3% monthly: Excellent (consumer subscriptions)
- 3-5% monthly: Good
- 5-7% monthly: Concerning
- >7% monthly: Crisis mode
2. Revenue Churn Rate (More Important)
Why it matters: Losing 10 small customers ($10/mo) hurts less than losing 1 big customer ($500/mo).
Formula:
```
Revenue Churn = (MRR Lost from Cancellations) / (MRR at Start of Month) × 100
```
Example:
- Jan 1 MRR: $10,000
- Cancellations: 30 customers totaling $2,000 MRR
- Revenue churn: $2,000 / $10,000 × 100 = 20%
Reality check: You lost 15% of customers but 20% of revenue. Your churning customers were higher-value.
3. Monthly Recurring Revenue (MRR)
Formula:
```
MRR = Sum of all active monthly subscription values
```
For annual subscriptions:
```
MRR = (Annual subscription price) / 12
```
MRR Movement:
```
New MRR = Revenue from new subscribers
Expansion MRR = Revenue from upgrades/add-ons
Contraction MRR = Revenue from downgrades
Churned MRR = Revenue from cancellations
Net New MRR = New + Expansion - Contraction - Churned
```
4. Retention Rate (The Flip Side of Churn)
Formula:
```
Retention Rate = 100% - Churn Rate
```
Example:
- 15% churn = 85% retention
Why track both:
- Churn shows problem magnitude
- Retention shows what you're keeping
- Goal: Maximize retention, not just minimize churn
5. Customer Lifetime (How Long Until They Cancel)
Formula:
```
Average Customer Lifetime (months) = 1 / Monthly Churn Rate
```
Example:
- 5% monthly churn = 1 / 0.05 = 20 months average lifetime
- 10% monthly churn = 1 / 0.10 = 10 months average lifetime
Why it matters: Affects how much you can spend on acquisition.
Build a Subscription Churn Dashboard in Excel
-
3
Export Subscription Data
From Shopify/ReCharge/Stripe: Export all subscriptions with Customer ID, Subscription Start Date, Cancel Date (if any), Monthly Value. Save as CSV. You need historical data (6+ months).
-
3
Calculate Monthly Churn by Month
Create summary table with months in rows. For each month: Customers at Start = COUNTIFS(Start_Date, '<=month_end', Cancel_Date, '>month_end' OR ISBLANK). Cancellations = COUNTIFS(Cancel_Date, month). Churn % = Cancellations / Start.
-
3
Calculate MRR by Month
For each month: MRR = SUMIFS(Monthly_Value, Start_Date, '<=month_end', Cancel_Date, '>month_end' OR ISBLANK). This sums monthly value of all active subscriptions on last day of month.
-
3
Build Cohort Retention Table
Rows = Cohort (month customer subscribed). Columns = Months since subscription (0, 1, 2, 3...). Each cell = % of cohort still active at that month. Shows which cohorts retain best and when cancellations typically happen.
-
3
Create Retention Curve Chart
Select cohort retention table → Insert → Line Chart. X-axis = Months since subscription. Y-axis = % retained. Each line = one cohort. Reveals if retention improving over time or typical drop-off points.
-
3
Add MRR Trend Chart
Create line chart: X-axis = Month. Y-axis = MRR. Add trend line to show if MRR growing/flat/declining. Add second chart showing MRR components: New, Expansion, Churned stacked.
-
3
Set Up Churn Alert Thresholds
Add conditional formatting: If monthly churn > 7%, cell turns red. If MRR declines month-over-month, cell turns red. Create alerts section at top of dashboard for at-a-glance health check.
Cohort Analysis: When Do Customers Actually Cancel?
What Cohort Analysis Reveals:
Most subscription businesses see this pattern:
- Month 0-1: 5-10% cancel (immediate buyer's remorse)
- Month 2-3: 15-25% cancel (didn't get expected value)
- Month 4-6: 10-15% cancel (forgot about it or tried alternative)
- Month 7-12: 5-10% cancel (steady churn)
- Month 12+: 2-5% cancel (loyal customers)
Building a Cohort Table in Excel:
Step 1: Define Cohorts by Start Month
Add column to customer data:
```excel
Cohort = TEXT(Start_Date, "MMM YYYY")
```
Now each customer tagged with their start month: "Jan 2024", "Feb 2024", etc.
Step 2: Calculate Months Since Subscription
For each customer each month:
```excel
Months_Since = DATEDIF(Start_Date, Analysis_Month_End, "M")
```
Step 3: Count Active Customers by Cohort by Month
Create table:
- Rows: Cohort (Jan 2024, Feb 2024, etc.)
- Columns: Months Since Subscription (0, 1, 2, 3...)
- Cells: % of original cohort still active
Formula for each cell:
```excel
=COUNTIFS(Cohort, Row_Cohort, Months_Since, Column_Month, Status, "Active") / COUNTIFS(Cohort, Row_Cohort)
```
Example Table:
| Cohort | Month 0 | Month 1 | Month 2 | Month 3 | Month 6 | Month 12 |
|--------|---------|---------|---------|---------|---------|----------|
| Jan 2024 | 100% | 92% | 85% | 78% | 65% | 52% |
| Feb 2024 | 100% | 94% | 88% | 82% | 70% | - |
| Mar 2024 | 100% | 95% | 90% | 85% | - | - |
What This Reveals:
- Feb/Mar cohorts retain better than Jan
- Biggest drop-offs: Month 1 (8-10%), Month 2 (7-8%)
- By month 12, you keep ~50% of customers
- Newer cohorts improving (learning from churn data)
MRR Analysis: The Revenue Health Check
MRR Movement Breakdown
Track 5 components that affect MRR:
1. New MRR:
- New customers who signed up this month
- Formula: `=SUMIFS(Monthly_Value, Start_Date, ">="&Month_Start, Start_Date, "<="&Month_End, Status, "Active")`
2. Expansion MRR:
- Existing customers who upgraded
- Track: Price changes where new > old for active customers
- Formula: `=SUMIFS(Price_Increase, Change_Date, Month)`
3. Contraction MRR:
- Existing customers who downgraded
- Track: Price changes where new < old for active customers
- Formula: `=SUMIFS(Price_Decrease, Change_Date, Month)`
4. Churned MRR:
- Customers who canceled
- Formula: `=SUMIFS(Monthly_Value, Cancel_Date, Month)`
5. Reactivation MRR:
- Churned customers who came back
- Track: Customers with cancel date AND new start date after cancel
Net New MRR Formula:
```excel
Net_New_MRR = New_MRR + Expansion_MRR + Reactivation_MRR - Contraction_MRR - Churned_MRR
```
Example Monthly MRR Movement:
```
Starting MRR (Jan 1): $50,000
New MRR: +$8,000 (80 new customers × $100/mo)
Expansion MRR: +$1,500 (15 upgrades avg $100 increase)
Reactivation MRR: +$500 (5 returns × $100/mo)
Contraction MRR: -$800 (8 downgrades avg $100 decrease)
Churned MRR: -$6,000 (60 cancels × $100/mo)
Net New MRR: +$3,200
Ending MRR (Jan 31): $53,200
MRR Growth Rate: 6.4% ($3,200 / $50,000)
```
MRR Growth Rate Targets:
- <5% monthly: Slow growth
- 5-10% monthly: Healthy growth
- 10-20% monthly: Fast growth
- >20% monthly: Hypergrowth (unsustainable long-term)
Negative Churn (The Holy Grail):
When expansion MRR > churned MRR, you have negative churn.
Example:
- Churned MRR: -$6,000
- Expansion MRR: +$7,000
- Net: +$1,000 from existing customers
You grow even if you get zero new customers. Rare but powerful.
Predictive Churn: Identify At-Risk Customers Before They Cancel
Early Warning Signs (Churn Predictors):
1. Declining Usage/Engagement:
- Subscription box: Orders skipped 2+ times
- Software: Login frequency dropped 50%+
- Content: Last login >30 days ago
2. Support Ticket Volume:
- Multiple support requests in short period
- Unresolved complaints
- Negative sentiment in communications
3. Payment Issues:
- Failed payment attempts
- Expired credit card not updated
- Downgraded after failed payment
4. Plan Changes:
- Downgraded from premium to basic
- Reduced quantity (3 boxes → 1 box)
- Moved from annual to monthly (less commitment)
5. Cohort Risk Patterns:
- Customer approaching high-churn month (month 3 if that's your pattern)
- Belongs to historically high-churn cohort
- Hasn't engaged since first month
Building a Churn Risk Score in Excel:
Assign Points for Risk Factors:
```excel
Risk_Score =
IF(Last_Login > 30 days, 25, 0) +
IF(Support_Tickets > 2, 20, 0) +
IF(Payment_Failed = TRUE, 30, 0) +
IF(Downgraded = TRUE, 15, 0) +
IF(Months_Since < 4, 10, 0)
```
Risk Categories:
- 0-20 points: Low risk (healthy customer)
- 21-40 points: Medium risk (monitor)
- 41-60 points: High risk (intervention needed)
- 61+ points: Critical risk (likely to churn)
Action Matrix:
| Risk Level | Action | Owner | Timeline |
|------------|--------|-------|----------|
| Critical (61+) | Personal outreach, discount offer | Account Manager | 24 hours |
| High (41-60) | Email survey, offer help | Customer Success | 3 days |
| Medium (21-40) | Educational content, engagement email | Marketing | 1 week |
| Low (0-20) | Standard nurture | Automated | Ongoing |
Advanced: Calculate Customer Lifetime Value (LTV) from Churn
Simple LTV Formula:
```
LTV = Average Revenue Per User (ARPU) × Average Customer Lifetime
Where:
Average Customer Lifetime = 1 / Monthly Churn Rate
```
Example:
- ARPU: $50/month
- Monthly churn: 5%
- Average lifetime: 1 / 0.05 = 20 months
- LTV: $50 × 20 = $1,000
Why This Matters:
If LTV = $1,000, you can spend up to ~$333 to acquire a customer (targeting 3:1 LTV:CAC ratio).
More Accurate LTV (Accounting for Time Value):
```
LTV = (ARPU × Gross Margin %) / (Monthly Churn Rate + Discount Rate)
```
Example:
- ARPU: $50
- Gross margin: 80% (product costs 20% of revenue)
- Monthly churn: 5% (0.05)
- Discount rate: 1% monthly (0.01)
LTV = ($50 × 0.80) / (0.05 + 0.01) = $40 / 0.06 = $667
More conservative than simple formula ($667 vs $1,000).
Excel Formula:
```excel
=AVERAGE(Monthly_Value) * 0.8 / (Average_Churn_Rate + 0.01)
```
Reducing Churn: The Retention Playbook
Phase 1: Onboarding (First 30 Days)
Goal: Get customer to "aha moment" fast
Tactics:
- Welcome series (5-7 emails over first month)
- Onboarding checklist (get 3 wins in first week)
- Personal check-in (founder/success manager email at day 7)
- Usage milestones ("You've unlocked X feature!")
Excel Tracking:
- % of customers who complete onboarding
- Churn rate: Completed onboarding vs didn't
- Typical: 3-5× lower churn if onboarded properly
Phase 2: Value Reinforcement (Days 30-90)
Goal: Build habit, demonstrate ongoing value
Tactics:
- Weekly wins emails ("You saved $X this month")
- Educational content matching their use case
- Feature spotlights (show underused features)
- Community building (user group, forum)
Excel Tracking:
- Engagement score (logins, feature usage)
- Cohort retention curves (is month 3 drop-off decreasing?)
Phase 3: Loyalty & Expansion (90+ Days)
Goal: Upsell, prevent boredom, create advocates
Tactics:
- Upgrade prompts based on usage patterns
- Annual plan incentive (2 months free)
- Referral program (advocate creation)
- Exclusive features for long-term customers
Excel Tracking:
- Expansion MRR from existing customers
- Negative churn rate (expansion > churn)
Rescue Mission: Cancellation Flow
When customer clicks "Cancel", offer:
1. Pause option: "Take a break, come back later"
2. Downgrade option: "Try smaller plan"
3. Discount offer: "Stay for 50% off next 3 months"
4. Exit survey: "What would make you stay?" (data gold)
Excel Tracking:
- Cancellation reason categories
- Save rate from cancellation flow interventions
Tired of manual churn tracking? Get automated subscription analytics with MRR trends, cohort analysis, and at-risk customer alerts delivered weekly.
Track Subscription MetricsIntegrates with Shopify, ReCharge, Stripe subscriptions
Common Mistakes to Avoid
Learn from these common pitfalls to ensure success
Calculating churn rate wrong: using end-of-month customers instead of start-of-month
Solution: Churn = Cancels / Customers at START of period. If you gained 50 and lost 30, denominator is start count, not end count.
Ignoring revenue churn vs customer churn
Solution: Losing 10 small customers is different from losing 1 whale. Track both customer count churn AND revenue churn (MRR lost).
Not segmenting churn by cohort or plan type
Solution: Overall churn hides patterns. Segment by: acquisition month, plan tier, acquisition channel. Different segments need different retention tactics.
Treating all churn as equal - some customers you WANT to lose
Solution: Low-value, high-support customers who churn might improve your unit economics. Focus retention on high-LTV segments.
How to Verify Your Numbers
Ensure accuracy with these verification steps
Your monthly churn rate stays consistent month-to-month (or shows improving trend)
MRR growth rate = (New + Expansion - Churn - Contraction) / Starting MRR matches your manual calculation
Cohort retention table shows clear patterns of when customers typically cancel
LTV calculation (ARPU / Churn Rate) gives you reasonable CAC budget (LTV should be 3-5× CAC)
Sum of all customer monthly values = your total MRR for that month
Frequently Asked Questions
What's a good churn rate for subscription boxes?
How do I calculate churn if customers pause subscriptions?
Should I focus on reducing churn or increasing acquisition?
How far back do I need subscription data?
What if my churn varies wildly month-to-month?
Ready to Transform Your Reporting?
Start with a free report and see exactly what you'll get every time.