RFM-analysis of a client base and data visualization in Google Data Studio


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. Number of achievements on the page, page views, users commentaries, number of marks can be used 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?

I was running RFM-analysis for my clients in two different ways. 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.

01 RFM анализ клиентской базы - Sales overview
The next section — we are going a little bit down by the level of detailing and examine revenue separately for each segment.

02 RFM анализ клиентской базы - Revenue by customer segments

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.

03 RFM анализ клиентской базы - Sales Overview by Segments

I like the chart below. There are visually shown the segments of users by the number of purchases and summary revenue.

04 RFM анализ клиентской базы - Revenue vs Number of Orders Plot

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.

05 RFM анализ клиентской базы - Main sales metrics by Customer ID

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.

06 RFM анализ клиентской базы - Custom Dimension

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.

If 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. It all can be easily done in the add-on to Google Sheet settings. 

07 RFM анализ клиентской базы - Run GA addon

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. The result is on the RFM Report_Work tab. Then we go back to our RFM tab where the magic happens!

  1. 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)

  1. last_purchase_date — last purchase date is being calculated 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))

  1. R – Recency is being counted 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),»»)

  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))

  1. F – Frequency is calculated by the same principle as purchase prescriptions. We 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)

  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))

  1. 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)

  1. RFM –  the total number for every client, based on which the client is being referred to any segment. 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)))

  1. Customer Segments are inserted from the Association table tab depending on the RFM column meaning.

            =iferror(VLOOKUP(H2,’Association table’!$A$1:$C$125,2,0),»»)

  1. Macro Cluster is being set by the same token.

=iferror(VLOOKUP(H2,’Association table’!$A$1:$C$125,3,0),»»)

  1. 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))

Here we finish the report’s main page review. 


User Segmentation

We didn’t take a look on the Customer Segments Info, Customer Segments и Association table tabs.

On Customer Segments Info tab its is pointed which RFM meanings correspond to which segment.  Soon this data will be transformed into a much more comfortable to use Association table.

On Customer Segments tab we can see how many customers there are for every RFM meaning. 

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. Here we indicate several time periods, minimum and maximum meanings of revenue and a number of made purchases by the customer.  In the end, depending on the data, we divide users into segments.

On the Bonus_RFM tab we describe these conditions using formulas.

08 RFM анализ клиентской базы - Bonus

Here I want to finish the article. I hope it was useful for you.

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 anm RFM-analysis based on your data.

admin administrator

Оставить ответ