Customer Lifetime Value (CLV or LTV) is arguably the most important metric in business. It answers: "How much can we afford to spend to acquire a customer?"
If your CLV is $500, you can profitably spend $100 on ads to get them. If your CLV is $50, spending $100 will bankrupt you.
1. Historical CLV (The Easy Way)
The simplest version is to just look at how much customers have already spent.
SELECT
customer_id,
SUM(amount) as lifetime_revenue
FROM orders
GROUP BY customer_id;
This is accurate but backward-looking. It doesn't tell you what a new customer is worth today.
2. Simple Predictive CLV (ARPU / Churn)
A common formula for subscription businesses (SaaS) is:
Formula:
CLV = (Average Monthly Revenue per User (ARPU)) / (Monthly Churn Rate)
If average users pay $10/month and 5% cancel every month, expected lifetime is $1 / 0.05 = 20$ months. $20 \times $10 = $200$ CLV.
Interactive Playground: Calculating ARPU and Churn
Let's calculate the inputs for this formula from raw subscription logs.
Cohort-Based CLV
The most accurate way to measure CLV is via Cohorts. You group users by the month they joined (e.g., "Jan 2024 Cohort") and track their cumulative spend over time.
This requires pivot tables or complex self-joins, but it reveals trends like "Newer cohorts are spending less than older cohorts," which a simple average would hide.
Conclusion
CLV isn't just a number; it's a health check for your business model. SQL allows you to track this dynamic metric daily, giving you early warning signs if your customer value starts to degrade.