Phân tích RFM (RECENCY – FREQUENCY – MONETARY) 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. Nếu bạn đang tự học SQL hay tìm hiểu về SQL cũng như cách phân tích RFM thì bài viết này sẽ giúp bạn giải cách quyết bài toán với database: AdventureWorkDW2019

Mục lục
Tự học SQL – 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:
COUNT(DISTINCT(fi.SalesOrderNumber))/DATEDIFF(YEAR,MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), '2015-01-01')
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:
COUNT(DISTINCT(fi.SalesOrderNumber))/CAST(DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), '2015-01-01') AS float)
PERCENT_RANK ()
Các bước thực hiện phân tích RFM – Tự học SQL
Mô tả các trường và cách tính (Các trường được tính đến ngày 2015-01-01)
STT | Tên trường | Mô tả | Cách tính |
1 | CustomerKey | Mã khách hàng | |
2 | CustomerName | Tên khách hàng( gồm FirstName, MiddleName (nếu có) và LastName) | CONCAT_WS(‘ ‘, c.FirstName, c.MiddleName, c.LastName) |
3 | MonthsFrom1stPurchchase | Số tháng từ ngày mua hàng đầu tiên | DATEDIFF(MONTH, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’) |
4 | NoPurchasePerYear | Số lần mua hàng trung bình năm(Tổng số lần mua hàng/số năm từ ngày mua hàng đầu tiên) | Tổng số lần mua hàng: đơn hàng được tính theo ngày, giả định khách không mua hàng hơn 1 lần một ngày COUNT(DISTINCT(fi.SalesOrderNumber)) /CAST(DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’) AS float) |
5 | AmountPerYear | Giá trị mua hàng trung bình năm(Tổng giá trị mua hàng/số năm từ ngày mua hàng đầu tiên) | CAST(SUM(SalesAmount) / DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’) AS float) |
6 | TotalProfit | Tổng SalesAmount- Tổng ProductStandardCost | SUM(SalesAmount) – SUM(fi.ProductStandardCost) |
7 | Customer Segment | Phân nhóm theo quy tắc |
Output cuối cùng là bảng sau:
CustomerKey | CustomerName | MonthsFrom1stPurchase | NoPurchasePerYear | AmountPerYear | TotalProfit | CustomerSegment |
11000 | Jon V Yang | 48 | 0.75 | 2062.2475 | 3513.6905 | Gold |
11001 | Eugene L Huang | 48 | 0.75 | 1595.97 | 2795.8839 | Gold |
11002 | Ruben Torres | 48 | 0.75 | 2028.51 | 3454.8801 | Gold |
11003 | Christy Zhu | 49 | 0.6 | 1627.858 | 3467.1264 | Gold |
11004 | Elizabeth Johnson | 48 | 0.75 | 2049.0025 | 3501.9051 | Gold |
11005 | Julio Ruiz | 49 | 0.6 | 1624.266 | 3459.8654 | Gold |
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:
STT | Tên trường | Cách tính |
1 | CustomerKey | |
2 | CustomerName | CONCAT_WS(‘ ‘, c.FirstName, c.MiddleName, c.LastName) |
3 | MonthsFrom1stPurchchase | DATEDIFF(MONTH, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’) |
4 | NoPurchasePerYear | COUNT(DISTINCT(fi.SalesOrderNumber)) /CAST(DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’) AS float) |
5 | AmountPerYear | CAST(SUM(SalesAmount) / DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’) AS float) |
6 | TotalProfit | SUM(SalesAmount) – SUM(fi.ProductStandardCost) |
- Câu lệnh:
SELECT fi.CustomerKey , CONCAT_WS(' ', c.FirstName, c.MiddleName, c.LastName) AS CustomerName , MAX(OrderDate) AS RecentOrder , DATEDIFF(MONTH, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), '2015-01-01') AS Monthsfrom1stpur , COUNT(DISTINCT(fi.SalesOrderNumber)) AS TotalOrder , DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), '2015-01-01') AS Yearsfrom1spur , COUNT(DISTINCT(fi.SalesOrderNumber)) /CAST(DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), '2015-01-01') AS float) AS NoPurchasePerYear , CAST(SUM(SalesAmount) / DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), '2015-01-01') AS float) AS AmountPerYear , SUM(SalesAmount) - SUM(fi.ProductStandardCost) AS TotalProfit FROM FactInternetSales AS fi LEFT JOIN DimCustomer AS c ON fi.CustomerKey = c.CustomerKey GROUP BY fi.CustomerKey , CONCAT_WS(' ', FirstName, MiddleName, LastName)
- Kết quả:
Customer Key | Customer Name | Recent Order | Month From 1stPurchase | Total Order | Years From 1stOrder | No Purchase PerYear | Amount PerYear | Total Profit |
20075 | Aaron A Allen | 12/2/11 0:00 | 37 | 1 | 4 | 0.25 | 849.9975 | 1487.836 |
15568 | Aaron A Hayes | 9/28/13 0:00 | 28 | 2 | 3 | 0.666667 | 1037.657 | 1356.908 |
28866 | Aaron B Adams | 4/28/13 0:00 | 21 | 1 | 2 | 0.5 | 58.98 | 52.4628 |
21414 | Aaron Butler | 12/26/13 0:00 | 13 | 1 | 2 | 0.5 | 7.49 | 9.3774 |
18695 | Aaron C Diaz | 3/25/13 0:00 | 43 | 2 | 4 | 0.5 | 1507.393 | 2300.355 |
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
Percent_Rank ()
- Câu lệnh:
SELECT * , PERCENT_RANK () OVER (ORDER BY AmountPerYear DESC) AS AmountPerYear_rank , PERCENT_RANK () OVER (ORDER BY TotalProfit DESC) AS TotalProfit_rank FROM rfm
Gắn điểm khách hàng theo yêu cầu đề bài:
SELECT CustomerKey , CASE WHEN YEAR(RecentOrder) = 2014 THEN 1 ELSE 0 END AS TopActive , CASE WHEN AmountPerYear_rank BETWEEN 0 AND 0.2 THEN 2 ELSE 0 END AS TopYear , CASE WHEN TotalProfit_rank BETWEEN 0 AND 0.2 THEN 2 ELSE 0 END AS TopProfit , CASE WHEN NoPurchasePerYear > 1 THEN 1 ELSE 0 END AS TopPur FROM percentrank
- Kết quả:
CustomerKey | TopActive | TopYear | TopProfit | TopPur |
12132 | 0 | 2 | 2 | 1 |
12308 | 0 | 2 | 2 | 1 |
12301 | 0 | 2 | 2 | 1 |
12300 | 0 | 2 | 2 | 1 |
12131 | 0 | 2 | 2 | 1 |
12321 | 0 | 2 | 2 | 1 |
Thu các cột TopActive, TopYear, TopProfit, TopPur thành một cột – Dùng Unpivot:
- Câu lệnh:
SELECT CustomerKey , Score , SegmentedCustomer FROM segment UNPIVOT( Score FOR SegmentedCustomer IN(TopActive, TopYear, TopProfit, TopPur) ) AS u
- Kết quả:
CustomerKey | Score | SegmentedCustomer |
12308 | 0 | TopActive |
12308 | 2 | TopYear |
12308 | 2 | TopProfit |
12308 | 1 | TopPur |
12300 | 0 | TopActive |
12300 | 2 | TopYear |
12300 | 2 | TopProfit |
12300 | 1 | TopPur |
12321 | 0 | TopActive |
12321 | 2 | TopYear |
12321 | 2 | TopProfit |
12321 | 1 | TopPur |
12307 | 0 | TopActive |
12307 | 2 | TopYear |
12307 | 2 | TopProfit |
Tính tổng điểm cuối cùng theo CustomerKey
- Câu lệnh:
SELECT CustomerKey , SUM(Score) AS TotalScore FROM ScoreCustomer GROUP BY CustomerKey
- Kết quả:
CustomerKey | Score | SegmentedCustomer |
12308 | 0 | TopActive |
12308 | 2 | TopYear |
12308 | 2 | TopProfit |
12308 | 1 | TopPur |
12300 | 0 | TopActive |
12300 | 2 | TopYear |
12300 | 2 | TopProfit |
12300 | 1 | TopPur |
12321 | 0 | TopActive |
12321 | 2 | TopYear |
12321 | 2 | TopProfit |
12321 | 1 | TopPur |
12307 | 0 | TopActive |
12307 | 2 | TopYear |
12307 | 2 | TopProfit |
Phân loại khách hàng: – Dùng CASE WHEN
- Câu lệnh:
SELECT CustomerKey , CASE WHEN TotalScore >=5 THEN 'Diamond' WHEN TotalScore = 4 THEN 'Gold' WHEN TotalScore = 3 THEN 'Silver' WHEN TotalScore < 3 THEN 'Normal' END AS CustomerSegement FROM CustomerFinalScore
- Kết quả:
CustomerKey | CustomerSegmented |
14324 | Gold |
15652 | Gold |
19897 | Normal |
28387 | Normal |
27059 | Normal |
11407 | Normal |
24142 | Normal |
18569 | Normal |
22814 | Normal |
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:
SELECT rfm.CustomerKey , rfm.CustomerName , rfm.Monthsfrom1stpur AS MonthFrom1stPurchase , rfm.NoPurchasePerYear , rfm.AmountPerYear , rfm.TotalProfit , fcs.CustomerSegement FROM rfm LEFT JOIN FinalCustomerSegment AS fcs ON rfm.CustomerKey = fcs.CustomerKey ORDER BY CustomerKey
- Kết quả:
CustomerKey | CustomerName | MonthsFrom1stPurchase | NoPurchasePerYear | AmountPerYear | TotalProfit | CustomerSegment |
11000 | Jon V Yang | 48 | 0.75 | 2062.2475 | 3513.6905 | Gold |
11001 | Eugene L Huang | 48 | 0.75 | 1595.97 | 2795.8839 | Gold |
11002 | Ruben Torres | 48 | 0.75 | 2028.51 | 3454.8801 | Gold |
11003 | Christy Zhu | 49 | 0.6 | 1627.858 | 3467.1264 | Gold |
11004 | Elizabeth Johnson | 48 | 0.75 | 2049.0025 | 3501.9051 | Gold |
11005 | Julio Ruiz | 49 | 0.6 | 1624.266 | 3459.8654 | Gold |
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.
DP-080 – Querying Data with Microsoft T-SQL
DAFD – Data Analytics Foundation