What are the three components of the rfm formula?

In this article, we'll take a look at RFM (recency, frequency, monetary value) analysis, which is based on the behavior of customer groups (or segments). This method of analysis allows you to study the behavior of users and how they make payments. As a result, you’ll receive valuable insights for direct marketing.

If you need RFM Analysis or any other special reports according to your business needs and accounting system, a team of OWOX BI analysts can help you. Sign up for a demo to request a meeting and find out the details.

Table of contents

• What is RFM analysis and why does a marketer need it?
• RFM analysis using OWOX BI and Excel
• RFM analysis example using OWOX BI
• Excel RFM analysis algorithm
• How to use RFM analysis in marketing
• Wrapping up

What is RFM analysis and why does a marketer need it?

RFM analysis allows you to segment customers by the frequency and value of purchases and identify those customers who spend the most money.

• Recency – how long it’s been since a customer bought something from you
• Frequency – how often a customer buys from you
• Monetary value – the total value of purchases a customer has made

According to these metrics, it’s possible to divide your customers into groups to understand which customers buy lots of things frequently, which buy few things but frequently, and which haven’t bought anything for a long time.

As a rule, only a small percentage of customers respond to general promotional offers. RFM is an excellent segmentation method for predicting customer responses, improving interactions, and increasing profits. RFM uses customer behavior data to determine how to work with each customer group.

RFM analysis using OWOX BI and Excel

The essence of RFM analysis is to divide customers into groups based on how recently they made their last purchase, how often they buy things, and the average value of their orders. For each of these metrics, we assign customers to one of three groups, which are assigned a number from 1 to 3.

Recency:

• 1 – long-standing customers.
• 2 – relatively recent customers.
• 3 – recent customers.

Frequency:

• 1 – purchases rarely (single orders).
• 2 – purchases infrequently.
• 3 – purchases often.

Monetary value

• 1 – low value of purchases.
• 2 – average value of purchases.
• 3 – high value of purchases.

Customers are assigned RFM values by concatenating their numbers for Recency, Frequency, and Monetary value. For example, customer 111 made one order with a low monetary value a long time ago. Customer 333, on the other hand, often makes large-value orders and made a purchase recently. Customers with 3’s in every category are your best customers.

There are two convenient ways to perform RFM analysis: using OWOX BI and using Excel (or Google Sheets).

RFM analysis example using OWOX BI

Unlike Excel, OWOX BI allows you to automatically calculate RFM.  

The data source for this analysis is a table or view in Google BigQuery with data about each order with the following fields:

• userId – the customer’s user ID in the website database / ERP.
• revenue – revenue from the order.
• created – date of order creation.

Calculating RFM segments

To calculate RFM segments we recommend using data on confirmed orders from your ERP. You can easily export this data to BigQuery using OWOX BI Pipeline.

OWOX BI also gives you the opportunity to customize the importing of RFM analysis results to Google Analytics. This will allow you to:

• Build new custom reports and segments in Google Analytics for more in-depth audience analysis using additional information about users
• Use RFM analysis to create remarketing audiences for Google Ads, email newsletters, and loyalty programs

Setting up the importing of RFM analysis data into Google Analytics from Google BigQuery consists of two steps:

1. Setting up a Google Analytics web resource.
2. Creating a stream in OWOX BI Pipeline.

Let's take a closer look at the last two points. 

Configure the Google Analytics web property

Create Custom Dimensions at the user level to store the results of RFM analysis (Custom Definitions → Custom Dimensions → +New Custom Dimension):

Create a data set to import data into Google Analytics (Data Import → New Data Set). Select the data set type — User data:

Specify the import behavior as Query time. This will allow you to combine imported data with historical data; otherwise, the data will be merged only with hits collected in Google Analytics after downloading the results of the RFM analysis. Note that Query time import is available only to users of Google ​Analytics 360.

Next, name the data set and determine the list of views in which the imported data will be available:​

Finally, define a data schema and save the data set:​

That’s it. You’ve now configured the Google Analytics web property settings for importing data. Now you can proceed to import.

Creating a stream in OWOX BI Pipeline

1. Prepare a SQL query that will return the data in the correct structure.
2. Create a stream in OWOX BI Pipeline as instructed here.

Wait until data appears in Google Analytics:

Everything is ready. OWOX BI will automatically perform RFM analysis without requiring much involvement from you.

Excel RFM analysis algorithm

1. To calculate RFM in Excel, start by downloading transaction data from your CRM: customer name, purchase date, and purchase amount:​
   

