DEV Community

Gurukiran Mg
Gurukiran Mg

Posted on

Churn Rate Spiked After a Deploy. Did Your Customers Actually Leave?

Your dashboard says 18 customers churned. Support can confirm only 11. Where do you look first?

Imagine you shipped a billing integration on Friday. By Monday, the dashboard shows a spike in customer churn. Someone opens the event log and finds repeated webhook deliveries, cancellations scheduled for next month, and an account with a second paid subscription still active.

Before the team starts looking for reasons customers left, engineering needs to answer a simpler question.

What exactly did the dashboard count?

If you build or maintain a subscription product, here’s how to calculate a churn rate your team can explain, test, and debug when the numbers look wrong.

  1. Define Churn in a Way You Can Test

For this example, monthly customer churn is the share of paying accounts active at the start of the month that have no active paid subscription at the start of the next month.

Say 200 accounts were active on June 1. Eight of those accounts had no active paid subscription on July 1.

June customer churn rate = 8 ÷ 200 × 100 = 4%

That definition also answers a question that comes up in reporting. What happens if an account cancels and returns within the same month? If it’s active at both boundaries, this metric counts it as retained. The event history can still capture the cancellation and reactivation.

Write down the reporting timezone, too. A subscription that expires near midnight can fall into different periods depending on the boundary. Use the same timezone in the warehouse, the tests, and the dashboard.

  1. Count Paying Accounts Using a Stable ID

A user, an account, and a subscription can each represent something different. One company might have 15 users and two paid subscriptions under a single account.

If that company cancels one subscription and keeps the other, you’ll record a subscription cancellation. The paying account remains active, so customer churn stays unchanged. Recurring revenue may still drop.

Plan migrations can create a similar problem. When an old subscription ID disappears and a new one takes its place, a join on subscription IDs can make a continuing customer look like a lost one.

Use a stable account ID to connect the records. Then use subscription history to determine whether that account still had at least one active paid subscription at each reporting boundary.

  1. Keep the Cancellation Request and Effective Date

A customer turns off auto-renewal on June 12. Their paid term runs through June 30.

Customer success may want to follow up on June 13. The churn report needs to know whether the customer still has paid access when the next snapshot is taken.

Keep both timestamps:

Request timestamp. When did the customer ask to cancel or turn off renewal?

Effective timestamp. When did their paid subscription end?

Apply the same reasoning to payment failures. If an account keeps paid access during a grace period, the first failed charge can put it on a payment recovery list. The churn calculation depends on when paid access actually ends.

  1. Build the Churn Query With Two Snapshots

Start with a table that records which accounts are paid at the start of each reporting day. To calculate June churn, compare the June 1 snapshot with the July 1 snapshot.

WITH starting_accounts AS (
SELECT DISTINCT account_id
FROM paid_account_snapshot
WHERE snapshot_date = DATE '2026-06-01'
),
ending_accounts AS (
SELECT DISTINCT account_id
FROM paid_account_snapshot
WHERE snapshot_date = DATE '2026-07-01'
)
SELECT
COUNT() AS starting_customers,
COUNT(
) - COUNT(e.account_id) AS churned_customers,
100.0 * (COUNT() - COUNT(e.account_id))
/ NULLIF(COUNT(
), 0) AS customer_churn_rate_pct
FROM starting_accounts AS s
LEFT JOIN ending_accounts AS e
ON e.account_id = s.account_id;

The query starts with the June 1 group. A customer who joined later can appear in July’s snapshot without changing June’s denominator. If there were no starting customers, the rate is NULL, and the dashboard can say “No starting customers.”

Check that both snapshots completed before publishing the result. The query assumes complete snapshots and non-null account IDs. If the July job fails and writes zero rows, every June account will appear to have churned. A job completion record helps distinguish a failed load from a genuinely empty snapshot.

  1. Put the Edge Cases Into Tests

A few test accounts can catch failures that a dashboard review would miss. Run these cases through the snapshot builder and check the result in the churn query.

A cancellation is scheduled for next month. The account still has paid access at the ending boundary, so count it as retained.

One of two subscriptions ends. The remaining paid subscription keeps the account active.

A customer signs up and leaves midmonth. The account is outside the starting group. Track this departure in an early cancellation report.

An account cancels and returns before the next snapshot. It counts as retained under this definition. Keep both changes in the event history.

The same webhook arrives twice. Reprocessing it produces the same subscription state and churn count.

A late correction changes the effective end date. Rebuilding the affected snapshot places the departure in the right period.

Test out-of-order events as well. Keep the event receipt time for debugging. Use the effective date and authoritative subscription record when rebuilding the account’s history.

  1. Reconcile the Spike Account by Account

Go back to the dashboard showing 18 departures. Pull the IDs that appeared in the starting snapshot and were missing from the ending snapshot. Trace a few through their subscription records.

Did paid access actually end? Did the customer receive a new billing identifier? Did the snapshot job run before the billing export finished?

Compare your list with the billing team’s using the same dates and account definition. If you find a discrepancy, add a test for that case before changing the query. Then rerun an earlier reporting period. You’ll see whether the problem started with the latest deploy or affected numbers the team has already used.

  1. Show the Counts Behind the Percentage

When churn rises from 4% to 6%, show how many customers you started with and how many you lost. A two-point change reflects 2 additional departures in a group of 100 and 200 in a group of 10,000.

Give authorized users a way to see which accounts contributed to the total. Build the chart and the account list from the same result so they reconcile when someone filters by plan or region. Show the snapshot dates and the last refresh time, too.

If a late billing correction changes June’s numbers after the monthly review, keep a record of when the figures were revised and which accounts moved. That gives the team an explanation the next time someone opens an older report and sees a different number.

Final Thoughts

A churn rate is easier to trust when you can trace it back to the customers who left. Start with a clear account definition and two complete snapshots. Then test the cases you expect to see in a real billing system, from scheduled cancellations to late events.

The next time churn spikes after a deploy, you’ll have a way to check whether paid accounts actually disappeared, which records changed, and where to investigate first.

What’s the trickiest churn edge case you’ve had to model? Scheduled cancellations, plan migrations, failed payments, or something else?

Top comments (0)