6. Segmenting by Customer Size
Profitability and cost can often vary by customer type. It's often worth segmenting customers by industry or by size to gain additional insights into your customers.
Segment data by customer size
- Segmentation by size is very easy to do
- Create upper and lower limits for each segment
- Use COUNTIFS and SUMIFS to find the # customers and total revenue in each segment
- Pie-charts and 100% stacked bar charts work well for segmented data
- 100% stacked bar charts in my opinion are easier to read than pie-charts
- However, you will need to calculate the percentages manually before creating the chart
SHIFT + →: Select next cell
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
CTRL + X: Cut selected item(s)
CTRL + V: Paste selected items(s)
ALT + N , C: Create column chart
ALT + E , S , F: Paste formulas
In our initial discussions with the Bloom Acre CEO, he mentioned that understanding the size of his customers was important.
This is because larger customers tended to be more profitable for the company and he was eager to see how much of his revenue had come from this customer segment.
According to the CEO, the company designates a large customer to be one which has annual revenue of $20,000 to $30,000 dollars.
A medium customer, between 10 and 20,000, and a small customer, between 0 and 10,000.
In our dashboard input sheet, I've entered these values as the upper limit and lower limit for large, medium and small segments.
For each of these segments, I'd like to calculate the total number of customers and the total revenue.
Let's start with the total number of customers. And to calculate this, I'll use the COUNTIFS function.
And the COUNTIFS function will count the number of customers that have revenue of greater than 20,000 and less than 30,000.
That also check that the customer has not lapsed and is either new or existing.
So my first criteria range will be the revenue column.
And my criteria will be greater than or equal to the lower limit.
The criteria in a COUNTIFS function must always be a text string.
So I'll need to use the & sign to change the cell reference G3, to include it in the function.
Once I've done this, I can move on to my second criteria where again the range will be the revenue column, but this time, the criteria will be less than or equal to & upper limit.
And then the final criteria range will be status, and this will check that our status is not equal to lapsed. I can then close the bracket and press Enter.
And then I'll copy and paste for the remaining two segments.
And this now tells me the number of large medium and small customers that Bloom Acre has.
To calculate the total revenue in each segment, I'll use the SUMIFS function and the exact same criteria I used for the number of customers.
Given that it's the exact same criteria, I'll actually jump back into the formula with F2 and holding Ctrl + Shift, I'll select the criteria.
I'll then copy with Ctrl + C and escape.
I'll then write my SUMIFS formula. Equals SUMIFS, open the bracket, enter the sum range, which is revenue, and for my criteria, press Ctrl + V to paste.
I'll then close the bracket and press Enter.
And this provides me with the revenue for the large segment.
Again I'll copy and paste for the remaining two cells.
The chart for these numbers will ideally show each segment as a percentage of the total.
Pie Charts and 100 percent stacked column charts are well suited to this task.
I find the stacked column chart easier to read than the pie chart so I'm going to go with this option.
Before we do this, I need to convert my values into percentages.
Off camera, I've created three new columns the first of which contains the large, medium and small, which is simply the labels for my chart and then the number of customers and revenue as a percentage of the total.
To calculate the number of large customers as a percentage, I'll simply take the value and divide by the total which is simply the sum of all three segments.
And I'll anchor this array.
I'll then copy and paste for the remaining two cells.
I'll do this as well for revenue so I'll take the large revenue amount and divide by the sum of the total again anchoring this array.
Then copy and paste for the remaining cells.
The last thing to do is to take these six values and format as percentage.
To do this I'll use a shortcut, Ctrl + Shift and the % sign.
We can now take this data and plot it as a stacked column chart.
So I'll select my data Alt + N C to bring up the column chart dialogue box and then select 100 percent stacked column.
I'll then need to select my data, switch the row and column, which are currently the wrong way around and then press Ok.
And as you can see that a smaller number of large customers contribute a lot more in terms of revenue.
Off camera, I'll make some formatting changes so that we're now ready to add our chart to the dashboard.