xmetricRecipes

Recipe Tableau and SQL

How do I calculate monthly customer churn and MRR churn?

Customer churn is the customers who paid this month but not the next, divided by everyone who paid this month; MRR churn is the same with their MRR. At this booking-software company, customer churn fell from 5.1% to 3.4% a month after June, while MRR churn rose from 2.6% to 6.9%: fewer customers left, but they were the big ones.

Updated

The data

Pawbook subscriptions, 2025

Synthetic datagroomer-subscriptions.csv

Booking software for dog groomers, billed monthly on three plans: Solo (29), Salon (79) and Multi-site (249). In June a cheaper rival for grooming chains launched.

customer_idbilling_monthplanmrr
PB-00012025-01-01Solo29
PB-00022025-01-01Solo29
PB-00032025-01-01Solo29
PB-00042025-01-01Solo29

Step 1

Count paying customers and MRR

The bars climb all year: more groomers pay every month. The line doesn't. MRR peaks in June, then falls three months running while the customer count keeps rising. Something changed in June.

Tableau

COUNTD([customer_id])
// and SUM([mrr]), by MONTH([billing_month])

SQL

SELECT
  billing_month,
  COUNT(DISTINCT customer_id) AS customers,
  SUM(mrr) AS mrr
FROM input
WHERE ('{knob.plan}' = 'All' OR plan = '{knob.plan}')
GROUP BY billing_month
ORDER BY billing_month

Rows

Billing monthCustomersMRR
Jan 2025130$10,170
Feb 2025139$10,801
Mar 2025140$10,930
Apr 2025146$11,154
May 2025146$11,254
Jun 2025151$11,669
Jul 2025155$10,735
Aug 2025158$10,142
Sep 2025161$9,959
Oct 2025170$10,320
Nov 2025176$10,444
Dec 2025188$11,162

Step 2

Mark who didn't pay the next month

A customer churns in the last month they paid. The guard on the latest month matters: nobody in December has a January row yet, so without it every December customer looks gone. December is left out on purpose.

Tableau

// Last paid month
{ FIXED [customer_id] : MAX([billing_month]) }
// Latest month in file
{ FIXED : MAX([billing_month]) }
// Churned
[billing_month] = [Last paid month] AND [billing_month] < [Latest month in file]

SQL

WITH s AS (
  SELECT *
  FROM input
  WHERE ('{knob.plan}' = 'All' OR plan = '{knob.plan}')
)
SELECT
  a.billing_month,
  COUNT(*) FILTER (WHERE b.customer_id IS NULL) AS churned,
  COALESCE(SUM(a.mrr) FILTER (WHERE b.customer_id IS NULL), 0) AS mrr_lost
FROM s a LEFT JOIN s b ON b.customer_id = a.customer_id AND b.billing_month = a.billing_month + INTERVAL 1 MONTH
WHERE a.billing_month < (
  SELECT MAX(billing_month)
  FROM input
)
GROUP BY a.billing_month
ORDER BY a.billing_month

Rows

Billing monthChurnedMRR lost
Jan 20253$137
Feb 20259$361
Mar 20255$195
Apr 202513$477
May 20256$224
Jun 20258$1,432
Jul 20258$1,332
Aug 20256$544
Sep 20253$407
Oct 20257$623
Nov 20251$29

Common mistake

Counting the last month as churn

The file ends in December, so no December customer has a next month to pay in. Leave out the latest month, or every customer in it counts as churned and the chart ends in a 100% spike.

WrongStarts with 2025-12-01, 2025-11-01, 2025-10-01…
Point at a bar to read it.
00.250.50.7512025-12-012025-11-012025-10-01

Tableau

COUNTD(IF [billing_month] = [Last paid month] THEN [customer_id] END) / COUNTD([customer_id])

SQL

SELECT
  a.billing_month,
  COUNT(*) FILTER (WHERE b.customer_id IS NULL)
    / COUNT(*) AS customer_churn
