Start segmenting your customers with this Free template

Table of Contents
Simple words for a complex subject. Everyone takes it for granted that we segment our customers and that thanks to this we obtain additional benefits, but we know that day-to-day life is complicated and overwhelming. In this article we would like to explain the basics of customer segmentation and, above all, its main applications.

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.

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:

Segment Activity Proposed actions
champions They have bought recently, in addition to buying very often and spending as much as anyone else. Reward them. They may actually be like-minded customers for new products. They can be ambassadors for your brand
Loyal customers They spend very often with us and would potentially respond to some promotional action. Try to generate an Upsell with higher value products. Ask for a review
Potential loyalist A recent customer who has bought more than once with a reasonable amount of money. You can offer some kind of loyalty program or recommend other products.
Recent Customers The most recent purchaser and not a new customer Try to improve or assist in onboarding
Promising Recent customers but who have not spent a lot of money with us Create brand awareness, for example by offering free trials
Customers needing attention They are above average in frequency, money and date of last purchase It offers limited offers, based on previous purchases, with the aim of reactivating them
about to sleep They are below average in frequency, money and date of last purchase. If we act in any way we will lose it Share quality resources. Recommends products that are frequently sold
at risk He spent a lot and bought several times but it was a long time ago. We have to try to get him to reconnect with our product. Send personalised emails, offer service renewals or additional products for past purchases.


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.


What should I do now?

If you have more questions and want to know how we work, contact us and one of our consultants will contact you for advice.

For further information on this and many other topics you can check out other articles in our blog where you will find references on this, and many more topics.

If you liked this article, you can share it on LinkedIn, Facebook or Twitter by clicking on the icons below. I’m sure you have acquaintances who will like it as much as you do.

Share this Post

Subscribe to our Blog

Enter your email and receive a welcome pack with our best items.


Are you thinking of optimizing any part of your digital strategy?


Do you need a digital partner?

¿Buscas un consultor digital?

Empieza ahora tu transformación digital rellenando el formulario que encontrarás a continuación.

Eduard Vivar Mompel co fundador CRONUTS DIGITAL

Edu Vivar


You deserve the best content

Subscribe to our blog and learn about the latest digital trends.