1. In order to determine the date of the last purchase for each customer, how many times the customer has bought from you in total, and the total monetary value of all those orders, use a pivot table.
   

1. Since some customers in the CRM may never have bought something, we need to combine all their transactions into one line. To do this, we add the value Client into the Rows.​
   

1. Now we need to count how many orders each customer has placed. To do this, add Purchase Date to the Values (just don’t forget to indicate COUNTA in the Summarize column).​

1. Next, calculate the total value of all orders for each customer. To do this, add Purchase Amount into Values, and in the Summarize by column, indicate SUM.​
   

1. Now we have to calculate the date of the last purchase for each customer. To do this, add Purchase Date to the Values and specify MAX in the graph already known to us.​

That’s it for analysis preparation. Now we need to transfer this data to a new page to calculate RFM values.

1. First, rename the columns as Customer, Last purchase date, Order frequency, Number of orders, and Purchase Amount.​Now we proceed to the main part of the analysis. Using the formula = TODAY () - we calculate how many days ago a customer made their last purchase. This will be the data for our R (recency) value:

1. At this point, we have all the data on Recency, Frequency (number of orders), and Monetary value (amount). Now we’ll assign values from 1 to 3 (according to the criteria mentioned above) based on this data.

Let’s start with the values for Recency. The first thing we need to do is calculate how long 33% and 66% of our customers have bought from us. This is easily done using the formula = PERCENTILE.INC (C2: C12; 0.33) and = PERCENTILE.INC (C2: C12; 0.66). We now know that 33% of customers bought our products less than 61 days ago, and 66% bought more than 93 days ago.​

1. Accordingly we assign those customers who bought less than 61 days ago the highest value of 3. Those who placed an order from 61 to 93 days ago are assigned a value of 2. The rest, who bought more than 93 days ago, we assign a value of 1. All this can be automatically calculated with the formula = IF (C2 <61; 3; IF (C2 <93; 2; 1)).​

1. Using the same principle, we calculate the values for F (Frequency) and M (Monetary value): first, we calculate how many times 33% and 66% of customers made a purchase and, based on the results, assign values from 1 to 3.​

1. For greater clarity, we calculate the RFM value by concatenating all three values for each customer. We can do that using the formula = D2 * 100 + F2 * 10 + H2.​
   

That’s it. We’ve made all the necessary calculations for RFM analysis in Google Sheets (or Excel). Bear in mind that although these formulas help to automate some miscalculations, you still have to spend a lot of time calculating RFM.

But if you value your time and the time of your employees, or if you have a large database, we recommend performing RFM analysis using OWOX BI. If you aren’t an OWOX BI user yet, you can try all the functionality for free.

How to use RFM analysis in marketing

When all the calculations are ready and you’ve segmented your customers, it’s time to move on to the marketing part.

By grouping customers by RFM values, you can immediately get a complete picture of what’s happening with your customer base. Let’s look at examples of some client groups.

Group 3R-3F-3M – the most active, buy often

These are your ideal customers. It’s possible to expand your engagement with them by launching a loyalty program, inviting them to special events, or asking them about how they would want the company to develop. It’s important to show these customers that they’re respected and welcome users.

Group 1R-1F-1M – the least active, bought once / never, spent little

Although these customers seem to be the least promising, you shouldn’t write them off completely: they showed interest in your products at least once. Most often, marketers prepare special provocative messages to divide these customers into “definitely disinterested in the product” and “promising.” Promising customers can be transferred to the next category.

1 in one of the categories

Some of your customers may have a value of 122 (lame Recency). This segment should be given a little time to decide about returning to you. Try to offer them products that are usually bought along with those that they purchased earlier in order to arouse a renewed interest in your company.

3 in one of the categories

These users are a promising segment for your research. They’re consistent enough for you to experiment and find a suitable way to raise their other indicators.

Useful materials to help you master the topic:

Wrapping up

The RFM methodology is far from absolute, but it’s an extremely useful tool for analyzing your customer base. With just a little bit of work, you’ll see how to take an individual approach to your customers.

At the same time, keep in mind that data is influenced by seasons, promotions, and holidays. If a customer with an extensive purchase history for the current month doesn’t buy anything next month, this doesn’t mean you should immediately transfer them to another segment. Perhaps this is just the effect of seasonality, and after a while, they’ll resume purchasing.

Which are the three factors considered in an RFM analysis?

What are the steps of RFM analysis?

What is RFM calculation?

What are the parameters used in RFM analysis?