How to do an RFM analysis for e-commerce

Stop having one-night stands with your customers. Marry the best ones with an RFM analysis of your e-commerce customer database.

“But wait, George. So, you mean our order data is not just about making sure we send the right products to the right address?”

Some of the things you’ll learn:

  • What is this “RFM analysis” I’m constantly harping about – what does RFM mean, and why RFM in that order?
  • Why every e-commerce marketer should use or at least know about the RFM analysis.
  • How to do an RFM analysis in excel, scoring your customers from best to worst (the ‘proper’ way).
  • How to apply RFM concepts in Google Analytics without customer scoring (slightly less ‘proper’ but still useful and much faster).
  • What to do after you figure out your customers’ RFM scores.
  • An intro to customer segmentation and labeling. 
  • Sample strategies for the customer segments you’ve identified.
  • Some thoughts on the limits of an RFM analysis and how to get around them.

An RFM analysis of your e-commerce customer database is the best way to stop having one-night stands with your customers. 

Shocking I know. 

Here’s what I mean. Most e-commerce entrepreneurs have a vague understanding of their customers. And by extension, the marketing they do is vague too. Spray and pray. The one-night stand approach. 

However, as I explained in my ultimate e-commerce marketing resource, sustainable e-commerce growth comes through a shift from a customer acquisition focus to a customer retention focus. And there’s no way in hell that you will retain (or monetize) any of your customers if you don’t deeply understand them. 

This is where RFM comes in. An RFM analysis is the simplest, most elegant, and easiest way to understand who your customers are. RFM is how you shift through the bad apples to find, nurture, and keep in touch with your best customers. It’s the marriage approach.

In this short guide, I’ll walk you through everything you need to know to start benefiting from RFM. 

But first, let me know if any of this sounds familiar.

Have you ever thought of one or more of the following questions?

Out of all your customers, why do some people spend more than others? What’s different about the customers that spend the most versus those people that only spend little? What makes some people only buy once? What makes some people come back and buy again, and again, and again? 

How should you prioritize your marketing activities? How should you segment your newsletters and advertising campaigns? How many customers are you about to lose, and when will you lose them? What could you do to retain them? Who are the customers that you’ve already lost? When should you stop marketing to them?

On top of that, have you ever wished, secretly or publicly, that you would have more multi-buyers than one-time buyers in your customer pool? Have you ever wondered if there’s a way to condition people to buy more, in larger quantities, and more often than average?

Have you ever wished your marketing performance, dollar-to-dollar, would be better?

Every single one of these questions can be answered directly or indirectly through an RFM analysis.

Here’s what RFM is. 

RFM is a criminally underused method of analyzing customer value. The methodology has origins in old-school database marketing, way before the internet, in the 1930s and 1940s.

RFM stands for Recency, Frequency, and Monetary value.

Recency (R) means, “How recently has a customer made a purchase?” Customers who’ve purchased more recently are far more likely to purchase again than people who made a purchase a long time ago.

Frequency (F) means, “How often has a customer made a purchase?” Similarly, people that buy frequently are far more likely to buy again than people who purchase less frequently or only once.

Monetary Value (M) means, “How much has the customer spent?” The higher the total spend in a customer’s lifetime, the higher the probability that they will purchase again in the future when compared to customers who have spent less.

There’s a way to simplify the framework

I’ll explain why and how to do so right below, in the Google Analytics sections of the guide. If you decide to apply the simplified framework, just remember that you should think of R and F as follows. 

Recency: “Days since last visit.”

Frequency: “Number of purchases.”

These three metrics make up an extraordinarily useful framework of segmenting a customer database and understanding customer value. These three metrics are also ordered by importance. This means that recency is more important than frequency, and both are more important than monetary value. 

Here’s how RFM works. We split our customer database into quintiles and rank them on these metrics based on their behavior. A quintile is one of five equal parts (just like a percent is one of a hundred equal parts). Therefore, every customer gets a score from one to five on recency, frequency, and monetary value. Five is the highest and one is the lowest.

For example, a customer with an R score of 5, an F score of 5, and an M score of 5 belongs to our highest-value customer segment. This is the type of person that last purchased recently, has historically purchased frequently, and has spent a lot of money with us. This is the type of person we should be acquiring through our marketing.

A customer with an R score of 1, an F score of 1, and an M score of 1 is the exact opposite. A low-value customer. They are the type of customer who last bought a long time ago, only bought once, and overall spent very little. We should not be spending our marketing budget to acquire this type of customer.

Does this make sense?

Why RFM is great for e-commerce marketers.

I am a big fan of RFM because it’s simple yet has profound implications. Here are a few ways how an RFM customer scoring analysis could benefit an e-commerce company of any size.

Higher marketing productivity. If we figure out who our highest-value segments are, we can determine how we acquired them. Through what channels and what ads. We can likewise figure out what channels are creating lower value customers for us. 

This means we can reinvest our marketing budget into acquiring more high-value buyers and less low-value buyers. So our profitability, dollar-to-dollar, will increase. Our marketing instantly becomes more productive. We waste less and earn more. 

