You are currently viewing RFM analysis with Power BI

RFM analysis with Power BI

  • Is there anything you have seen someone do in Power BI and have always wondered how?

A couple of days ago, Olubukunola Akinsola showed the Power BI learning community session on how to perform an RFM analysis using Power BI. 

In this article, you will learn how to quantitatively group and identify your best customers based on their RFM score (recency, frequency, and monetary). It will help you perform better-targeted marketing campaigns and make the best of your advertising budget. By the end of this article, you will be able to perform an RFM analysis with Power BI.

Requirements and availability

Our readers should have a basic understanding of Power BI to get the most out of this course.

What is RFM analysis?

So 80 percent of your business originates from 20% of your consumers, according to a marketing cliché. The Pareto Principle is the name for this.

But who are the 20%? 

RFM stands for Recency, Frequency, and Monetary value. All these are crucial customer traits. Recency deals with retention and acts as a measure for engagement, whereas the frequency and monetary value work as a metric for engagement. 

By performing an RFM analysis, we can categorize our customers based on the:

  1. Recency: when was their last purchase?
  2. Frequency: how engaged are they with our brand, and how often do they purchase from us?
  3. Monetary: who are our heavy spenders, and who are the low-value purchasers? Who should get our discount?

How does RFM analysis work?

Generally, the RFM score is between 1 to 5 is given, with five being the highest. This score will help you find your most valuable customers. However, the score could be adjusted to meet your business needs and goals. It applies in a variety of employment, nonprofit, and business environments.

Segmentation of your customers and clients

For this session, our speaker groups her customer as follows: 

  • About to Sleep: You will lose them if not reactivated.
  • At-Risk: They purchase often but, they have not in a while. 
  • Cannot lose Them: They make big purchases often but for a long time.
  • Champions: Spend the most, bought recently and frequently.
  • Hibernating customers: last purchase was a long time ago but low spenders and order count.
  • Lost Customers: you have lost them.
  • Loyal: response to promotion and buy often.
  • Need Attention: Above your average recency, frequency, and monetary value. 
  • New Customers: First-timer and potential to be loyal.
  • Potential Loyalist: not devoted but have the potential to be.
  • Promising: recent shopper but have not spent so much yet.

Getting Started

Different businesses and organizations can use various tools to segment and analyze, from CRM software to Excel and Power BI. However, in this post, we will be using Power BI for our RFM analysis.  

  1. To get started, we would import our data into Power BI. To learn more about that, you should read this post
  1. Transform your data.
  2. Create your date table and data modeling.
Power BI Date Table
  1. Calculate the recency value: This means your last transaction date.
RFM analysis with Power BI: Recency value
  1. Calculate the frequency value by finding out how many transactions a customer has made.
RFM analysis with Power BI: Frequency value
  1. Calculate the monetary value. Here, we have to take the quantity into consideration.
RFM analysis with Power BI: Monetary value
  1. Summarize your newly created data by creating a RFM table with all these values.
RFM analysis: RFM Table
  1. Using a SWITCH statement, we categorize our customer by creating a recency, frequency, and monetary score. The score varies based on your business and organizations’ needs.
RFM analysis with Power BI: RFM value
  1. Using the score, we create an RFM score.
RFM Score
  1. We match our customer’s RFM score to our predefined segmentation baseline.
Customer segmentation
  1. Visualization

Do check out the video to learn more and understand. 

Also, I am officially inviting you to the Power BI learning community Come and learn from various speakers.


Leave a Reply