LTVをSQLで計算する方法｜顧客生涯価値を正確に出す実務クエリ

-- orders テーブル
-- order_id      : 注文ID
-- customer_id   : 顧客ID
-- order_date    : 注文日（DATE型）
-- total_amount  : 注文金額（税抜）
-- status        : 'completed' / 'cancelled' など

-- customers テーブル
-- customer_id   : 顧客ID（主キー）
-- registered_at : 会員登録日（DATE型）
-- channel       : 初回流入チャネル（'organic' / 'paid' / 'referral' など）

-- STEP1: 顧客別の累計購買実績
WITH customer_orders AS (
    SELECT
        customer_id,
        COUNT(order_id)             AS total_orders,
        SUM(total_amount)           AS total_spent,
        ROUND(AVG(total_amount), 0) AS avg_order_value,
        MIN(order_date)             AS first_order_date,
        MAX(order_date)             AS last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    co.customer_id,
    c.registered_at,
    c.channel,
    co.total_orders,
    co.total_spent,
    co.avg_order_value,
    co.first_order_date,
    co.last_order_date,

    -- 初回購買から最終購買までの活動期間（日数）
    DATE_DIFF('day', co.first_order_date, co.last_order_date)  AS active_days
FROM customer_orders  co
JOIN customers        c  ON co.customer_id = c.customer_id
ORDER BY co.total_spent DESC;

-- STEP2: 年間購買頻度と在籍期間を追加

WITH customer_orders AS (
    SELECT
        customer_id,
        COUNT(order_id)             AS total_orders,
        SUM(total_amount)           AS total_spent,
        ROUND(AVG(total_amount), 0) AS avg_order_value,
        MIN(order_date)             AS first_order_date,
        MAX(order_date)             AS last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
customer_metrics AS (
    SELECT
        co.customer_id,
        c.registered_at,
        c.channel,
        co.total_orders,
        co.total_spent,
        co.avg_order_value,
        co.first_order_date,
        co.last_order_date,

        -- 在籍日数（登録日〜基準日）
        DATE_DIFF('day', c.registered_at, DATE '2024-12-31')  AS tenure_days,

        -- 年間購買回数 = 総購買回数 ÷ 在籍年数
        -- 在籍日数が0にならないよう NULLIF で保護する
        ROUND(
            co.total_orders * 365.0
            / NULLIF(DATE_DIFF('day', c.registered_at, DATE '2024-12-31'), 0)
        , 2)  AS annual_purchase_frequency
    FROM customer_orders  co
    JOIN customers        c  ON co.customer_id = c.customer_id
)
SELECT *
FROM customer_metrics
ORDER BY total_spent DESC;

-- STEP3: 予測LTV算出（完成版）

WITH customer_orders AS (
    SELECT
        customer_id,
        COUNT(order_id)             AS total_orders,
        SUM(total_amount)           AS total_spent,
        ROUND(AVG(total_amount), 0) AS avg_order_value,
        MIN(order_date)             AS first_order_date,
        MAX(order_date)             AS last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
customer_metrics AS (
    SELECT
        co.customer_id,
        c.registered_at,
        c.channel,
        co.total_orders,
        co.total_spent,
        co.avg_order_value,
        co.first_order_date,
        co.last_order_date,
        DATE_DIFF('day', c.registered_at, DATE '2024-12-31')  AS tenure_days,
        ROUND(
            co.total_orders * 365.0
            / NULLIF(DATE_DIFF('day', c.registered_at, DATE '2024-12-31'), 0)
        , 2)  AS annual_purchase_frequency
    FROM customer_orders  co
    JOIN customers        c  ON co.customer_id = c.customer_id
),
ltv_calculated AS (
    SELECT
        customer_id,
        registered_at,
        channel,
        total_orders,
        total_spent,
        avg_order_value,
        annual_purchase_frequency,
        tenure_days,

        -- 予測LTV = 平均注文金額 × 年間購買頻度 × 残存期間（3年）
        ROUND(
            avg_order_value * annual_purchase_frequency * 3
        , 0)  AS predicted_ltv_3yr,

        -- LTVランク（予測LTV上位20%をHighと定義）
        CASE
            WHEN NTILE(5) OVER (ORDER BY avg_order_value * annual_purchase_frequency ASC) = 5
                THEN 'High'
            WHEN NTILE(5) OVER (ORDER BY avg_order_value * annual_purchase_frequency ASC) >= 3
                THEN 'Mid'
            ELSE
                'Low'
        END  AS ltv_rank
    FROM customer_metrics
    WHERE annual_purchase_frequency IS NOT NULL
)
SELECT *
FROM ltv_calculated
ORDER BY predicted_ltv_3yr DESC;

-- STEP4: チャネル別LTVサマリ

WITH customer_orders AS (
    SELECT
        customer_id,
        COUNT(order_id)             AS total_orders,
        SUM(total_amount)           AS total_spent,
        ROUND(AVG(total_amount), 0) AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
customer_metrics AS (
    SELECT
        co.customer_id,
        c.channel,
        co.avg_order_value,
        ROUND(
            co.total_orders * 365.0
            / NULLIF(DATE_DIFF('day', c.registered_at, DATE '2024-12-31'), 0)
        , 2)  AS annual_purchase_frequency
    FROM customer_orders  co
    JOIN customers        c  ON co.customer_id = c.customer_id
),
ltv_calculated AS (
    SELECT
        customer_id,
        channel,
        ROUND(avg_order_value * annual_purchase_frequency * 3, 0)  AS predicted_ltv_3yr
    FROM customer_metrics
    WHERE annual_purchase_frequency IS NOT NULL
)
SELECT
    channel,
    COUNT(customer_id)                  AS customer_count,
    ROUND(AVG(predicted_ltv_3yr), 0)    AS avg_ltv,
    ROUND(MEDIAN(predicted_ltv_3yr), 0) AS median_ltv,
    MAX(predicted_ltv_3yr)              AS max_ltv,
    SUM(predicted_ltv_3yr)              AS total_ltv
FROM ltv_calculated
GROUP BY channel
ORDER BY avg_ltv DESC;