RFM Analysis for Customer Segmentation with Power BI

Power BI series

Ploii Tubsamon

5 min readSep 3, 2021

Recency | frequency | Monetary |

This is a step-by-step for creating RFM analysis using Power BI.

Beneficial for analyzing customer behavior.

View a sample dashboard by clicking here.

Table of Contents.

Understand RFM.
How to generate RFM scores.
Start to create RFM analysis using Power BI.

How do we design customer segmentation?

If you want to know: when your customer last made a purchase, how frequently they make purchases, or how large their ticket size is. That is all you need to know about customer behavior. Following that, find a group of people who will be profitable customers and purpose some privilege for them. This is called ‘customer segmentation’. To determine the customer segmentation, we can use the RFM analysis.

RFM is a technique used to prioritize customers. RFM stands for recency, frequency, and monetary.

Recency (R) tells us when is the latest purchase date.

Frequency (F) tells us how frequently do they make purchases.

Monetary (M) tells us how large their average ticket size is made. Actually, it is customer lifetime value.

How to generate RFM scores.

Basically, to create RFM scores, we need 4 parameters

Identify the customer: Maybe name or ID
Recency: to tell the lastest day purchased
Frequency: total number of transactions
Monetary: total spending (or ‘ticket size’)

To define RFM score on a scale of 1–5 for each customer in terms of recency, frequency, and monetary value. 1 is the lowest and 5 is the highest.

The description to clarify the difference between RFM values and RFM scores, please see the detail on a link.

RFM Scores are defined differently for each business.

Remark: To compute the RFM score, I utilize the percentile method.

Let’s start with Power BI !!

1. Data for use

You can download it from Kaggle.
Get and transform data to a power query editor (To explore and clean data). Then, close and apply. And go to the report view.
To apply the correct segment, we have to use another table called ‘Segment-Scores Table’ to filter the RFM score.

2. DAX (Data Analysis Expression) for RFM

Start at the ‘scanner_data’ sheet :

To calculate the ‘R value’, we need to know the latest date of the transaction. Therefore, we’ll create a new measure called ‘last transaction date’

-- To create a new measure called 'last transaction date'last transaction date =MAXX(FILTER('scanner_data','scanner_data'[Customer_ID]='scanner_data'[Customer_ID]),'scanner_data'[Date])

Create a new measure for R-value and F-value (number of the ticket)

-- To create a new measure called 'R value'
R value = DATEDIFF('scanner_data'[last transaction date],TODAY(),DAY)-- To create a new measure called 'F value'
F value = DISTINCTCOUNT('scanner_data'[Transaction_ID])

Create a new measure for M-value:

-- To create a new measure called 'M value'
M value =var TotalSales = SUM('scanner_data'[Sales_Amount])
var TotalQuantity = sum(scanner_data[Quantity])Return 
DIVIDE (TotalSales,TotalQuantity,0)

Next, Generate the new table called ‘RFM table’ :

To create the ‘RFM table’ use dax as below

-- To create a new table called 'RFM table'RFM table = SUMMARIZE(
   'scanner_data','scanner_data'[Customer_ID],
   "R Value",[R Value],
   "F Value",[F Value],
   "M Value",[M Value])

After we generate the ‘RFM table’, we get the table as shown below

Next, we create a new column ‘R Score’, ‘F Score, and ‘M Score’. We separate group each data by percentile.

-- To create a new column called 'R Score'R Score = SWITCH (
  TRUE (),
   [R value] <= PERCENTILE.INC ( 'RFM table'[R Value], 0.20 ), "5",    
   [R value] <= PERCENTILE.INC ( 'RFM table'[R Value], 0.40 ), "4", 
   [R Value] <= PERCENTILE.INC ( 'RFM table'[R Value], 0.60 ), "3", 
   [R value] <= PERCENTILE.INC ( 'RFM table'[R Value], 0.80 ), "2",
   "1"
       )-- To create a new column called 'F Score'F Score =SWITCH (
  TRUE (),
   [F value] <= PERCENTILE.INC ( 'RFM table'[F Value], 0.20 ), "1",    
   [F value] <= PERCENTILE.INC ( 'RFM table'[F Value], 0.40 ), "2", 
   [F Value] <= PERCENTILE.INC ( 'RFM table'[F Value], 0.60 ), "3", 
   [F value] <= PERCENTILE.INC ( 'RFM table'[F Value], 0.80 ), "4",
   "5"
       )-- To create a new column called 'M Score'M Score =SWITCH (
  TRUE (),
   [F value] <= PERCENTILE.INC ( 'RFM table'[F Value], 0.20 ), "1",    
   [F value] <= PERCENTILE.INC ( 'RFM table'[F Value], 0.40 ), "2", 
   [F Value] <= PERCENTILE.INC ( 'RFM table'[F Value], 0.60 ), "3", 
   [F value] <= PERCENTILE.INC ( 'RFM table'[F Value], 0.80 ), "4",
   "5"
       )

Create the new column called ‘RFM’ by concatenating column ‘R Score’, ‘F Score’, and ‘M Score’

-- To create a new column called 'RFM'RFM = 'RFM table'[R Score]& 'RFM table'[F Score]&'RFM table'[M Score]

3. Define criteria for each segment

Import the table ‘Segmnt-Scores Table’ into Power BI. You can download the CSV file here.

4. Manage relationship

To create a relationship between the ‘RFM table’ and the ‘Segmnt-Scores Table’ by using the columns ‘RFM’ in the RFM table and ‘Scores’ in the Segmnt-Scores table.

Relationship between RFM table and Segmnt-Scores table

5. Design your RFM dashboard !!

Let’s take a look at the sample dashboard.

See more by clicking here.

Conclusions

As you see, it’s extremely simple to understand RFM analysis and create a dashboard.
Power BI can also generate more visually appealing graphics.
Do not use any code!! So easy to do and results in a wonderful visual.

If you require Power Bi File (.pbix), please visit and download it on my Github.

Thanks.

Kaggle for providing the dataset.
Many thanks to Power BI for letting us improve our skills.
Picture from Flaticon.com

Other analytics dashboards using Power BI.

See you in the next post.

Knowledge Is Endless To Learn. Let’s study together.

I’m available on LinkedIn.

Tubsamon Dusitsopittawong