Analysis

Calculate RFM Score in Excel using this simple method

RFM stands for Recency, Frequency and Monetary value. It’s a mathematical way to categorize customers based on their buying behavior and identify which group they belong in. The more information you have about your customer's RFM score, the better you can tailor different offers for them because you know what kind of customer they are.

Table of Content

TDLR: RFM Analysis is a proven marketing model that measures customer behavior and helps marketers make smarter decisions. It is based on the customers' last purchases, how often they buy from you, and their average transaction value.

The RFM Score, or Recency-Frequency-Monetary score, was created by marketing professionals to measure the strength of a customer. The higher the customer score on this scale, the more likely they will purchase from you again.

If you are new to this concept, visit our previous blog that gives detailed insights on RFM customer segmentation or types of customers, how to identify them and which ones can generate you the highest sales.

What is RFM Analysis?

Bult and Wansbeek originally introduced the concept of RFM in 1995. It was initially used to minimize printing and shipping costs while maximizing returns for catalog marketers.

RFM (Recency, Frequency, Monetary) Analysis uses sales data to segment a pool of customers based on their purchasing behavior. The resulting customer segments are neatly ordered from most valuable to least valuable. This makes it easy to recognize the best customers.

An extensive study done by Blattberg et al, back in 2008, proved the effectiveness when applied to marketing databases! Sounds too confusing? Let me simplify this for you.

To perform the RFM Analysis, you'll be using the dataset containing the purchase history to identify various customer types, which will eventually help you create personalized marketing campaigns for each type/segment.

Why Choose RFM Analysis?

Measuring customer value on a single parameter (e.g. recency) may give you an inaccurate customer purchase report.

For example, you could say people who spend the most money on your products would be considered the best customers. However, just because someone spends more money on one purchase doesn't mean they're automatically high-value! What if this person only brought something once? Or what if it's been months since their last visit?

A single answer to all of the above questions is applying the RFM Model in your business. It is a clever way to understand your customer base. This approach combines three different attributes: recent purchases, frequency of purchases, and the amount spent in total when buying from you.

If they bought in recent past, they get higher points. If they spend a lot more than average on items and make frequent purchases, their RFM score will be even better! And the higher their RFM Scores are, the more likely they are to respond to your promotions.

Pareto's Principle: Proven RFM effectiveness

RFM has been around for decades. The process relies on the Pareto Principle, referred to as "the 80-20 rule."

Consider this case: Emily has designed the perfect e-book with appropriate content, call to action, social media links. She sent it out via email, reasoning that even if only 10% of her 5000 customers purchase her e-book, she'd be rich in no time. Days passed, but she could only achieve one sale; why is that so?

Emily broadcasted her generalized mail to all the customers instead of targeting only customers with a good RFM Score. With targeted campaigns customized for people who have a solid history of purchases, business owners can dramatically increase their revenue!

Pareto's Principle

This can be further supported by Pareto's Principle that says 80% of the results come from 20% of the causes, i.e., 20% of customers contribute to 80% of your total revenue. In simple words, the more a customer purchases from you, the more likely they are to purchase again in the future.

Pareto's Principle constitutes the fundamental element of the RFM Model. It also teaches us that the fact that customer retention is key to any successful enterprise. Therefore, the old saying "it's better to have a bird in the hand than two in the bush" has more meaning when considering that it takes an average of eleven months and $4,000.00 for new customers before they're pulling their weight on ROI.

What is RFM Score?

The RFM score is a marketing metric that helps companies assess the customer lifetime value. It was initially developed by marketing researchers at the University of Chicago.

There are several methods to calculate RFM scores. In this blog post, we have only used the Quintiles Method as it is proven to yield one of the most accurate results compared to other methods and is used by major companies like Amazon.

Note: If you want to dive deeper and know more about RFM Scores and its computation by other simpler methods, refer to this blog post.

RFM Workflow

The quintiles method is one of the most popular RFM Analysis procedures for segmenting customer data and determining the best and worst customers. This method divides customers into five groups based on their spending patterns, with group 5 being the highest scorers and group 1 being the lowest. To compute your company's RFM scores, you need to know how many customers fall into each quintile category (1st through 5th) from the most valuable top-down.

