What Is a Churn Report? Churn Rate, Churn Metrics and How to Build One in Excel

What a churn report is, which churn metrics it holds and how to build one each month in Excel or Google Sheets, with formulas checked on IBM's telco data.

Founder, Glow Reports. , 4 min read. Updated
  • Churn
  • Retention
  • Excel
Customers leaving a subscription box one by one, sorted into colored segments on a report
On this page
  1. What is a churn report?
  2. What is churn rate?
  3. Which churn metrics go in a churn report?
  4. How do you build a churn report in Excel or Google Sheets?
  5. How do you identify churned customers without a churn column?
  6. How do you read a customer churn table?
  7. Which churn signals show up in a customer export?
  8. What should a monthly churn report contain?
  9. Where does Glow Reports fit?
  10. Common questions

A churn report is one page that says how many customers left in a period, how much revenue left with them, and which customers leave most often. This guide lists the churn metrics it holds and shows how to build it from a customer export in Excel or Google Sheets. Every figure comes from IBM's public telco sample of 7,043 customers, and each formula reproduces its figure on that file, so you can follow along before you run it on yours.

What is a churn report?

A churn report is a regular summary, usually monthly, of the customers you lost: how many, what they paid, who they were, and what the team will do about it. It turns a list of cancellations into a few numbers that mean the same thing every month.

It answers four questions:

  1. How many customers left, as a count and as a rate?
  2. How much monthly revenue left with them?
  3. Which groups of customers leave most often?
  4. What will the team change, who owns it, and by when?

The first two are the headline. The third is where the report earns its keep, because a single churn rate tells you that customers leave and nothing about why. The fourth turns the report into a decision.

What is churn rate?

Churn rate is the number of customers who left during a period, divided by the number of customers you had at the start of that period. Always name the period. A 2% monthly churn rate and a 2% annual one describe two different businesses.

Churn rate
Churn rate = customers lost during the period / customers at the start of the period

In IBM's sample, the Churn column marks customers who "departed within the last month" IBM documentation. 1,869 of the 7,043 customers left.

26.54% of the customers in IBM's sample left within the month.

IBM describes the company as fictional, so treat these numbers as a teaching set and not as a benchmark. An earlier version of this article called that 26.54% an annual rate. It is monthly, because of how the Churn column is defined.

Which churn metrics go in a churn report?

Five metrics cover most churn reports: the churn rate, the customers lost, the revenue lost, the share of revenue lost, and the churn rate of each customer group. Put the first four at the top and the groups below.

MetricFormula in the sampleResult
Churn rate26.54%
Customers lost1,869
Monthly revenue lost$139,130.85
Share of monthly revenue lost30.50%
Average monthly charge of customers who left$74.44

Column U holds Churn and column S holds MonthlyCharges. The same AVERAGEIF with "No" gives $61.27 for customers who stayed.

Count revenue as well as customers. 26.54% of customers left and took 30.50% of the monthly revenue with them, because customers who left paid a median of $79.65 a month against $64.43 for those who stayed. When churn sits in the higher tiers, a customer count alone hides the damage. For subscription revenue in Stripe and HubSpot, with gross and net MRR churn, see how to calculate real MRR and churn.

How do you build a churn report in Excel or Google Sheets?

Start from one row per customer with an ID, a status or end date, a plan and a monthly charge. Count the customers at the start, mark the ones who left, then compute the metrics above and the rate of each group with COUNTIFS.

  1. Export your customers as CSV or Excel. If the file needs tidying first, see how to clean data in Excel.
  2. Download IBM's file from its public repository if you want to practise first.
  3. Type the five formulas from the table above in empty cells.
  4. Add one rate per group: gives the churn rate of month-to-month customers. Column P holds Contract.
  5. Put the definitions on the sheet: the period, what counts as a customer who left, and which customers you exclude.

The ranges start at row 2 on purpose. A formula on the whole column, such as , counts the header too and divides by 7,044 instead of 7,043.

A PivotTable gives the same group rates without one formula per group. Add a column with , put the group in Rows and show the average of that column. The pivot table by month guide shows how to repeat it month after month.

Google Sheets accepts COUNTIF, COUNTIFS, SUMIF and AVERAGEIF with the same arguments Google Docs Editors Help. Microsoft documents the Excel versions on its COUNTIFS page.

How do you identify churned customers without a churn column?

Compare two lists. Take the customers active at the start of the month, look each one up in the list at the end of the month, and mark as lost every ID that is missing. New customers never enter the count.

Most exports have no Churn column like IBM's. With last month's IDs in column A and this month's export on a sheet named , type in column B. TRUE marks a customer who left. The churn rate is then for 500 starting customers.

Avoid the shortcut of start count minus end count. New customers who joined during the month raise the end count, so the result understates churn and can even turn negative. A HubSpot user asking for churn by owner got the same advice from the community: count cancellations against active companies at the start of the month, and export to a spreadsheet when the tool can't HubSpot Community.

When customers buy without a contract, decide when silence means gone, for example no order in 90 days, and write that rule on the report. Without it, a customer who skipped one month counts as lost.

How do you read a customer churn table?

Read each row as a group's churn rate next to its size. A high rate on a large group calls for action. The same rate on a dozen customers is noise. Compare each rate with the overall 26.54%.

By contract

ContractCustomersLeftChurn rate
Month-to-month3,8751,65542.71%
One year1,47316611.27%
Two year1,695482.83%

Month-to-month customers leave 15 times as often as customers on a two-year contract.

By payment method

Payment methodCustomersChurn rate
Electronic check2,36545.29%
Mailed check1,61219.11%
Bank transfer (automatic)1,54416.71%
Credit card (automatic)1,52215.24%

By monthly charge

Group the charges into bands with , then compare the bands.

Monthly chargeCustomersChurn rate
$0-206568.84%
$20-401,18213.20%
$40-601,08025.56%
$60-801,45932.42%
$80-1001,76437.02%
$100+90228.05%

The $80-100 band loses the most customers: 653.

Combined segments

Join three columns into one key with , then compare the keys. Among segments of 100 customers or more, the four highest rates:

ContractInternetPaymentCustomersChurn rate
Month-to-monthFiber opticElectronic check1,30760.37%
Month-to-monthFiber opticMailed check20150.75%
Month-to-monthFiber opticBank transfer (automatic)32745.57%
Month-to-monthFiber opticCredit card (automatic)29341.64%

Which churn signals show up in a customer export?

A churn signal is a trait that customers who leave share more often than customers who stay. In IBM's sample, the clearest are short tenure, a month-to-month contract, payment by electronic check and no tech support. Each one is a column you already have.

SignalCustomers with itChurn rate with itChurn rate of the others
Tenure of 12 months or less2,18647.44%17.13%
Month-to-month contract3,87542.71%6.76%
Pays by electronic check2,36545.29%17.06%
No tech support, among internet customers3,47341.64%15.17%

Customers who left had stayed a median of 10 months, against 38 for those who stayed. More than half of them (55.48%) left within their first 12 months. returns the first figure in Excel for Microsoft 365, with tenure in column F. Band tenure with to put it in the report.

Signals describe who left. They don't say who will leave next, and they don't say why. Complaints and support tickets work the same way when your export has them: add the count per customer as a column and compare the churn rate of customers with and without one. IBM's file has no complaint column, so the TechSupport column is the closest proxy here.

What should a monthly churn report contain?

One page, in the same layout every month: the headline metrics with their period, churn by group with customer counts, the largest high-churn segments, actions with owners, and the definitions. Keeping the layout fixed is what makes month-on-month trends readable.

  1. The headline: churn rate with its period, customers lost, monthly revenue lost and its share.
  2. Churn by contract, payment method, price band, service and tenure, each with its customer count.
  3. The five largest high-churn segments.
  4. Actions, each with an owner and a date.
  5. Definitions: the period, what counts as a customer who left, which customers are excluded.

Changing a definition or the file layout between months breaks the trend. For keeping churn stable when the figures come from Stripe, see standardizing Stripe MRR and churn.

Where does Glow Reports fit?

Glow Reports builds this report from the export you already have. You upload the CSV or Excel file, then ask in plain language for churn by contract, payment method, price band and tenure, with your own definitions.

The AI writes the SQL behind each figure, builds the charts and drafts the text. The SQL sits under each figure, so you can check that a rate divides by the right count, edit it when it doesn't, and export the report to PDF. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

What is the difference between a churn report and a churn rate?

The churn rate is one number. The churn report puts that number next to the customers and revenue lost, the churn rate of each group, the actions and the definitions, so the team knows where churn comes from.

Is churn rate monthly or annual?

It is whatever period you choose, so state it every time. To turn a monthly rate into an annual one, compute with the monthly rate in B2. A 2% monthly churn rate becomes 21.53% a year.

What is the difference between customer churn and revenue churn?

Customer churn counts the customers who left. Revenue churn counts the revenue they took with them. In IBM's sample, the first is 26.54% and the second is 30.50%, because customers on higher charges left more often.

Can I use IBM's telco dataset?

Yes. IBM publishes it in a public GitHub repository under the Apache 2.0 licence. It is fictional, so use it to practise the method, not to compare your churn rate.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your customer export to Glow Reports and get a churn report with the SQL behind each figure.

Keep reading