Churn Analysis — Rates, Timing, and Leading Indicators
Churn Analysis — Rates, Timing, and Leading Indicators
Churn is the single biggest threat to SaaS revenue. This lesson covers monthly churn rate calculation, time-to-churn distribution, and detecting leading indicators from session and feature data.
1. Monthly Churn Rate
Churn rate = churned accounts / accounts active at start of month:
WITH monthly_counts AS (
SELECT
DATE_TRUNC('month', converted_at)::DATE AS month,
COUNT(*) AS new_customers
FROM saas_accounts
WHERE converted_at IS NOT NULL
GROUP BY 1
),
monthly_churn AS (
SELECT
DATE_TRUNC('month', churned_at)::DATE AS month,
COUNT(*) AS churned
FROM saas_accounts
WHERE churned_at IS NOT NULL
GROUP BY 1
),
active_base AS (
SELECT
mc.month,
SUM(mc2.new_customers) AS active_start
FROM monthly_counts mc
JOIN monthly_counts mc2 ON mc2.month <= mc.month
GROUP BY mc.month
)
SELECT
ab.month,
ab.active_start,
COALESCE(mc.churned, 0) AS churned,
ROUND(100.0 * COALESCE(mc.churned, 0) / NULLIF(ab.active_start, 0), 2) AS churn_rate_pct
FROM active_base ab
LEFT JOIN monthly_churn mc ON mc.month = ab.month
ORDER BY ab.month;2. Time-to-Churn Distribution
How long did churned customers stay before leaving?
SELECT
DATE_PART('month', AGE(churned_at, converted_at))::INT AS months_to_churn,
COUNT(*) AS accounts,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM saas_accounts
WHERE churned_at IS NOT NULL AND converted_at IS NOT NULL
GROUP BY 1
ORDER BY 1;What This Returns
months_to_churn | accounts | pct
----------------+----------+-----
2 | 12 | 15.2
3 | 10 | 12.7
4 | 9 | 11.4
...3. Churn by Plan and Channel
SELECT
p.name AS plan,
a.acquisition_channel,
COUNT(*) FILTER (WHERE a.status = 'active') AS active,
COUNT(*) FILTER (WHERE a.status = 'churned') AS churned,
ROUND(100.0 *
COUNT(*) FILTER (WHERE a.status = 'churned')
/ NULLIF(COUNT(*), 0), 1) AS churn_rate_pct
FROM saas_accounts a
JOIN saas_plans p ON p.id = a.plan_id
GROUP BY p.name, a.acquisition_channel
ORDER BY churn_rate_pct DESC;4. Leading Indicators — Low Engagement Before Churn
Accounts with low session activity in the 30 days before churning are likely disengaging:
WITH churned AS (
SELECT id AS account_id, churned_at
FROM saas_accounts
WHERE churned_at IS NOT NULL
),
pre_churn_sessions AS (
SELECT
c.account_id,
COUNT(s.id) AS sessions_last_30d,
AVG(s.page_count) AS avg_pages
FROM churned c
LEFT JOIN saas_sessions s
ON s.account_id = c.account_id
AND s.started_at >= c.churned_at - INTERVAL '30 days'
AND s.started_at < c.churned_at
GROUP BY c.account_id
)
SELECT
CASE
WHEN sessions_last_30d = 0 THEN 'No sessions'
WHEN sessions_last_30d <= 2 THEN 'Very low (1-2)'
WHEN sessions_last_30d <= 5 THEN 'Low (3-5)'
ELSE 'Normal (6+)'
END AS pre_churn_engagement,
COUNT(*) AS accounts,
ROUND(AVG(avg_pages), 1) AS avg_pages_per_session
FROM pre_churn_sessions
GROUP BY 1
ORDER BY accounts DESC;5. Feature Usage as Churn Predictor
Accounts using zero features in a month are high churn risk:
WITH last_month_usage AS (
SELECT
a.id AS account_id,
a.status,
SUM(fu.usage_count) AS total_usage
FROM saas_accounts a
LEFT JOIN saas_feature_usage fu
ON fu.account_id = a.id
AND fu.period_date = DATE_TRUNC('month', NOW() - INTERVAL '1 month')::DATE
WHERE a.converted_at IS NOT NULL
GROUP BY a.id, a.status
)
SELECT
CASE WHEN total_usage = 0 OR total_usage IS NULL THEN 'Zero usage' ELSE 'Active user' END AS usage_category,
COUNT(*) AS accounts,
COUNT(*) FILTER (WHERE status = 'churned') AS churned,
ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'churned')
/ NULLIF(COUNT(*), 0), 1) AS churn_rate_pct
FROM last_month_usage
GROUP BY 1;Key Takeaway
Churn rate calculation requires careful denominator selection — "accounts active at start of month" avoids the survivorship bias of using end-of-month count. The pre-churn engagement pattern (joining to activity in the N days before churned_at) is the foundation of early-warning churn models and feeds into customer success playbooks.