In the Quintiles method, the dataset, i.e., RFM Values, is split into five equal groups. For example, if there are 100 customers, the top 20 get a score of 5, the next 20, i.e. 21-40 - score 4, followed by a score of 3 for 41-60, and so on.

If you really want to know all the ins and outs of the Quintiles Method, we shall get started. This is where it gets a bit complicated, but I'll try my best not to bore you with a bunch of technical jargon that will make your eyes gloss over like so many other articles out there.

Quintiles Method to Calculate RFM Score

Here's a brief illustration of how the process of RFM Analysis flows and the integral aspects required to conduct the analysis:

  • Customer Data: The first thing you require to begin RFM Analysis is customer data from past transactions. This should include the name or email address or unique customer ID, most recent purchase date, number of purchases, and amount of money spent on those purchases.
  • RFM Values: Decipher Recency, Frequency, and Monetary Values from the above customer data, based on which individual RFM Scores will be calculated using the Quintiles method(given below).
  • RFM Scores: Convert the individual R, F, and M values to corresponding R, F, and M Scores. Combine the individual scores into aggregated RFM scores.
  • Segments: Build segments by distinguishing customers on their  RFM Scores. These segments determine customers' behavior and the unique marketing strategies required for their retention.

Process of Calculating RFM Score using Quintiles Method

Following these few simple steps will help you compute RFM Scores in Excel with accuracy.

  • Step 1: Obtain your customer sales data for a specific period; it could be six months, one year, two, or three years. The list of information that you require from the customer database are:
  1. Recency: How recently did they purchase or made some engagement with your business? E.g., 20 days ago, 200 days ago, etc.
  2. Frequency: How many purchases have there been within a certain period? E.g., If a customer placed four orders in separate periods, their frequency value is 4. But if he/she purchased four items in a single checkout, his/her frequency value is 1.
  3. Monetary Value: What is their total expenditure on your products? Eg. 20$, 500$.
  • Step 2: Now make three columns - Recency Value, Frequency Value, and Monetary Value. Insert their respective values that you acquired from your customer database.
  • Step 3: Now that you have all the R-F-M Values, sort them in descending order.
  • Step 4: Add a column named "R" beside Recency and give the top 20%, a score of 5, the next 20%, a score of 4, and so on. Note: This is your Recency Score.
  • Step 5: Do the same for Frequency and Monetary Value by inserting the columns, "F" and "M", respectively. These are your Frequency and Monetary Scores.

Note: Do not get confused between RFM Value and RFM Score. RFM Value is the actual value that you yield directly from your customer purchase database, whereas RFM score is the number from 1-5 that you assign to each RFM Value using the Quintiles Method.

Example: Look at the table below. If we consider the example of Emma Smith's Sphere, she has the highest RFM Score. Let's have a look at her RFM Value and RFM Score.

RFM Scores
  • Recency value: 5. It is in the 5th quintile of the Recency Score Range, which falls within 1-32. → Her R-Score is 5.
  • Frequency value: 56, it is in the 5th quintile of Frequency Score Range, which falls within 41-84. Her F-Score is 5.
  • Monetary value: 100,000,000. It is in the 5th quintile of the Monetary Score Range, which ranges from 8,991,000 to 100,000,000 → Her M-Score is 5.
  • Step 6: Now, make a new column named RFM and calculate their sum, R+F+M, using the formula =SUM(D2+F2+H2) and paste it for each customer row. Here, D2, F2, and H2 are the column names. Your column names may differ from this.
  • Step 7: The last step is to sort the RFM column in descending order to obtain the highest score at the top and lowest at the bottom.

Now, take a look at your results! Those who've spent the most, purchased recently and often, are on top of the list! These folks make up some of your best clients. Now take a look at those lower down—those might have spent a lot frequently on your products, but that's long back. Therefore, they may not be worth investing too much time into, with all their potential purchase opportunities dwindling each day as they don't buy anything new from you.

How to use these scores?

It is a great idea to split your customer segments into three categories. The top 20% of your customers are Gold, the next Silver, and Bronze. The remaining 40% need to be scrutinized to find out where you have gone wrong with them. This way, you can predict who will be your loyal customers and whom you should exclude from the list of sending promotional offers.

There are several other, more specific customer segments based on RFM scores. If you want to learn more about these segments and the marketing strategies used to retain them, refer to our previous blogpost!

How can we help?

Many marketers are frustrated with the fact that their marketing campaigns don't produce as much revenue as they should. Continuously designing and improving marketing campaigns is hard. LatticeAI makes it very easy to create and launch personalized campaigns, A/B test them, and improve them - All of it automatically. All you have to do is review and launch the campaigns created by our AI.


Customer Segments generated by LatticeAI


These are only some of the inbuilt segments automatically created by LatticeAI based on the RFM analysis of an online store. Further, we also generate highly targeted campaigns for each customer segment to ensure high engagement and conversion.

Our predictive models deliver the right message to the right customer at just the right time, every time, with no guesswork involved. This way, you can generate greater revenue, reach more customers without risking annoying anyone.

Conclusion

In this blog post, we’ve discussed the importance of understanding how your customers think and act. We also introduced you to RFM analysis – a method for calculating customer loyalty that is based on Pareto's Principle of 80/20. The Quintiles method was used to calculate customer scores across all five categories (referral, frequency, monetary value). If you want help with identifying which segments are worth investing in and creating personalized email campaigns for your brand, contact our experts today!

FAQs

What are the variables required for calculating RFM Score?

The three most important variables to determine if a customer will respond or purchase from you again are Recency (R), frequency (F), and monetary value. The RFM model is an easy way of predicting whether your customers will buy more products shortly by looking at how often they had made purchases before when those transactions occurred last time around and what each one cost them.

What are RFM Segments?

RFM segmentation is a data-driven approach to categorizing your customers. RFM Segments are the different types of consumers who should be sent tailored messages for each stage so that business owners can ensure every member receives relevant information during his/her journey through the company.

How can I find the Recency Score?

A recency score is a calculation that measures the number of days since you purchased. To find your most recent purchase date, subtract today from it to get an accurate Recency Score.

Can I compute RFM Scores without excel?

Yes, definitely. Performing RFM Analysis can be difficult and time-consuming, but not anymore! LatticeAI does all the work for you. It calculates your RFM Scores using advanced algorithms and generates target-based campaigns, so they're finally satisfied.

What is RFM Analysis used for?

The RFM analysis allows companies to see who is giving them the most money. It also tells you how often someone buys from your company and which levers must be pulled for that person to become a repeat customer instead of just a one-time buyer.

The RFM Analysis enables organizations to attempt more successful marketing strategies by telling us about our customers' purchase behavior!

Do I really need to perform RFM analysis if I am a small-scale business owner?

RFM analysis is for every merchant, whether big or small. It helps you understand your customer segments, identify high-value customers or loyal customers, and more, all while understanding the needs of each segment so that they can be better served with just a touch of personalization!

RFM analysis is an essential tool in today's competitive marketplace. It allows businesses to gain valuable insight into their various consumer bases without spending too much time analyzing who makes up these groups.

How do I make the RFM Model?

The first step in building an RFM model is yielding Recency, Frequency, and Monetary values to each customer. Once complete, you can divide the customers into tiered groups based on these three dimensions using Excel or Google Sheets.

curved line

Latest Articles

View All

Growing SMS subscribers via LatticeAI

SMS is one of the most reliable channels for marketing with over 90% open rates. LatticeAI provides SMS capabilities and makes it easy to grow your subscribers.

September 21, 2021

Everything you need to know about a/b testing email campaigns

A/B testing emails is a process in which two different versions of the same email are sent to subscribers. The goal is for one version, A or B, to be more successful than the other at increasing subscriber activity and revenue. Basically, you want your recipients not only to open an email but also click-through on links from it that take them through conversion processes like purchasing products or signing up for newsletters with their contact information.

July 26, 2021

How ios 15 update will impact email marketing campaigns?

The updated Ios 15 had a major impact on email open rates which has hindered the ability to analyze email marketing.

July 20, 2021