The data set contains the purchase history of 100 customers for certain hardware, software, and services products during the period 2001 – 2004. The data is organized in such a way that the date of purchase, product purchased, product category, and purchase amounts are given for each customer for each purchase occasion. For instance, customer 1 purchased Desktop PC (hardware category) for $500 during February 2001 (indicated by Feb-01). In addition to the transaction history, the data set also includes information such as number and type of marketing touches, and the contribution margin for each product. This dataset is given in the following file:CLV_data.xls.
Projection of Contribution Margin
In order to project contribution margin in a given year, we need to build the model for contribution margin. This is done by doing a linear regression with ‘Contribution margin in 2004’ as dependent variable and variables like ‘tenure till December 2003’, ‘average contribution margin per year for the period 2001-2003’, and ‘the average purchase frequency’. This model using the available data showed the following relationship as significant.
PredCM2004 = -17 + 0.76 * AvgCM2003 + 4*Tenure0103 + 23*Avg_Purchase_Freq, where
PredCM2004 = Predicted Contribution Margin in 2004
AvgCM0103 = Average CM (per year) for 2001-2003
Tenure0103 = Tenure till December 2003.
Applying this model, the predicted contribution margin in 2005, 2006, and 2007 can be computed. The values of predicted contribution margins are obtained by simply entering the values for Average CM and Tenure for appropriate time periods in the worksheet “Enter values” in the following file: CLV_Cal.xls.