Churn analytics: diagnose customer losses

Churn analytics identifies which customers left, which revenue disappeared, and where those losses concentrate. Use a fixed opening customer population, verify each exit, then compare customer counts and recurring revenue by segment and cohort. A reconciled account table gives the review a defensible starting point.

Fix the population before comparing losses

Start with the customers active at the beginning of the reporting window. This example reviews a US software supplier's September 2026 opening accounts through September 30. New customers belong in a separate acquisition schedule; they cannot offset exits inside this opening population.

The churn glossary owns the basic definition. The diagnostic question here is more specific: does the overall result hide a problem in a particular segment or tenure group?

Use recurring monthly revenue on a consistent basis at both dates. Exclude one-time setup charges, taxes, and unrelated credits from this recurring-revenue comparison. Record the currency and any exchange-rate convention beside the extract. Keep an explicit exit flag backed by the cancellation or nonrenewal record; do not manufacture it from a blank revenue cell.

For businesses that sell through occasional orders, use B2B churn without a subscription to establish the inactivity rule first. A contractual exit and an unusually long gap between purchases are different evidence.

Build the account table

Export one row per opening account. Include the account identifier, opening segment, cohort label, opening recurring revenue, closing recurring revenue, and confirmed exit flag. Fix segment and cohort at the start so an account moving between tiers cannot move the denominator after the fact.

In this illustrative table, Recent and Established are the company's prepared tenure groups. Columns A through F contain the source fields. All revenue amounts are monthly recurring revenue, or MRR.

The table shows two complete exits. Accounts C and H still have recurring revenue and are contractions. D and G expanded. Treating every revenue reduction as an account exit would confuse different customer actions.

Calculate losses and prove the movement

Opening-cohort account churn = Confirmed exits / Opening accounts

Full-exit revenue loss rate = Opening MRR of confirmed exits / Total opening MRR

These definitions use opening customers and opening revenue. State them explicitly whenever the figures leave the analysis file.

Put the source rows in rows 2 through 9 of Excel. In G2 calculate full-exit MRR, H2 retained-account contraction, and I2 expansion, then fill down:

=IF(F2=1,D2,0)
=IF(F2=0,MAX(D2-E2,0),0)
=MAX(E2-D2,0)

Each account must pass this row check in J2:

=D2-G2-H2+I2-E2

The result must be zero. Investigate any confirmed exit with positive closing revenue or missing opening revenue before summarizing it.

The identity is $22,000 minus $7,500 minus $600 plus $700 = $14,600. It matches the closing source column. This spreadsheet check must also return zero:

=SUM(D2:D9)-SUM(G2:G9)-SUM(H2:H9)+SUM(I2:I9)-SUM(E2:E9)

Account churn is 2 / 8 = 25.0%. Full-exit revenue loss is $7,500 / $22,000 = 34.1%, rounded to one decimal place. The difference says departing customers carried more than an equal share of opening revenue. It does not identify why they left.

Compare segment and cohort on the same rows

Put Small and Enterprise in L2 and L3. Opening account count and exit count use these formulas, copied down. Microsoft documents COUNTIFS as counting records for which all supplied criteria are met.

=COUNTIFS($B$2:$B$9,L2)
=COUNTIFS($B$2:$B$9,L2,$F$2:$F$9,1)

Opening and lost MRR use the same grouping. SUMIFS sums values matching the specified criteria.

=SUMIFS($D$2:$D$9,$B$2:$B$9,L2)
=SUMIFS($G$2:$G$9,$B$2:$B$9,L2)

Divide exits by opening accounts, and lost MRR by opening MRR. For cohort comparisons, replace the segment range $B$2:$B$9 with $C$2:$C$9 and use the cohort label as the criterion.

Each view independently reconciles to the account table. Do not add the segment and cohort views together; they describe the same customers twice. Displayed rates are rounded to one decimal place, while calculations retain full precision.

Choose the next investigation

Both segments have the same account churn, while the established cohort contains both full exits. Start with B and F's documented exit reasons and renewal conversations. Then examine the retained accounts with contraction; a zero-exit recent cohort still contains shrinking customers.

Customer success owns the account review, finance validates the revenue bridge, and sales checks commercial changes and renewal terms. Record an owner and next action for each exception. Use churn versus dormancy when the next action concerns inactivity before a confirmed departure. Businesses managing multiple service lines also need the separate account and line view in telecom churn analytics.

Where it goes wrong

• Letting acquisition hide departures. Keep new customers outside the opening-cohort diagnosis.
• Treating blanks as exits. Verify the event and the extract's completeness.
• Changing segment labels midperiod. Compare on the opening assignment and show migrations separately.
• Averaging group percentages. Divide summed exits by summed opening accounts for the overall rate.
• Claiming a cause from a small group. A concentration directs investigation; recorded evidence must support the explanation.

Covirage is not affiliated with Microsoft.

Diagnose losses on your own file in Covirage

Bring opening and closing account exports with recurring revenue, confirmed exit flags, and prepared segment or cohort labels. Covirage maps the columns, its tools compute and check the losses and revenue bridge, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See renewal watch for the supporting customer review context.