You’re probably thinking, ‘I already know who my most profitable customers are, they’re the ones who spent the most money, right?’ However, this is not always the case. For example, somebody could buy the most expensive item on sale, but then never return to buy from your business again. On the other hand, a customer could buy the cheapest product, but return regularly to buy it. Which of these customers is the most profitable? You’ll be glad to know that there’s a way to find out using Microsoft Excel.
When figuring out which customers are profitable and which are not, it’s of vital importance that we recognize the fact that revenue does not always equal profit. It’s easy for customers to talk about their future spending plans, but what’s more important when looking for profitable customers is that the customers actions clearly show evidence of past buying as well as a good prediction to buy again in the future.
Figure Out Your Customers’ Recency, Frequency, and Monetary Value
Things to consider when identifying profitable customers
The technique we will discuss here is known as RFM analysis. RFM stands for
- Recency: How recently did the customer purchase?
- Frequency: How often does the customer make purchases?
- Monetary Value/Margin: How much does the customer spend?
Each one of these three measures is able to give you a good idea of how good any one individual customer is, however when used together it provides a strategy that can provide you with a great deal of information about where you are getting your profit from.
Create Your Customer List
Depending on the type of program you use, you may have customer information readily available to you. Most fully-featured ecommerce systems will store basic information about customers and their purchases ready to export.
Depending on the type of system you use sometime it’s necessary to convert other formats to Excel. For example to convert PDF to Excel you can use a tool like Cogniview and get a fully functioning spreadsheet.
If you do not have easy access to this information, you may need to talk to your tech team, or input data yourself. An example of a basic spreadsheet that you could create that would have the same effect as a shopping card system is the following:
- Customer Name or ID.
- Last purchase date.
- Number of purchases over a specific time frame (e.g. one year).
- Total spend over a specific time frame – should be the same time frame used for number of purchases.
In order to create the first column, use the formula A2+1 which will create a sequence from one to one hundred. For the second column, you are looking to generate a random date from within your time-frame.
For example, if your time-frame is within 28 days, the formula would be =TODAY()-INT(RAND()*28). This formula translates in simple terms to ‘Today minus random days from 1 to 28’. The third and fourth columns are also created using the same formula, but in relation to maximum and minimum spend.
Bringing It All Together
Once you’ve got the basics of your spreadsheet laid out, it’s time to start bringing it all together in order to be able to score your customers’ levels of profitability. In order to do this, the first thing that you will need to do is copy the cells and paste them onto a new sheet using the ‘Paste Special’ option – this allows you to keep a copy of the values. You should now have a list of ‘customers’ along with their associated buying activity. The next steps are:
- Sort the list by ‘Last Purchase’ descending.
- Create an additional column titled ‘R’, and allocate scores – e.g. give the top 20% a score of 5, the next group down 4, and so forth.
- Remember that you will need to copy the column of scores in order to be able to paste it later in the process.
- Create additional columns, named ‘F’ and ‘M’.
- Sort according to ‘Purchases’ descending, paste the scores from the ‘F’ column, then sort according to ‘Total Spend’ descending and paste the scores from the ‘M’ column.
- Once you have done this, you can then sort the spreadsheet to ordered by ‘Customer’. You will see that once this is done, you will have the appropriate values clearly displayed for the RFM (Recency, Frequency, Monetary Value) for each customer. Adding these scores together will provide the total RFM score per customer.
- An easy way to do this is to create a new column named RFM, and use the formula =E2+F2+G2 or similar, and paste this into each customer row.
- Once complete, you should now be able to sort the spreadsheet by RFM descending, so that the customers with the highest score will be at the top.
The customers who have appeared at the top of this list are your best customers – the ones who have bought from you multiple times, regularly, and recently. Because of this, they are the customers who are most profitable to your company. Now look at the bottom –although these customers may still have spent a lot of money, they haven’t been back often, and haven’t bought anything from you recently. This proves that the customers who make the biggest purchases aren’t always the most profitable – you are more likely to be making much more profit from those who spend smaller amounts, but buy often.
Using the Results
Split the list into percentages – e.g. the top 20% are classed as ‘Gold’ customers and need to be focused on more, then silver, then bronze, for example. The last 20-40% are most likely making you no profit, so they could be used as an example of where you’ve gone wrong.
Never underestimate your regular customers who spend small amounts – they are the backbone of your business. Customers who don’t return usually do so because of a bad experience, whether it be poor customer service, poor quality products, or some other bad experience – so identify your best customers, and work to keep them!
There are quite a few Creately users who run small to medium size businesses and one thing they all want to identify is profitable customers. So we’re delighted to present this guest post which introduce a scientific method to find them using MS Excel. Just because we’re a Visio alternative doesn’t mean we don’t like Microsoft products.
