RFM Model Using Power BI

Your first Question knocking your head is What is RFM lets dive to help you understand this terminology well

What Is RFM?

RFM consists of three components:

1. Recency (R): This metric refers to how recently a customer made a purchase. Customers who made a purchase recently are considered more likely to respond to marketing messages and offers compared to those who haven’t made a purchase in a long time.
2. Frequency (F): This component tracks how often a customer makes purchases within a certain period. Frequent customers are usually more loyal and engaged than customers who purchase sporadically.
3. Monetary (M): This refers to how much money a customer spends. Customers who spend more are typically more valuable to the business. Identifying high-spending customers allows businesses to focus marketing efforts on retaining these valuable clients

Understanding RFM Analysis for Customer Segmentation

After understanding the meaning of RFM shortly lets explaining RFM Analysis and how it used in helping Organization to improve Customer Experience in their business. RFM Analysis(Recency, Frequency, Monetary) is a powerful marketing tool that helps businesses analyze and segment their customers based on purchasing behavior. The technique is particularly popular in retail, e-commerce, and other consumer-facing industries because it enables companies to understand customer activity patterns and tailor marketing efforts to enhance customer retention, improve engagement, and ultimately increase sales. By categorizing customers based on how recently they made a purchase (Recency), how often they make purchases (Frequency), and how much they spend (Monetary), businesses can identify which customers are most likely to respond positively to different marketing strategies.

The RFM Model Process

Back to our task of doing this analysis using Microsoft Power BI, previously many Data Scientists used to develop this Model using python or R but today I will show how to implement the same tactics using Microsoft Power BI with help of DAX and Power query editor as our tools.

1. ETL Process:
The first step in RFM analysis is collecting relevant customer transaction data which I used the Online Retail-II Dataset (in Excel format) from 2009–2010 . The dataset include details such as the date of each transaction(Invoice Date), Quantity, Price, Description, Country, Stock Code, Invoice Number, and the unique customer identifier(Customer ID).

Dataset Cleaning Processes:-
Data loaded to the Power BI and Cleaning process done in Power query editor as the steps listed below
1-Remove nulls in Customer ID column
2-Extract the needed Stock Code and Invoice Number
3-Set all datatypes accordingly of the entire table
4-Add a Total Amount Column (Total Amount = Quantiy * Price)
5-Load data to the Power BI

In Power BI — Table View
Create a calculated Columns for Reference date using DAX , and Difference in days between Reference date and Invoice date put in days to helps us to see how many previous days the customer purchase from us for my side the reference date is maximum date in my invoice dates plus one I do this because my data is from 2009–2010 but if you are using current data for recent years use today as Reference date

ReferrenceDate = MAX('FAct: ReailSalesData'[InvoiceDate])+1

Days_ = ABS(DATEDIFF('FAct: ReailSalesData'[ReferrenceDate],'FAct: ReailSalesData'[InvoiceDate],DAY))

2. RFM Score Calculation:
Before to jump into calculation you have to create a virtual calculated table(name it RFM) using DAX the code will be show below , this table will help us to do all our calculations scores and segments the customers base on their behavior's

// RFM Analysis Calculation: Summarize customer transaction data to get Recency, Frequency, and Monetary Value.
RFM = 
    SUMMARIZECOLUMNS(
        // Specify the table and the customer identifier for grouping the data
        'Fact: RetailSalesData'[Customer ID], 
        
        // Calculate the Recency: Sum of the 'Days_' column which indicates how many days since last purchase
        "Recency", SUM('Fact: RetailSalesData'[Days_]),
        
        // Calculate the Frequency: Count distinct invoices to determine how often a customer made a purchase
        "Frequency", DISTINCTCOUNT('Fact: RetailSalesData'[Invoice]),
        
        // Calculate the Monetary Value: Sum of the total amount spent by the customer
        "Value", SUM('Fact: RetailSalesData'[TotalAmount])
    )

Result from DAX above

RFM calculation start by using quantiles but in power BI we use percentiles values (0.25 ,0.5 ,0.75) and the score policy is different Recency category , but for Frequency and Monetary (Value) category we use the same policy lets see down the DAX code how the score process done

R score Column

R = 
VAR RecencyQuantile25 = PERCENTILEX.INC('RFM', 'RFM'[Recency], 0.25)
VAR RecencyQuantile50 = PERCENTILEX.INC('RFM', 'RFM'[Recency], 0.50)
VAR RecencyQuantile75 = PERCENTILEX.INC('RFM', 'RFM'[Recency], 0.75)

-- Recency Score
VAR RScore = 
    SWITCH(
        TRUE(),
        'RFM'[Recency] <= RecencyQuantile75, 1,
        'RFM'[Recency] <= RecencyQuantile50, 2,
        'RFM'[Recency] <= RecencyQuantile25, 3,
        4
    )
RETURN
RScore

F Score Column

F = 
VAR FrequencyQuantile25 = PERCENTILEX.INC('RFM', 'RFM'[Frequency], 0.25)
VAR FrequencyQuantile50 = PERCENTILEX.INC('RFM', 'RFM'[Frequency], 0.50)
VAR FrequencyQuantile75 = PERCENTILEX.INC('RFM', 'RFM'[Frequency], 0.75)

