RFM analysis in Google Sheets and Looker Studio – template overview
Do you know that you can create an RFM analysis in Google Sheets and Looker Studio? I have created a RFM analysis template and launched it in my store. It is a vital report for e-commerce directors.
If you are willing to know how to do it yourself, let’s start!
Check out the video overview now:
Looker Studio overview
Before reviewing the dashboard, I would like to explain what RFM analysis is. The letters in RFM stand for Recency, Frequency, Monetary. It is a marketing tool used to identify a company’s best clients based on their spending habits. We have written a big article about RFM analysis, how it can be useful for your business and how to create it. Check it out here.
It is a one-page dashboard.
In the header of the dashboard you can see Gaille Reports logo, report name and a date range. By default, the range is set up to last 12 months, but if you need, you can change it easily.
The very first section of this dashboard is Sales Overview. It contains a review of sales related KPIs. Among them, we can see Total Revenue, Number of Purchases, Number of Users, Purchase Frequency, Average Order Value and Average Revenue per User.
As you can see, Average Revenue per User is almost four times bigger than the Average Order Value, meaning that a single user usually makes a couple of purchases.
The next section is Revenue by Customer Segments. All the customers are divided into segments, and as you can see, Champions has brought us more than $107K. Using this section, we can see what segment needs additional work – we can apply different marketing strategies to make them stay.
Also, in this section we have a nice pie chart that represents a ratio of customer segments. If you select some of the segments of the pie chart with your mouse, it will show you the revenue of each segment.
Then we have a Sales overview by segments. It gives a deep understanding of each segment. Besides total revenue, we can see a number of purchases, a number of users, purchase frequency, AOV and ARPU (our dashboard KPIs) for each segment separately.
It is very nice to analyze this table and make adjustments to your customer approach.
The next section of the report is Revenue vs Number of Orders Plot. It has the data from the previous charts but presented in a different way.
And finally, the last section of RFM analysis in Google Sheets is about Main sales metrics by Customer ID. It allows us to see Macro Clusters and Customer Segments owing to two filters.
There are 4 main macro clusters – loyal, sleep, promising and lost.
For example, I want to see only promising customers. So I apply “promising” in the macro cluster filter and in the table below I will see the segments belonging to this cluster, for example, need attention, promising and new customers.
Note: when you purchase this template, you are going to have an extra page for your information “How to use this dashboard”.
Google Sheets overview
The back-end of this RFM analysis dashboard takes place in Google Sheets. When purchasing the template in my template store, you are going to receive an identical file to what I am going to show now.
The first tab of the configurational Sheets is an instruction of how to use this document. In a shellnut, what you need to know is that you need to put your data into all the yellow cells.
In this case, you need to input your data only on 01_RFMReport page and that’s it.
If you need this dashboard once a month, you can input all your data manually, but if you’re going to use it frequently, I highly recommend using third party connector services to pull up the data from CRM systems. You can do it by going to Extensions menu in Google Sheets, then Add-ons, Get add-ons.
Generally, I work with three connectors – Supermetrics, Power My Analytics and Windsor.ai. It is a lifesaver when speaking about updating data because connectors pull up the freshest data automatically and it saves a lot of time.
Returning to our Google Sheets. In other tabs you don’t have to do anything.
Everything going on in the further sheets is interconnected and contains formulas. In the image above you can see the tab with all the data we have. This tab is connected to Looker Studio.
In the next tab we have the indexes for each of the customer segments. Here you can change indexes to move users from one segment to another.
The next tab contains basically the same info but also has data about macro clusters.
Followingly, there is a tab where you can see how many clients you have in each of the segments and categories. Precisely, you have 222 Champions (second row) and in the following cells watch them split into categories.
The last configuration tab is my helper to create random data.
***
Having purchased the RFM template, I recommend you to change the design of my report to the design of your company. It is really easy to do in Themes and Layout.
In case you want to save your time, feel free to approach the Gaille Reports team and we will do everything for you! We can also come in handy if you want to customize your dashboard somehow, add more charts, change the layout etc. You can read about individual design and report customization in our store!
Besides that, I decided to make a gift for you – all the newsletter subscribers will get a free report template. Click here to gain it!