RFM分析をSQLだけで実装する方法｜顧客ランクを自動分類する実務クエリ

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

-- STEP1: 顧客別R・F・M実値の計算
-- 分析基準日：2024-12-31 とする（実務では CURRENT_DATE に置き換える）

WITH rfm_raw AS (
    SELECT
        customer_id,

        -- R: 最終購買日から基準日までの経過日数（小さいほど最近）
        DATE_DIFF('day',
            MAX(order_date),
            DATE '2024-12-31'
        )                                   AS recency_days,

        -- F: 購買回数
        COUNT(order_id)                     AS frequency,

        -- M: 購買総額
        SUM(total_amount)                   AS monetary
    FROM orders
    WHERE
        status = 'completed'
        AND order_date >= DATE '2024-01-01'  -- 集計対象期間（直近1年）
        AND order_date <= DATE '2024-12-31'
    GROUP BY customer_id
)
SELECT *
FROM rfm_raw
ORDER BY monetary DESC;

-- STEP2: R・F・MスコアをNTILEで付与

WITH rfm_raw AS (
    SELECT
        customer_id,
        DATE_DIFF('day', MAX(order_date), DATE '2024-12-31')  AS recency_days,
        COUNT(order_id)                                        AS frequency,
        SUM(total_amount)                                      AS monetary
    FROM orders
    WHERE
        status = 'completed'
        AND order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
    GROUP BY customer_id
),
rfm_scored AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,

        -- Rスコア：経過日数が少ない（最近買った）ほど高スコア
        NTILE(5) OVER (ORDER BY recency_days DESC)   AS r_score,

        -- Fスコア：購買回数が多いほど高スコア
        NTILE(5) OVER (ORDER BY frequency ASC)       AS f_score,

        -- Mスコア：購買金額が高いほど高スコア
        NTILE(5) OVER (ORDER BY monetary ASC)        AS m_score
    FROM rfm_raw
)
SELECT *
FROM rfm_scored
ORDER BY r_score DESC, f_score DESC, m_score DESC;

-- STEP3: セグメント名の付与（完成版）

WITH rfm_raw AS (
    SELECT
        customer_id,
        DATE_DIFF('day', MAX(order_date), DATE '2024-12-31')  AS recency_days,
        COUNT(order_id)                                        AS frequency,
        SUM(total_amount)                                      AS monetary
    FROM orders
    WHERE
        status = 'completed'
        AND order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
    GROUP BY customer_id
),
rfm_scored AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        NTILE(5) OVER (ORDER BY recency_days DESC)  AS r_score,
        NTILE(5) OVER (ORDER BY frequency ASC)      AS f_score,
        NTILE(5) OVER (ORDER BY monetary ASC)       AS m_score
    FROM rfm_raw
),
rfm_segmented AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        r_score,
        f_score,
        m_score,
        CASE
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4
                THEN 'ロイヤル顧客'
            WHEN r_score >= 4 AND f_score >= 3
                THEN '優良顧客'
            WHEN r_score >= 3 AND f_score <= 2
                THEN '新規・見込み顧客'
            WHEN r_score <= 2 AND f_score >= 4
                THEN '離脱危機優良顧客'
            WHEN r_score <= 2 AND f_score >= 2
                THEN '休眠顧客'
            ELSE
                'その他'
        END  AS segment
    FROM rfm_scored
)
SELECT *
FROM rfm_segmented
ORDER BY r_score DESC, f_score DESC, m_score DESC;

-- STEP4: セグメント別サマリ

WITH rfm_raw AS (
    SELECT
        customer_id,
        DATE_DIFF('day', MAX(order_date), DATE '2024-12-31')  AS recency_days,
        COUNT(order_id)                                        AS frequency,
        SUM(total_amount)                                      AS monetary
    FROM orders
    WHERE
        status = 'completed'
        AND order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
    GROUP BY customer_id
),
rfm_scored AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary,
        NTILE(5) OVER (ORDER BY recency_days DESC)  AS r_score,
        NTILE(5) OVER (ORDER BY frequency ASC)      AS f_score,
        NTILE(5) OVER (ORDER BY monetary ASC)       AS m_score
    FROM rfm_raw
),
rfm_segmented AS (
    SELECT
        customer_id,
        monetary,
        CASE
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'ロイヤル顧客'
            WHEN r_score >= 4 AND f_score >= 3                  THEN '優良顧客'
            WHEN r_score >= 3 AND f_score <= 2                  THEN '新規・見込み顧客'
            WHEN r_score <= 2 AND f_score >= 4                  THEN '離脱危機優良顧客'
            WHEN r_score <= 2 AND f_score >= 2                  THEN '休眠顧客'
            ELSE                                                      'その他'
        END  AS segment
    FROM rfm_scored
)
SELECT
    segment,
    COUNT(customer_id)          AS customer_count,
    ROUND(
        COUNT(customer_id) * 100.0 / SUM(COUNT(customer_id)) OVER ()
    , 1)                        AS customer_ratio,
    SUM(monetary)               AS total_monetary,
    ROUND(AVG(monetary), 0)     AS avg_monetary
FROM rfm_segmented
GROUP BY segment
ORDER BY total_monetary DESC;