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 web-based front end to the database allowing the user to view and maintain RFM data. I used Javascript 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.