If you don’t want to keep sending your offers to poor responders, here’s a well established method for segmenting your customers and identifying the best from the rest.

To make this calculation, you’ll need transaction dates, a customer ID and the value of each purchase.  This example will use Microsoft Access to help with the calculations.

What we will end up with is assigned quintile values for Recency, Frequency and Monetary. By combining those values into one score, all customers are ranked with the best rising to the top of the list.

First, arrange your data into a flat file format such as an Excel spreadsheet.  Add a field entitled Recency Index.  Next, sort the file by transaction date.  Take the top 20% of your records and assign them a value of “5” in the Recency Index.  Repeat this process for each quintile so that the bottom 20% have a value of “1” in the Recency Index column.

For the next steps, I recommend using Access, but it can be done using Excel and a lot of manual entries.

To create the other indexes, import the spreadsheet into Access.  Add three fields to your table:  Frequency Index, Monetary Index and RFM Index.

Next, write a query that groups your records by customer ID and provides a count by customer ID.  Write another query that updates the resulting count to the Frequency Index field. 

For the Monetary measure, write a new query that groups on customer ID and sums the value of the purchases for each customer ID.  Write an additional update query that writes the resulting value to the Monetary Index. 

Your final query will be an update query, where you will update the RFM Index with the product of the Recency, Frequency and Monetary Indexes.  That is, you will multiply together the scores of the three indexes and enter the result as the RFM Index.

When you rank your customers, highest to lowest, by RFM Index, you’ll have a customer ranking that lets the most valuable customers rise to the top.

Note that you can add weighting to the formula that updates the RFM Index so to adjust the Recency, Frequency or Monetary values as you judge best for your application.