FROM input a LEFT JOIN input b ON b.customer_id = a.customer_id AND b.billing_month = a.billing_month + INTERVAL 1 MONTH
GROUP BY a.billing_month
ORDER BY a.billing_month DESC
LIMIT 3
RightStarts with 2025-11-01, 2025-10-01, 2025-09-01…
Point at a bar to read it.
00.020.042025-11-012025-10-012025-09-01

Tableau

COUNTD(IF [billing_month] = [Last paid month] AND [billing_month] < [Latest month in file] THEN [customer_id] END) / COUNTD([customer_id])

SQL

SELECT
  a.billing_month,
  COUNT(*) FILTER (WHERE b.customer_id IS NULL)
    / COUNT(*) AS customer_churn
FROM input a LEFT JOIN input b ON b.customer_id = a.customer_id AND b.billing_month = a.billing_month + INTERVAL 1 MONTH
WHERE a.billing_month < (
  SELECT MAX(billing_month)
  FROM input
)
GROUP BY a.billing_month
ORDER BY a.billing_month DESC
LIMIT 3

Step 3

Divide by who was paying

Both rates divide by what you had at the start of the month, never by the month's new signups. Until May, MRR churn sits below customer churn: the leavers are small Solo accounts. In June the lines cross, and MRR churn stays on top through October.

Tableau

// Customer churn
COUNTD(IF [Churned] THEN [customer_id] END) / COUNTD([customer_id])
// MRR churn
ZN(SUM(IF [Churned] THEN [mrr] END)) / SUM([mrr])

SQL

WITH s AS (
  SELECT *
  FROM input
  WHERE ('{knob.plan}' = 'All' OR plan = '{knob.plan}')
), f AS (
  SELECT a.*, b.customer_id IS NULL AS churned
  FROM s a LEFT JOIN s b ON b.customer_id = a.customer_id AND b.billing_month = a.billing_month + INTERVAL 1 MONTH
  WHERE a.billing_month < (
    SELECT MAX(billing_month)
    FROM input
  )
)
SELECT
  billing_month,
  COUNT(*) FILTER (WHERE churned) / COUNT(*) AS customer_churn,
  COALESCE(SUM(mrr) FILTER (WHERE churned), 0)
    / SUM(mrr) AS mrr_churn
FROM f
GROUP BY billing_month
ORDER BY billing_month

Rows

Billing monthCustomer churnMRR
Jan 20250.020.01
Feb 20250.060.03
Mar 20250.040.02
Apr 20250.090.04
May 20250.040.02
Jun 20250.050.12
Jul 20250.050.12
Aug 20250.040.05
Sep 20250.020.04
Oct 20250.040.06
Nov 20250.010

Is churn getting better or worse?

Both, depending on which churn you watch. Before June, 5.1% of customers left each month but they took only 2.6% of MRR: mostly one-person Solo shops on the cheapest plan. From June, customer churn improved to 3.4% while MRR churn jumped to 6.9%. A rival built for grooming chains launched that month, and 13 Multi-site accounts left by November. They were 74.1% of the MRR lost in those six months.

Step 4

Before and after June

Pooling the months smooths out the noise of single months. Customer churn improved; MRR churn got much worse. Switch the plan to Multi-site to see where the money went.

Tableau

IF MONTH([billing_month]) < 6 THEN "Jan-May"
ELSE "Jun-Nov"
END
// with both churn rates

SQL

WITH s AS (
  SELECT *
  FROM input
  WHERE ('{knob.plan}' = 'All' OR plan = '{knob.plan}')
), f AS (
  SELECT a.*, b.customer_id IS NULL AS churned
  FROM s a LEFT JOIN s b ON b.customer_id = a.customer_id AND b.billing_month = a.billing_month + INTERVAL 1 MONTH
  WHERE a.billing_month < (
    SELECT MAX(billing_month)
    FROM input
  )
)
SELECT
  CASE
    WHEN month(billing_month) < 6 THEN 'Jan-May'
    ELSE 'Jun-Nov'
    END AS period,
  COUNT(*) FILTER (WHERE churned) AS churned,
  COUNT(*) FILTER (WHERE churned) / COUNT(*) AS customer_churn,
  COALESCE(SUM(mrr) FILTER (WHERE churned), 0)
    / SUM(mrr) AS mrr_churn
