Bài tập: chấm điểm RFM

Bạn sẽ học được

• Dựng ba chỉ số RFM từ dữ liệu bán hàng: recency, frequency, monetary.
• Chấm điểm ngũ phân vị bằng NTILE(5) với chiều sắp xếp đúng cho từng chỉ số.
• Vì sao phải dùng COUNT(DISTINCT o.id) khi đã nối order_items.
• Lọc đơn huỷ ở đúng vị trí và hệ quả của việc lọc sai chỗ.
• Đọc kết quả RFM để phân loại khách hàng.

Khái niệm cốt lõi

Đề bài RFM — Chấm điểm ngũ phân vị:

Tính R/F/M rồi chia mỗi chỉ số thành 5 nhóm bằng NTILE(5) (r_score, f_score, m_score; recency nhỏ là tốt nên xếp giảm dần, frequency và monetary xếp tăng dần); trả về name và ba điểm, sắp theo tên.

RFM là mô hình ba chiều quen thuộc trong phân tích khách hàng:

Ba chỉ số đo ba thứ khác nhau, nên chiều sắp xếp khi chấm điểm cũng khác nhau. Đây là điểm dễ sai nhất của bài:

• Recency nhỏ là tốt → sắp giảm dần để người mua lâu nhất nhận điểm 1.
• Frequency và Monetary lớn là tốt → sắp tăng dần để người mua ít/chi ít nhận điểm 1.

Kết quả là điểm 5 luôn là nhóm tốt nhất ở cả ba cột. Nhờ vậy mới cộng hoặc so sánh ba điểm với nhau được.

Đáp án:

WITH base AS (
  SELECT c.id, c.name,
         DATE '2026-01-01' - MAX(o.order_date) AS recency_days,
         COUNT(DISTINCT o.id) AS frequency,
         SUM(oi.quantity * oi.unit_price) AS monetary
  FROM customers c
  JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY c.id, c.name
), scored AS (
  SELECT *,
         NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
         NTILE(5) OVER (ORDER BY frequency) AS f_score,
         NTILE(5) OVER (ORDER BY monetary) AS m_score
  FROM base
)
SELECT name, r_score, f_score, m_score
FROM scored
ORDER BY name;

Kết quả 10 dòng:

Ba quyết định quan trọng

Một, COUNT(DISTINCT o.id) chứ không phải COUNT(*). Sau khi nối orders với order_items, mỗi đơn xuất hiện thành nhiều dòng — một dòng cho mỗi mặt hàng. Đếm COUNT(*) sẽ ra số dòng hàng, không phải số đơn. Bình Trần có 3 đơn nhưng 6 dòng hàng; nếu đếm sai, frequency của anh là 6 và toàn bộ điểm F lệch theo.

Đây là lỗi phổ biến nhất khi tính RFM. Quy tắc: mỗi khi JOIN một bảng one-to-many, mọi phép đếm thực thể cha phải dùng DISTINCT.

Hai, lọc status <> 'cancelled' trong ON, không phải WHERE. Với INNER JOIN hai vị trí cho kết quả giống nhau, nhưng đặt trong ON diễn đạt đúng ý: "chỉ nối những đơn không bị huỷ". Nếu sau này đổi sang LEFT JOIN để giữ cả khách chưa mua, điều kiện trong ON vẫn giữ nguyên semantics; điều kiện trong WHERE sẽ âm thầm biến LEFT JOIN thành INNER JOIN. Viết đúng ngay từ đầu để khỏi phải sửa sau.

Ba, DATE '2026-01-01' là ngày tham chiếu cố định. Không dùng CURRENT_DATE, vì kết quả sẽ đổi mỗi ngày và bài chấm — cũng như báo cáo lịch sử — không tái lập được. Trong dự án thật, ngày tham chiếu thường là ngày cuối kỳ phân tích và được ghi rõ trong tài liệu.

Ai bị loại và vì sao

Mười hai khách trong bảng customers, nhưng chỉ 10 dòng kết quả:

• Hà Bùi không có đơn nào → JOIN loại cô.
• Inh Đỗ chỉ có một đơn đã huỷ → bị điều kiện status <> 'cancelled' loại.

Nếu đề muốn giữ cả hai, phải dùng LEFT JOIN và COALESCE cho các chỉ số. Đề này không yêu cầu, nên làm đúng phạm vi: khách có ít nhất một đơn không huỷ.

Vì sao mỗi nhóm có đúng 2 người?

Mười dòng chia 5 nhóm: 10 / 5 = 2, không dư. Mỗi nhóm đúng hai khách ở cả ba chỉ số. Đây là trường hợp đẹp nhất của NTILE; với 11 hay 12 khách, nhóm đầu sẽ nhận thêm người.

Xem chi tiết chỉ số gốc để hiểu điểm:

Sắp recency giảm dần: An 315, Em 304, Khánh 264, Dũng 240, Linh 197, Bình 181, Minh 145, Nga 109, Giang 92, Chi 51. Cắt hai người một nhóm: An và Em nhận r_score = 1 (lâu nhất), Giang và Chi nhận r_score = 5 (gần nhất). Khớp bảng kết quả.