-- Frequency Score
VAR FScore = 
    SWITCH(
        TRUE(),
        'RFM'[Frequency] <= FrequencyQuantile25, 1,
        'RFM'[Frequency] <= FrequencyQuantile50, 2,
        'RFM'[Frequency] <= FrequencyQuantile75, 3,
        4
    )
RETURN
FScore

M Score Column

M = 
VAR ValueQuantile25 = PERCENTILEX.INC('RFM', 'RFM'[Value], 0.25)
VAR ValueQuantile50 = PERCENTILEX.INC('RFM', 'RFM'[Value], 0.50)
VAR ValueQuantile75 = PERCENTILEX.INC('RFM', 'RFM'[Value], 0.75)

-- Monetary (Value) Score
VAR MScore = 
    SWITCH(
        TRUE(),
        'RFM'[Value] <= ValueQuantile25, 1,
        'RFM'[Value] <= ValueQuantile50, 2,
        'RFM'[Value] <= ValueQuantile75, 3,
        4
    )
RETURN
MScore

Result from above DAX Codes

After created a calculated columns of R, F , and M the create a Segment(call RFM Segment)Score by Concatenate the values found in R, F and M columns as the code below

RFM_Segment = CONCATENATE('RFM'[R],CONCATENATE('RFM'[F],'RFM'[M]))

Finally finish to calculate the RFM Score by adding the values form R, F, and M columns as the code show below

RFM_Score = 'RFM'[R]+'RFM'[F]+'RFM'[M]

Result from above DAX codes

RFM Score consist of 3–15 numbers after summation so the next step is to categorize the score so as to divide our customers in three groups for my side used Low-Value , Mid-Value and High-Value customers as Segment labels but you can use yours based on the labels your organization use lets see the Code Below

Segment_label = 
SWITCH(
    TRUE(),
    [RFM_Score] < 5, "Low-Value",
    [RFM_Score] < 9, "Mid-Value",
    "High-Value"
)

then I go further to do RFM customer segment to helps us to partition our customers in well and understandable way see the code below

RFM_Customer_Segments = 
SWITCH(
    TRUE(),
    [RFM_Score] >= 9, "VIP/Loyal",
    [RFM_Score] >= 6 && [RFM_Score] < 9, "Potential Loyal",
    [RFM_Score] >= 5 && [RFM_Score] < 6, "At Risk Customers",
    [RFM_Score] >= 4 && [RFM_Score] < 5, "Can't Lose",
    [RFM_Score] >= 3 && [RFM_Score] < 4, "Lost",
    "Other" -- Optional, if the score falls outside of defined categories
)

The final Result of RFM Model show below with all details the another part is to do data Visualization to presents Insights to business leaders or stakeholder for further decisions

RFM Model in Table view

Data Visualization and Insights

tool : Power BI

RFM Report

Summary of RFM & Customer Retention Report

- Total Number of Customers: 4,413 customers are segmented into three major groups:
— Low-Value: 1,037 customers
— Mid-Value: 1,696 customers
— High-Value:1,680 customers

-Customer Segments Based on RFM Analysis:
— VIP/Loyal Customers: 1,680 customers — These are the most valuable and loyal customers.
— Potential Loyal Customers: 1,233 customers — Customers who show promise to become more loyal.
— Can’t Lose Customers: 531 customers — These customers are on the verge of churning.
— Lost Customers: 506 customers — Customers who have already churned.
— At Risk Customers: 463 customers — Customers at high risk of churning.

Customer Retention Insights:
— The heatmap shows a decline in customer retention month-over-month after the first purchase.
— Highest customer retention occurred in December 2009 with 1,045 new customers.
— A significant drop in retention after the first 2–3 months.

- Customer Retention by Cohort:
— The retention for each cohort significantly drops after the first month, indicating potential gaps in retention strategies post-initial purchase.

Recommendations

1. Focus on Retention Programs for VIP/Loyal Customers:
— Enhance loyalty programs to further engage and retain these high-value customers.
— Personalized offers and exclusive deals to maintain engagement and prevent them from churning.

2. Target Potential Loyal Customers with Upsell/Cross-Sell Strategies:
— Create targeted campaigns to convert these customers into VIPs through loyalty incentives, discounts, and tailored promotions.

3. Re-engage “At Risk” and “Can’t Lose” Customers:
— Design targeted re-engagement strategies such as win-back campaigns, personalized communication, and special offers.
— Analyze their purchasing behavior and identify commonalities in attrition to address root causes.

4. Investigate and Improve Initial Retention Rates:
— Focus on onboarding programs and post-purchase engagement for new customers.
— Consider follow-up marketing efforts within the first 30 days after a customer’s first purchase to increase retention.

5. Revamp the Strategy for “Lost Customers”:
— Though these customers are categorized as “lost,” consider a reactivation campaign with compelling offers to bring them back.
— Utilize feedback mechanisms to understand why they churned and how to improve retention moving forward.

By focusing on these strategies, the company can improve customer retention, maximize customer lifetime value, and optimize marketing efforts to increase overall revenue.