PHÂN TÍCH RFM TRONG SQL

Phân tích RFM (RECENCY –  FREQUENCY – MONETARY) trong SQL là một kỹ thuật được sử dụng trong marketing để xếp hạng và phân nhóm khách hàng dựa trên số lần truy cập gần đây, tấn suất và tổng số tiền giao dịch gần đây để có thể tìm ra những khách hàng tiềm năng và thực hiện các chiến dịch marketing. Bài viết trình bày cách giải quyết bài toán với database

Các vấn đề và giải pháp với phân tích RFM

Các vấn đề gặp phải:

• NoPurchasePerYear (Số lần mua hàng trung bình năm kỳ vọng tính ra sẽ là một số thập phân nhưng thực tế tính ra chỉ có giá trị nguyên:1COUNT(DISTINCT(fi.SalesOrderNumber))/DATEDIFF(YEAR,MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), '2015-01-01')
• Tìm khách hàng trong nhóm 20% khách có AmountPerYear và TotalProfit cao nhất.
• Sau khi tính điểm khách hàng theo quy tắc đề bài, kết quả trà về là những cột TopActive, TopYear, TopProfit và TopPur riêng biệt. Kết quả này khó có thể tính được tổng điểm từng khách hàng vì không thể cộng tổng cột.

Cách giải quyết:

• Với NoPurchasePerYear: chuyển một trong 2 giá trị trong công thức thành số thập phân:1COUNT(DISTINCT(fi.SalesOrderNumber))/CAST(DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), '2015-01-01') ASfloat)
• Với top 20% AmountPerYear và TotalProfit, dùng Window Funtion: 1PERCENT_RANK (), sắp xếp theo AmountPerYear và TotalProfit giảm dần, đánh dấu 1 điểm cho khách hàng trong khoảng từ 0% đến 20%.
• Với tổng điểm khách hàng: Dùng UNPIVOT, kết quả sẽ trả về một cột với CustomerKey mỗi khách hàng được tăng thêm nhiều lần, ứng với điểm tương ứng. Kết quả này có thể dùng để tính tổng điểm của mỗi khách hàng.

Các bước thực hiện phân tích RFM

Mô tả các trường và cách tính (Các trường được tính đến ngày 2015-01-01)

Output cuối cùng là bảng sau:

Cách tính điểm khách hàng:

• Khách hàng Active: Mua hàng trong vòng 1 năm gần nhất: 1 điểm
• Khách hàng top 20% có AmountPerYear cao nhất: 2 điểm
• Khách hàng top 20% có TotalProfit cao nhất: 2 điểm
• Khách hàng có NoPurchasePerYear >1 : 1 điểm

Phân loại khách hàng:

• Lớn hơn hoặc bằng 5 điểm: Diamond
• 4 điểm: Gold
• 3 điểm: Silver
• Dưới 3 điểm: Normal

Các bước thực hiện phân tích RFM

Tính các trường sau, group by khách hàng:

• Câu lệnh:

• Kết quả:

Phân loại khách hàng:

Tìm phần trăm khách hàng có AmountPeryear và TotalProfit cao nhất:

– Dùng window function

1	Percent_Rank ()

• Câu lệnh:

Gắn điểm khách hàng theo yêu cầu đề bài:

• Kết quả:

Thu các cột TopActive, TopYear, TopProfit, TopPur thành một cột

– Dùng Unpivot:

• Câu lệnh:

Kết quả:

Tính tổng điểm cuối cùng theo CustomerKey

• Câu lệnh:

• Kết quả:

Phân loại khách hàng:

– Dùng CASE WHEN

• Câu lệnh:

• Kết quả:

Mỗi bước ở trên là một CTE, JOIN CTE đầu là các trường dữ liệu đã được tính và CTE cuối có cột phân loại khách hàng cuối cùng.

• Câu lệnh:

• Kết quả:

Kết luận:

Phân tích RFM giúp phân loại khách hàng và trả lời cho những câu hỏi:

• Khách hàng nào thuộc nhóm trung thành với lượt mua trung bình năm nhiều nhất?
• Khách hàng công ty đang có nguy cơ mất?
• Cần tập trung chiến lược marketing cho nhóm khách hàng nào?

Kết quả cuối cùng cho thấy nhóm khách hàng ‘Diamond’ và ‘Gold’ tuy có số lượt mua trung bình năm ít hơn các nhóm khách hàng khác nhưng là những nhóm mang lại lợi nhuận nhiều nhất cho công ty do có sức mua lớn. Kết quả này cũng có mối liên hệ với nguyên lý Pareto: “80% doanh thu công ty đến từ 20% khách hàng.”

Dựa vào kết quả phân tích, công ty có thể cân nhắc chiến lược kinh marketing phù hợp cho từng nhóm đổi tượng khách hàng.

Chúng tôi chuyên cung cấp những khoá học về Phân tích dữ liệu, đăng ký ngay để nhận được tư vấn chi tiết lộ trình dành riêng cho bạn nhé!

SQL Level 1: SQL for Beginner (for Data Analyst/ Business Analyst/ Tester Data) – Truy vấn và thao tác dữ liệu cho người bắt đầu

SQL Level 2: Advanced SQL (for Data Engineer) – Lập trình dữ liệu nâng cao

Nguồn: Internet