The Basics of RFM Segmentation - RFM Reports

This section of the article describes a few reports that use RFM.

Mail Planning and Sales Reporting

Sample Mail Plan (.xls, 22k)

This spreadsheet shows a small part of a sample mail plan. The customer count, estimated response rate and estimated average order size are plugged in to calculate forecasted orders and sales.

Add mailing costs and gross margin to compute expected marketing margin.

Once the campaign is over use the same format for campaign results. Plug in the actual customers mailed, orders, sales, costs, and gross margin to get the campaign's actual response rate, average order size, profitability, etc.

RFM Cell Groups

RFM Cell Groups (.xls, 35k)

This is more of a scratch pad than a report. I have used this spreadsheet to aid in planning the mailings for a half or full year. It allows you to see mailing costs as you add and subtract cell groups.

It is limited but helps to quickly get the first cut of a plan that hits a given direct selling cost budget.

RFM Results with Customer Retention Over Time

RFM Results (.pdf, 56k)

This example shows a complete RFM report. The report's purpose is to study customer retention by cell.

The RFM of each customer is calculated as of January 1 of the current year. Periodically during the year this report is run to show how retention is holding up cell by cell.

Compare the current report to a similar one from the previous year to see if retention is improving or declining.

The output was created using Microsoft Access, and is easiest to reproduce as a PDF file. The data is totally made up so some things don't make sense!

RFM Dynamics

RFM Dynamics (.xls, 33k)

This is a fragment of a report comparing customer retention by RFM Cell over multiple years. Three RFM reports (similar to the one above) are run, one for each year, with retention results calculated for the following year. The key numbers are placed next to each other by cell. You can easily see where you need to improve your efforts - or where you are doing well.

A fourth RFM gives the current customer count, shown in the last column. Start with these counts, plug in estimates for retention and sales per customer (based on the prior years), and you have an excellent sales forecast.

This report often shows in stark terms that a reduction in sales volume is due to a lack of new customers. You see the retention and order size hold up well, but the best RFM cells just keep getting smaller. This helps to justify the expense of prospecting.


Back to the start: Introduction

All sections: