Loyalty Spoilers: Merchanics

Here's one approach to running a Loyalty Spoiler analysis.


Step 1:  Identify all items sold in 4th quarter of 2011.


Step 2:  Identify all customers who purchased merchandise in the 4th quarter of 2011.


Step 3:  At the end of the 4th quarter of 2011, calculate the future value of all customers who purchased in the 4th quarter.  Many folks will predict future value as the demand you expect this customer to generate in the 1st quarter of 2012.


Step 4:  Measure the repurchase rate (you can measure $/customer as well, it's just noisier) of all customers who purchased in the 4th quarter of 2011, measuring repurchase rate during the 1st quarter of 2012.


Step 5:  Create a spreadsheet, one row per customer/item combination, containing the following fields.

Step 6:  Aggregate the dataset to one row per item number.  Calculate the mean value of predicted customer future value.  Calculate the mean value of Q1 - 2012 repurchase rate.


Step 7:  Select all items that sold at least 50 units in Q4 - 2011.


Step 8:  Run a weighted least squares regression analysis.

• Independent Variable = Average Predicted Customer Value.
• Dependent Variable = Q1 - 2012 Average Repurchase Rate.
• Save the Predicted Value = Predicted Average Repurchase Rate.

Step 9:  Create an index (this step is important):

• Index = (Predicted Average Repurchase Rate) / (Average Repurchase Rate) - 1.

This index is a percentage.  It tells us how much an item spoils future customer loyalty.

For example, an item that has customers with an average predicted future value of $100 should yield an average repurchase rate of, say, 20%.

Now, let's say that an item instead yields customers with an average repurchase rate of 15%.  The index, then, is (15% / 20%) - 1 = -25%.  This item spoils future customer loyalty by 25%.

In the next post in this series, we'll address a practical outcome of the methodology.