Customer churn comparison template
By the Analistable team · Updated · 2 min read
A free workbook that compares last period's customer list with this period's. Each old customer is labelled Churned, Contracted, Expanded or Retained flat; new customers are marked New; and a Summary sheet calculates logo churn rate, revenue lost to churn and net revenue retention. Paste two exports with a customer ID and revenue column.
Part of our guide: How to compare two spreadsheets for differences and matches
What the template does
Churn analysis starts with a plain comparison of two lists: who was here last period, who is here now. This template does that comparison by customer ID, the same logic as merging two lists and flagging new and removed rows, then adds revenue so you can see how much churn cost. The approach is described more broadly in the comparing spreadsheets guide.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Last period | Customer ID, Customer, Revenue | Yes |
| This period | Customer ID, Customer, Revenue, Status | First three columns |
| Comparison | Customer ID, Customer, Last revenue, This revenue, Change, Status | No |
| Summary | Metric, Value | No |
How to fill it in
- Export revenue by customer for two equal periods (two months or two quarters) from your billing or accounting system.
- Paste one row per customer. Customer ID must be the same in both exports — names change, IDs shouldn't.
- Extend the Comparison rows to match the number of customers on Last period.
How the formulas work
Status on Comparison first checks whether the customer appears at all this period:
=IF(COUNTIFS('This period'!$A$2:$A$500,A2)=0,"Churned",IF(D2<C2,"Contracted",IF(D2>C2,"Expanded","Retained flat")))New customers are found the other way round, on the This period sheet:
=IF(COUNTIFS('Last period'!$A$2:$A$500,A2)=0,"New","Existing")Net revenue retention is this period's revenue from existing customers divided by last period's revenue:
=IFERROR(ROUND(SUMIFS('This period'!C2:C500,'This period'!D2:D500,"Existing")/B6,4),0)Worked example
Ten customers in Q2 and nine in Q3 give these Comparison statuses:
| Customer | Q2 revenue | Q3 revenue | Change | Status |
|---|---|---|---|---|
| Acme | 4200 | 4600 | 400 | Expanded |
| Coastal | 1800 | 0 | -1800 | Churned |
| Delta | 3500 | 3100 | -400 | Contracted |
| Elm | 900 | 0 | -900 | Churned |
| Iris | 700 | 0 | -700 | Churned |
| Juniper | 1200 | 1400 | 200 | Expanded |
| Metric | Value |
|---|---|
| Customers last period | 10 |
| Churned customers | 3 |
| Logo churn rate | 30.0% |
| New customers | 2 |
| Revenue last period | 21300 |
| Revenue this period | 21200 |
| Revenue lost to churn | 3400 |
| Net revenue retention | 86.9% |
Total revenue barely moved, but that hides three lost customers worth 3,400, offset by expansion and two new accounts.
Limits and when to move on
Two snapshots tell you who left, not why or when. For order-level data rather than revenue totals, see finding customers who stopped ordering and finding new customers this month. If you track twelve monthly files, Analistable can load them together and answer 'which customers churned in each month?' in one question.
Frequently asked questions
- What counts as churn here?
- A customer with revenue last period and no row at all this period. A customer with a row but zero revenue shows as Contracted, so remove zero rows if you want them counted as churned.
- Can I match on email instead of ID?
- Yes, if emails are consistent. Lower-case and trim them in both sheets first so 'Anna@x.com ' matches 'anna@x.com'.
- Why is net revenue retention below 100% when revenue is flat?
- NRR excludes new customers. Flat total revenue with NRR below 100% means new business replaced revenue lost from existing accounts.