Involuntary churn in SQL

Why involuntary churn deserves its own metric

Picture a familiar Monday morning. The finance partner pings the data team on Slack at 8:47am: "Our churn jumped 1.4 points last month, the board call is at noon, what happened?" If the only number on the dashboard is total churn, the answer is a shrug. If the dashboard splits voluntary from involuntary, the answer is a one-line diagnosis — either the product is leaking customers who actively pressed the cancel button, or the billing system is silently culling them through failed payments. The two diagnoses point at completely different teams and different roadmaps.

Voluntary churn happens when a customer opens the app, opens the settings panel, and pushes the button labelled "cancel my subscription." It is a product, pricing, or value-perception problem owned by product, design, and lifecycle marketing. Involuntary churn happens when the billing system terminates a subscription because a charge could not be collected — an expired card, a declined transaction, a chargeback. The customer never decided to leave. The lever sits with payments engineering and the dunning vendor. At most consumer SaaS companies on Stripe or Adyen, involuntary churn is twenty to forty percent of total churn, and a meaningful share of it is recoverable.

The distinction is also why this metric shows up in analyst loop interviews at Stripe, Shopify, and Notion. A senior data person should write the query on a whiteboard in ten minutes and defend each filter when the interviewer pokes. This post walks through the recipe, the breakdown by failure reason, and the traps that quietly inflate or deflate the number on a production dashboard.

Voluntary vs involuntary, the data model

The minimum schema you need is a subscriptions table with one row per subscription contract, a cancellation_reason column, and a churned_at (or ended_at) timestamp on every cancelled row. Different stacks normalize the cancellation reason differently, but in practice the values cluster into four buckets that every payments team uses.

cancellation_reason | bucket
user_cancel         | voluntary
downgrade           | voluntary
payment_failed      | involuntary
expired_grace       | involuntary
chargeback          | involuntary
card_expired        | involuntary

The user_cancel and downgrade rows came from a deliberate human action inside the product. The four involuntary buckets are all flavors of "the billing system terminated this for a payment reason." Some teams also surface fraud_blocked or risk_review; those usually belong in involuntary because the customer did not voluntarily leave.

If your data does not yet have a cancellation_reason column, this post is the right justification to instrument one. Without it you cannot run any of the queries below. The instrumentation cost is a single column on subscriptions and a small change in the code path that closes a subscription — under a day of engineering work and the highest analytics ROI you will ship that quarter.

The SQL formula

The canonical query is two filtered counts inside the same scan, divided into a rate at the end. Most warehouses (Postgres, Snowflake, BigQuery with COUNTIF, Databricks SQL) support the FILTER clause directly. Where it is missing, a SUM(CASE WHEN ...) is the equivalent rewrite.

SELECT
    COUNT(*) FILTER (
        WHERE cancellation_reason IN (
            'payment_failed', 'expired_grace', 'chargeback', 'card_expired'
        )
    )                                            AS involuntary,
    COUNT(*) FILTER (
        WHERE cancellation_reason IN ('user_cancel', 'downgrade')
    )                                            AS voluntary,
    COUNT(*)                                     AS total_churn
FROM subscriptions
WHERE churned_at >= DATE '2026-04-01'
  AND churned_at <  DATE '2026-05-01';

The window is a half-open interval — inclusive on the left, exclusive on the right — the only style that does not double-count midnight on the boundary. The IN list is the policy decision. If your data engineering team has a documented mapping between raw reason codes and buckets, use it verbatim; if there is no mapping, ship one and write it into a one-pager next to the dashboard. Silently changing the list in version four of the SQL is how trend lines drift for reasons nobody can explain.

The grain is "one row per cancelled subscription inside the window." If your subscriptions table contains multiple historical rows per customer because of plan changes or annual renewals, deduplicate to one row per subscription_id first, then aggregate. Otherwise you will overcount cancellations on customers who renewed and cancelled multiple times.

Share of total churn

The headline number leadership wants is not the raw involuntary count — it is the share of total churn that involuntary explains. This is what tells the room whether to invest the next sprint in onboarding or in payment retries.

