How to calculate Customer Lifetime Value (CLV) and Retention Churn Cohorts accurately?

Calculate CLV by summing the discounted cash flows of each cohort’s retained revenue and subtracting acquisition cost; compute churn by tracking month‑over‑month cohort attrition.

Step‑by‑step implementation

1. Extract raw events – Pull order_date, customer_id, revenue from your warehouse (e.g., Snowflake):

SELECT customer_id,
       DATE_TRUNC('month', order_date) AS cohort_month,
       SUM(revenue) AS month_rev
FROM sales
WHERE order_date >= '2022-01-01'
GROUP BY 1,2;

2. Assign cohort – The first purchase month defines the cohort:

WITH first_purchase AS (
  SELECT customer_id,
         MIN(DATE_TRUNC('month', order_date)) AS cohort_month
  FROM sales
  GROUP BY 1
)
SELECT s.customer_id,
       f.cohort_month,
       DATE_TRUNC('month', s.order_date) AS period,
       s.revenue
FROM sales s
JOIN first_purchase f USING (customer_id);

3. Calculate retention rate – retention = active_customers / cohort_size per period.
4. Compute discounted revenue – Use a monthly discount rate (e.g., 0.8 % → 0.008):

def discounted_rev(rev, month, dr=0.008):
    return rev / ((1+dr) ** month)

5. Aggregate CLV per cohort:

SELECT cohort_month,
       SUM(discounted_rev) - AVG(cac) AS clv
FROM (
  SELECT cohort_month,
         month,
         SUM(revenue) AS rev,
         discounted_rev(SUM(revenue), month) AS discounted_rev,
         FIRST_VALUE(cac) OVER (PARTITION BY cohort_month) AS cac
  FROM cohort_data
  GROUP BY 1,2
) t
GROUP BY 1;

6. Derive churn – churn_rate = 1 - retention_rate. Plot month‑over‑month churn for each cohort.
7. Validate – Compare cohort CLV against rolling‑window CLV (e.g., 12‑month moving average). Discrepancies > 5 % signal data‑quality issues.

Quick comparison

| Metric | Cohort‑based | Rolling 12‑mo |
|--------|--------------|---------------|
| Sensitivity to seasonality | High | Low |
| Data latency | 1‑2 months | Real‑time |
| Typical error margin | ≤ 3 % | 5‑8 % |

Checklist before publishing
- [ ] All dates normalized to UTC.
- [ ] Discount rate matches company WACC.
- [ ] CAC calculated per acquisition channel.
- [ ] Cohort sizes ≥ 30 customers for statistical stability.