Skip to main content

Knowledge > Runbooks > Business Ops > Customer Churn Analysis

Customer Churn Analysis

Analyze cancelled subscriptions to understand why customers leave and improve retention before the pattern repeats.

Prerequisites​

  • Stripe CLI with live mode access (STRIPE_LIVE_SECRET_KEY)
  • Access to Supabase SQL editor
  • At least 3–5 churned customers to identify meaningful patterns (this runbook is most useful after the first few months of paying customers)

Note: As of 2026-04-10, there are 3 Stripe subscriptions (1 customer + 2 founder tests) and 0 churned customers. Run this analysis once you have your first churn event. The runbook is ready — just needs data.

Steps​

1. Pull Churned Subscriptions from Stripe​

# List cancelled subscriptions (most recent first)
stripe subscriptions list --status canceled --limit 50 --api-key $STRIPE_LIVE_SECRET_KEY

For each cancelled subscription, note:

  • Customer ID
  • Plan/product
  • Cancellation date
  • How long they were a customer (tenure)
  • Cancellation reason (if provided in Stripe cancellation survey)

2. Identify Churned Records in the Database​

-- premium_churches where stripe_subscription_id is NULL but was previously set
-- (This depends on whether we log the history — adjust to actual schema)
SELECT
  pc.id,
  pc.church_id,
  pc.plan,
  pc.tier,
  pc.updated_at as churned_at,
  c.name as church_name,
  c.city,
  c.state
FROM premium_churches pc
JOIN churches c ON pc.church_id = c.id
WHERE pc.stripe_subscription_id IS NULL
  AND pc.plan = 'free'  -- was downgraded to free
  AND pc.updated_at > now() - interval '90 days'
ORDER BY pc.updated_at DESC;

If there's a separate churn_log table or event log: query that instead.

3. Analyze Pre-Churn Engagement​

For each churned church, look at their engagement in the 30 days before churning:

-- Voice calls in last 30 days before churn
SELECT church_id, count(*) as calls_before_churn
FROM voice_call_logs
WHERE church_id = '[churned-church-id]'
  AND created_at BETWEEN '[churn_date]'::timestamp - interval '30 days' AND '[churn_date]'::timestamp;

-- KB content count at time of churn
SELECT count(*) as kb_entries_at_churn
FROM unified_rag_content
WHERE church_id = '[churned-church-id]'
  AND created_at < '[churn_date]';

4. Look for Common Churn Patterns​

Cluster churned customers by attributes to find patterns:

DimensionQuestions to Answer
Plan typeDid a particular plan churn more? (Starter vs. Pro vs. Suite?)
TenureDid most churn in month 1, 2, 3? (Early vs. late churn)
Feature usageDid they ever set up the KB? Ever make a voice call?
Church sizeWere they small (40 members), medium, large?
Admin loginsWhen did they last log in before churning?
Cancellation reasonWhat reason did they give in Stripe's cancellation flow?

5. Calculate Churn Rate​

Monthly Churn Rate = (Customers Lost This Month) / (Customers at Start of Month) × 100%

Target: < 5% monthly churn (best-in-class SaaS). > 10% is a significant problem requiring immediate action.

6. Update Customer Health Check Based on Findings​

After analyzing churn, update customer-health-check.md with newly discovered early warning signals. Example discoveries:

  • "Churches that never added KB content churned within 60 days" → add "0 KB entries after 14 days" as Red signal
  • "Churches with no admin login in 30 days always churned within 15 days" → escalate Yellow to Red threshold
  • "Pro plan churned less than Starter" → feature differentiation may be working

7. Take Action on Insights​

For each confirmed pattern:

  • If it's a product gap (e.g., "setup was too hard"): create a feature or onboarding improvement task
  • If it's a pricing issue (e.g., "too expensive for small churches"): surface to founder with data
  • If it's a missing feature (e.g., "needed X that we don't have"): add to FEATURE_REGISTRY.md as a consideration

8. Document Findings​

Append to DECISION_LOG.md:

- 2026-03-25: Churn analysis — Q1 2026. X churned, X% monthly rate. Top pattern: [finding]. Actions: [list].

Verification​

  • Every churned customer in the analysis period has been reviewed
  • At least 2–3 patterns identified (even if they're "no pattern yet" with small N)
  • Customer health check updated with any new early warning signals
  • Feature or onboarding improvements filed if product gaps identified

See Also​