How to Analyze Customer Churn: Key Metrics, Warning Signals, and How to Build a Churn Report
Customer churn is costing your business more than you think. This guide covers every metric you need, churn rate, churned MRR, payment method signals, contract cohorts, with step-by-step Excel formulas and a real dataset. Plus, see how Glow Reports generates the same analysis automatically.
Customer churn, the rate at which customers stop doing business with you, is one of the most expensive and undertracked problems in any subscription or recurring-revenue business. The good news: it is also one of the most preventable, once you know which data signals to watch. This guide walks you through how to analyze customer churn from scratch: which KPIs to calculate, how to identify high-risk segments, and how to build a churn report your leadership team can actually act on, with a real-world telco dataset as a running example throughout.
What Is Customer Churn and Why Does It Hurt Your Revenue?
Customer churn rate is the percentage of customers who cancel or leave during a given period. For subscription businesses, it directly erodes Monthly Recurring Revenue (MRR), inflates Customer Acquisition Cost (CAC) payback periods, and compresses Customer Lifetime Value (LTV).
A 26.54% annual churn rate, like the one we found in our telco dataset of 7,043 customers, means that more than 1 in 4 customers are gone every year. If your average customer pays $70/month, losing 1,869 customers wipes out over $1.5 million in annualized revenue. That is not a CX problem. That is a finance problem.
Why most companies miss it: churn hides in plain sight inside billing exports and CRM data that nobody analyzes systematically. The fix is building a repeatable churn analysis workflow, and this article shows you exactly how.
Step 1: Calculate Your Core Churn KPIs
Before you can act on churn, you need three foundational metrics. Here is how to calculate them in Excel, and what they looked like in our example dataset.
Overall Churn Rate
The baseline metric. It tells you what share of your customer base left in a given period.
Formula:
Churn Rate = Churned Customers ÷ Total Customers at Start of Period
In Excel (assuming column A = CustomerID, column B = Churn status with "Yes"/"No"):
=COUNTIF(B:B,"Yes") / COUNTA(A:A)
Format the cell as a percentage.
In our dataset: 1,869 churned out of 7,043 customers = 26.54% churn rate.
Revenue at Risk (Churned MRR)
Knowing how many customers churned matters less than knowing how much revenue they represented. High-value churners are a bigger problem than low-value ones.
Formula:
Churned MRR = SUM of Monthly Charges for all churned customers
In Excel (column C = Monthly Charges, column B = Churn):
=SUMIF(B:B,"Yes",C:C)
In our dataset: Churned customers had an average monthly charge of $74.44 vs. $61.27 for retained customers, meaning churners were disproportionately high-value accounts.
Average Revenue Per User (ARPU) by Churn Status
Comparing ARPU between churned and retained customers reveals whether your churn problem is concentrated at the top of your price ladder.
In Excel:
=AVERAGEIF(B:B,"Yes",C:C) → Average charge for churned customers
=AVERAGEIF(B:B,"No",C:C) → Average charge for retained customers
In our dataset: The median monthly charge for churners was $79.65, significantly above the median for retained customers. This is a warning sign that your premium tier has a value-perception problem.
Step 2: Identify Your Highest-Risk Churn Segments
Once you have your top-line numbers, the real analytical work begins: understanding who is churning and why. In our analysis, five dimensions consistently separated high-risk customers from stable ones.
Signal 1: Contract Type — Your Strongest Retention Lever
What to look for: Compare churn rates across different contract lengths. Month-to-month, annual, and multi-year customers almost always show dramatically different churn behavior.
In Excel: Create a PivotTable with Contract Type in Rows, and calculate churn rate per group:
- Add a helper column:
=IF(B2="Yes",1,0)(1 = churned) - In your PivotTable, use Average of the helper column to get churn rate per contract type
What we found in Glow Reports:
| Contract Type | Total Customers | Churned | Churn Rate |
|---|---|---|---|
| Month-to-month | ~3,875 | 1,655 | 42.71% |
| One year | ~1,473 | 166 | 11.30% |
| Two year | ~1,695 | 48 | 2.83% |
The gap is staggering. A customer on a two-year contract is 15× less likely to churn than a month-to-month customer. This is your single biggest commercial lever for churn reduction.
Signal 2: Payment Method — A Hidden Behavioral Signal
What to look for: Customers paying by manual methods (especially electronic checks) tend to churn far more than those on automatic payments. This can indicate payment failures, lower commitment, or a specific customer profile that is more price-sensitive.
In Excel: Repeat the PivotTable approach with Payment Method in Rows.
What we found in Glow Reports:
| Payment Method | Churn Rate |
|---|---|
| Electronic check | 45.29% |
| Mailed check | 19.11% |
| Bank transfer (auto) | 16.71% |
| Credit card (auto) | 15.24% |
Electronic check customers churn at nearly 3× the rate of automatic payment customers. If you can migrate even a portion of this group to auto-pay, you will see an immediate churn reduction.
Signal 3: Monthly Charges — The Price Band Problem
What to look for: Do your highest-paying customers churn more? If yes, you have a value-perception problem at the top of your pricing tiers, customers who pay more expect more, and if they don't feel the value, they leave.
In Excel: Create charge bands using an IF or IFS formula:
=IFS(C2<=20,"0-20", C2<=40,"20-40", C2<=60,"40-60", C2<=80,"60-80", C2<=100,"80-100", TRUE,"100+")
Then PivotTable by band to compare churn rates.
What we found in Glow Reports:
| Monthly Charge Band | Churn Rate |
|---|---|
| $0–20 | 9.01% |
| $20–40 | ~13% |
| $40–60 | ~23% |
| $60–80 | ~30% |
| $80–100 | 37.19% |
| $100+ | 28.53% |
The $80–100 band has 653 churned customers alone. These are high-value customers who are leaving, and each one costs more to replace than a low-tier customer.
Signal 4: Product / Service Bundle
What to look for: Certain product combinations churn at much higher rates than others. This can reveal pricing mismatches, service quality issues, or over-bundling.
In Excel: Add Internet Service as a filter or secondary row in your PivotTable to calculate churn by service type.
What we found in Glow Reports:
| Internet Service | Phone Service | Churn Rate |
|---|---|---|
| Fiber optic | Yes | 41.89% |
| DSL | Yes | 24.93% |
| DSL | No | 16.62% |
| No internet | Phone only | 7.40% |
Fiber optic + phone customers are churning at more than 5× the rate of phone-only customers. This points to a perceived value or quality gap in the premium bundle.
Signal 5: High-Risk Combinations (Segment Crossing)
What to look for: The most actionable churn insights come from combining multiple dimensions. A month-to-month customer paying by electronic check with Fiber optic internet is a very different risk profile from a two-year customer on auto-pay.
In Excel: Use a concatenated key to create micro-segments:
=A2&" | "&D2&" | "&E2
(combining Contract Type, Internet Service, and Payment Method into one field), then PivotTable to get churn rates per combination.
What we found in Glow Reports, Top High-Risk Segments:
| Contract | Internet Service | Payment | Churn Rate |
|---|---|---|---|
| Month-to-month | Fiber optic | Electronic check | 60.37% |
| Month-to-month | Fiber optic | Mailed check | 50.75% |
| Month-to-month | Fiber optic | Bank transfer | 45.57% |
| Month-to-month | DSL | Electronic check | 41.64% |
The single worst segment, month-to-month + Fiber optic + electronic check, churns at 60.37%. These customers are practically one foot out the door. Identifying this segment in your own data allows you to launch a targeted retention campaign before they leave.
Step 3: Build a Churn Report You Can Share
Knowing the numbers is only half the job. The other half is packaging them into a report your CFO, VP of Sales, and Product team can read and act on in 10 minutes.
A solid churn report should contain:
- Overall churn KPI : rate, absolute number, MRR lost
- Churn by contract type : with a bar chart showing rate and volume
- Churn by payment method : with flagging for high-risk methods
- Churn by price band : with average and median charges for churned vs. retained
- Churn by service / product bundle : to surface bundle-level issues
- Top 10 high-risk micro-segments : the specific combinations that need urgent attention
- Recommended actions : tied directly to each finding
In Excel, building this from scratch means creating 6–8 PivotTables, writing chart macros, formatting tables to be presentable, and then manually refreshing everything each month. For most teams, this takes a day or more and the output rarely looks polished enough for a board-level audience.
How Glow Reports Does All of This in Minutes
This is exactly the workflow that Glow Reports was built to replace.
With Glow Reports, you upload your customer data as a CSV or Excel file, the same export you already have from your CRM or billing system. You describe what you want to analyze ("analyze churn by contract type, payment method, price band, and service bundle"), and Glow Reports automatically:
- Profiles your data for completeness and distributions
- Calculates churn rates across every relevant dimension
- Identifies your highest-risk micro-segments
- Generates a clean, narrative report with charts, tables, and recommended actions, ready to share as a link or PDF
The report we showed throughout this article, with all the churn rates, segment breakdowns, cohort heatmaps, and action plans, was generated by Glow Reports from a raw CSV in a matter of minutes. No SQL. No Excel formulas. No manual chart formatting.
From Churn Analysis to a Monthly Retention Rhythm
The most effective churn programs don't just analyze once they track churn continuously and adjust retention actions based on what's working.
A simple monthly cadence looks like this:
- Refresh your data : export updated customer and billing data from your CRM
- Generate your Glow Reports churn report : get an up-to-date view of churn rate, MRR at risk, and segment shifts
- Review with stakeholders : share the report link with Commercial, Product, and Operations; prioritize the top 2–3 retention actions for the month
- Track milestones : monitor whether targeted interventions (contract migration incentives, payment method campaigns, bundle pricing changes) are moving the churn needle
Specific, measurable goals make this actionable, for example:
- Shift 10% of month-to-month customers to annual contracts within 90 days
- Reduce electronic check churn by 15 percentage points within 6 months
- Cut Fiber optic bundle churn by 8 percentage points through a targeted service quality initiative
Every business has its own data structure, retention goals, and reporting needs. If you'd like a churn analysis tailored to your specific context, your data model, your KPIs and your team's workflow, the Glow Reports team is available to help you set up a custom solution.
Getting Started: Analyze Your Own Churn Data Today
Whether you start in Excel with the formulas above or go straight to Glow Reports, the most important thing is to start. Churn is predictable and once you can see which customers are most at risk and why, you have everything you need to intervene before revenue walks out the door.
If you want to skip the manual work and get a board-ready churn report from your data in minutes, try Glow Reports, upload your customer file, describe your analysis goal, and see the results for yourself.
