DataOps Peach

Data Foundations · Dimensional modeling và Gold · Bài 5/5

Bảng đặc trưng khách hàng (RFM)

Dựng bảng một dòng mỗi khách với recency, frequency, monetary; giữ khách chưa mua, phân biệt NULL với 0, và tính recency từ một reference_date cố định.

Học tương tác →

RFM là gì

Ba đặc trưng kinh điển để mô tả hành vi mua của một khách:

Chữ Đặc trưng Cột trong bài
Recency Lần mua gần nhất cách đây bao lâu days_since_last_order
Frequency Mua bao nhiêu lần order_count
Monetary Chi bao nhiêu tiền total_revenue (và avg_order_value)

gold_customer_360 là bảng gold có grain một dòng mỗi khách. Marketing dùng nó để chia nhóm khách, và Track 4 dùng nó làm input cho mô hình ML.

Đi từ dim_customer, không từ fact

FROM dim_customer c
LEFT JOIN fact_orders f ON f.customer_id = c.customer_id AND f.status = 'completed'
GROUP BY ALL

Đi từ fact (rồi JOIN dim_customer để lấy vùng) thì khách chưa có đơn completed nào biến mất: bảng có 499 dòng thay vì 500, và không ai biết có một khách "ngủ đông" cần chăm sóc. Điều kiện status = 'completed' nằm ở ON vì cùng lý do như bài 3.2.

Kiểm grain ngay sau khi dựng: count(*) phải bằng count(DISTINCT customer_id) và bằng số khách trong dim_customer.

NULL khác 0

Khách C0148 chưa có đơn completed nào:

order_count total_revenue avg_order_value days_since_last_order
0 NULL NULL NULL

NULLIF(count(f.revenue), 0) tránh lỗi chia cho 0. Dùng count(f.revenue) chứ không count(f.order_id): đơn thiếu giá (revenue NULL) không được tính vào sum thì cũng không được tính vào mẫu số.

reference_date cố định, không dùng current_date

DATE '2026-01-01' - max(f.date_key) AS days_since_last_order

Nếu viết current_date - max(f.date_key):

reference_date là tham số của lần chạy: lần chạy này dùng 2026-01-01, lần backfill dùng ngày khác, nhưng cùng tham số luôn ra cùng kết quả. Bài kiểm chứng bằng vân tay (số dòng, tổng M, tổng R) trước và sau khi chạy lại: y hệt. Module 04 biến phép thử này thành tiêu chuẩn cho mọi pipeline.

flowchart LR
  C[dim_customer · 500] -->|LEFT JOIN| G[gold_customer_360 · 500]
  F[fact_orders · completed] --> G
  R[reference_date 2026-01-01] --> G

Một con số để nhớ

Tổng M của bảng là 45,463,601,000, nhỏ hơn tổng doanh thu completed của fact đúng 1,254,226,000: phần của đơn mồ côi C99999, không gắn được với khách nào. Đây là chênh lệch có giải thích, khác với chênh lệch của bài trước; ghi nó vào giả định của bảng.

Lệnh hay dùng

Lệnh Để làm gì
FROM dim_customer c LEFT JOIN fact_orders f ON ... AND f.status = 'completed' giữ mọi khách
count(f.order_id) frequency, ra 0 khi không có đơn
sum(f.revenue) / NULLIF(count(f.revenue), 0) trung bình, không chia cho 0
DATE '<reference_date>' - max(f.date_key) recency theo tham số

Lỗi hay gặp

Học tương tác →