Customer Segmentation

1-- Step 1: Tính Recency, Frequency, Monetary
2WITH rfm_base AS (
3    SELECT
4        customer_id,
5        DATEDIFF(DAY, MAX(order_date), '2024-12-31') AS recency_days,
6        COUNT(DISTINCT order_id) AS frequency,
7        SUM(total_amount) AS monetary
8    FROM fact_orders
9    WHERE status = 'Delivered'
10        AND YEAR(order_date) IN (2023, 2024)
11    GROUP BY customer_id
12),
13
14-- Step 2: Scoring bằng NTILE (chia 4 nhóm)
15rfm_scores AS (
16    SELECT
17        customer_id,
18        recency_days,
19        frequency,
20        monetary,
21        -- Recency: ngày càng nhỏ càng tốt → score cao
22        NTILE(4) OVER (ORDER BY recency_days DESC) AS r_score,
23        -- Frequency: càng nhiều càng tốt → score cao
24        NTILE(4) OVER (ORDER BY frequency ASC) AS f_score,
25        -- Monetary: càng lớn càng tốt → score cao
26        NTILE(4) OVER (ORDER BY monetary ASC) AS m_score
27    FROM rfm_base
28)
29
30-- Step 3: Tạo RFM segment
31SELECT
32    customer_id,
33    recency_days,
34    frequency,
35    monetary,
36    r_score,
37    f_score,
38    m_score,
39    CONCAT(r_score, f_score, m_score) AS rfm_score,
40    CASE
41        WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Champions'
42        WHEN r_score >= 3 AND f_score >= 2 AND m_score >= 2 THEN 'Loyal Customers'
43        WHEN r_score >= 3 AND f_score <= 2 THEN 'New Customers'
44        WHEN r_score = 2 AND f_score >= 2 THEN 'At Risk'
45        WHEN r_score = 2 AND f_score = 1 THEN 'About to Sleep'
46        WHEN r_score = 1 AND f_score >= 3 THEN 'Cant Lose Them'
47        WHEN r_score = 1 AND f_score >= 2 THEN 'Hibernating'
48        WHEN r_score = 1 AND f_score = 1 THEN 'Lost'
49        ELSE 'Others'
50    END AS segment
51FROM rfm_scores;

1-- Tổng hợp khách hàng theo segment
2WITH rfm_full AS (
3    -- (... CTE bên trên ...)
4    SELECT *, CASE ... END AS segment FROM rfm_scores
5)
6SELECT
7    segment,
8    COUNT(*) AS customers,
9    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct_customers,
10    ROUND(AVG(recency_days), 0) AS avg_recency,
11    ROUND(AVG(frequency), 1) AS avg_frequency,
12    ROUND(AVG(monetary), 0) AS avg_monetary,
13    SUM(monetary) AS total_revenue,
14    ROUND(SUM(monetary) * 100.0 / SUM(SUM(monetary)) OVER(), 1) AS pct_revenue
15FROM rfm_full
16GROUP BY segment
17ORDER BY avg_monetary DESC;

1-- CLV = Avg Order Value × Purchase Frequency × Customer Lifespan
2WITH customer_metrics AS (
3    SELECT
4        customer_id,
5        COUNT(DISTINCT order_id) AS total_orders,
6        SUM(total_amount) AS total_spent,
7        AVG(total_amount) AS avg_order_value,
8        DATEDIFF(MONTH, MIN(order_date), MAX(order_date)) + 1 AS active_months,
9        DATEDIFF(DAY, MAX(order_date), '2024-12-31') AS days_since_last
10    FROM fact_orders
11    WHERE status = 'Delivered'
12    GROUP BY customer_id
13)
14SELECT
15    -- Thống kê tổng thể
16    COUNT(*) AS total_customers,
17    ROUND(AVG(avg_order_value), 0) AS avg_aov,
18    ROUND(AVG(total_orders), 1) AS avg_frequency,
19    ROUND(AVG(active_months), 1) AS avg_lifespan_months,
20    ROUND(AVG(total_spent), 0) AS avg_clv,
21
22    -- Phân bố CLV
23    ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_spent), 0) AS clv_p25,
24    ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_spent), 0) AS clv_median,
25    ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_spent), 0) AS clv_p75,
26    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_spent), 0) AS clv_p95
27FROM customer_metrics;

1-- CLV trung bình theo RFM segment
2-- (Giả sử đã có bảng rfm_segments từ bước trước)
3SELECT
4    segment,
5    COUNT(*) AS customers,
6    ROUND(AVG(monetary), 0) AS avg_clv,
7    ROUND(AVG(frequency), 1) AS avg_orders,
8    ROUND(AVG(monetary / NULLIF(frequency, 0)), 0) AS avg_order_value
9FROM rfm_segments
10GROUP BY segment
11ORDER BY avg_clv DESC;