Everything below runs on two tables: one row per subscription, and one row per charge attempt. Most subscription apps can export both. The queries are written for Postgres; other warehouses need small changes to the date functions.

The data contract

Treat a pause as an end on the day it starts. Record a resume as a new subscription row with reactivation = true and the original ID in previous_subscription_id, and leave reactivation rows out of the survival table and the first-renewal-by-offer query. That’s close to how Peloton counts pauses, and it stops a pause-heavy cancel flow from hiding churn.

Monthly churn, split by cause

-- losses among subscriptions active on the 1st, by how they ended
-- months: a calendar table with one row per month start (m)
WITH base AS (
  SELECT k.m, s.subscription_id, s.ended_at, s.end_type
  FROM months k
  JOIN subscriptions s
    ON s.started_at < k.m
   AND (s.ended_at IS NULL OR s.ended_at >= k.m)
)
SELECT m,
       COUNT(*) AS active_on_1st,
       COUNT(*) FILTER (WHERE ended_at < m + INTERVAL '1 month'
                          AND end_type = 'voluntary')      AS voluntary,
       COUNT(*) FILTER (WHERE ended_at < m + INTERVAL '1 month'
                          AND end_type = 'failed_payment') AS failed_payment,
       COUNT(*) FILTER (WHERE ended_at < m + INTERVAL '1 month')::numeric
         / COUNT(*)                                        AS churn
FROM base
GROUP BY m
ORDER BY m;

Pauses fall into churn but not into either named cause; report them on their own line.

The survival table

-- share of each start month still active when renewal r was due
SELECT DATE_TRUNC('month', started_at) AS start_month,
       k.r AS renewal,
       COUNT(*) AS subscriptions,
       AVG(CASE WHEN ended_at IS NULL
                  OR ended_at > started_at + k.r * cadence_days * INTERVAL '1 day'
                THEN 1.0 ELSE 0 END) AS still_active
FROM subscriptions
CROSS JOIN (VALUES (1), (2), (3), (6), (12)) AS k(r)
WHERE started_at + k.r * cadence_days * INTERVAL '1 day' <= CURRENT_DATE
  AND NOT reactivation
GROUP BY 1, 2
ORDER BY 1, 2;

The WHERE line keeps each subscription out of a column until that renewal was due, so young groups show blanks, not zeros. Measuring “still active” rather than “paid the renewal” counts a skipped delivery as survived, which is what it is. If many subscribers change cadence, rebuild the due dates from the cadence history table.

First renewal by offer

SELECT first_offer,
       COUNT(*) AS subscriptions,
       AVG(CASE WHEN ended_at IS NULL
                  OR ended_at > started_at + cadence_days * INTERVAL '1 day'
                THEN 1.0 ELSE 0 END) AS reached_first_renewal
FROM subscriptions
WHERE started_at + cadence_days * INTERVAL '1 day' <= CURRENT_DATE
  AND started_at >= CURRENT_DATE - INTERVAL '6 months'
  AND NOT reactivation
GROUP BY first_offer
ORDER BY subscriptions DESC;

Saves still active at 60 days

-- cancel_attempts: one row per attempt, with outcome and the option taken
SELECT c.save_option,
       COUNT(*) AS saves,
       AVG(CASE WHEN s.ended_at IS NULL
                  OR s.ended_at > c.attempted_at + INTERVAL '60 days'
                THEN 1.0 ELSE 0 END) AS active_at_60_days
FROM cancel_attempts c
JOIN subscriptions s USING (subscription_id)
WHERE c.outcome = 'saved'
  AND c.attempted_at <= CURRENT_DATE - INTERVAL '60 days'
GROUP BY c.save_option
ORDER BY saves DESC;

Put the same query next to one for subscribers who never tried to cancel, from the same months. The gap between saved subscribers and untouched ones is the true measure of what a save is worth.