Recap — LTV Three Ways

# LTV Three Ways "What's the LTV of our customers?" is the most-asked, worst-understood question in SaaS analytics. There isn't one LTV. There are at least three, each with a different SQL pattern. ## 1) Historic LTV — what each customer has paid so far The simplest version. For every customer, sum every dollar they have ever paid. ```sql SELECT customer_id, SUM(amount) AS historic_ltv FROM payments GROUP BY customer_id; ``` This is honest about the past but says nothing about the future. Useful for *who are our most valuable customers*, useless for *what's the LTV of a customer we acquired yesterday*. ## 2) Cohort LTV — average revenue from each acquisition cohort over time Group customers by the month (or week) they signed up, then watch how cumulative revenue evolves. ```sql WITH cohorts AS ( SELECT customer_id, DATE_TRUNC('month', first_payment_date) AS cohort_month FROM customer_first_payment ), revenue_by_cohort AS ( SELECT c.cohort_month, DATE_TRUNC('month', p.payment_date) AS revenue_month, SUM(p.amount) AS revenue, COUNT(DISTINCT c.customer_id) AS cohort_size FROM cohorts c JOIN payments p ON p.customer_id = c.customer_id GROUP BY 1, 2 ) SELECT cohort_month, revenue_month, SUM(revenue) OVER (PARTITION BY cohort_month ORDER BY revenue_month) AS cumulative_revenue, cohort_size, SUM(revenue) OVER (PARTITION BY cohort_month ORDER BY revenue_month)::float / cohort_size AS cumulative_ltv FROM revenue_by_cohort; ``` The output: for the Jan cohort, after 3 months avg LTV is X; after 6 months, Y. That's the *real* LTV curve. ## 3) Predictive LTV — the naive analyst version The classic interview answer: `ARPU / churn_rate`. ```sql WITH metrics AS ( SELECT AVG(monthly_revenue) AS arpu, AVG(CASE WHEN churned_this_month THEN 1.0 ELSE 0 END) AS monthly_churn FROM customer_month WHERE month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') ) SELECT arpu / monthly_churn AS predicted_ltv FROM metrics; ``` It's a back-of-envelope. It assumes constant churn forever (wrong) and constant ARPU per customer (also wrong). In an interview you should *compute* it, then immediately call out its limitations. That's the signal of a senior analyst. ## Which one when? | Question | LTV flavour | |---|---| | Who are our top customers? | Historic LTV | | What's the payback period on Q1 acquisitions? | Cohort LTV | | Quick estimate for a board deck? | Predictive (ARPU / churn) | | Comparing acquisition channels? | Cohort LTV by channel | ## Common interview traps 1. **Using historic LTV for new customers** — gives misleadingly low values for recent signups who haven't had time to generate revenue yet. 2. **Forgetting to discount future cash flows** — a strict definition requires NPV, but most interviews don't go there unless you mention it first. 3. **Mixing gross vs net** — LTV usually means gross revenue. If asked for *contribution margin LTV*, subtract COGS first. ## Takeaway When asked "compute LTV", the right answer is "which one?" Show you know there are several, pick the one that fits the use case, and write the SQL. That's the difference between an analyst who has seen the question and one who has answered it before.