Higher retention rates. RFM allows us to understand how our customers behave. It also allows us to figure out the points during which they’re most engaged and receptive to repurchasing. This means we can create automated systems designed to condition ‘high-value behavior’ into new buyers. 

Knowing when to incentivize a second purchase from our first-time buyers becomes obvious—knowing when to time an automated win-back sequence to declining customers is simple. In other words, we are in a better position to monetize our current customers and all new customers we acquire. 

More relevant marketing. When we figure out who our highest value buyers are, we are in the perfect position to re-orient our company to serve them better. This means we can expand our product lines more effectively. We can make our advertising more efficient and relevant.

Ultimately, and RFM analysis can give us the knowledge we need to market to people that will appreciate it more—because we market to people who signaled they’re ready to be sold. RFM can help us strengthen our product launches, tighten up any acquisition channel’s performance, increase engagement, reduce our costs, and ultimately increase our profitability dramatically. 

And if you are not excited at this point, you’re probably not paying attention. But let’s jump right into the nitty-gritty. 

Here’s how you can conduct an RFM analysis. 

How to conduct an RFM analysis in excel (or google sheets).

The beauty of RFM is that you don’t need fancy or paid analytics tools to conduct an analysis. A simple spreadsheet will suffice. To get started, we only need to export our customer file in a .csv format and open it in Excel or Google Sheets. We can find this in our e-commerce solution’s customer/order database.

Before we do any analysis, we need to clean our data. So we need to remove irrelevant stuff like fraudulent orders, non-customers, or outliers from our dataset.

Now we need to add 6 additional columns to our spreadsheet. From left to right, let’s label them as follows, “Last Purchase Date,” “R,” “# of Purchases,” “F,” “Total Spend,”and  “M.”

We now need to aggregate our order data under a single record per customer. To do so, let’s use the customer’s email address. So if the same email address has placed three orders, we should sum these into one record and mark the “# of Purchases” as 3. If a customer has only placed one order, we should fill the column “# of Purchases” with the number 1. 

In the “Last Purchase Date” column, we should add the date of the last purchase of each record. And in the “Total Spend” column, let’s just sum the monetary value of all the orders a customer has made. 

We’re almost done preparing our spreadsheet. To calculate the RFM scores, we need to sort the columns “Last purchase date,” “# of Purchases,” and “Total Spend” from highest to lowest, one by one, and break our database into quintiles, meaning 5 equal groups. 

Keep in mind that when it comes to the “Last Purchase Date” and R (recency) scoring, the most recent dates are considered best, so those should get a 5, whereas the oldest dates should get a 1.

So, if we have a record of 5000 customers total, and sort the “# of Purchases” column from highest to lowest, then the top 1000 will get an F (frequency) score of 5. The next 1000 will get an F score of 4, and so forth, until the bottom 1000 get an F score of 1. 

We have one more step before we finish our RFM analysis. Let’s add an extra column, and let’s label it as “RFM score.” Now let’s just concatenate the numbers of the R, F, and M columns and add them to the “RFM Score” column. The final outcome should be a combination of our three metrics. For example, 555, 554, 545, 544, 543, and so forth. 

Now let’s pat ourselves on the back because we did it. We segmented our customers through RFM – which is the starting point for amazing things to come!

Quick and dirty alternative: Simplified RFM analysis segments on Google Analytics Analytics.

Before we get into how to profit from RFM segments, I want to make this short section for slightly less spreadsheet-savvy marketers.

What if the previous section flew over your head? Is there a way for you to still get the benefits of an RFM analysis? Sure. Though it is simplified and less vigorous, we can create RFM inspired segments straight from Google Analytics.

As I mentioned earlier in the green help box, if we opt for this method, then we should define Recency as “days since last visit” and Frequency as “number of purchases.” So, we abandon the quintile scoring method. 

In this approach, an F2 segment means “these customers have made two purchases.” An R10 segment means these customers visited our website within the last 10 days. Does this make sense?

So, here’s how to apply this to your Google Analytics. First, we will go to Audience > Overview and add a “Made a Purchase” segment. That’s our total number of buyers, or, in other words, our “customer database.”

Let’s create Recency segments. We need a new custom segment. We need to go to behavior, conditions (under “Advanced”), filter for “Users”, select “Session Date”, then select “is on or after.” Now we add the appropriate period for our product types. If we choose the last 30 days, we have an R30 recency segment, meaning people who visited our website in the previous 30 days. We can also create R7, R14, R60 segments, and so forth. 

Frequency segments are straightforward. We need to create custom behavior-based segments, and we need to manipulate the parameter “Transactions per user.” So an F1 segment is a segment that’s set as “Transactions per user = 1.” We can easily create F2, F3, or F1+ to define our multi-buyers. 

Monetary value segments. To create M segments, we first need to go to Conversions > E-commerce > Overview. Let’s pick our AOV. AOV means Average Order Value. So we can very easily create two extra segments, one called AOV+ and one called AOV-. We will set revenue-based parameters. AOV+ will be set as “> [Store-AOV”] and AOV- will be set as “< [“Store-AOV”].

