We recently had a customer asking us for some assistance with a business challenge.
He had been asked to provide a report analysing their customer base by % contribution to company revenue. But that wasn’t all! He also had to produce an additional report that tracked the customers ‘lost’ by the company Year on Year by looking at sales activity.
This approach is also often used when looking at profitability, and many businesses like to classify their customers in this way:
- group ‘a’ of key customers make up 40% of our company revenue and group ‘b’ make up the next 30%
- which customers have we ‘lost’ or have become inactive over a period
This is where XLCubed’s capability and flexibility comes into play – we have an advanced selection mode for handling exactly this type of scenario.
We have two sample reports which handle the requirement to share with you.
Report 1 – Customer segmentation
In our first report, based on a demo dataset, imagine that the cities listed are customers:
Points to note:
- ‘A’ segment customers provide the first 40% of company revenues, and as such are carefully monitored.
- ‘D’ segment customers are of much less importance to the business
The report is built using two XLCubed Slicers, on Product Group and Year, and four XLCubed grids, one for each segment.
Grids
The grids are each restricted on Product group and Year by the slicers.
On columns, each grid displays the selected year as a value. Months within that year are displayed as a Sparkline.
Advanced selections
The segregation itself is carried out through an advanced selection on the Geography hierarchy, as shown below for the first grid (top 40%):
It’s important to note that the ranking is set as Top 40% rather than the default count, which would return the top 40 members.
Also key is that the Date.Calendar selection is set to $B$4 – which is in turn set by the Year slicer.
The second and third grids (40-70% and 70-95%) are a little more complex, the advanced selection is shown below.
To get 40-70%, the approach used is to first pull the top 70, then take away the top 40, which we have already retrieved in the first grid.
Use the same approach to get 70-95% by taking the top 95 and deducting the top 70. The fourth grid is bottom 5% of sales revenue in the same way as the first grid is the top 40%.
Lastly, the summary table at the top right uses standard Excel data bars to visualise the number of customers in each segment.
Report 2 – Customer tracking
This report enables the user to monitor the changes over time and quickly check which customers have been ‘lost’ over a period.
The user first selects a year and then another year to compare via the slicers at the top of the report. Slicers are also in place to select products and % to rank. Having the latter as a slicer allows for even more flexibility for the report user.
The two grids initially are created the same except for the years – the grid on the right then removes any entries that are also in the grid on the left.
Note that the two items bordered below are Excel ranges – these are the outputs from % to rank slicer and (in this case) the output from the ‘comparison’ date slicer. There is similar ranking set up for the other date slicer.
Once in place in the right-hand grid, this leaves only those entries which have been ‘lost’.
Check out these examples in more detail here