Churn rate: choose the population at risk

For a simple opening-cohort churn measure, divide customers lost from the opening paying population by the number of paying customers at the start. Using the ending customer count changes the denominator with acquisition and reactivation, making the result answer a different question.

Customer churn and revenue churn are also different measures. Losing one small account and losing one large account count equally in customer churn but differently in recurring-revenue loss.

Inspect a small subscription ledger

The synthetic product analytics lab includes six subscription records. Amounts are monthly recurring value in paise. They are teaching inputs, not invoices, recognized revenue or course fees.

Four customers were paying at the start. One of those four has no recurring value at the end. Under this endpoint definition, customer churn is 25%. Five customers are paying at the end, but 1/5 = 20% is not the opening-cohort churn rate.

Calculate both denominators explicitly

SELECT
 SUM(start_mrr_paise>0) AS opening_paying_customers,
 SUM(start_mrr_paise>0 AND end_mrr_paise=0) AS lost_opening_customers,
 1.0*SUM(start_mrr_paise>0 AND end_mrr_paise=0)
     /NULLIF(SUM(start_mrr_paise>0),0) AS customer_churn,
 SUM(CASE WHEN start_mrr_paise>0 AND end_mrr_paise=0
          THEN start_mrr_paise ELSE 0 END) AS churned_mrr_paise,
 1.0*SUM(CASE WHEN start_mrr_paise>0 AND end_mrr_paise=0
              THEN start_mrr_paise ELSE 0 END)
     /NULLIF(SUM(start_mrr_paise),0) AS churned_mrr_share
FROM subscriptions;

The query returns four opening customers, one loss, 25% customer churn and 30,000 paise of churned MRR from an opening 70,000. The churned MRR share is about 42.86%. The large lost account explains why it exceeds the customer rate.

from math import isclose
from build_and_verify import database

db = database()
rows = db.execute('SELECT * FROM subscriptions').fetchall()
db.close()
opening = [r for r in rows if r[1] > 0]
lost = [r for r in opening if r[2] == 0]
assert len(opening) == 4 and len(lost) == 1
assert len(lost)/len(opening) == .25
assert isclose(sum(r[1] for r in lost)/sum(r[1] for r in opening), 3/7)
assert [r[0] for r in lost] == ['C03']
print({'opening_customers': 4, 'lost_customers': 1,
       'customer_churn': .25, 'churned_mrr_share': 3/7})

State what the snapshots cannot reveal

This dataset records two endpoints. A customer who cancels and restarts between them may appear unchanged. A newly acquired customer who also leaves before the closing snapshot may be absent from this opening-cohort calculation.

If the question concerns cancellation events, time to first churn or short-lived new customers, obtain dated subscription transitions. Do not pretend that endpoint snapshots contain that history. Define treatment of pauses, delinquency, scheduled cancellation and grace periods with the business owner.

A zero opening denominator produces an undefined rate, represented by SQL NULL here. Reporting 0% would imply an observed population with no losses, which is different from having nobody at risk.

Separate churn from contraction and net retention

C02 remains a customer but loses 8,000 paise of recurring value. That contraction belongs in a revenue-retention bridge even though it is not a lost logo. C01's expansion should not erase the count of customers lost.

Use the companion expansion and contraction analysis to reconcile opening and closing value. Its net retention calculation follows only the opening population, excluding new and reactivated customers from the numerator.

Exercise: add a customer with opening MRR of zero and closing MRR of zero. Verify that the customer does not change either opening-cohort denominator. Then document what event history you would need to decide whether that account acquired and churned within the period.

NeuraPath's Data Analytics with Generative AI course links SQL measures to business definitions. A churn report should let a stakeholder identify exactly who was at risk, who was lost and which events the available data cannot establish.

Continue learning

This article is part of the Customer and product analytics sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Customer lifetime value with transparent assumptions.
• Continue with RFM analysis: validate segments against business actions.