Customer Lifetime Value (LTV)

Overview

Customer Lifetime Value (LTV) represents the total revenue a business can expect from a single customer throughout their entire relationship. This fundamental metric helps businesses understand how much they can afford to spend on customer acquisition and retention.

LTV is crucial for making informed decisions about marketing spend, pricing strategies, and customer success investments. It provides the foundation for calculating important ratios like LTV:CAC and guides long-term business strategy.

Why It Matters

• Acquisition Investment: Determines how much to spend on customer acquisition
• Customer Segmentation: Identifies most valuable customer segments
• Pricing Strategy: Informs pricing and packaging decisions
• Resource Allocation: Guides investment in customer success and retention
• Business Valuation: Critical component of company valuation models
• Product Development: Prioritizes features based on customer value

How to Measure It

LTV can be calculated using historical data, predictive models, or simplified formulas depending on data availability and business maturity.

Historical LTV Calculation

-- Calculate LTV using historical customer data
WITH customer_revenue AS (
  SELECT 
    customer_id,
    DATE_TRUNC('month', created_date) as acquisition_month,
    acquisition_channel,
    customer_segment,
    SUM(revenue_amount) as total_revenue,
    SUM(gross_profit) as total_gross_profit,
    MAX(last_activity_date) as last_active,
    EXTRACT(days FROM AGE(MAX(last_activity_date), MIN(created_date))) as customer_lifespan_days
  FROM customer_transactions
  WHERE created_date >= '2022-01-01'
  GROUP BY 1, 2, 3, 4
),
ltv_analysis AS (
  SELECT 
    acquisition_month,
    acquisition_channel,
    customer_segment,
    COUNT(*) as customers,
    AVG(total_revenue) as avg_ltv_revenue,
    AVG(total_gross_profit) as avg_ltv_gross_profit,
    AVG(customer_lifespan_days) as avg_lifespan_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_gross_profit) as median_ltv
  FROM customer_revenue
  WHERE customer_lifespan_days >= 90  -- Only include customers with sufficient history
  GROUP BY 1, 2, 3
)
SELECT 
  acquisition_month,
  acquisition_channel,
  customer_segment,
  customers,
  ROUND(avg_ltv_gross_profit, 2) as avg_ltv,
  ROUND(median_ltv, 2) as median_ltv,
  ROUND(avg_lifespan_days / 30.0, 1) as avg_lifespan_months
FROM ltv_analysis
ORDER BY avg_ltv DESC;

Predictive LTV Model

-- Calculate LTV using churn-based prediction
WITH customer_metrics AS (
  SELECT 
    customer_id,
    acquisition_channel,
    plan_type,
    monthly_revenue,
    gross_margin_percentage,
    months_active,
    CASE WHEN status = 'churned' THEN 1 ELSE 0 END as is_churned
  FROM customer_summary
  WHERE months_active >= 3
),
churn_rates AS (
  SELECT 
    acquisition_channel,
    plan_type,
    AVG(monthly_revenue) as avg_monthly_revenue,
    AVG(gross_margin_percentage) as avg_gross_margin,
    SUM(is_churned)::FLOAT / COUNT(*) as monthly_churn_rate
  FROM customer_metrics
  GROUP BY 1, 2
),
ltv_prediction AS (
  SELECT 
    acquisition_channel,
    plan_type,
    avg_monthly_revenue,
    avg_gross_margin,
    monthly_churn_rate,
    -- LTV = (Monthly Revenue × Gross Margin %) / Monthly Churn Rate
    (avg_monthly_revenue * avg_gross_margin / 100) / NULLIF(monthly_churn_rate, 0) as predicted_ltv
  FROM churn_rates
)
SELECT 
  acquisition_channel,
  plan_type,
  ROUND(avg_monthly_revenue, 2) as avg_mrr,
  ROUND(avg_gross_margin, 1) as gross_margin_pct,
  ROUND(monthly_churn_rate * 100, 2) as churn_rate_pct,
  ROUND(predicted_ltv, 2) as predicted_ltv,
  ROUND(1 / NULLIF(monthly_churn_rate, 0), 1) as predicted_lifespan_months
FROM ltv_prediction
ORDER BY predicted_ltv DESC;

Best Practices

1. Choose the Right Method

• Historical LTV: For mature businesses with 2+ years of data
• Predictive LTV: For stable churn rates and sufficient cohorts
• Simple LTV: For early-stage companies or new segments
• Cohort LTV: For understanding trends over time

2. Segmentation Strategies

• By acquisition channel (organic, paid, referral)
• By customer size (SMB, mid-market, enterprise)
• By plan type or pricing tier
• By geographic region or market

3. Revenue Components

• Include all recurring revenue streams
• Account for expansion revenue potential
• Use gross margin-adjusted figures
• Consider one-time revenue appropriately

4. Time Considerations

• Update LTV calculations regularly (quarterly)
• Account for seasonality in churn patterns
• Use appropriate cohort maturity periods
• Consider discounting future cash flows

5. Optimization Tactics

• Reduce churn through better onboarding
• Increase ARPU through feature adoption
• Drive expansion revenue with upsells
• Focus acquisition on high-LTV segments

Related Metrics

• Customer Acquisition Cost (CAC)
• LTV:CAC Ratio
• Customer Churn Rate
• Average Revenue Per User (ARPU)
• Net Revenue Retention
• CAC Payback Period