新規・既存・復活・休眠顧客をSQLで自動判定する方法

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

-- STEP1: 今月購買顧客の抽出

WITH this_month_buyers AS (
    SELECT DISTINCT
        customer_id
    FROM orders
    WHERE
        status = 'completed'
        AND order_date >= DATE '2024-12-01'
        AND order_date <  DATE '2025-01-01'  -- 翌月1日未満にすることで月末を正確に含む
)
SELECT *
FROM this_month_buyers;

-- STEP2: 今月以前の購買履歴を集計

WITH past_purchase_history AS (
    SELECT
        customer_id,
        MAX(order_date)  AS last_purchase_date  -- 今月より前の最終購買日
    FROM orders
    WHERE
        status = 'completed'
        AND order_date < DATE '2024-12-01'  -- 今月より前のみ
    GROUP BY customer_id
)
SELECT *
FROM past_purchase_history;

-- STEP3: 今月購買顧客のステータス判定

WITH this_month_buyers AS (
    SELECT DISTINCT
        customer_id
    FROM orders
    WHERE
        status = 'completed'
        AND order_date >= DATE '2024-12-01'
        AND order_date <  DATE '2025-01-01'
),
past_purchase_history AS (
    SELECT
        customer_id,
        MAX(order_date)  AS last_purchase_date
    FROM orders
    WHERE
        status = 'completed'
        AND order_date < DATE '2024-12-01'
    GROUP BY customer_id
),
buyer_status AS (
    SELECT
        t.customer_id,
        p.last_purchase_date,

        -- ステータス判定
        CASE
            -- 過去購買なし → 新規
            WHEN p.last_purchase_date IS NULL
                THEN '新規'
            -- 過去購買あり、かつ最終購買が90日以上前 → 復活
            WHEN DATE_DIFF('day', p.last_purchase_date, DATE '2024-12-01') >= 90
                THEN '復活'
            -- 過去購買あり、最終購買が90日以内 → 既存
            ELSE
                '既存'
        END  AS customer_status
    FROM this_month_buyers  t
    LEFT JOIN past_purchase_history  p  ON t.customer_id = p.customer_id
)
SELECT *
FROM buyer_status
ORDER BY customer_status;

-- STEP4: 休眠顧客の抽出（完成版・全ステータス統合）

WITH this_month_buyers AS (
    SELECT DISTINCT
        customer_id
    FROM orders
    WHERE
        status = 'completed'
        AND order_date >= DATE '2024-12-01'
        AND order_date <  DATE '2025-01-01'
),
past_purchase_history AS (
    SELECT
        customer_id,
        MAX(order_date)  AS last_purchase_date
    FROM orders
    WHERE
        status = 'completed'
        AND order_date < DATE '2024-12-01'
    GROUP BY customer_id
),

-- 今月購買者のステータス
buyer_status AS (
    SELECT
        t.customer_id,
        p.last_purchase_date,
        CASE
            WHEN p.last_purchase_date IS NULL                                              THEN '新規'
            WHEN DATE_DIFF('day', p.last_purchase_date, DATE '2024-12-01') >= 90          THEN '復活'
            ELSE                                                                                '既存'
        END  AS customer_status
    FROM this_month_buyers  t
    LEFT JOIN past_purchase_history  p  ON t.customer_id = p.customer_id
),

-- 今月購買していない顧客の中で、直近90日以内に購買歴がある顧客＝休眠
dormant_customers AS (
    SELECT
        p.customer_id,
        p.last_purchase_date,
        '休眠'  AS customer_status
    FROM past_purchase_history  p
    -- 今月購買者でないこと
    WHERE p.customer_id NOT IN (SELECT customer_id FROM this_month_buyers)
    -- 直近90日以内に購買歴があること（古すぎる顧客は対象外）
    AND DATE_DIFF('day', p.last_purchase_date, DATE '2024-12-01') < 90
)

-- 全ステータスをUNION ALLで統合
SELECT customer_id, last_purchase_date, customer_status FROM buyer_status
UNION ALL
SELECT customer_id, last_purchase_date, customer_status FROM dormant_customers
ORDER BY customer_status, last_purchase_date DESC;

-- STEP5: 月次ステータス別サマリ

WITH this_month_buyers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE status = 'completed'
    AND order_date >= DATE '2024-12-01'
    AND order_date <  DATE '2025-01-01'
),
this_month_sales AS (
    SELECT
        customer_id,
        SUM(total_amount)  AS monthly_amount
    FROM orders
    WHERE status = 'completed'
    AND order_date >= DATE '2024-12-01'
    AND order_date <  DATE '2025-01-01'
    GROUP BY customer_id
),
past_purchase_history AS (
    SELECT
        customer_id,
        MAX(order_date)  AS last_purchase_date
    FROM orders
    WHERE status = 'completed'
    AND order_date < DATE '2024-12-01'
    GROUP BY customer_id
),
buyer_status AS (
    SELECT
        t.customer_id,
        p.last_purchase_date,
        CASE
            WHEN p.last_purchase_date IS NULL                                          THEN '新規'
            WHEN DATE_DIFF('day', p.last_purchase_date, DATE '2024-12-01') >= 90      THEN '復活'
            ELSE                                                                            '既存'
        END  AS customer_status
    FROM this_month_buyers  t
    LEFT JOIN past_purchase_history  p  ON t.customer_id = p.customer_id
),
dormant_customers AS (
    SELECT
        p.customer_id,
        p.last_purchase_date,
        '休眠'  AS customer_status
    FROM past_purchase_history  p
    WHERE p.customer_id NOT IN (SELECT customer_id FROM this_month_buyers)
    AND DATE_DIFF('day', p.last_purchase_date, DATE '2024-12-01') < 90
),
all_status AS (
    SELECT customer_id, customer_status FROM buyer_status
    UNION ALL
    SELECT customer_id, customer_status FROM dormant_customers
)
SELECT
    a.customer_status,
    COUNT(a.customer_id)                AS customer_count,
    COALESCE(SUM(s.monthly_amount), 0)  AS total_sales,
    COALESCE(
        ROUND(AVG(s.monthly_amount), 0)
    , 0)                                AS avg_sales_per_customer
FROM all_status           a
LEFT JOIN this_month_sales  s  ON a.customer_id = s.customer_id
GROUP BY a.customer_status
ORDER BY
    CASE a.customer_status
        WHEN '新規' THEN 1
        WHEN '既存' THEN 2
        WHEN '復活' THEN 3
        WHEN '休眠' THEN 4
    END;

# digdag の例
_export:
  target_month: "2024-12-01"
  next_month: "2025-01-01"

CASE
    WHEN DATE_DIFF('day', last_purchase_date, DATE '2024-12-01') < 90   THEN '休眠（浅）'
    WHEN DATE_DIFF('day', last_purchase_date, DATE '2024-12-01') < 365  THEN '休眠（中）'
    ELSE                                                                      '休眠（深）'
END