Using RFM Segment Score (Table), SQL (Analyzing)
& Power BI (Visualization)

What is RFM Analysis in Customer Segmentation?

RFM Analysis is a marketing technique used to analyze and segment customers based on their past behavior. RFM stands for Recency, Frequency, and Monetary Value, and each of these factors plays a significant role in understanding customer behavior and identifying customer segments for targeted marketing strategies.

  • Recency (R): How recently did the customer make a purchase?
  • Frequency (F): How often does the customer make purchases within a specific timeframe?
  • Monetary (M): How much money has the customer spent within a specific timeframe?

RFM Customer Segmentation helps businesses better understand their customers, target specific segments with tailored marketing efforts, enhance customer loyalty, and increase profitability through optimized marketing strategies.


The Data Sources

Ảnh 1
Ảnh 2
Ảnh 3

File data source customer.csv with columns: Customer, Customer Name, Segment, Age,...

File data source sales.csv with columns: Order Line, Order ID, Order Date, Ship Date,...

File segment scores.csv with two columns: Segment and Scores.

Download dataset in my repository: Here

SQL Script - Using RFM Analysis and Segment Scores

SELECT * FROM sales; SELECT * FROM customer; SELECT * FROM [segment scores]; -- -- -- -- -- -- -- RFM Calculate -- -- -- -- -- -- -- WITH RFM_Base AS ( SELECT s.Customer_ID as CustomerID, c.Customer_Name AS CustomerName, DATEDIFF(DAY, MAX(s.Order_Date), CONVERT(DATE, GETDATE())) AS Recency_Value, COUNT(DISTINCT s.Order_ID) AS Frequency_Value, ROUND(SUM(s.Sales), 2) AS Monetary_Value FROM sales AS s INNER JOIN customer AS c ON s.Customer_ID = c.Customer_ID GROUP BY s.Customer_ID,c.Customer_Name ) -- SELECT * FROM RFM_Base , RFM_Score AS ( SELECT *, NTILE(5) OVER (ORDER BY Recency_Value DESC) as R_Score, NTILE(5) OVER (ORDER BY Frequency_Value ASC) as F_Score, NTILE(5) OVER (ORDER BY Monetary_Value ASC) as M_Score FROM RFM_Base ) -- SELECT * FROM RFM_Score , RFM_Final AS ( SELECT *, CONCAT(R_Score, F_Score, M_Score) as RFM_Overall -- , (R_Score + F_Score + M_Score) as RFM_Overall1 -- , CAST(R_Score AS char(1))+CAST(F_Score AS char(1))+CAST(M_Score AS char(1)) --as RFM_Overall2 FROM RFM_Score ) -- SELECT * FROM RFM_Final SELECT f.*, sg.Segment FROM RFM_Final f JOIN [segment scores] sg ON f.RFM_Overall = sg.Scores ; -- -- -- -- -- -- -- Done -- -- -- -- -- -- --

In this script, I created some CTE (Common Table Expression) by using "WITH AS"

  • 1. RFM_Base: calculating Recency Value, Frequency Value and Monetary Value
    • Reecency Value: using DATEDIFF to Calculate the difference between the maximum order date of each customer (MAX(s.Order_Date)) and the current date (CONVERT(DATE, GETDATE()))
    • Frequency Value: using COUNT(DISTINCT(Order_ID)) to count the number of distinct (unique) values of the "Order_ID"
    • Monetary Value: using ROUND(SUM(s.Sales), 2) to calculate the rounded total of the "Sales", and round it by 2
  • 2. RFM_Score: calculating the RFM scores for each customer based on their Recency_Value, Frequency_Value, and Monetary_Value. I used the NTILE function along with the OVER clause to distribute the customers into five equal-sized groups (quintiles) for each of the three metrics.
  • 3. RFM_Final: concatenating the R_Score, F_Score, and M_Score to create an "RFM_Overall" value for each customer.
  • 4. Finally, joining with segment table to find the segment for each customer
  • After that, I loaded this SQL Scripts to Power BI to visualize.

RFM Customer Segmentation Dashboard

Visits my social profile and get connected: