I created a customer retention grid that tracks customer counts by territory by year. The sales managers have loved it. The numbers were terrible, but now they know what they didn't know and can address it.
The follow up question that the report generated was calculating customer retention vs product line count. We have five major product lines and the sales managers want to see if there's a correlation between the number of product line purchases and retention, which I think is a great question. The assumption is that if they only buy one from one product line, the retention is low, but if we get the whole system in place and they buy 4 or 5, the retention is high.
What I'm stuck on is how to group and display the data.
- Customer A buys 1 product line in year 1, then 3 in year 2, and then 2 in year 3, and then 0 in year 4.
- Customer B buys 1 product line in year 1, then 1 in year 2, and then 0 in year 3, and then 0 in year 4.
- Customer C buys 2 product lines in year 1, then 2 in year 2, and then 2 in year 3, and then 1 in year 4.
- Customer D buys 2 product lines in year 1, zero in the rest
My initial thought is to average the counts and then graph based on that.
Is there a better / fancier / cooler way to calculate what I'm trying to achieve?