How to Calculate Customer Lifetime Value (LTV) Using SQL and Python — Full Worked Example

WITH customer_orders AS (
    SELECT
        o.customer_id,
        c.customer_name,
        c.segment,
        c.acquisition_channel,
        SUM(o.order_value)                    AS lifetime_revenue,
        SUM(o.order_value * o.gross_margin)   AS lifetime_gross_profit,
        COUNT(o.order_id)                     AS total_orders,
        AVG(o.order_value)                    AS avg_order_value,
        MIN(o.order_date)                     AS first_order_date,
        MAX(o.order_date)                     AS last_order_date,
        MAX(o.order_date) - MIN(o.order_date) AS customer_lifespan_days,
        NOW()::date - MAX(o.order_date)      AS days_since_last_order
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY
        o.customer_id, c.customer_name,
        c.segment, c.acquisition_channel
)
SELECT
    *,
    -- Annualised LTV (gross profit per year)
    CASE
        WHEN customer_lifespan_days > 0
        THEN ROUND(lifetime_gross_profit / (customer_lifespan_days / 365.0), 2)
        ELSE lifetime_gross_profit
    END                                      AS annual_ltv,

    -- LTV quartile ranking
    NTILE(4) OVER (
        ORDER BY lifetime_gross_profit DESC
    )                                        AS ltv_quartile

FROM customer_orders
ORDER BY lifetime_gross_profit DESC;

-- Top results example:
-- customer  | segment    | ltv_revenue | ltv_profit | orders | quartile
-- Acme Ltd  | Enterprise |   £284,420  |   £96,703  |   47   |    1
-- Beta Co   | Mid-Market |   £142,810  |   £42,843  |   31   |    1
-- Gamma Inc | Enterprise |    £98,340  |   £29,502  |   22   |    2

WITH rfm_raw AS (
    SELECT
        customer_id,
        NOW()::date - MAX(order_date)  AS recency_days,
        COUNT(order_id)               AS frequency,
        SUM(order_value)              AS monetary
    FROM orders
    GROUP BY customer_id
),
rfm_scored AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        -- Recency: lower days = better = higher score
        NTILE(4) OVER (ORDER BY recency_days ASC)  AS r_score,
        -- Frequency: higher = better = higher score
        NTILE(4) OVER (ORDER BY frequency DESC)    AS f_score,
        -- Monetary: higher = better = higher score
        NTILE(4) OVER (ORDER BY monetary DESC)     AS m_score
    FROM rfm_raw
)
SELECT
    customer_id,
    recency_days,
    frequency,
    monetary,
    r_score, f_score, m_score,
    (r_score + f_score + m_score)           AS rfm_total,
    CASE
        WHEN r_score = 4 AND f_score = 4  THEN 'Champions'
        WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
        WHEN r_score = 4 AND f_score <= 2  THEN 'New Customers'
        WHEN r_score <= 2 AND f_score >= 3  THEN 'At Risk'
        WHEN r_score = 1 AND f_score >= 2  THEN 'Cannot Lose Them'
        WHEN r_score = 1 AND f_score = 1  THEN 'Lost'
        ELSE 'Potential'
    END                                     AS rfm_segment
FROM rfm_scored
ORDER BY rfm_total DESC;

import pandas as pd
import matplotlib.pyplot as plt
import sqlalchemy

# Connect and load
engine = sqlalchemy.create_engine(DATABASE_URL)
df = pd.read_sql("SELECT * FROM rfm_results", engine)

# Segment summary
summary = df.groupby('rfm_segment').agg(
    customer_count  = ('customer_id',  'count'),
    avg_ltv         = ('monetary',     'mean'),
    total_revenue   = ('monetary',     'sum'),
    avg_recency     = ('recency_days', 'mean'),
    avg_frequency   = ('frequency',    'mean')
).round(2).sort_values('total_revenue', ascending=False)

print(summary)

# Output:
# segment          | count | avg_ltv  | total_rev   | avg_recency
# Champions        |   142 | £18,420  | £2,615,640  |   12 days
# Loyal Customers  |   384 |  £6,280  | £2,411,520  |   34 days
# At Risk          |   218 |  £4,940  | £1,076,920  |  142 days
# Cannot Lose Them |    89 |  £8,120  |   £722,680  |  198 days
# New Customers    |   312 |    £840  |   £262,080  |    8 days
# Lost             |   441 |    £320  |   £141,120  |  380 days

# At-risk customers to action immediately
at_risk = df[df['rfm_segment'] == 'At Risk'].sort_values('monetary', ascending=False)
print(f"\n{len(at_risk)} at-risk customers — £{at_risk['monetary'].sum():,.0f} at stake")
# 218 at-risk customers — £1,076,920 at stake

# Export list for sales team
at_risk[['customer_id', 'monetary', 'recency_days', 'frequency']].to_csv(
    'at_risk_customers.csv', index=False
)