FROM f
GROUP BY period
ORDER BY period

Rows

PeriodChurnedCustomer churnMRR
Jan-May360.050.03
Jun-Nov330.030.07
Plan
Point at the line to read it.
12025-06-01$1,43222025-07-01$1,33232025-10-01$62342025-08-01$54452025-04-01$47762025-09-01$40772025-02-01$36182025-05-01$22492025-03-01$195102025-01-01$137

With the plan set to All, the same steps rerun on that slice. Solo churn dropped from 7.7% to 1.6% a month; Multi-site went from no cancellations at all to the biggest leak in the business. A board deck with only the customer churn line would have reported a good year.

How do I turn monthly churn into annual churn?

Common mistake

Multiplying monthly churn by 12

Churn compounds: each month you lose a share of the customers who are left, not of the ones you started with. At this company's 3.4% a month since June, that's 34.0% a year, not the 40.8% that times-twelve gives. Annual churn is 1 - (1 - monthly churn)^12.

Wrong
Point at a bar to read it.
0.41Annual churn

Tableau

12 * [Customer churn]

SQL

WITH f AS (
  SELECT a.*, b.customer_id IS NULL AS churned
  FROM input a LEFT JOIN input b ON b.customer_id = a.customer_id AND b.billing_month = a.billing_month + INTERVAL 1 MONTH
  WHERE a.billing_month BETWEEN DATE '2025-06-01' AND DATE '2025-11-01'
)
SELECT
  12 * COUNT(*) FILTER (WHERE churned) / COUNT(*) AS annual_churn
FROM f
Right
Point at a bar to read it.
0.34Annual churn

Tableau

1 - POWER(1 - [Customer churn], 12)

SQL

WITH f AS (
  SELECT a.*, b.customer_id IS NULL AS churned
  FROM input a LEFT JOIN input b ON b.customer_id = a.customer_id AND b.billing_month = a.billing_month + INTERVAL 1 MONTH
  WHERE a.billing_month BETWEEN DATE '2025-06-01' AND DATE '2025-11-01'
)
SELECT
  1
    - POWER(1 - COUNT(*) FILTER (WHERE churned) / COUNT(*), 12) AS annual_churn
FROM f

Run it on your own data

Any table with one row per customer per billed month, with a customer id, the month and its MRR, works. Spread annual invoices over their months first. Drop your CSV and pick the columns; the steps above recompute in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, groomer-subscriptions.csv, shows the shape.

Questions

What goes in the denominator of a churn rate?

The customers (or MRR) you had at the start of the period. Customers who signed up during the month can't churn from it yet, so adding them to the denominator makes churn look lower, and the faster you grow, the bigger that flattery gets.

What is the difference between gross and net MRR churn?

Gross MRR churn counts MRR lost to cancellations and downgrades. Net MRR churn subtracts expansion from upgrades. Net churn below zero means existing customers grew their spend faster than others left: net revenue retention above 100%.

My Tableau and SQL numbers differ. Why?

The Tableau version marks a customer's last paid month as their churn month; the SQL compares each month with the next. They agree unless customers pause and come back. If yours do, the SQL is right: it counts every gap as churn, and the return as a reactivation.

How do annual plans affect monthly churn?

An annual invoice shows up in one month only, so the customer looks churned the month after. Spread each annual invoice over its twelve service months (MRR = annual price / 12) before you start.

What counts as churn without subscriptions?

Pick an inactivity window, for example 90 days without an order, and treat a customer as churned once their last order is older than that. A cohort retention table often answers the question better.

Sources