RFM analysis increases eCommerce sales. Companies able to use data to better understand how customer's think, feel, and most importantly, make purchasing decisions, have an advantage.
RFM Analysis enables personalized marketing, increases engagement, and allows you to create specific, relevant offers to the right groups of customers. Further, it is one of the best ways to maximize cohort analysis to increase retention.
This post explores the benefits of RFM analysis, shares step by step instructions on how to perform RFM analysis in Excel, and finally shares effective RFM segments to maximize ROI.
What is RFM Analysis? A definition and context.
The idea is to segment customers based on when their last purchase was, how often they’ve purchased in the past, and how much they’ve spent overall. All three of these measures have proven to be effective predictors of a customer's willingness to engage in marketing messages and offers.
While RFM analysis was born in direct-mail, it is a powerful tool for eCommerce stores to use today. As Ogilvy & Mathers shares in their Red Paper, Learning to Read the River
Benefits of RFM Analysis
Conducting an RFM analysis on your customer base and sending personalized campaigns to high value targets has massive benefits for your eCommerce store.
How to Create a RFM Model in Excel?
Segmenting customers with a RFM analysis doesn't have to be overly complicated. Below we will show how you can create a RFM model in Excel. We go step by step, and include screenshots so you can easily replicate the model.
Keep in mind that your eCommerce brand is unique.
This isn't going to be the best way for your particular business, but it will be a great template for you to iterate on.
Automate Your RFM Analysis:
Barilliance connects your offline and online customer data. You can define as many segments as you like, automatically enroll customers based on their actions, and trigger any number of marketing campaigns. Learn more here.
Step 1: Set yourself up right
Before you can begin, you need to define the one KPI that matters most to your business for each segmentation vector: recency, frequency, and monetization.
To do this, you will have to connect your purchase history to each customer, and select a timeframe that you want to work with.
For our example, we are going to use the following KPI's and timeframe.
Recency: Date of Last of Purchase
Frequency: Total Number of Orders
Monetization: Average Order Value
Timeframe: 2 years
As you will see, RFM analysis is a straight forward process. The goal is to systematically score each customer according to recency, frequency, and monetization. We do that by first rank ordering all customers across our chosen metric, and then scoring them based on how well they perform relative to the other customers in your database.
Step 2: Increase Response with Recency
There are a number of KPI's you can use for Recency. Example KPIs include
For this walk through, we are going to use days from last purchase as the primary metric.
You should intuitively know which metric makes the most sense for your business. Chances are if you are a traditional eCommerce store, date of last purchase will be your metric of choice also.
Step 2.a : Import your data
First, we want to download your client information with your specified KPI's. Here, we simply downloaded this information directly from Barilliance, and uploaded it into a Google Sheet.
Next, we want to clean up the sheet a bit.
I delete some unwanted columns I forgot to close out: Sessions, Last Seen, First Seen, First Ordered, and AOV.
Next, you are going to add three columns for your RFM scores. Title them across the top "Recency", "Frequency", and "Monetization".
Finally, I apply a filter on the data to make it very easy to sort across. If you don't know how to apply a filter the process is easy. Select all of your data including your headings (you can do this quickly by holding down shift+command+arrows).
Once all of your data is selected, click data->filter.
After you are done, your sheet should look something like this.
Step 2.b : Sort your customers according to Recency KPI
Navigate over to your Recency KPI, and sort the list in the appropriate order. In our case, we go to "Days from last purchase" and sort in descending.
Step 2.c : Score each customer according to position
Finally, score each customer according to their position.
While there a few methodologies for doing this, I've found the easiest and most helpful to be with quartiles.
Take your total number of customers divided by four. Next, give each quartile a score reflective of the position.
First Quartile: 1
Second Quartile: 2
Third Quartile: 3
Fourth Quartile: 4
Don't worry too much about getting everything "perfect". For example, here we have two customers who have both ordered in the last three days. I simply assigned them both a recency score of 1, even though that puts 5 customers with a score of 1 and three with the score of 2.
At this point, your sheet should look similar to the one below.
Step 3: Increase Conversions with Frequency
The process is very similar for both Frequency and Monetization.
While we are using the total number of orders over the past two years as our Frequency KPI, there are a number of competing metrics you can select. Some of them include:
As mentioned before, we are going to be using # of orders as our Frequency KPI. Repeat steps 2.b and 2.c, expect use your Frequency KPI as your guiding metric. After scoring, your sheet should look similar to the one below.
Step 3: Increase AOV with Monetization
Finally, you are ready to knock out the Monetization scores.
Monetization KPI's include
As before, repeat steps 2.b and 2.c, expect use your Monetization KPI as your guiding metric. After scoring, your sheet should look similar to the one below.
RFM Segment Examples: Segments That Make Sales
At this point you have the analysis done. Next comes the fun part - actually using these newfound insights to identify profitable segments.
There are many ways marketers have used this segmentation to help guide their marketing. Here are a few ideas to cherry pick from.
Core - Your Best Customers
RFM Score: 111
Who They Are: Highly engaged customers who have bought the most recent, the most often, and generated the most revenue.
Marketing Strategies: Focus on loyalty programs and new product introductions. These customers have proven to have a higher willingness to pay, so don't use discount pricing to generate incremental sales. Instead, focus on value added offers through product recommendations based on previous purchases.
Loyal - Your Most Loyal Customers
RFM Score: X1X
Who They Are: Customers who buy the most often from your store.
Marketing Strategies: Loyalty programs are effective for these repeat visitors. Advocacy programs and reviews are also common X1X strategies. Lastly, consider rewarding these customers with Free Shipping or other like benefits.
Whales - Your Highest Paying Customers
RFM Score: XX1
Who They Are: Customers who have generated the most revenue for your store.
Marketing Strategies: These customers have demonstrated a high willingness to pay. Consider premium offers, subscription tiers, luxury products, or value add cross/up-sells to increase AOV. Don't waste margin on discounts.
Promising - Faithful customers
RFM Score: X13, X14
Who They Are: Customers who return often, but do not spend a lot.
Marketing Strategies: You've already succeeded in creating loyalty. Focus on increasing monetization through product recommendations based on past purchases and incentives tied to spending thresholds (pegged to your store AOV).
Rookies - Your Newest Customers
RFM Score: 14X
Who They Are: First time buyers on your site.
Marketing Strategies: Most customers never graduate to loyal. Having clear strategies in place for first time buyers such as triggered welcome emails will pay dividends.
Slipping - Once Loyal, Now Gone
RFM Score: 44X
Who They Are: Great past customers who haven't bought in awhile.
Marketing Strategies: Customers leave for a variety of reasons. Depending on your situation price deals, new product launches, or other retention strategies.
Segmenting empowers personalized, high performing campaigns and preserves profit margin. RFM Analysis provides a rubric to score each customer, and identify high ROI segments.
How Barilliance Enables RFM Analysis
1. Automated multivariate AB test suite
Segmenting your customer base is not enough. Effective database marketing depends on testing. David Ogilvy sums it up wonderfully in this clip:
Barilliance empowers you to not only create personalized experiences for each RFM segment. It also gives you the ability to create multivariate ab tests.
You can quickly see which offers resonate most with a given segment, discover what content leads to sales, and more.
2. Connecting your data: a full 360 view of your customers
One of the significant challenges with RFM Analysis (and segmentation in general) is creating a full, 360 degree view of a customer.
The truth is, today's omnichannel shopper isn't much different than the direct mail world RFM was born from. RFM is still an incredibly powerful model.
But it's effectiveness is determined by the quality of the data you have.
This is where Barilliance shines. It creates a holistic customer view, across devices, shopping sessions, and channels. You can see which pages customers interact with, time since last purchase, order volume, brand engagement and more. In other words, you have the ability to create highly detailed segments, including all the ones we talked about above, through one simple interface.
RFM is an established, clear way to get more out of your client list.
If you want to take RFM to the next level you need to consider Barilliance's Retention. It improves traditional RFM analysis in two fundamental ways.
First, it connects your online and offline purchases, web behavior, and demographic data in one place - allowing you to make more accurate segmentations.
Second, it lets you automate campaigns to defined segments. It connects to Web Personalization and individual email product recommendations to customize each interaction not at the segment level, but at the individual level.
Request a demo for Retention here.