RFM Analysis: Learn more about your customers and RFM Segmentation

In this article (Updated in August 2024), 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 marketers to segment existing customers based on specific behaviors, leading to more targeted messaging and offers.

RFM analysis approach not only increases response rates and customer retention but also enhances customer satisfaction and lifetime value by predicting future buying behavior and tailoring strategies to maximize engagement with different customer 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.

Imagine you run a store, and you want to figure out who your best customers are. Instead of getting lost in a sea of data, RFM helps you categorize customers based on when they last shopped with you (that’s the “Recency“), how often they shop (that’s the “Frequency“), and how much they tend to spend when they do (and that’s the “Monetary“ part).

By leveraging customer data, RFM enhances the accuracy and granularity of customer segmentation and marketing strategies. You can give a little extra love to your top customers and maybe nudge those who haven’t been around in a while. It’s a neat way to make sense of your customer base!

Let’s dive into what R-F-M stands for a bit deeper and with more context.

Recency: It’s all about the ‘when‘. How long has it been since a customer made a purchase from you? If someone just bought something yesterday, they’re probably more engaged with your brand than someone who hasn’t shopped in a year. It’s like remembering who came to your last party and who didn’t. The ones who showed up recently are probably your closer buddies.

Frequency: Now, let’s talk about the ‘how often‘. Some folks might pop into your store (or online site) regularly, like those coffee addicts who need their daily fix. Others might just drop by occasionally. Knowing how often someone shops with you can give you a good idea about their loyalty and how integral you are to their routine.

Monetary: Lastly, it’s about the ‘how much‘. This tells you about the buying power or spending habits of your customers. Some might be big buyers, always going for the premium items, while others might be more on the budget-friendly side.

By understanding how much they typically spend, you can tailor your offers or promotions to suit their pocket. Understanding these aspects can significantly boost customer satisfaction by aligning communication and offers with the specific needs and behaviors of different customer groups.

By combining these three aspects, RFM Analysis helps businesses categorize their customers into different segments. For instance, a customer who scores high on all three aspects is a golden one — they shop frequently, they’ve shopped recently, and they spend a good amount. These are the buyers you’d want to roll out the red carpet for!

On the flip side, if someone hasn’t shopped in ages, only comes by once in a lifetime, and barely spends anything, they might need a bit more incentive or engagement to get them back on track and increase their score.

All in all, RFM Analysis is a brilliant way to personalize your marketing efforts. Instead of treating all customers the same, you can understand their habits and preferences better and tailor your approach to match. It’s like having a cheat sheet for building stronger, more meaningful relationships with your customers and growing sales!

RFM Analysis is a powerful tool for segmenting customers based on their purchasing behavior, using Recency, Frequency, and Monetary value. This segmentation enables businesses to tailor personalized marketing campaigns, making it a game-changer for both established companies with vast data and startups looking to optimize their strategies.

RFM analysis supports direct mail marketing by segmenting customers into clusters, allowing for targeted advertising strategies tailored to specific customer profiles.

At its core, business is all about relationships. Think of RFM Analysis as a magnifying glass, helping you zoom in on what your customers truly want.

By understanding how recent, frequent, and how much they spend, you’re essentially learning their buying behavior. This knowledge is power - it’s the difference between guessing what your customers might like and actually knowing it.

Moreover, understanding customer behavior through RFM analysis can help predict future customer behavior, aiding in crafting effective marketing strategies.

Imagine throwing a party. Would you serve the same drinks to everybody? Probably not, right? Similarly, RFM allows you to tailor your marketing campaigns.

Loyal customers are key contributors to a business's success, and RFM analysis can help in offering personalized messaging to make loyal customers feel valued and engaged.

Instead of sending generic promotions to your entire customer base, you can craft targeted promotions and special offers that resonate with specific customer segments. This increases engagement and, more importantly, sales.

Let’s face it, in business, resources - whether it’s time or money - are always limited. RFM Analysis is like your savvy business advisor, guiding you on where to invest. Instead of spreading your efforts thinly across all customers and praying for success, focus on the ones who bring the most value, ensuring a better return on investment.

Identifying the most valuable customers through RFM analysis can help businesses allocate resources more effectively, ensuring a better return on investment.

The beauty of RFM is that it’s not just retrospective; it has a crystal ball quality. By analyzing past behaviors, you can predict future ones. This means you can anticipate needs, preferences, and potential churn, putting you a step ahead in addressing them.

By predicting future customer behavior, RFM analysis helps businesses identify and understand their most valuable customers based on past purchasing patterns. Predictive modeling helps businesses forecast the customers’ future actions, aiding in optimal strategy formation for marketing, sales, and customer management efforts.

*the quality of your reports always depends on the quality of data collected.

RFM Analysis helps build lasting relationships by understanding and catering to customers’ habits and preferences, turning one-time shoppers into loyal advocates. It’s not just about data; it’s about connecting with customers on a deeper level. RFM analysis helps businesses identify their most and least valuable customers, allowing for more effective engagement and retention strategies.

If you want to be a brand that’s loved, RFM should be in your strategy. Ready to get started? Next, we’ll guide you step-by-step through RFM Analysis using tools like Google Sheets. Let’s dive in!

When it comes to data analytics tasks, it all starts from the Plan: you define the Business Goals, the Questions you're supposed to be asking and the Metrics you need to measure.

In our case, we already have all that information. We want to improve the revenue from the existing list by segmenting the uses, so we can create more targeted campaigns. Our questions and the metrics are related to the whole R-F-M framework.

RFM analysis helps businesses segment customers based on their transaction behaviors, enabling personalized marketing campaigns.

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 has recently made a purchase. Customers with 3’s in every category are your best customers.

Now we need to Collect the data. We start by gathering the necessary data about your customers.

You will need data on customer transactions (orders), including the date of the transaction, the customer ID, and the monetary value of the transaction (order amount). This data can typically be obtained from your CRM, ERP or database.

It is crucial to collect data on new customers and distinguish marketing strategies between new and existing customers.

With purchasing behavior data collected, the next move is to prepare our data for RFM analysis. We need to make our raw data analysis ready.

Let us assume you have a table of customer transactions.

• In Column A, you have the Customer Identification - like userid,
• Column B - Date of Transaction,
• Column C - Order Value

To calculate the recency in Google Sheets or Microsoft Excel, first, we need to find the latest transaction date for each of the customers. Let's create a new column and call it DaysSinceTransaction

The formula would be =TODAY()-B2. Here, 'TODAY()' returns the current date, and 'B2' refers to the cell containing the date of the last transaction for a particular transaction. The result will be the number of days since the transaction.

We've successfully gathered all the transactions and their corresponding customer IDs. Plus, we've also determined the number of days that have passed since each transaction.

Given that our database contains a list of multiple transactions for individual customers, it's important to select only the most recent transaction date for each customer when calculating Recency.

So how do you pinpoint the latest transaction date after establishing the number of days passed since each transaction?

Let's select the data range, click Insert -> Pivot Table

Add Customer ID as Rows

Add DaysSinceTransaction as a Value. And summarize by MIN

This would help us achieve us a smaller dataset with a single, latest transaction date per customer.

When the aforementioned steps are done correctly, Google Sheets will then generate a new pivot array. This dataset solely consists of rows showcasing each unique customer ID in tandem with their most recent transaction dates.

Next, add the Customer ID summarized by COUNTA to Values now to save our time later.

Let's also calculate the total value of all orders for each customer. To do this, add Order Value as a Value to get the total amount spent per customer, and in the Summarize by column, indicate SUM.​

That's it for analysis preparation.

Now we need to copy this pivot table data to a new sheet to calculate RFM values and rename the columns to Recency, Frequency and Monetary.

In SQL, you'd typically utilize the DATEDIFF function to get the DaysSinceTransaction

Assuming your table of customer transactions contains a column named 'DateOfTransaction', the SQL query would be:

This query returns a list of customers together with the transactions and the number of days since their last transaction.

You can utilize SQL to fine-tune your data. It allows you to pick the most recent date for each customer by grouping the transactions by customer ID and using the MIN function. This SQL function gives you the lowest value in the selected column, in this case, the least number of days since the last transaction.

Your SQL command may look like this:

After running this command, you'll have a list with each customer's most recent transaction identificator, which aligns perfectly with the recency aspect of RFM Analysis.

Once you've gathered the necessary data, you can pivot or group the 'customer_id' by the respective order value with SQL, helping you calculate the monetary value and frequency components of RFM. Start by running a simple query for this.

Here's a substance to illustrate:

This SQL statement does two things. It first groups all orders that belong to each customer, represented by the 'customer_id'. Following that, the total order value and the number of orders for each customer are calculated.

The 'SUM(order_value)' expression calculates the total spent by each customer, thus representing the 'Monetary' part of RFM.

By applying 'COUNT(*)', the query counts the number of rows for each customer, thus representing the 'Frequency' part of RFM.

You can run this SQL query in typical SQL environments like Google BigQuery, Postgres, SQL Server, or OWOX BI and it should produce a list of your customers alongside their total purchase value and frequency of transactions.

Once you have the data, the next step is to assign RFM scores to each customer. This involves going through the data and categorizing each customer based on recency, frequency, and monetary (RFM) value.

The Recency score represents how recently a customer made a purchase, with a higher score indicating a more recent purchase.

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 ([range]; 0.33) and = PERCENTILE.INC ([range]; 0.66)

We now know that 33% of customers bought our products less than 11.28 days ago, and 66% bought less than 14.28 days ago.​

• Accordingly, we assign the highest value to those customers who bought less than 11.28 days ago, which is 3.
• Those who placed an order from 11.28 to 14.28 days ago are assigned a value of 2.
• For the rest, who bought more than 14.28 days ago, we assign a value of 1.

All this can be automatically calculated with the formula =IF(B2<11.28; 3; IF(B2 <14.28; 2, 1)).

The Frequency score measures how often a customer makes purchases, with a higher score indicating more frequent purchases.

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

The Monetary score reflects the total value of a customer's purchases, with a higher score indicating higher spending.

Using the same principle, we calculate the values for M (Monetary Value): first, we calculate how much 33% and 66% of our customers spent and, based on the results, assign values from 1 to 3.​

Once you have the R, F, and M values, you need to calculate the RFM score for each customer. We concatenate all three values for each customer. We can do that using the formula =E2*100+F2* 10+G2.​

That's it. We've made all the necessary calculations for RFM analysis in Google Sheets (or Excel).

All you have to do now is sort the range by RFM Score and plan how to engage with those groups or segments based on priorities. Look for patterns and trends within each segment to understand their behavior and preferences. Tailor your marketing strategies and offerings to better serve each segment.

Now you can analyze each segment to gain insights.

Look for patterns and trends within each segment to understand their behavior and preferences. Tailor your marketing strategies and offerings to better serve each segment.

Bear in mind, that although these formulas help to automate some calculations, you still have to spend a lot of time calculating RFM score manually. Especially if you have a larger data set of hundreds, thousands, or even millions of transactions over time.

Based on the insights gained from the RFM analysis, take action to improve customer engagement and retention. Develop targeted marketing campaigns for each segment, offer personalized promotions, and provide exceptional customer service to enhance the overall customer experience.

Remember, RFM analysis is an ongoing process, so regularly update your data and repeat the steps to keep refining your customer segmentation and strategies.

I hope this step-by-step guide helps you understand how to perform RFM analysis and leverage it to gain valuable insights about your customers!

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.

• How to Measure the Effectiveness of Online Advertising
• Most popular e-commerce reports
• Cost Analysis in Google Analytics

• RFM (Recency, Frequency, and Monetary value) analysis is an essential business tool that helps understand customer behavior and identify high-value patrons and apply individual approach to your customers.
• This technique is beneficial for better understanding customers, tailoring marketing efforts, allocating money and effort accurately, predicting future buying behavior, and fostering customer relationships.
• Performing an RFM analysis involves a sequence of steps including data collection, data preparation, calculation of recency, frequency, and monetary value, and finally calculating the RFM score. This process can be facilitated through common tools such as Google Sheets, Excel, or using SQL.
• RFM analysis aids in uncovering high-value customers, which helps optimize marketing strategies and increase profitability.
• The Recency element of this analysis focuses on buyer habits and tendencies, while the Monetary value aspect highlights the direct financial impact of customers on your business. This comprehensive view enables a more targeted approach to customer management and marketing.
• At the same time, keep in mind that seasons, promotions, and holidays influence data. 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; after a while, they'll resume purchasing.