Managing by Averages… of Averages

Much of my visual analysis is performed to help large companies – usually retailers and their suppliers – to better understand a perplexing problem or opportunity in their organisation. How can we reduce waste in our stores? Which promotions drive the desired behaviour in customers and why? What effect does non-delivery at depot have on availability in store? What’s the optimum product range for our biscuits category? etc.

These questions have always been addressed before we’re engaged – they’re not new questions, but the answers yielded by traditional techniques are not always well-understood. If they were, I wouldn’t be asked to shed new light on them. We often surprise clients by asking for a lot more data, in a lot more detail, than they are used to dealing with; most perform “last mile” analysis and report presentation using Excel and so tend to prefer a relatively small and nimble data set from which they can create a suite of cross-tab reports and a few bar charts.

A major problem with this approach, and a great deal of business reporting in general, is that it presents senior management with a host of averages:

• category average margin
• average space in store
• average basket size
• average customer spend
• average shelf life etc.

Stephen Few (data visualisation luminary) quotes Alfred North Whitehead on his Perceptual Edge website;

“Seek simplicity and distrust it.”.

Why do I consider presenting average answers a major problem? Because high-level averages – or the Averages of Averages – can present a highly misleading picture; they over-simplify and present a summary which is untrustworthy. For example, we all know that the nuclear family was supposed to have 2.4 children, but we all also know that no family has 2.4 children. We can use the average as a guide suggesting that it is common for families to have two or three children, but some may have none or one and fewer will have five or six. In this case we can see the average for what it is, but averages are not always such useful guides. What does an average customer spend of £25.00 tell us? In isolation, almost nothing – other than we are unlikely to be a small corner shop, where we can imagine few customers spending £25 per visit.

Through exploration and visualisation of underlying data, we can discover and communicate far more about the richness of our customers and head towards the goal of any commercial analytical exercise – the oft-requested “actionable insight.”

Let’s consider a scenario where senior management of a fictional online retailer has asked us to recommend means to improve profitability (a typical request). We are able to source data about sales of products to customers and can use this information to present insightful information which the executives can use to change how they conduct their business. We read some their existing reports, which have identified that the average selling price of an item is £78.37 and that the business operates with a 12% profit ratio (gross profitability) – this needs to be improved, but how?

Graphing the average Profit Ratio and Selling Price for all orders processed over the past year does indeed show the averages presented in company reports. Of course, the retailer’s reporting team has gone further than this and reports on performance by Customer Segment and by Product Category. We can visualise this information in a similar way, firstly by Customer Segment:

Here there is a slight deviation from the company average – shown as the red star, here and on all subsequent images – but the 3 segments (Consumer, Business – not labelled in the image above – and Corporate) do behave similarly to it.

However, when we look at performance by Product Category, we start to see a more significant divergence from the average:

Products in the Office Supplies category sell at a significantly lower price point than the company average (£41.95 vs £78.37) but a higher margin (15% vs 12%), whereas the opposite is true in the Furniture category; here higher priced products (average £135.64) have a significantly lower margin (7.8%).

This is interesting and prompts a more detailed breakdown by Sub-Category. When asked why this information isn’t presented in company reports, the reporting team advises that it has been in the past, but the large tables that result aren’t read by managers and are deemed unusable. When we plot this in visual form, however, the results are easily read:

At which point, the Tables subcategory leaps out, at the bottom-right of the scatter plot, as presenting an average selling price of £346.26 but an average margin of -18.5%. Suddenly the lower margin achieved in the Furniture category has a focal point; selling loss-making tables is a questionable business strategy (unless these form a loss-leader for otherwise profitable customers?).

Taking a similar approach to Customers – showing detail of individuals, rather than the segment summary – shows further wide disparity of price point and margin:

Top-right we see our ‘best’ customer this year; Patrick Jones has paid an average of £568.57 for items at an average of 50% margin. Bottom-left we see Becky Martin has paid an average of £9.47 at a margin of -240%! Now any business prepared to sell items for less than 30% of their cost price has a serious problem – but perhaps this represents a discount offered by a salesperson who doesn’t have visibility of true cost price? Certainly loss-making sales like this should be clearly visible to senior management so that corrective action can be taken.

This picture is further enhanced by converting the (2-variable) scatter plot into a (3-variable) bubble chart, using Sales value to size the bubbles:

Here the scale of Patrick Jones value (top-right) is more clearly displayed as a significantly large bubble. Note that we can’t easily tell exactly how valuable Patrick is – size is a useful indicator, but not a means of assessing exact values (unlike our X & Y axes) – but we can tell that his business is more valuable than that of most others. We can also see that Corinna Mitchell is worth further exploration, given the size (Sales Value) of her plot and the negative margin associated with it.

We can add total Profit (or Loss), using colour to present this fourth variable, to highlight the variability in cash impact of our customers:

Corinna and Patrick now stand out even more clearly, and we can now easily focus attention on large dark orange plots – these are customers who spend a considerable amount with us, but generate a loss in aggregate. This is actionable insight; we can consider repricing for these customers, or choosing not to take their business. To explore their overall value, we might allow the user to review the order history for each to see if negative margin does represent a loss-leader for a long-term profitable customer (probably desirable) or simply a loss-making customer (undesirable).

Here we have created a simple interactive dashboard, based on these principles but exploring average order value (rather than item price) to encourage consideration of a customer’s lifetime value. In these examples we are looking for customers with 4 or more orders:

Here we can see that customer Delfina Latchford has spent a total of £13,192.71 with the retailer at an average margin of 17%. Delfina’s second order was sold at a loss, but she has gone on to order with solid margin and has placed 4 orders in the most recent year (2014). This suggests that the decision to server her at a loss back in 2011 has been repaid.

However, if we look at the long-term value of Maureen Grade in the same way:

Here we started out with a profitable relationship back in 2011, sold at cost in 2012, and have then sold at an aggregate loss in 2014; this is the inverse of a desirable trading pattern, and one which needs dealing with – either revising the discount(s) that Maureen receives, or ceasing trading with her or her company.

So looping back to the original question posed by senior management – “How can we improve profitability?” – we now have a simple visual explanation of profitability, which we can explore by Customer or Product, and a recommendation to review customer discounting policy in the light of long-term customer value. The result may be the loss of some customers, but a significant increase in profitability and the ability to invest in securing the business of the right long-term customers.

We have moved away from Averages of Averages, providing a reasonable balance between simplicity and complexity; through Data Animation we have delivered simple presentation of more useful levels of detail, and limited intuitive interactivity (in the sample dashboard). The resulting tool, as simple as it is, enables effective presentation to senior managers, explaining the high degree of variation in customer profitability, and identifying customer relationships which require corrective action.