How to Use Excel to Identify Your Best Customers

Posted by Lior Weinstein on Monday, Jul 8th, 2013
Category : Microsoft Excel

How to Use Excel to Identify Your Best Customers

In the previous article I showed how you can measure visitor value and engagement using Excel. Now I would like to show you how you can identify your best, most profitable customers, also using Excel.

Now immediately you might think it is obvious who your most profitable customer is. Isn’t it the one who spends the most?

No!

First, someone could buy your biggest ticket item and then go away never to return. Are they a better customer than one who buys a smaller item each month? This is why we need to look at the data!

We also need to remind ourselves that revenue is not the same as profit. We will get into working out profitable from unprofitable customers and/or product later.

Customers can often talk up their future spending plans which can turn a sales persons head. Ideally what we want to discover who has shown good customer evidence through their actions and an evidence-based prediction of propensity to buy in future.

For simplicity sake in these examples I will be using revenue because I don’t want to distract you from the valuable knowledge that you can gain by getting involved in just getting hold of some of this data. If you have profit data then wherever you see revenue or spend, substitute that with profit or margin.

Discover Your RFM

The technique I am showing you here is called RFM Analysis. RFM stands for

  • Recency – How recently the customer purchased
  • Frequency – How often the customer purchases
  • Monetary Value (or sometimes Margin) – How much they spend

Each of these measures is an important indicator of how good a customer they are, but together you get a potent scoring metric that can tell you a great deal about where (or who) your profit is coming from.

Set Up Your Customer List

If you use a fully-featured ecommerce or CRM system then this information might be readily available to export or in reports. Otherwise you need to talk to your tame techie or do some inputting. For this example I set up a spreadsheet to simulate the output from a shopping cart system.

  • Customer ID or Name
  • Date of last purchase
  • Number of purchases for time frame (in my example I chose 365 days)
  • Total spend over time frame (again, for my spreadsheet I chose 365 days)
  • To create the first column I used the formula =A2+1 to create a sequence from 1 to 100.
  • For the second column I wanted a random date from the last 365 days, so used the formula =TODAY()-INT(RAND()*365) which translates to “Todat minus random days from 1 to 365”.
  • The third and fourth columns are also randon, 1-20 and 1-2000 respectively. This means the maximum spend would be $2,000 between 1 and 20 purchases, with minimum $1 from 1 purchase.

I then copied the cells and pasted into a new sheet using “Paste Special” as I only wanted the values, not the formulas, just in case it recalculated as I worked with the data.

This gave me 100 “customers” with associated purchase activity.

Now we need to “score” them!

Essentially scoring your customers is as easy as sorting and adding some additional numbers:

  1. Sort by “Last Purchase” descending.
  2. Add a column marked “R” and give the top 20% a score of 5, the next 4, and so on. (Do not use a formula, just copy and paste).
  3. Copy the column of scores so you can paste in the next steps.
  4. Add columns for “F” and “M”.
  5. Sort by “Purchases” descending, score in “F” (paste the scores), then sort by “Total Spend” descending and place the “M” scores.
  6. Now when you sort the spreadsheet back to ordered by “Customer” you will see each customer has the appropriate values for Recency, Frequency and Monetary Value. All we need to do is add those numbers together to get their RFM scores.
  7. Create a new column marked RFM and add up their R+F+M using something like the formula =E2+F2+G2 and paste that in for each customer row.
  8. Now you should be able to sort by the RFM column descending to get the people with the highest score at the top.

Take a look at the top of your results.

Those at the top have spent a lot, recently and often. Your best customers.

Now look at the bottom.

These folks might have spent a lot, but a year ago and not bought anything since, or perhaps made one small purchase more recently. As you can see further up, small purchases made often can make a brilliant customer, and we would want to attract more of them if we can service them without too much hassle and cost.

Using these Results

If you do nothing else, it is a good idea to split the list up into Quintiles/Fifths, the top 20% are your Gold Customers, the next Silver, then Bronze. The next 20-40% of your customers need looking at to learn where you have gone wrong.

Already we have data that can provide us insights.

We have identified two types of customer that just looking at revenue wouldn’t have revealed. High ticket customers who go away, and new, low ticket customers. Those latter type might turn into brilliant customers but it is too early to tell, but they will not grow into top customers without our attention. The former type of customer likely went away because of either poor customer service or because of some other bad experience – ie, we dropped the ball.

If we only looked at $ we would have thought the former were fine and might have considered latter not worthy of much attention compared to the high-rollers!

In the next part of this series we will look at two additional metrics that can tell you a great deal about how profitable these customers really are, Engagement and Support.

Latest From Our Blog