Part 2: Analytic Customer Data Mining

In part 2 of this series on analytic customer data mining, we will focus on how to perform a recency/frequency analysis on your customer database. This process is more commonly known as RF scoring.

RF scoring will allow you to quickly identify your most valuable customers. Customers with high RF scores are most likely to respond to your promotions and marketing communications. This is the whole basis behind analytic customer data mining.

It will be easiest if you import your customer database into Microsoft Excel or a similar spreadsheet program. If you already keep your database in Excel, then you're in good shape.

Before you actually start the RF scoring process, you'll need to consolidate the transactions of each unique customer in your database. A transaction is defined as any contact point with the customer that you measure and keep in your database. Below is a list of some possible transaction types.

• Filling out a form on your website
• Placing an order over the phone
• Purchasing a product from your website
• Downloading an ebook from your website
• Signing up for your monthly newsletter
• Providing a donation
• Paying for one of your services (e.g., consulting)

Depending on your business and what you already code into your database, you may have additional transactions that can be used for your RF scoring and analytic customer data mining. It's ok to only have one type of transaction in your database. Maybe the only transaction you record is the purchase of an item. That's perfectly fine but if you record additional transactions, use them all for this analysis. You will want to consolidate all the purchase transactions for each customer so that you know how many times they have purchased from you since they were added to your database.

The other piece of data you will need before running an RF analysis is the date of the most recent transaction for every customer. As mentioned in part 1 of this series, the date of last transaction is known as the recency.

In your Excel customer database, you want to see the date of last transaction (recency) as well as the historical sum of all the transactions you have measured for each of these customers.

Let's take a look at an example. Suppose you have a customer that filled out a form on your website in order to download a free report (transaction #1.) Three months later, this same customer purchased your ebook (transaction #2.) Then, 6 months later on 2/18/2008, the same customer purchased your consulting services.

Here's how you want the data summarized in Excel.

Using an ID number that is unique to each customer allows you to leave out all other customer information like first name, last name and address info that would only make the spreadsheet harder to read. This is just a suggestion. You can use the complete customer record if you wish but when performing analytic customer data mining, you'll find things easier to scan if you eliminate unneeded information.

Ok, we're just about ready to start the RF scoring process. RF scoring can be useful even if you have a small database of only 50 names but it will become more powerful as your list grows. Look at the example below to see how a small list of 15 names might look before the RF scoring process.

Here is the step-by-step process to RF score your customers using the method of quintiles.

Step 1: Sort by recency
You will first need to sort the data by the recency column, from most recent to least recent. Use the sort function within Excel and be sure to select the entire data set. If you are not sure how to use the sort function, Excel's help function will give you detailed instructions on performing this operation.

Step 2: Add column for recency quintile
Add a fourth column to the end of your Excel sheet and label it "Recency Quintile." Now divide the total number of customers in your database by 5. The resulting number is how many customers you have in each quintile. For example, if you have 100 customers, you will have 100/5 or 20 customers in each quintile.

In the "Recency Quintile" column, plug in the number "5" for the top quintile of customers. If your database contains 100 customers, you will place the number "5" into the "Recency Quintile" column for the first 20 records. Then, place a "4" into the same column for the next 20 records and continue until you've placed a "1" into the last quintile of records (the least recent.)

At this stage of our analytic customer data mining, we have the customers ranked into quintiles by recency, with "5" being the most recent and "1" being the least recent customers.

Here's what our 15-name database would look like after performing this step.

Note that when you do this sorting, the recency data will no longer be ordered from high to low.

Using our small 15-name database, here's what you would get.

Here's how it works.

Create a new column at the end of your spreadsheet and label it "RF Score." Click in the first blank cell of this column and type in "=concatenate=(d2,e2)" and then hit "Enter." What you are doing is taking the number in cell "d2" and the number in cell "e2" and placing them together in a new cell. See the figure below for clarification.

Once you have typed in the concatenate formula, hit "enter" and you will see the recency and frequency quintile numbers side by side in this new "RF score" column. Copy the formula in this first cell and paste it into all the cells below until you've reached the bottom of the spreadsheet. You now have a new column labeled "RF Score" that contains a 2-digit number in each cell. The first digit of this number represents the recency quintile score while the 2nd digit represents the frequency quintile score. All the power of analytic customer database mining comes from this 2-digit RF score.

The last thing to do is sort the spreadsheet by the "RF Score" column from high to low. In our 15-name database example, here's how the final RF scoring spreadsheet would look.

Want to know how to use this information to to maximize the results of your marketing program? I thought you might. Just click here to read part 3 of this series on analytic customer data mining.