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?


There are probably a good few options, as usual I would start with something simple (possibly similar to what you have in mind).

  • Simple year1 vs year2 or year2 vs year3 global comparison: proportion of customer who go from number X in year N to number Y in year N+1. A heatmap would probably work well with this kind of data, and if you want some fancy visualization to impress your boss you could try a chord diagram or a Sankey diagram. Of course you can vary the input with average of the past 2 years, etc.
  • Since you have data by territory and it appears to show different patterns, it might be interesting to look at something by territory. I'm not sure which value should be used as a "summary" of the trend for a territory, I can think of a few options:
    • simple difference of the mean number of products between year N and year N+1 (i.e. shows positive or negative trend)
    • some measure of how different the distributions are, i.e. how much variation from year N to year N+1
    • Simple Pearson correlation over all the customers between number of products in year N vs year N+1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.