How to calculate Customer Lifetime Value (LTV)

Formula

LTV = ARPU × gross margin % ÷ customer churn rate (monthly)

SQL query

This example assumes: subscriptions(customer_id, mrr, started_at, ended_at), with gross margin supplied as a constant. It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

WITH base AS (
  SELECT SUM(mrr) / NULLIF(COUNT(DISTINCT customer_id), 0) AS arpu
  FROM subscriptions
  WHERE started_at <= CURRENT_DATE AND (ended_at IS NULL OR ended_at > CURRENT_DATE)
), churn AS (
  SELECT COUNT(DISTINCT customer_id) FILTER (
           WHERE ended_at >= DATE '2026-10-01' AND ended_at < DATE '2026-11-01') * 1.0
         / NULLIF(COUNT(DISTINCT customer_id) FILTER (
           WHERE started_at < DATE '2026-10-01'
             AND (ended_at IS NULL OR ended_at >= DATE '2026-10-01')), 0) AS monthly_churn
  FROM subscriptions
)
SELECT base.arpu * 0.80 / NULLIF(churn.monthly_churn, 0) AS ltv   -- 0.80 = assumed gross margin
FROM base, churn;

Common mistakes that make Customer Lifetime Value (LTV) wrong

• It is highly sensitive to churn: a churn estimate from a few months can swing LTV wildly.
• Using revenue instead of gross profit overstates the value.
• LTV from the average hides segments; compute it per plan or cohort.

Ask for Customer Lifetime Value (LTV) in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• What is the customer lifetime value by plan?
• How would LTV change if monthly churn dropped by one point?