Customer Lifetime Value (LTV): Predict Long-Term Revenue

Customer Lifetime Value (LTV) is the total revenue a business expects to earn from a customer over their entire relationship.

Why LTV Matters

The Business Question: "How much should I spend to acquire a customer?"

Without LTV:

With LTV:

LTV Formula (Basic)

Real Example: Swiggy Customer LTV

Scenario: Calculate LTV for Swiggy food delivery customer.

Data (from cohort analysis):

Calculation:

Business Decision:

• LTV/CAC > 3× = Profitable, scale acquisition
• Payback period: ₹1,200 CAC / (₹21,600 Year 1 revenue) = 20 days (fast payback)
• Action: Invest aggressively in customer acquisition (high ROI)

Method 1: Historical LTV (Simple Average)

Use Case: Quick estimate using existing customer data.

SQL Query:

WITH customer_revenue AS (
  SELECT
    customer_id,
    MIN(order_date) AS first_order_date,
    MAX(order_date) AS last_order_date,
    COUNT(DISTINCT order_id) AS total_orders,
    SUM(order_value) AS total_revenue,
    DATEDIFF('day', MIN(order_date), MAX(order_date)) AS lifespan_days
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '24 months'
  GROUP BY customer_id
)
SELECT
  AVG(total_revenue) AS avg_ltv,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_revenue) AS median_ltv,
  AVG(total_orders) AS avg_orders_per_customer,
  AVG(total_revenue / NULLIF(total_orders, 0)) AS avg_order_value,
  AVG(lifespan_days) AS avg_lifespan_days
FROM customer_revenue
WHERE lifespan_days >= 90;  -- Exclude very new customers

Output:

Pros: Simple, uses real data Cons: Backward-looking (doesn't predict future), biased by mature customers

Method 2: Cohort-Based LTV (Most Accurate)

Use Case: Predict LTV using retention curves from cohort analysis.

SQL Query:

WITH cohorts AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders
  GROUP BY customer_id
),
cohort_revenue AS (
  SELECT
    c.cohort_month,
    DATEDIFF('month', c.cohort_month, DATE_TRUNC('month', o.order_date)) AS months_since_signup,
    COUNT(DISTINCT o.customer_id) AS active_customers,
    SUM(o.order_value) AS revenue
  FROM cohorts c
  JOIN orders o ON c.customer_id = o.customer_id
  WHERE c.cohort_month >= '2025-01-01'
  GROUP BY c.cohort_month, months_since_signup
),
cohort_sizes AS (
  SELECT
    cohort_month,
    COUNT(*) AS cohort_size
  FROM cohorts
  WHERE cohort_month >= '2025-01-01'
  GROUP BY cohort_month
)
SELECT
  cr.months_since_signup,
  AVG(cr.revenue / cs.cohort_size) AS avg_revenue_per_user,
  SUM(AVG(cr.revenue / cs.cohort_size)) OVER (ORDER BY cr.months_since_signup) AS cumulative_ltv
FROM cohort_revenue cr
JOIN cohort_sizes cs ON cr.cohort_month = cs.cohort_month
GROUP BY cr.months_since_signup
ORDER BY cr.months_since_signup;

Output:

Pros: Accounts for retention decay, predicts future revenue Cons: Requires cohort data (minimum 12 months history)

Method 3: Formula-Based LTV (Quick Estimate)

Use Case: Early-stage companies without historical data.

Formula:

Example — PhonePe Wallet:

Pros: Fast, works with limited data Cons: Assumes constant churn (unrealistic), simplified

Method 4: Discounted Cash Flow (DCF) LTV

Use Case: Account for time value of money (future revenue worth less than today).

Formula:

Example — Netflix Subscription:

Pros: Financially accurate (accounts for time value) Cons: Complex, requires retention curve + discount rate

LTV/CAC Ratio

Definition: How many times LTV exceeds CAC (return on acquisition investment).

Real Company Examples

Netflix:

Swiggy (Food Delivery):

Blinkit (Quick Commerce):

SaaS Startup (Typical):

Payback Period

Definition: Time to recover CAC from customer revenue.

Example — Zomato Gold Subscription:

Improving Payback:

1. Reduce CAC: ₹600 → ₹400 (reduce first-order discount) → Payback = 10 months
2. Increase frequency: 2 orders/month → 3 orders/month → Payback = 10 months
3. Increase margin: 10% → 15% (premium subscription) → Payback = 10 months

1. Increase Average Order Value (AOV)

Tactics:

Example — Myntra:

2. Increase Purchase Frequency

Tactics:

Example — Nykaa:

3. Increase Customer Lifespan (Reduce Churn)

Tactics:

Example — Netflix:

4. Increase Gross Margin

Tactics:

Example — Flipkart:

LTV Improvement Priority Matrix

| Metric | Difficulty | Impact | Priority | |--------|------------|--------|----------| | Reduce Churn | Hard | Very High | 🔴 Critical (10-50% LTV increase) | | Increase Frequency | Medium | High | 🟡 High (20-50% LTV increase) | | Increase AOV | Easy | Medium | 🟢 Quick Win (10-20% LTV increase) | | Increase Margin | Medium | Medium | 🟡 High (10-30% LTV increase) |

Recommended Approach:

1. Quick wins (Month 1): Increase AOV (cross-sell, bundles) — easy, fast impact
2. High impact (Month 2-3): Increase frequency (loyalty program, subscriptions)
3. Long-term (Month 4+): Reduce churn (product improvements, customer success)