Which markets are actually performing, and which just look big?
A chain with 606 stores has a reporting problem, not a data problem. Total sales tells you where the stores are. It does not tell you whether a store is any good, and the two answers point in opposite directions more often than anyone expects.
This is a working dashboard for a made-up retailer. Pick a measure, a time window and a store format, and every market recolours: seven measures, five windows, four formats. California turns out to be an ordinary performer carrying a lot of volume. Utah is the best market in the chain and you would never find it on a sales report.
The same numbers, and the same filled map, run in a downloadable Excel workbook, so the analysis does not stop at the edge of my website.
Interactive dashboard
Strongest markets
| # | Market | Value | Index | Stores | Region | Net sales, 24 months |
|---|
hover a market on the map and it lights up in the table too
The decisions behind it
Most of the work in a map like this is not the map. It is deciding what deserves to be on it.
No totals on the map
Colour a map by total sales and California is the darkest thing on it in every view, forever. You have drawn a population map with extra steps. So every measure here is a rate: sales per square foot, sales per labour hour, quota attainment, units per basket. Those compare a 6,000 foot store to a 28,000 foot one honestly. Totals belong in the figures at the top and in the table, where size is the point.
Return rate is the only measure where low is the good outcome, so it runs the colour scale backwards. It is the sort of thing that silently ruins a dashboard if nobody checks.
A colour scale that does not flatter
Each market gets an index from 0 to 100 where 50 is the benchmark. For level measures the index is a log ratio, so 1.75 times the benchmark and one part in 1.75 of it sit the same distance from the middle. On a linear scale those land at plus 75% and minus 43%, which quietly gives weak markets half the colour range and makes them look milder than they are. For measures of change like comp growth, the scale is linear around a fixed point, because zero growth is a real number in a way that an average is not.
The scale constants are fixed rather than recalculated per view, so a colour means the same thing in all 140 combinations. Change the period and you can compare what you are looking at against what you just saw. That is worth more than using the full colour range every time.
Comparing like with like
A state is scored against the US rate and a country against the global rate. Scoring US states against a global rate pushes the whole US map to one side and tells you about the international mix instead of about the states. Both benchmarks are a ratio of sums rather than an average of averages, so a market with two stores cannot drag the centre around.
Comp growth only counts stores that were open for the whole of the prior-year window, on both sides of the comparison. Without that, opening stores looks like growth.
How it is put together
Boundaries are public GeoJSON, simplified and served as two small files. The map is MapLibre GL JS with no basemap and no API key, because a choropleth does not need roads underneath it. The figures are pre-aggregated per period so switching views is instant rather than a round trip.
The Excel version runs the same arithmetic in worksheet formulas, using only functions that existed in Excel 2007, and it is generated by a script rather than assembled by hand. It ships with values already cached so the file is readable the moment it opens. That is only safe if the cached numbers are right, so a second copy is built, recalculated through LibreOffice, and compared against what Python computed. Forty-seven checks have to pass before it goes out.
It also draws the same filled map, which Excel is not supposed to be able to do. The Filled Map chart type that would needs a Microsoft 365 subscription and a live connection to Bing, so it is no use in a file somebody downloads. An ordinary scatter chart, though, draws a line as thick as you ask and breaks it wherever a value is #N/A. So every state is a stack of short horizontal lines three pixels apart, each drawn a shade thicker than the gap so the rows touch. Excel gives a whole series one colour and never a single point, so each performance band has to be its own series. Change a dropdown and 3,367 scanlines move between six of them at once.
What it will not do
- The data is invented. Ollie's Outfits is not a real company and every sheet says so.
- A map is not readable by a screen reader. The table under it holds everything the map encodes, sortable, and it is not an afterthought.
- Excel gets no hover tooltips, because that needs a macro and a macro-enabled workbook is a rude thing to email someone.
- The Excel fill is horizontal scanlines, so a steep border stair-steps by three pixels. The outline drawn over the top hides it until you magnify the map a long way.
the first Excel version was made of spreadsheet cells. the second was dots. both were clever and both looked bad