Sắp monetary tăng dần: Nga 580k, Em 640k, Linh 1,19M, Khánh 1,64M, Dũng 2,15M, An 3,53M, Minh 4,03M, Chi 4,27M, Giang 5,49M, Bình 6,75M. Cặp đầu nhận m_score = 1, cặp cuối nhận 5.

Cạm bẫy đồng hạng của NTILE

Sáu khách chỉ có frequency = 1. Khi sắp ORDER BY frequency, sáu người này bằng nhau, và NTILE(5) chia họ vào ba nhóm đầu (mỗi nhóm hai người) theo thứ tự không xác định. Vì vậy f_score của họ có thể là 1, 2 hoặc 3 tuỳ thứ tự engine chọn — dù dữ liệu hoàn toàn giống nhau.

Đây không phải lỗi của bạn. Nó là hệ quả trực tiếp của việc dùng NTILE trên dữ liệu đồng hạng. Nếu cần kết quả tái lập tuyệt đối, phải thêm tiêu chí phá hoà (ORDER BY frequency, id) — nhưng làm vậy sẽ thay đổi cách chia nhóm và do đó thay đổi điểm so với đáp án. Với bài chấm, hãy dùng đúng câu lệnh mẫu; hiểu bản chất quan trọng hơn việc cố đoán thứ tự.

Bài học rộng hơn: NTILE không đảm bảo các giá trị bằng nhau nằm cùng nhóm. Khi báo cáo RFM thật, nên nêu rõ "điểm F được chia theo ngũ phân vị vị trí" thay vì ngụ ý đó là ngưỡng tuyệt đối.

Kiểm chứng trước khi nộp

WITH base AS (
  SELECT c.id, c.name,
         DATE '2026-01-01' - MAX(o.order_date) AS recency_days,
         COUNT(DISTINCT o.id) AS frequency,
         SUM(oi.quantity * oi.unit_price) AS monetary
  FROM customers c
  JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY c.id, c.name
)
SELECT COUNT(*) AS so_khach,
       SUM(frequency) AS tong_don,
       SUM(monetary) AS tong_chi
FROM base;

Kết quả kỳ vọng 10 | 15 | 30270000. Mười khách, 15 đơn không huỷ (16 đơn trừ đơn huỷ của Inh; hai đơn pending vẫn được tính vì đề chỉ loại cancelled), tổng chi 30.270.000. Con số này nhỏ hơn tổng doanh thu 31.230.000 đúng bằng 960.000 — giá trị đơn bị huỷ. Nếu tong_chi ra 31.230.000, bạn đã quên loại đơn huỷ; nếu ra con số khác nữa, hãy kiểm tra hai JOIN.

Kiểm tra thêm phân bố điểm:

-- Sau khi chạy đáp án, bọc lại và đếm
SELECT r_score, COUNT(*) FROM scored GROUP BY r_score ORDER BY r_score;

Mỗi giá trị 1..5 phải có đúng 2 dòng. Nếu có nhóm 3 dòng và nhóm 1 dòng, NTILE vẫn đúng (10 không chia hết cho 5 thì mới xảy ra), nên nếu thấy bất thường, hãy kiểm tra số dòng đầu vào — có thể bạn đang có 11 khách do quên lọc đơn huỷ.

Thực hành

1. Nộp đáp án cho bài RFM — Chấm điểm ngũ phân vị (rfm-scores).
2. Chạy riêng CTE base và đối chiếu 10 dòng với bảng chỉ số ở trên.
3. Bỏ status <> 'cancelled' và xem Inh Đỗ xuất hiện với điểm nào. Giải thích vì sao kết quả sai.
4. Đổi COUNT(DISTINCT o.id) thành COUNT(*) và quan sát toàn bộ f_score xáo trộn. Ghi lại số đơn thật của Bình (3) so với số dòng hàng (6).
5. Tính thêm r_score + f_score + m_score và xếp hạng khách theo tổng điểm. Ai đứng đầu?

Lỗi thường gặp

Tóm tắt

• RFM = recency (nhỏ tốt), frequency (lớn tốt), monetary (lớn tốt).
• NTILE(5) chấm ngũ phân vị; recency sắp giảm dần, hai chỉ số còn lại tăng dần.
• COUNT(DISTINCT o.id) là bắt buộc sau khi nối order_items.
• Lọc đơn huỷ trong ON, ngày tham chiếu cố định.
• 10 khách đủ điều kiện; 15 đơn không huỷ; tổng chi 30.270.000; mỗi nhóm điểm đúng 2 người.

Chương tiếp theo chuyển từ xếp hạng sang phân tích thời gian: gom theo tháng, tính cohort, đo tỷ lệ giữ chân và dựng cửa sổ trượt — những công cụ biến dữ liệu giao dịch thành câu chuyện tăng trưởng.