Client Segmentation and Lifetime Value

Not all clients are equal. Some are high-value, sticky, long-term partners. Others are occasional buyers with low spend. Effective sales and account management requires understanding which bucket each client falls into so you can direct attention and resources appropriately.

Client Lifetime Value (LTV or CLV) is the total revenue (or profit) a business can expect from a client over the entire relationship. It's the most important metric for understanding client worth — and SQL is the right tool to compute it.

Why Segment Clients?

Segmentation serves multiple purposes:

• Resource allocation: High-value clients deserve dedicated account managers; low-value clients may be better served through self-service
• Retention focus: Losing a £100K/year client has 100x the impact of losing a £1K/year client
• Upsell targeting: Identify clients who could buy more based on their industry peers' behaviour
• At-risk detection: Which high-value clients haven't ordered recently?

Lifetime Value Calculation

LTV is straightforward when you have complete billing history: the sum of all invoices for a client. The nuance is in whether you use billed amounts (optimistic) or collected amounts (realistic).

WITH client_ltv AS (
    SELECT
        i.client_id,
        MIN(i.issue_date) AS first_invoice,
        MAX(i.issue_date) AS last_invoice,
        COUNT(DISTINCT i.invoice_id) AS total_invoices,
        ROUND(SUM((SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id)), 2) AS total_billed,
        ROUND(SUM(CASE WHEN i.status = 'paid' THEN (SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id) ELSE 0 END), 2) AS total_collected,
        ROUND(SUM(CASE WHEN i.status IN ('overdue', 'sent') THEN (SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id) ELSE 0 END), 2) AS outstanding,
        -- Client tenure in months
        EXTRACT(YEAR FROM AGE(MAX(i.issue_date), MIN(i.issue_date))) * 12
            + EXTRACT(MONTH FROM AGE(MAX(i.issue_date), MIN(i.issue_date))) + 1 AS tenure_months
    FROM invoices i
    WHERE i.status != 'cancelled'
    GROUP BY i.client_id
),
with_rates AS (
    SELECT *,
        ROUND(total_collected / NULLIF(tenure_months, 0), 2) AS monthly_revenue_rate,
        ROUND(total_collected / NULLIF(total_billed, 0) * 100, 1) AS collection_rate_pct
    FROM client_ltv
)
SELECT
    c.company_name, c.industry, c.account_manager,
    w.first_invoice, w.last_invoice,
    w.tenure_months, w.total_invoices,
    w.total_billed, w.total_collected,
    w.outstanding,
    w.monthly_revenue_rate,
    w.collection_rate_pct,
    RANK() OVER (ORDER BY w.total_collected DESC) AS ltv_rank
FROM with_rates w
JOIN clients c ON c.client_id = w.client_id
ORDER BY w.total_collected DESC;

What This Returns

monthly_revenue_rate is LTV normalised for tenure — a client who's been with you 5 years with £500K LTV has a rate of £8.3K/month. A newer client with £200K in 1 year has a rate of £16.7K/month — they're actually more valuable on a run-rate basis. collection_rate_pct < 90% flags clients who dispute or don't pay reliably.