tự học SQL Phân tích RFM

TỰ HỌC SQL: PHÂN TÍCH RFM CHÍNH XÁC, HIỆU QUẢ

Ngày đăng: 13/03/2022

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

Tự học SQL 1

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')
  • 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:
    COUNT(DISTINCT(fi.SalesOrderNumber))/CAST(DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), '2015-01-01') AS float)
  • Với top 20% AmountPerYear và TotalProfit, dùng Window Funtion:
  • PERCENT_RANK ()
  • , sắp xếp theo AmountPerYear 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 – 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)

    STTTên trườngMô tảCách tính
    1CustomerKeyMã khách hàng
    2CustomerNameTên khách hàng( gồm FirstName, MiddleName (nếu có) và LastName)CONCAT_WS(‘ ‘, c.FirstName, c.MiddleName, c.LastName)
    3MonthsFrom1stPurchchaseSố tháng từ ngày mua hàng đầu tiênDATEDIFF(MONTH, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’)
    4NoPurchasePerYearSố 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)
    5AmountPerYearGiá 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)
    6TotalProfitTổng SalesAmount- Tổng ProductStandardCostSUM(SalesAmount) – SUM(fi.ProductStandardCost)
    7Customer SegmentPhân nhóm theo quy tắc

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

    CustomerKeyCustomerNameMonthsFrom1stPurchaseNoPurchasePerYearAmountPerYearTotalProfitCustomerSegment
    11000Jon V Yang480.752062.24753513.6905Gold
    11001Eugene L Huang480.751595.972795.8839Gold
    11002Ruben Torres480.752028.513454.8801Gold
    11003Christy Zhu490.61627.8583467.1264Gold
    11004Elizabeth Johnson480.752049.00253501.9051Gold
    11005Julio Ruiz490.61624.2663459.8654Gold

    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:

    STTTên trườngCách tính
    1CustomerKey
    2CustomerNameCONCAT_WS(‘ ‘, c.FirstName, c.MiddleName, c.LastName)
    3MonthsFrom1stPurchchaseDATEDIFF(MONTH, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’)
    4NoPurchasePerYearCOUNT(DISTINCT(fi.SalesOrderNumber))       /CAST(DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’) AS float)
    5AmountPerYearCAST(SUM(SalesAmount) / DATEDIFF(YEAR, MIN(CONVERT(CHAR(10), fi.OrderDate, 120)), ‘2015-01-01’) AS float)
    6TotalProfitSUM(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 KeyCustomer NameRecent OrderMonth From 1stPurchaseTotal OrderYears From 1stOrderNo Purchase PerYearAmount PerYearTotal Profit
    20075Aaron A Allen12/2/11 0:0037140.25849.99751487.836
    15568Aaron A Hayes9/28/13 0:0028230.6666671037.6571356.908
    28866Aaron B Adams4/28/13 0:0021120.558.9852.4628
    21414Aaron Butler12/26/13 0:0013120.57.499.3774
    18695Aaron C Diaz3/25/13 0:0043240.51507.3932300.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ả:
    CustomerKeyTopActiveTopYearTopProfitTopPur
    121320221
    123080221
    123010221
    123000221
    121310221
    123210221

    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ả:
    CustomerKeyScoreSegmentedCustomer
    123080TopActive
    123082TopYear
    123082TopProfit
    123081TopPur
    123000TopActive
    123002TopYear
    123002TopProfit
    123001TopPur
    123210TopActive
    123212TopYear
    123212TopProfit
    123211TopPur
    123070TopActive
    123072TopYear
    123072TopProfit

    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ả:
    CustomerKeyScoreSegmentedCustomer
    123080TopActive
    123082TopYear
    123082TopProfit
    123081TopPur
    123000TopActive
    123002TopYear
    123002TopProfit
    123001TopPur
    123210TopActive
    123212TopYear
    123212TopProfit
    123211TopPur
    123070TopActive
    123072TopYear
    123072TopProfit

    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ả:
    CustomerKeyCustomerSegmented
    14324Gold
    15652Gold
    19897Normal
    28387Normal
    27059Normal
    11407Normal
    24142Normal
    18569Normal
    22814Normal

    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ả:
    CustomerKeyCustomerNameMonthsFrom1stPurchaseNoPurchasePerYearAmountPerYearTotalProfitCustomerSegment
    11000Jon V Yang480.752062.24753513.6905Gold
    11001Eugene L Huang480.751595.972795.8839Gold
    11002Ruben Torres480.752028.513454.8801Gold
    11003Christy Zhu490.61627.8583467.1264Gold
    11004Elizabeth Johnson480.752049.00253501.9051Gold
    11005Julio Ruiz490.61624.2663459.8654Gold

    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
     

    Trả lời

    Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *