Start segmenting your customers with this Free template

What is customer segmentation and how to get started?

At Cronuts we want to talk about “customer segmentation” and provide you with a template so you can start segmenting your users and try to take actions to maximise your retention. It is a very common conversation when customers tell us about the high cost of sales acquisition or that sales in general are not as expected, especially organic sales.

Trying to be as honest as possible, we always say that retention is probably one of the most important actions in what we call the conversion funnel.

This is when we have to say the typical cliché, “getting a new user is much more expensive than getting an existing one to repeat“. Leaving aside the mythical phrase, it doesn’t take away from the fact that it is totally true. Understanding your retention is key to being able to launch your campaigns, what do I mean.

Example;
Think that in the hypothetical case that we have a CAC (cost of acquisition) of about 10 euros. The average ticket we have per purchase is about 20 euros. At first we might think that there is no deal in having such a high acquisition cost. But on the other hand, if we know that on average this person buys 2 times. To sum up in an easy way:

• LTV1 = Purchase – CAC = 20 -10 = 10
• LTV2 = Purchase 1 y 2 – CAC = 40 – 10 = 30

In other words, a good acquisition strategy helps us to make our database more profitable and also to understand to what extent we can increase or decrease our acquisition costs. But in order to be able to carry out a good retention strategy.

What questions should you ask yourself to segment your customers?

• Who are my best customers?
• Which customers are most susceptible to stimuli?
• Who has the potential to become the most profitable customers?
• Who are the lost customers that you don’t need to pay much attention to?
• Which customers do you need to retain?
• Who are your loyal customers?
• Which group of customers is most likely to respond to your current campaign?

Downloadable template

We don’t want you to think that this is a typical article where a lot is written but then little of what is said can be applied. In fact, we are looking for the opposite. We want to provide you with a google sheets template that will help you to do a customer segmentation based on the RFM (Recency, Frequency, Monetary) model.

In fact, to be honest, the template on customer segmentation that we are offering you, here we found it on the internet and we have improved it so that only with the following data:

• customer orders
• date of purchase
• customer
• quantity

You can make a customer segmentation based on the RFM model to better understand your customers. The first step is to paste the fields mentioned above into the days tab:

What is the RFM model based on?

The RFM model is based on the Pareto Principle: 80% of the results come from 20% of the causes. Similarly, 20% of customers contribute to 80% of your total revenue.

People who spend once are more likely to spend again. People who make large purchases are more likely to make repeat purchases.

How is the RFM model calculated?

For each of our customers we will rank them from 1-5 within the three RFM variables, being:

• Recency, as the time since the last purchase until today for example.
• Frequency, as the total number of transactions
• Monetary, as the sum total of your purchases

This calculation of 1-5 for each of the variables is obtained based on the percentiles of the sample. But let’s not get complicated, each customer will be scored as we said from 1-5 in each of the three variables based on the formulas that are already stipulated in the google sheets.

An example would be the following:

Using the following criteria we will assign the segments based on R and F&M scores.

Description of each Segment

• Champions: have bought recently, in addition to buying very often and spending as much as anyone else.
• Loyal customers: They spend very often with us and would potentially respond to a promotional action.
• Potential loyalist: A recent customer who has bought more than once with a reasonable amount.
• Recent Customers: the one who has purchased most recently and is not a new customer
• Promising: Recent customers but who have not spent a lot of money with us
• Customers needing attention: Are above average in money frequency and date of last purchase
• About to sleep: They are below average in frequency of money and date of last purchase. If we act in any way we will lose it
• At risk: Spent a lot and bought several times but a long time ago. We must try to get them to reconnect with our product.

To give you an example, in two dimensions: the base looks like this in the customer segmentation template:

In the customer tab you can see metrics such as:

• Repetition
• average ticket
• The average total sales sum per customer

I already have my customer base segmented, what actions can I take to improve my retention?

Once we have the segmented base is when we have to understand what can be done for each of the segments, here are some examples:

RFM analysis is a practical method to find your best customers, understand their behaviour and then conduct targeted marketing/email campaigns to increase sales, satisfaction and customer lifetime value.

We hope this post on “Customer Segmentation Template” has helped you to understand the concept a little better and that you can start applying it yesterday to your beloved customers.