How to Calculate Customer Lifetime Value in SQL

LTV gets quoted in board decks and investor conversations, but when you sit down to compute it, the first thing you hit is a fork: subscription business or transactional business? The formula is different in a way that isn't cosmetic.

Transactional (e-commerce, marketplaces): LTV = avg order value x purchase frequency x avg customer lifespan. Subscription (SaaS, recurring billing): LTV = avg revenue per month / monthly churn rate. These aren't interchangeable approximations of the same thing. A SaaS customer paying $100/month for 18 months before canceling has a completely different revenue shape than an e-commerce customer making four $150 purchases over three years. Use the wrong one and your LTV will be off by 2-5x. I've watched teams do exactly this when a subscription company applies the transactional formula because "we also sell one-time add-ons."

Every query below uses three tables: customers(id, email, created_at, acquisition_channel), orders(id, customer_id, total, created_at), and subscriptions(id, customer_id, plan_amount, status, created, canceled_at). Most businesses have something close to this already.

Historical LTV: the number you can actually defend

Predictive models are only as good as their assumptions. Historical LTV has no assumptions. It's "how much has this customer paid us, total, to date."

SELECT
  c.id                       AS customer_id,
  c.acquisition_channel,
  count(o.id)                AS total_orders,
  coalesce(sum(o.total), 0)  AS ltv_to_date,
  min(o.created_at)          AS first_order,
  max(o.created_at)          AS last_order,
  extract(month FROM age(now(), c.created_at)) AS months_since_signup
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.acquisition_channel
ORDER BY ltv_to_date DESC;

The LEFT JOIN matters. Switch it to INNER JOIN and you silently drop everyone who signed up but never purchased, inflating your average and hiding the real conversion gap.

For subscription businesses, replace the orders table with subscriptions and compute duration from created and canceled_at:

sum(plan_amount * extract(month FROM age(coalesce(canceled_at, now()), created)))

Same idea, different revenue source.

Cohort LTV: how value develops over time

Historical LTV per customer is useful for sales and support. But for growth decisions you need cohort-level LTV — total revenue from everyone who signed up in month X, divided by the number of people in that cohort. This shows you whether newer cohorts are more or less valuable than older ones, and how quickly value accumulates.

WITH cohort_revenue AS (
  SELECT
    date_trunc('month', c.created_at)  AS cohort_month,
    c.id                               AS customer_id,
    o.total                            AS order_total,
    extract(month FROM age(o.created_at, c.created_at)) AS months_since_signup
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
),
cohort_sizes AS (
  SELECT
    date_trunc('month', created_at) AS cohort_month,
    count(*)                        AS cohort_size
  FROM customers
  GROUP BY 1
)
SELECT
  cs.cohort_month,
  cs.cohort_size,
  round(sum(cr.order_total) / cs.cohort_size, 2)
    AS avg_ltv_to_date,
  round(sum(cr.order_total) FILTER (WHERE cr.months_since_signup <= 3) / cs.cohort_size, 2)
    AS avg_ltv_first_3mo,
  round(sum(cr.order_total) FILTER (WHERE cr.months_since_signup <= 6) / cs.cohort_size, 2)
    AS avg_ltv_first_6mo,
  round(sum(cr.order_total) FILTER (WHERE cr.months_since_signup <= 12) / cs.cohort_size, 2)
    AS avg_ltv_first_12mo
FROM cohort_sizes cs
LEFT JOIN cohort_revenue cr ON cr.cohort_month = cs.cohort_month
GROUP BY cs.cohort_month, cs.cohort_size
ORDER BY cs.cohort_month;

The FILTER (WHERE months_since_signup <= N) columns let you compare cohorts fairly. Without them, a 2-year-old cohort will always look more valuable than a 3-month-old one simply because it's had more time to accumulate revenue. The 3-month and 6-month columns put every cohort on equal footing. If your January cohort's 6-month LTV is $400 and your April cohort's is $280, something changed — pricing, channel mix, onboarding, product — and you can go investigate.

Predictive LTV: useful, but only if churn is stable

The classic subscription formula: ARPU (average revenue per user per month) divided by monthly churn rate. If a customer pays $80/month on average and your monthly churn rate is 5%, the predicted LTV is $80 / 0.05 = $1,600.

WITH monthly_churn AS (
  SELECT
    count(*) FILTER (WHERE canceled_at IS NOT NULL)::numeric
      / NULLIF(count(*), 0) AS churn_rate,
    avg(plan_amount)        AS avg_plan_amount
  FROM subscriptions
  WHERE created >= now() - interval '6 months'
),
transactional_ltv AS (
  SELECT
    avg(customer_aov)       AS avg_order_value,
    avg(order_count)        AS avg_frequency,
    avg(lifespan_months)    AS avg_lifespan_months
  FROM (
    SELECT
      c.id,
      avg(o.total)                  AS customer_aov,
      count(o.id)::numeric          AS order_count,
      extract(month FROM age(
        max(o.created_at), min(o.created_at)
      )) + 1                        AS lifespan_months
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
    HAVING count(o.id) >= 2
  ) per_customer
)
SELECT
  -- Subscription LTV (ARPU / churn)
  round(mc.avg_plan_amount / NULLIF(mc.churn_rate, 0), 2)
    AS predicted_ltv_subscription,
  mc.churn_rate,
  mc.avg_plan_amount AS arpu,
 
  -- Transactional LTV (AOV × frequency × lifespan)
  round(t.avg_order_value * t.avg_frequency, 2)
    AS predicted_ltv_transactional,
  round(t.avg_order_value, 2)     AS avg_order_value,
  round(t.avg_frequency, 1)       AS avg_purchase_frequency,
  round(t.avg_lifespan_months, 1) AS avg_lifespan_months
FROM monthly_churn mc, transactional_ltv t;

Pick the formula that matches your business. Don't average them together.

Here's the honest caveat: predictive LTV assumes your churn rate is relatively stable. If you're a six-month-old startup and your churn rate swung from 12% to 4% to 8% across your first three months, dividing ARPU by any of those numbers gives you a fiction. For early-stage companies, historical LTV is more trustworthy than predictive LTV — you're reporting what actually happened rather than extrapolating from a rate that hasn't settled yet. Revisit the predictive formula once you have 12+ months of data and churn has found a range.

LTV by acquisition channel

This is where LTV becomes an allocation tool instead of just a reporting number. Join customers with their revenue and group by acquisition_channel to see which channels produce the highest-value customers, not just the most customers.

WITH customer_ltv AS (
  SELECT
    c.id,
    c.acquisition_channel,
    coalesce(sum(o.total), 0) AS ltv_to_date
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  WHERE c.created_at >= '2025-01-01'
  GROUP BY c.id, c.acquisition_channel
)
SELECT
  acquisition_channel,
  count(*)                          AS customers,
  round(avg(ltv_to_date), 2)       AS avg_ltv,
  round(percentile_cont(0.5) WITHIN GROUP (ORDER BY ltv_to_date), 2)
                                    AS median_ltv,
  round(sum(ltv_to_date), 2)       AS total_revenue,
  round(100.0 * count(*) FILTER (WHERE ltv_to_date = 0)
    / count(*), 1)                  AS pct_zero_revenue
FROM customer_ltv
GROUP BY acquisition_channel
ORDER BY avg_ltv DESC;

The median_ltv column matters more than avg_ltv in practice. Averages get distorted by a handful of big spenders — if your Google Ads channel brought in 500 customers and one of them spent $50k, the average looks great but the median tells the real story. The pct_zero_revenue column catches a different problem: channels that drive signups but not purchases. A channel with 40% zero-revenue customers has a top-of-funnel problem regardless of what the average LTV says.

You can slice this same query by plan tier, geography, or first product purchased — just replace acquisition_channel with the dimension you care about. Segment by (SELECT plan_amount FROM subscriptions WHERE customer_id = c.id ORDER BY created LIMIT 1) for first-plan tier, or add a country column to your customers table for geographic LTV.

LTV:CAC ratio by channel

The number that actually determines whether a channel is profitable. You need a table (or CTE) with acquisition cost per channel. If you're pulling ad spend from Google Ads or Meta, you'll typically aggregate spend by campaign and map campaigns to channels.

WITH channel_cac AS (
  -- Replace with your actual ad spend data
  -- Pull from your ad platform or a summary table
  VALUES
    ('google_ads',    45000.00, 320),
    ('meta_ads',      28000.00, 185),
    ('organic',           0.00, 410),
    ('referral',       5000.00, 95),
    ('linkedin_ads',  12000.00, 60)
) AS spend(channel, total_spend, customers_acquired),
channel_ltv AS (
  SELECT
    c.acquisition_channel,
    avg(coalesce(order_totals.ltv, 0)) AS avg_ltv
  FROM customers c
  LEFT JOIN (
    SELECT customer_id, sum(total) AS ltv
    FROM orders
    GROUP BY customer_id
  ) order_totals ON order_totals.customer_id = c.id
  WHERE c.created_at >= '2025-01-01'
  GROUP BY c.acquisition_channel
)
SELECT
  s.channel,
  s.customers_acquired,
  round(s.total_spend / NULLIF(s.customers_acquired, 0), 2)
    AS cac,
  round(cl.avg_ltv, 2) AS avg_ltv,
  round(cl.avg_ltv / NULLIF(s.total_spend / NULLIF(s.customers_acquired, 0), 0), 1)
    AS ltv_cac_ratio
FROM channel_cac s
LEFT JOIN channel_ltv cl ON cl.acquisition_channel = s.channel
ORDER BY ltv_cac_ratio DESC;

The conventional benchmark is LTV:CAC > 3:1 for a healthy business. Below 1:1, you're losing money on every customer from that channel. Between 1:1 and 3:1, the economics might work if your payback period is short enough, but it's marginal. Above 5:1, you're probably under-spending on that channel and leaving growth on the table.

Organic will always look infinite (CAC near zero), which is correct but not actionable. The more interesting comparison is between your paid channels — if Google Ads is 4.2:1 and LinkedIn is 1.8:1 with similar LTV, the difference is purely acquisition efficiency.

From calculation to decisions

These five queries give you a solid foundation, but the real value comes from running them regularly and watching how the numbers move. Historical LTV trending down across recent cohorts? Something changed in your product or your channel mix. LTV:CAC ratio dropping on a channel you're scaling? Time to tighten targeting before you burn through budget.

If you're pulling revenue from Stripe and ad spend from Google Ads or Meta, you can compute the full LTV:CAC picture across all your channels without manually exporting CSVs. This connects directly to MRR calculation — your MRR broken down by cohort is essentially the subscription version of the cohort LTV query above, just viewed monthly instead of cumulatively.

Fastero connects to your billing system and ad platforms, so you can write these queries against live data, schedule them as recurring reports, and set up alerts when LTV:CAC drops below your threshold — without stitching together exports from three different dashboards.

Try Fastero free — connect your billing and ad platforms, compute LTV:CAC across every channel from live data, and get alerts when the ratio moves. No credit card required.