Retention vs Churn

Technical Explanation

Retention and churn are complementary metrics that measure customer loyalty from opposite perspectives. Retention measures customers kept; churn measures customers lost.

Definitions

Metric	Formula	Meaning
Retention Rate	(Customers at end - New) / Customers at start × 100	% kept
Churn Rate	Lost customers / Customers at start × 100	% lost
Net Retention	(Start - Churned + Expansion - Contraction) / Start × 100	Net growth

Key Periods

• Day 1 Retention: Did they come back the next day?
• Week 1 Retention: Did they return within 7 days?
• Month 1 Retention: Active in the second month?

---

Code Examples

Using the CatCafe dataset:

-- Tables:
-- customers: id, registration_date
-- orders: id, customer_id, order_date, total_amount

-- Basic monthly retention cohort
WITH cohort_sizes AS (
    -- Month 0 cohort sizes (new customers per month)
    SELECT
        DATE_TRUNC('month', c.registration_date) as cohort_month,
        COUNT(DISTINCT c.id) as cohort_size
    FROM customers c
    GROUP BY DATE_TRUNC('month', c.registration_date)
),
monthly_activity AS (
    -- When each customer was active (month number)
    SELECT
        c.id,
        c.registration_date,
        DATE_TRUNC('month', c.registration_date) as cohort_month,
        DATE_TRUNC('month', o.order_date) as activity_month,
        DENSE_RANK() OVER (
            PARTITION BY DATE_TRUNC('month', c.registration_date)
            ORDER BY DATE_TRUNC('month', o.order_date)
        ) as months_since_signup
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    WHERE o.status = 'completed'
)
SELECT
    cs.cohort_month,
    cs.cohort_size,
    SUM(CASE WHEN ma.months_since_signup = 1 THEN 1 ELSE 0 END) as month_1_active,
    SUM(CASE WHEN ma.months_since_signup = 2 THEN 1 ELSE 0 END) as month_2_active,
    SUM(CASE WHEN ma.months_since_signup = 3 THEN 1 ELSE 0 END) as month_3_active
FROM cohort_sizes cs
LEFT JOIN monthly_activity ma ON cs.cohort_month = ma.cohort_month
GROUP BY cs.cohort_month, cs.cohort_size
ORDER BY cs.cohort_month;

-- Churn calculation by month
WITH monthly_customers AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        COUNT(DISTINCT customer_id) as active_customers
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    current.month,
    current.active_customers,
    previous.active_customers as prev_month_active,
    previous.active_customers - current.active_customers as churned,
    (previous.active_customers - current.active_customers) * 100.0
        / NULLIF(previous.active_customers, 0) as churn_rate
FROM monthly_customers current
LEFT JOIN monthly_customers previous ON current.month = previous.month + INTERVAL '1 month'
ORDER BY current.month;

-- Rolling churn: Customers active in month N who didn't return in N+1
WITH active_customers AS (
    SELECT DISTINCT
        DATE_TRUNC('month', order_date) as month,
        customer_id
    FROM orders
    WHERE status = 'completed'
),
churned AS (
    SELECT
        ac.month,
        ac.customer_id
    FROM active_customers ac
    WHERE NOT EXISTS (
        SELECT 1
        FROM active_customers ac2
        WHERE ac2.customer_id = ac.customer_id
        AND ac2.month = ac.month + INTERVAL '1 month'
    )
)
SELECT
    churned.month,
    COUNT(DISTINCT churned.customer_id) as churned_customers,
    COUNT(DISTINCT ac.customer_id) as prev_month_active,
    COUNT(DISTINCT churned.customer_id) * 100.0
        / NULLIF(COUNT(DISTINCT ac.customer_id), 0) as churn_rate
FROM churned
JOIN active_customers ac ON churned.customer_id = ac.customer_id
    AND ac.month = churned.month - INTERVAL '1 month'
GROUP BY churned.month
ORDER BY churned.month;

---

The Cat Analogy

Think of retention/churn like cat shelter return rates:

Retention = Cats who stayed in their new homes:

Month 1: 80 of 100 adopted cats still home (80% retention)
Month 2: 65 still home (65% cumulative retention)
Month 3: 55 still home (55% cumulative retention)

Churn = Cats returned to shelter:

Month 1: 20 cats returned (20% churn)
Month 2: 15 more returned (15% churn of remaining)
Month 3: 10 more returned (10% churn)

Why both matter:

If you only track retention: "80% kept after month 1" sounds great
If you only track churn: "20% left" sounds alarming

Reality: Both tell the story. High retention/low churn = healthy adoption program.

---

Common Pitfalls

Confusing Periods

-- WRONG: Mixing monthly and daily
SELECT
    COUNT(DISTINCT customer_id) as retained
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
AND customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '60 days'
    AND order_date < CURRENT_DATE - INTERVAL '30 days'
);
-- Better: Use proper period definitions

Calculating Churn from Non-Overlapping Periods

-- WRONG: Churn of 100% because you're comparing different cohorts
SELECT
    (SELECT COUNT(*) FROM customers) -
    (SELECT COUNT(*) FROM customers WHERE ...);
-- This doesn't make sense as churn!

-- RIGHT: Churn is month-over-month comparison
SELECT
    prev_active - curr_active as churned,
    churned * 100.0 / prev_active as churn_rate
FROM monthly_retention;

---

Exercises

Exercise 1

Write a query to calculate the month-1 retention rate for customers who signed up in the last 6 months.

Exercise 2

Explain the difference between "churn rate" and "retention rate."

Exercise 3

Write a query to find customers who churned (no orders in 30+ days) and calculate their lifetime value.

Exercise 4

What does a churn rate of 0% mean? Is that always good?

Exercise 5

Write a query showing churn rate by customer acquisition source.

---

Key Takeaways

• Retention = % of customers who stayed; Churn = % who left
• Retention + Churn = 100% (for same period)
• Churn rate = (Lost customers / Start customers) × 100
• Cohort retention shows if new customers are becoming more/less loyal
• Rolling churn: customers active last month who didn't return this month
• High churn early often indicates poor fit or expectations mismatch