Customer analytics — RFM, retention, churn, cohorts, CLV and renewal

RFM analysis

Worked example — segment sizes at Orion Retail (analysis date 2025-12-31):

WITH c AS (
  SELECT o.customer_id,
         julianday('2025-12-31') - julianday(MAX(o.order_date)) AS recency,
         COUNT(DISTINCT o.order_id) AS frequency,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS monetary
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed' GROUP BY o.customer_id),
s AS (
  SELECT *, NTILE(5) OVER (ORDER BY recency DESC) AS r,
            NTILE(5) OVER (ORDER BY frequency) AS f,
            NTILE(5) OVER (ORDER BY monetary) AS m FROM c)
SELECT CASE WHEN r >= 4 AND f >= 4 THEN 'Champions'
            WHEN r >= 3 AND f >= 3 THEN 'Loyal'
            WHEN r <= 2 AND f >= 3 THEN 'At risk'
            WHEN r >= 4 AND f <= 2 THEN 'New / promising'
            ELSE 'Hibernating' END AS segment,
       COUNT(*) AS customers, ROUND(SUM(monetary)) AS revenue,
       ROUND(100.0 * SUM(monetary) / (SELECT SUM(monetary) FROM s), 1) AS revenue_share_pct
FROM s GROUP BY segment ORDER BY revenue DESC;

Interpretation. Champions and Loyal customers are a minority of customers but the majority of revenue; "At risk" customers used to buy often but have gone quiet — the highest-value win-back target. Decisions. Loyalty perks for Champions; personalised win-back for At risk; low-cost nurture for New; minimal spend on Hibernating. Common mistakes. Using "today" as analysis date (results drift); ignoring ties in NTILE; segment rules nobody can explain.

Churn, retention and renewal

Worked example — telecom monthly churn (2025):

WITH active AS (SELECT month, COUNT(*) AS active FROM monthly_usage GROUP BY month),
     lost AS (SELECT churn_month AS month, COUNT(*) AS churned FROM subscribers WHERE churned = 1 GROUP BY churn_month)
SELECT a.month, a.active, l.churned, ROUND(100.0 * l.churned / a.active, 2) AS churn_pct
FROM active a JOIN lost l ON l.month = a.month
WHERE a.month >= '2025-01' ORDER BY a.month LIMIT 6;

churn_month is the last billed month, so a subscriber churning in month M is active in M — the denominator is correct for "share of this month's base that leaves after this month". A 2% monthly churn compounds to about 1 − 0.98¹² ≈ 21.5% a year.

Renewal (insurance):

SELECT policy_type, COUNT(*) AS due, SUM(renewed) AS renewed, ROUND(100.0 * AVG(renewed), 1) AS renewal_pct
FROM policies WHERE end_date <= '2025-12-31' GROUP BY policy_type ORDER BY renewal_pct;

Travel policies renew far less — often because trips are one-off. The decision may be to not judge travel by renewal at all but by repeat purchase.

Cohort analysis

Covered technically in SQL (Module 2). Business reading: plot each cohort's retention curve; compare at the same age (month 3 of the 2024-01 cohort vs month 3 of the 2025-01 cohort). Flattening curves = a loyal core; steepening curves for newer cohorts = deteriorating acquisition quality or onboarding.

Customer lifetime value (CLV)

Definition. The expected (discounted) gross margin a customer generates over their relationship. Simple formula (constant retention):

CLV = m × r ÷ (1 + d − r)

where m = annual margin per active customer, r = annual retention rate, d = annual discount rate (cost of capital). (Variant without margin in the first year: multiply by r; including year 1: m × (1 + d) ÷ (1 + d − r).)

Worked example. m = 120, r = 0.70, d = 0.10 → CLV = 120 × 0.70 ÷ (1.10 − 0.70) = 84 ÷ 0.40 = 210.

Interpretation & decisions. If acquiring a customer costs 150 (CAC), CLV/CAC = 1.4 — thin. Raising retention to 0.80 gives 120 × 0.8 ÷ 0.3 = 320 — retention is often the strongest lever. Common mistakes. Using revenue instead of margin; ignoring discounting; averaging CLV across very different segments; treating historical averages as guarantees.

Practice

Summary

• RFM segments customers on recency, frequency, monetary value with a fixed analysis date and explicit rules.
• Churn/retention/renewal live or die by their denominators.
• Cohorts compare customers at the same age; CLV turns retention into money — use margin and discounting.