SQL Customer Lifetime Value

A per-user SUM of everything they've ever spent — simple math, meaningful framing.

What & Why

Customer Lifetime Value estimates the value a customer generates across the relationship, usually from revenue, margin, frequency, and lifespan inputs. CLV guides acquisition spending only when every input and assumption is documented.

See How It Works

BUSINESS QUESTION

Calculate observed lifetime paid-invoice value for every SaaS account without inventing future revenue.

id	name	plan	mrr	created_at	churned_at	country	employee_count	industry
1	Acme Labs	growth	240.00	2024-01-08 10:00:00+00	NULL	US	45	software
2	Northstar Co	starter	90.00	2024-02-12 09:30:00+00	2024-05-18 12:00:00+00	CA	18	services
3	Atlas Works	scale	480.00	2024-03-04 15:10:00+00	NULL	GB	120	manufacturing
4	Bright Path	growth	180.00	2024-04-19 11:45:00+00	NULL	US	62	education

id	account_id	amount	status	due_date	paid_at
10	1	240.00	paid	2024-02-01	2024-01-29 13:00:00+00
11	1	260.00	paid	2024-03-01	2024-02-28 16:30:00+00
12	2	90.00	overdue	2024-03-15	NULL
13	3	480.00	open	2024-04-01	NULL

EXAMPLE QUERY

SELECT
  a.id AS account_id,
  a.name,
  a.plan,
  COALESCE(SUM(i.amount) FILTER (WHERE i.status = 'paid'), 0) AS observed_lifetime_value
FROM saas.accounts a
LEFT JOIN saas.invoices i ON i.account_id = a.id
GROUP BY a.id, a.name, a.plan
ORDER BY observed_lifetime_value DESC, account_id;

RESULT — observed paid value per account

account_id	name	plan	observed_lifetime_value
1	Acme Labs	growth	500.00
2	Northstar Co	starter	0
3	Atlas Works	scale	0
4	Bright Path	growth	0

Only Acme Labs has paid invoices in the representative data; the LEFT JOIN and COALESCE preserve the other accounts with zero value.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice