RFM-analysis of a client base and data visualization in Google Data Studio
Today I want to tell about one of the methods of client base segmentation that is called RFM-analysis. The method is based on users behavior, it is simple enough and quite effective in usage.
What is RFM-analysis?
RFM – is an abbreviation of Recency, Frequency and Monetary. Basis of RFM-analysis are key indicators of user behavior like date of last purchase, purchase frequency, total summary of all the purchases.
For the business types where usage of LTV (lifetime value) is not suitable, this metric can be replaced with Engagement. Thus it will be more correct to call the analysis RFE instead of RFM. You can use the n umber of achievements on the page, page views, users commentaries, number of marks as metrics of engagement.
RFM-analysis helps to answer the following questions:
- Who are your most valuable clients?
- Which clients have the biggest potential?
- Which clients should we pay more attention to, not to lose them?
- Which clients should we remind ourselves about firstly?
Firstly, I have to say that I was running RFM-analysis for my clients in two different ways. To clarify, in the first case we were dividing the clients into segments depending on average values in the project. In the second one, there were certain rules for adding the user to a certain group.
Let me show you an example of segmentation based on average meanings and I will add a couple of words in the end about how to convert the result for the second method.
An example of RFM-analysis and data visualization in Google Data Studio
An example of a report you can find by the link.
The template is based on test data and doesn’t contain any commercial secrets. The report starts from the basic metrics of the project.
- Revenue
- Number of orders
- Number of clients
- Average frequency of purchases
- Average revenue
- Average revenue from one user.
The next section – we are going a little bit down by the level of detailing and examine revenue separately for each segment.
Adding details and showing the main metrics for each segment in particular in the table is going to be our next step. The metrics are the same as in the beginning of the report but now we can make any deduction. For example, that the Champions and the Loyals are making purchases more often than the others. Meanwhile they have a high average bill and the revenue per client is much higher compared to the other ones in the company.
I like the chart below. You can see the segments of users by the number of purchases and summary revenue.
In the end of the report we are going one more level of detailing down and we see main metrics by every User-ID separately. Probably, looking like this the chart is not very useful. But if we connect the data to the email addresses of our clients and know which clients he is connected to, we can:
- send the most relevant mailing,
- use the data to create audiences for contextual ads,
- settings look-alike campaigns in Facebook Ads.
Let’s peek inside the report!
If we switch into the report editing mode and see the sources of the chart, we will find there one Google Sheets table. You can see the chart if you go to RFM-tab by the link.
At first, it may seem to be difficult but let’s sort this all out.
Data import from Google Analytics
Everything starts from the Report Configuration tab, where a regular data upload from Google Analytics is being set up. I’m importing data from GA using a default GA add-on for Google Sheets. You can upload this supplement for free by the link.
On the Report Configuration tab it is necessary to add View-ID of your Google Analytics account and change the date range, if you wish.
Very important moment! Using this Google Sheets supplement it is impossible to upload your User-ID, even if you have a corresponding performance in GA. Firstly, you will have to create a custom dimension and send the value of your User-ID right there. Official article from Google Help can be found by link.
When the custom dimension is set up, put it’s number to the Dimensions field on Report Configuration tab. Settings 6 and 7 are indicated in my example. There are two of them there because we were tracking down if the user is signed up for the website or not and we were sending this information as a special setting as well.
Above all, the e-commerce in your GA account is set, we are able to see the data about the transactions and the revenue in the report
When the pre-set of GA is don’t and you have corrected the data upload settings, you can launch the import and set up an automatic report update. You can easily do it in the add-on to Google Sheet settings.
The results will be uploaded to the RFM Report tab and they will be updating due to the schedule.
The formulas for calculation
On the next stage I am going to transform the data from RFM Report tab a little bit so that it will be easier to work with them. Certainly, the result is on the RFM Report_Work tab. Then we go back to our RFM tab where the magic happens!
- The first column unique_customer_id shows us only unique User-ID. To get this I am using the formula where I am referring to the User-ID column on RFM Report_Work tab.
=UNIQUE(‘RFM Report_Work’!$A$2:$A)
- last_purchase_date — we can calculate the last purchase date by the formula where we are searching for the maximal data for each and every unique User-ID.
=if(A2=«»,«», MAXIFS(‘RFM Report_Work’!C:C,’RFM Report_Work’!A:A,A2))
- R – we count Recency by the formula below. We are calculating the average purchase date and comparing a certain user to the average meaning and depending on the result we prioritize it with the number from 1 to 5.
=iferror((ROUNDDOWN(RANK.AVG(B2,B$2:B, TRUE) / ((COUNT(B$2:B) + 1) / 5), 0) + 1),»»)
- purchase_count – we are counting an average purchase quantity for every customer by the formula:
=if(A2=«»,«», countif(‘RFM Report_Work’!$A$2:$A,$A2))
- F – we calculate Frequency by the same principle as purchase prescriptions. To clarify, are calculating the average meaning for the company and compare the value of taken user to the average and prioritizing it with the number from 1 to 5.
=if(A2=«», «», ROUNDDOWN(RANK.AVG(D2,D$2:D, TRUE) / ((COUNT(D$2:D) + 1) / 5), 0) + 1)
- total_revenue – total revenue by every client taken separately.
=if(A2=»», «», sumif(‘RFM Report_Work’!$A$2:$A,$A2,’RFM Report_Work’!$E$2:$E))
More formulas
- M – Monetary –by the same token with other metrics of our analysis we find the average and comparing average meaning of a certain customer to the average.
=if(A2=»», «», ROUNDDOWN(RANK.AVG(F2,F$2:F, TRUE) / ((COUNT(F$2:F) + 1) / 5), 0) + 1)
- RFM – the total number for every client, based on which the client refers to some segment. In other words, we get the RFM by putting all together the values of every separate setting. If R = 2, F = 4, M = 5, то RFM = 245. Formula looks like this:
=if(A2=»», «», VALUE(CONCATENATE(C2,E2,G2)))
- You insert Customer Segments from the Association table tab depending on the RFM column meaning.
=iferror(VLOOKUP(H2,’Association table’!$A$1:$C$125,2,0),»»)
- You set Macro Cluster by the same token.
=iferror(VLOOKUP(H2,’Association table’!$A$1:$C$125,3,0),»»)
- Member – my personal case which I have left here to show that it is also possible. We put the maximum meaning of “signed in for the website” for every user. Thus we can see if the presence of registration influences the segments. For example, most of the Champions are registered on the website. And most clients that are in a risk zone do not have the registration. Formula.
=if(A2=»»,»», MAXIFS(‘RFM Report_Work’!D:D,’RFM Report_Work’!A:A,A2))
Finally, here we finish the report’s main page review.
User Segmentation
Subsequently, we didn’t take a look on the Customer Segments Info, Customer Segments и Association table tabs.
Which RFM meanings correspond to which segment pointed in the Customer Segments Info tab. Then we transform this data into a much more comfortable to use Association table.
After that, on Customer Segments tab we can see how many customers there are for every RFM meaning.
In addition, here is one more segmentation option.
As I have promised before, I am sharing one more segmentation option. If you have specific measures that you are willing to fulfill the segmentation with the help of, you can use your individual data.
If you take a look on Bonus_Segments tab, you will find a table of rules for customer segmentation. Obviously, here we indicate several time periods, minimum and maximum meanings of revenue and a number of made purchases by the customer. Further, depending on the data, we divide users into segments.
On the Bonus_RFM tab we describe these conditions using formulas.
Lastly, here I want to finish the article. Hope it was useful for you.
You can find my other articles by the link.
If you like the report, but it is hard for you to sort everything out by yourself do not hesitate to write to me. I can help you run an RFM-analysis based on your data.