WITH stats AS (
    SELECT
        COUNT(*) FILTER (
            WHERE cancellation_reason IN (
                'payment_failed', 'expired_grace', 'chargeback', 'card_expired'
            )
        )                                        AS involuntary,
        COUNT(*)                                 AS total
    FROM subscriptions
    WHERE churned_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    involuntary,
    total,
    involuntary::NUMERIC * 100
        / NULLIF(total, 0)                       AS involuntary_pct
FROM stats;

At most consumer SaaS shops the answer lands between twenty and forty percent. Below twenty usually means the dunning system is already well-tuned or that voluntary cancellations are unusually high. Above forty almost always means the payments stack has a fixable problem: a stale retry schedule, no card account updater, or a checkout that fails Apple Pay tokens on app reinstall. The number itself does not prescribe the fix, but the gap between the current value and a sensible target tells you how much revenue is on the table.

To express the same idea as a rate against the at-risk base rather than against total churn, divide involuntary by active subscriptions at the start of the window. That version is the "involuntary churn rate" the CFO quotes on earnings calls; mature dashboards report both side by side.

Breakdown by failure reason

The single-number rate is not enough to take action. Two companies with identical 30% involuntary churn shares can require completely different fixes if one is dominated by expired cards and the other by insufficient funds.

SELECT
    cancellation_reason,
    COUNT(*)                                     AS churns,
    SUM(mrr)                                     AS lost_mrr,
    COUNT(*) * 100.0
        / SUM(COUNT(*)) OVER ()                  AS pct_of_total
FROM subscriptions
WHERE churned_at >= CURRENT_DATE - INTERVAL '90 days'
  AND cancellation_reason IN (
      'payment_failed', 'expired_grace', 'chargeback', 'card_expired',
      'user_cancel', 'downgrade'
  )
GROUP BY cancellation_reason
ORDER BY churns DESC;

Each reason maps to a distinct intervention. Expired cards respond to a card account updater service plus a pre-expiration email a week before renewal. Insufficient funds responds to retry timing — moving the second retry to a Friday or to the customer's payday window. Chargebacks respond to clearer billing descriptors on the credit card statement and a friction-free in-product cancel flow that keeps users from disputing instead of cancelling. Fraud-blocked accounts need a risk policy review, not a dunning tweak.

Sorting by lost_mrr instead of churns is often more revealing. A small number of expired-card cases on enterprise customers can dwarf a long tail of insufficient-funds events on $5 consumer plans. The metric should always be reported in both currencies — customer count for product and lost MRR for finance — to avoid optimizing for the wrong thing.

Common pitfalls

When teams calculate involuntary churn for the first time, the most common mistake is to lump everything into a single churn bucket and never instrument the reason code. Without the split, voluntary and involuntary land in the same number and the team chases the wrong fix for a quarter. The cure is the single column and the small engineering change described earlier — the highest-ROI piece of analytics instrumentation a subscription business ever ships.

A second trap is to treat involuntary churners as permanently lost revenue. In practice, a tuned dunning sequence recovers thirty to sixty percent of involuntary cases inside the grace window. Reporting raw counts as "MRR we will never see again" overstates impact and obscures the recoverable share. Report gross involuntary churn (everyone who failed) and net involuntary churn (those who did not recover by end of grace) side by side.

A subtler problem is double-counting chargeback fees. A chargeback both removes revenue and incurs a fee from the payment network — usually fifteen to twenty-five dollars per event. If your dashboard adds the fee to lost MRR you have inflated the number; the fee belongs in a separate "payments cost of churn" line. Most analysts only realize this when finance asks where the extra five thousand dollars came from.

The "expired grace" bucket is where voluntary and involuntary intentions blur. Some customers see the failed-payment banner, decide they did not want the subscription after all, and let the grace period lapse on purpose. They look mechanically identical to a customer whose card silently expired. A useful proxy is whether the customer logged in during the grace window — a monthly review of grace-expired cohort against login activity keeps the metric honest.

Finally, a single dunning policy applied across every customer segment is the trap consumer-grade teams fall into when they scale into B2B. An enterprise customer paying $5,000 a month deserves a phone call from a customer success rep when their card fails. A consumer customer paying $9.99 deserves a friendly retry plus an email with a deep link to the card-update screen. Always segment recovery by customer tier before drawing roadmap conclusions.

Optimization tips

Involuntary churn queries usually run against the subscriptions table, which is small relative to event tables but tends to be hot during finance close. The most useful index is on (churned_at, cancellation_reason). The leading column gives partition pruning on the date range; the trailing column lets the warehouse evaluate the FILTER predicates without re-reading rows. On Snowflake and BigQuery, use the same combination as the clustering key.

If the dashboard refreshes every page load, materialize a daily_churn_breakdown table that pre-aggregates by reason code and date. The query collapses to a sum over a tiny rollup and runs in milliseconds even on five years of history. Schedule the rollup to refresh once a day after the upstream subscription ETL finishes.

For the failure-reason breakdown, expose the cancellation reason mapping as a small dimension table joined into the query. That makes the policy auditable, lets non-engineers change the mapping through a pull request, and keeps every dashboard consuming the same buckets. Hardcoded IN lists scattered across twenty notebooks is the analytics technical debt every mature company eventually pays down with a quarter-long cleanup.

Related reading

• How to calculate churn in SQL
• How to calculate grace period recovery in SQL
• How to calculate chargeback rate in SQL
• How to calculate contraction MRR in SQL

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

What is a healthy involuntary churn share?

For consumer subscriptions on a tuned dunning stack, twenty to thirty percent of total churn is typical. Below twenty usually means a well-instrumented payments stack or unusually high voluntary churn pulling the ratio down. Above forty means a meaningful share of cancelled customers never intended to leave. For B2B enterprise SaaS with annual contracts, the number is five to fifteen percent — annual prepayments catch card issues long before they cause silent churn.

How do I lower involuntary churn?

Three levers in order of ROI. First, smart retry scheduling — retrying at the customer's payday rather than a fixed offset can move recovery by ten points without new code. Second, a card account updater integration so re-issued cards are tokenized automatically and the next charge succeeds without a customer action. Third, a clean card-update flow surfaced through email, push, and an in-app banner during the grace window.

Should involuntary churn be reported inside total churn rate?

By default, yes. Total churn is total churn regardless of cause, and reporting it any other way invites accusations of moving the goalposts. Report total churn as the headline number with the voluntary versus involuntary split directly underneath and the recovered share called out separately. Some companies also publish a "net churn excluding involuntary" line for internal comparison — fine as a secondary metric, never as a replacement for the headline.

Is a card account updater service worth the cost?

The major networks charge twenty to forty cents per successful card refresh. For any customer whose lifetime value crosses ten dollars, the math is trivially positive — one prevented cancellation pays for hundreds of failed lookups. The bigger question is implementation complexity. If your payment processor exposes the service natively (Stripe Card Account Updater, Adyen RealTime Account Updater) the integration is hours of work. If you have to build it directly against the networks, it is a multi-quarter project worth it only at significant scale.

How much of involuntary churn is actually recoverable?

For consumer subscriptions, a tuned dunning sequence recovers thirty to sixty percent of involuntary cancellations inside a fourteen-day grace window. The rate is dominated by insufficient-funds cases, which clear automatically when the next paycheck lands. Expired cards recover at much lower rates without a card-update flow, and chargebacks rarely recover at all. The composition of failure reasons sets the ceiling on recovery regardless of dunning quality.

How often should the involuntary churn dashboard refresh?

Daily is appropriate for trend monitoring. Finance only needs monthly close numbers. Payments engineering, however, will sometimes want hourly during a dunning experiment, because a regression in retry logic is a five-figure-per-day revenue hit and waiting overnight to spot it is unacceptable. Build the pipeline with both cadences in mind: a daily rollup for everyone, and a raw event view that payments can query during incident response.