Database Marketing and RFM Analysis
In order to make more efficient use of marketing dollars, Cedar Springs Christian
Stores, Inc. has adopted a new database marketing technique for direct mail marketing. They are now
using RFM analysis techniques for our mailing list selection. RFM analysis, based on recency,
frequency, and monetary values, helps to predict which customers are more likely to respond to a
mailing. By mailing to only these select customers, response rates skyrocket, boosting profits
dramatically. I was given the responsibility of learning how the analysis process works and developing
a plan for applying the process to the current direct marketing procedure. A design for the project
was then drafted and reviewed by the marketing and MIS departments. The specifications included
methods for the extraction of data from the point of sale system (POS), calculating of RFM rankings, a
user-friendly selection screen, and the production of RFM analysis reports.
After extracting customer and transaction information from the POS, I wrote Perl
scripts to parse the data and store it in a relational database (MySQL). I designed and implemented a
and DHTML to automate the maintenance of the database and assist the user in the selection process.
Through the web the user can add and modify details for mailings, mailing methods, company branches,
and distribution areas. These screens allow the user to create custom parameters and criteria for
each mailing. All maintenance screens provide a simple GUI that hides the complexity of RFM analysis.
I then wrote several programs designed to analyze the transaction data and assign each customer an RFM
ranking. Because of the large amount of customer and transaction data in the database, these programs
required extra effort in optimizing the performance of the sorting algorithms. The first of these
programs examines the transaction database and then updates the last sale date, total spent, and
frequency fields in the customer database. The second program creates a universe of discourse from
the customer database based on several factors including customer type, distribution area, and valid
address information. The customers in this universe are then sorted and assigned an RFM ranking. The
RFM cells to mail to are then selected by the user from the web-based menus.
After the promotional period, the response rate of each cell is analyzed and reports
are generated. These reports show response rate and profit for each RFM cell and help in the
selection process for future mailings. I then worked to automate the entire process using cron and
phantom so that all analyses are completed during the night. This reduces the load on the server
during the day and makes current reports available each morning to the appropriate managers.