The data
One table of 51,243 purchases made by 18,417 customers between 2005 and 2015: a customer id, an amount and a date. The dataset is simulated and supplied by the course.
The method
Recency, frequency and average spend are computed per customer in SQL. Customers are then placed in rule-based segments: inactive, cold, warm and active, with warm and active split further into new customers and high or low value, where high value means an average purchase of $100 or more.
The same segmentation is rebuilt as of the end of 2014. Comparing the two years gives a transition matrix: the share of each segment that moves to every other segment in a year. Applying that matrix repeatedly projects segment sizes forward, and revenue per segment with a 10% discount rate turns the projection into a value for the customer base.
A separate script calibrates two scoring models on 2014 data to predict 2015: a multinomial logit for the probability that a customer stays active and a log-log regression for how much they spend.
What the scripts produce
About half of the customer base, 9,158 of 18,417, had not purchased in three years. The active segments together hold 5,398 customers.
| Segment at end of 2015 | Customers |
|---|---|
| Inactive | 9,158 |
| Cold | 1,903 |
| Warm, high value | 119 |
| Warm, low value | 901 |
| New warm | 938 |
| Active, high value | 573 |
| Active, low value | 3,313 |
| New active | 1,512 |
Under the scenario coded in the projection script, 1,000 new customers a year and a 10% discount rate, discounted revenue for 2016 to 2025 comes to about $3.31 million.
What this does not show
The data is simulated and the structure of the scripts comes from the course, so this is evidence that I have worked through the methods, not an original analysis. The scoring models are calibrated the way the course does it, without a separate hold-out test. Nothing here was used on real customers.