Does this make sense?

What we’ve accomplished so far and what we should do next.

Okay, so here’s what we’ve done so far. I’ve given you two methodologies to segment your customer base.

If you used the spreadsheet methodology, you have a list of customer segments, labeled with three digits that range from 1 to 5. So, you have a 555 segment and a 111 segment, plus everything in between.

If you used the Google Analytics methodology, you have F segments (F1, F2, F3, F1+, etc.), R segments (R7, R14, R30, R60), and three M segments (AOV+, AOV, and AOV-).

This means that we have, one way or another, defined or are in a position to define our highest-value buyers. Meaning our 555s or, in other words, the people that have bought more than once, spent a lot, and last bought recently.

But that’s not all we’ve accomplished.

We’ve identified a segment of loyal customers, meaning people who have spent a good amount of money and frequently but haven’t purchased recently or with top tier frequency. These people could be conditioned to slide into our highest-value buyer segment.

We’ve identified a segment of potential loyal customers, meaning customers who’ve spent more than average (AOV+) and bought more than once. These people could be conditioned to enter our loyal customer segment.

We’ve also identified recent customers (high recency) – meaning customers who bought recently. Remember that recency is the most powerful predictor of purchase behavior. So, our most recent customers are the most receptive to new offers.

Let’s look at the opposite side of the spectrum. We’ve also defined, or are in a position to define, the least valuable segments. Starting from the lost segment – people that haven’t spent a lot, haven’t bought many times, and last bought months ago.

Moving on to the at-risk segment – people that used to spend a lot, used to buy frequently, but haven’t bought anything recently. This is a segment that we need to reactivate.

And of course, a needs attention segment, including people who buy often, spend more than average, but have purchased less recently than average.

There are many other segments we can create, and of course, how we name them is up to us, but the key takeaway here is the following.

Each of the segments we create with RFM deserves its own set of marketing activities.

What we’ve accomplished so far has monstrous implications. For example, we can use the above segmentation to supercharge our conversion rates. 

According to the MECLABS conversion heuristic, the strongest lever we can use to boost conversion rate is user motivation. This is also consistent with BJ Fogg’s Behavior Model research at Stanford University. 

There’s no better way to increase a customer’s motivation than to increase our marketing’s relevance to them. And there’s no better way to be relevant than to be timely. Barring advanced data-science models, the RFM analysis model is the straightest path to making sure our communication timing is appropriate.

Here’s a simple application of the behavioral patterns we uncover. If, for example, we identify that our multi-buyers tend to place their second purchase after 15 days pass since their first purchase, how about we time a full-margin (no offer, no discount) email at the 16th day, to stimulate multi-buyer behavior in one-time buyers? How about we follow up with a series of automated emails, offering that product, but with an additional, progressive incentive?

But that’s just the surface. How about we solicit testimonials from our highest-value customers? How about we survey them or ask them for feedback? How about we sign them up in a loyalty or affiliate program?

How about we create specific offers and incentives, sent to the people who demonstrate “at-risk” or “needs attention” behavior, so we can bring them back before they cross over the edge?

There are so many possibilities. 

Okay, so is RFM analysis the best thing since sliced bread?

If this is the first time you’ve been exposed to these concepts, RFM may sound incredible. Nothing sort of marketing math wizardry. And frankly, it is, for the most part. But it has some inherent limitations. 

The primary limitation is that RFM is not extremely helpful in analyzing the 111 customers. Meaning the customers who haven’t bought recently, have only bought once, and haven’t spent all that much. 

The RFM argument is that we should be building our business to best serve our 555s, spending as little time as we can with 111s. And it’s a solid argument. For the most part, we should be focusing on our best customers. But what do you do with the 111s? 

According to RFM logic, we should either trim them off our database or deprioritize them in our marketing. But should we? Are 111s really as homogenous as RFM would have us to believe? 

I don’t think so. There are two additional variables we can use to analyze 111s better. One is postal code information. We can extrapolate information on income, occupation, and more based on a customer’s community’s economics. The question we need to answer is, “Where do they live, and how does this matter?” 

The second type of variable is transactional data. Through what channels did we acquire them? What product did they purchase? What size? What color? What is the use case? What was the price point? 

We can classify the 111s based on these variables, segment them into smaller groups, and unearth useful information in the process.  

Let's bring RFM back in vogue.

At this point, I’ve hopefully convinced you that RFM analysis is a fantastic tool for any e-commerce company (and not only). 

RFM helps us understand how our customers behave, which helps us make our marketing more productive, retain more customers, and, ultimately, create timely and relevant communications. 

If this is new to you, the best way to get familiar with these concepts is to dive into your Google Analytics and start creating R, F, and M segments. Then start asking questions. How do these high-value segments compare to the average visitor? What products do they buy? What channels do they come from? What demographics do they have?

I hope you found this post as useful as I found it entertaining to write!

If you liked this post, then you should help get it in front of more people by cliking one of the buttons below.