You are working as a digital marketer or digital marketing analyst in a company. The company has several thousand online customers, but beyond some top level metrics they don’t have any customer-focused insights. As such you might have the typical Google Analytics reports (e.g. which sources are customers coming from), know what products are purchased most often and what the average order value is. But what your stakeholders lack is a better understanding of the customers in order to drive marketing & content decisions and strategies for acquisition, growth and retention. That’s why they come to you asking to share some general “who are the customers?” insights with them.
Even though this probably one of THE typical asks for marketers and analysts it might lead to some inner stress levels rising due its vagueness. Above question can mean everything and anything from rather top level customer personas to in-depths customer and purchasing behavior reports.
So imagine you had an analysis that would not only group all customers into different clusters but also allow you to develop and present to your stakeholders targeted strategies for each cluster based on its characteristics. All leading to an optimized marketing & sales approach and in-depth customer insights for improved conversion rates. And the best of all is all you need is the transactions data of your company.
That’s where the RFM analysis comes into play. It’s a simple to understand and easy to apply data analysis model to segment your customers. The following is a step-by step tutorial on how to create such a model in Google Sheets. Furthermore it shows you specific strategy recommendations for each of the key customer clusters (and if you want to get started quickly you can plug-in your data into the provided workbook to use it as a template in order to segment your clients right away).
The recency frequency monetary (RFM) analysis
The recency frequency monetary analysis (RFM analysis) is a classic analysis model for behavior based consumer segmentation. It segments customers by scoring them on a 1-5 scale in regards to how recently, how often, and how much they have bought (different scales may be used, however the 1-5 is usually the one used in a commercial context). Those three factors can then be used to predict how likely it is that a customer will purchase (or for some business models engage, e.g. apps) again.
Furthermore those segments can be grouped into clusters allowing you to develop targeted individualized content and promotion strategies which are more likely to convert with the customers in each cluster. Because each cluster is assigned a monetary value high value high-value customers can be identified easily and marketing spend can be allocated accordingly.
This guide gives a RFM Analysis example and shows step-by-step how to conduct a recency frequency monetary analysis with your data in Google Sheets. Afterwards it explains the content and promotion strategies, which can be applied to each individual cluster.
As usual I recommend working along in the above provided Google Sheet in order to understand everything. The workbook contains two sheets: Sample Data includes dummy data representing a transaction list (this could be anything from software sales to ecommerce store orders) and RFM Model which includes the solution to the RFM analysis example this guide is working towards.
If you are in a hurry: It is possible to replace to the dummy with your own data to use the RFM Model as a ready made template.
Creating a RFM analysis example step by step
First step is to prepare the data and to calculate the following metrics for each customer:
- The most recent transaction
- The number of transactions per month for each customer
- The average amount purchased each month by each customer
In the following we will work in the sheet Tutorial of the provided workbook.
We’ll start by finding out the number of transactions each individual customer had. This is easily done by copying the formula =COUNTIF(‘Sample Data’!B:B,A2)from B2 down to B3403. It will count how often the value from the referenced cell occurs in column B from the sheet Sample Data.
Next step is to identify the most recent transaction for each customer. You can do so by copying the following formula from C2 down to C3403:
=MAXIFS(‘Sample Data’!C$2:C,‘Sample Data’!B$2:B,$A2)
The formula uses cell A2 as a reference to filter the corresponding rows in the sheet Sample Data with column B and returns the highest (=most recent) date from column C respectively.
Similar to this we use the formula =MINIFS(‘Sample Data’!C$2:C,‘Sample Data’!B$2:B,$A2) in in cells D2 to C3403 to get the date of the first transactions of each customer.
For the final RFM model you’ll need the amount of time the customer has been with the business. In our example we’ll use months for this. As such put =DATEDIF(D2,now(),“M”)into E2 and drag it down E3403.
Next we want to know how much each customer spends on average each month. Plug in =SUMIF(‘Sample Data’!B:B,A3,‘Sample Data’!D:D)/E3 from F2 to F3403.
The last step is to calculate the average number of transactions per month for each customer. So simply write =B2/E2 into G2 and copy it down.
For all above instead of “M” you could also use “Y” or “D” to set the time unit to years or days respectively. It doesn’t really matter what you choose as we we’ll be coding each data point into a 1-5 scale for the RFM analysis later on anyway. However to make your data more vividly choose a unit, which make sense to your business model, e.g. if you are selling cars it would make sense to choose years, while it might make more sense to choose days when you are selling coffee.
Calculating R,F and M
Two steps are necessary to calculate the R, F and M scores:
- Determine how each customer ranks for recency, frequency and monetary
- Assign a score to each recency, frequency and monetary rank
Luckily Google Sheets has a handy formula for returning the rank of a specified value in a dataset.
As such plug in and copy down the following formulas.
For the recency rank: =RANK(C2,C$2:C$4296,1) in H2 to H3403
For the frequency rank: =RANK(G2,G$2:G$4296,1) in I2 to I3403
For the monetary rank: =RANK(F2,F$2:F$4296,1) in J2 to J3403
The last argument (1) in the formulas ensures that the highest values in the respective dataset gets a higher rank and vice versa. E.g. a customer with an average order value of 10$ would get a higher rank than a customer with an order value of 5$.
Next we’ll create a RFM rank matrix to convert a customer’s ranks on recency, frequency and monetary into the wanted 1-5 rating. For this you will use the formula PERCENTILE to get the minimum rank a customer has to have on each for the three factors to get a certain rating. Since we have ratingd from 1-5 we’ll need five percentiles which equal to 20% percent steps.
Put =PERCENTILE(H$2:H$4296,0.8) into Q2 to get the lowest possible rank for the highest R score, similar put =PERCENTILE(H$2:H$4296,0.6) into Q3 to get the lowest possible rank for a R score of 4 and so on. Do the same for the F score in column R and and for the M score in column S.
No we’ll do the actual conversion for each customer. Put the formula =if(H2>=Q$2,$T$2,if(H2>=Q$3,$T$3,if(H2>=Q$4,$T$4,if(H2>=Q$5,$T$5,$T$6)))) in K2 and copy it down to K3403 to determine the R score. The conditional statements will determine if the respektive rank is above one of the thresholds of the RFM rank matrix and then assign an according score. Do the same for the F score by copying =if(I2>=R$2,$T$2,if(I2>=R$3,$T$3,if(I2>=R$4,$T$4,if(I2>=R$5,$T$5,$T$6)))) from L2 to L3404 and for the M score by copying =if(J2>=S$2,$T$2,if(J2>=S$3,$T$3,if(J2>=S$4,$T$4,if(J2>=S$5,$T$5,$T$6)))) from M2 to M3404.
As a last step we want count how often each of the RFM combination (there are 5x5x5 = 125 combinations) occurred. Again we will use the COUNTIF formula for this. Write =COUNTIF(N:N,Q10)into R10 and copy it down to R134.
You are done! ….at least technically. You gave each of your customers a RFM score based on the recency, frequency and monetary value of past purchases. However in order to make your analysis actionable you should group your 125 segments into more meaningful clusters and define strategies for each of them.
Clustering the RFM segments
Even though many others suggest having ten or more clusters I would recommend focusing on having only six key clusters (at least in the beginning). As such the implications for each cluster are still manageable and content and promotion strategies for each can be created.
The following describes content strategies for your email list and consequently customer segmentation for each of the defined clusters.
High Value Customers
Description: These customers are your most valuable customers. They buy frequently, are spending a high amount on each transaction and are still very active (=bought something recently).
Strategy: Obviously these customers have proven that they are willing to pay and to buy often from your. So don’t use price incentives (e.g. discounts) to generate incremental sales. They love engaging with you so rather reward them by testing new product (or feature) launches with them first. Being your most loyal customers the probability that they give some valuable feedback on the new products and recommend them to others will be highest with them. If possible try implementing loyalty programs (as well as advocacy and review programs) to reward and keep their loyalty. In addition these are the customers you should target your most expensive products at.
Segments: 34X, 35X, 44X, 45X, 54X, 551, 552, 553, 554
Description: Your core group of loyal customers. While they might be spending less frequently or lower amounts than your High Value Customers, they are still very valuable as they are regular and recent purchasers of your products.
Strategy: Probably not as effective as your High Value customers you can still look into upsell opportunities. Aa such if you are selling several products (e.g. as an ecommerce business) you can add value for those clients by recommending products based on previous purchases. Advocacy and review programs can help you spread word of mouth for your business through this cluster.
Segments: 51X, 52X
Description: Your newest customers. They recently had their first transaction with you. As such they obviously will have low frequency score. Even though they can have high monetary scores already, if they are high spenders.
Strategy: Most first time buyers will never graduate to promising and finally loyal customers. It is important to have an optimized onboarding with clear strategies in place (such as a triggered welcome email sequence) to encourage repeat purchases.
Segments: 33X, 43X, 53X
Description: Customers, who finished the onboarding process but aren’t in the Loyal Customer cluster yet. They buy fairly often, but haven’t reached the frequency levels of the Loyal Customers or High Value Customers yet.
Strategy: You have already accomplished an initial relationship with the customers. Now you should focus on increasing monetization and frequency depending on what they are currently lacking. You can test personalized product recommendations based on past purchases and special offers based on spending thresholds. Increase brand awareness to stay top of mind and to increase frequency.
Need Attention Customers
Segments: 24X, 25X
Description: Customer, who once purchased from you with a medium to high frequency but stopped for some reason a while ago.
Strategy: Goal for this customer cluster is to reactivate them before they get lost at all. Part of this can be to try to find out, why they left through analyzing their behavior or surveying them. Try limited-time offers as well as individualized recommendations based on past transactions. Price incentives can also be tested.
Description: These customers have not purchased from you in a long while. Some of them might have been high frequency and big spenders, but stopped buying at some point.
Strategy: As with the “Need Attention” segment you should try to reconnect with these customers. By having even more aggressive offers and price incentives you can try to reactivate them. However it can make sense here to segment this cluster even further by F and M in order to identify low value customers
Obviously there are still some white spots left for some of the customer segments. Above are only key clusters you should focus on in the beginning. You can cluster all the left out segments into a General Population group sending those more generalized and less segment specific content. Once you have mastered above key groups you can start clustering the rest of your segments.
In addition the thresholds for above clusters aren’t set in stone. As soon as you fully understand the implications of the model and the clusters for you business you can start moving the segments around to form your own clusters, fitting best to your business.
You can add the following formula to cell O2 and copy to O3403 to group the segments with above clusters in the Google Sheets workbook:
IF(AND(K2=2,L2>3),“Need Attention”, “General”))))))
Did You Enjoy This?
Then feel free to sign up for my newsletter. Get my newest articles on career & skill development for marketers and guides on technical marketing every week.