Customer segmentation - dynamic template chart

posted Feb 9, 2014, 2:23 PM by Krisztina Szabó   [ updated Feb 10, 2014, 1:33 PM by r ]
Freddy: I think you can find the way out by yourself, can't you?
Inspector Kemp: Of course. Until we meet again, Baron.

by The FrankensTeam

This post is just to share our template file created to visualize customer segmentation. The problem came up in a post on Chandoo's site written by Jeff. There was a great discussion about the different visualization ideas - worth to read the comments of the post too. Also, you can find some more additions in this post on Junk Charts by Kaiser Fung.

So here is our version:

The base idea is to highlight groups using the average revenue of the group. The light-colored boxes serve as if all the customers from the group would provide the same (=average of group) revenue. This way the area of the light-colored box gives the total revenue of the group. Unfortunately the area is not easily comparable, so we added the total revenue of the group next to it as a label. If you use equally sized groups, so for example all the groups represents 50 customers, you can compare the size of the squares.

Some technical details:
We used stacked column chart type with 0 gap between the columns. The box and group labels are added with the help of xy scatter series.
Your input data should be on Support sheet column A. You can see a table here - this makes the calculations and the chart dynamic.The file is prepared to work with up to 10 groups. The base of the chart is stacked column type: for each group two data series are added; one for the original values and another for the average (the missing part only, it is colored lighter).
On the Data_and_Settings sheet you can find a column for Separator points - here you can set at which customer the group should end. Then in the next column you can set a label for the group. (The green color marks the next input cells.)

How to determine the number of customer for the groups? The easiest way is the visual analyse. First, set up only one group with the total number of customers, then take a look at the chart, and move your mouse above a data point where you can see a significant change in the trend of the revenues:

So your first group should be set by adding 14 as separator point... end so on.

If you are interested in this solution, you can download our file.