Which of your stores should share a target?
Twenty-four stores grouped on revenue, basket and collection share. The group with the highest average revenue has a basket barely half the size of the top group, which is the sentence that tells you an estate-wide target is the wrong instrument.
Operations Intermediate Clustering free
After you install, this is the model to open.
Which Stores Behave the Same Way?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- Your 24 stores
- 3 kinds not one estate: clusters of 6, 9 and 9
- The big-basket six
- $89 average basket on $65.5K weeks, 5.5% click and collect
- The collection nine
- 42% click and collect share, on $33 baskets
- The middle nine
- $69.1K a week, $53 baskets, 22% click and collect
Twenty-four stores, described by what they take, what a customer spends per visit, and how much of the trade is collection rather than shopping. With K-Means selected and k set to 3, click Run. Six stores cluster around $65.5K a week, an $89 basket and 5.5% collection: destination stores where people arrive to buy something considerable. Nine cluster around $42.8K, a $33 basket and 42.4% collection: sites that work mostly as pickup points with a shop attached.
And nine cluster around $69.1K, a $53 basket and 22.2% collection. Read those three centers in the right order and the surprise is in the third one. The middle group has the highest average weekly revenue of the three, $69.1K against the destination group's $65.5K, on a basket barely more than half the size. It gets there on volume, and it is the largest revenue block in the estate.
An estate-wide target expressed as basket growth asks that group to become something it is not, and an estate-wide target expressed as collection growth asks the destination group the same. Neither target is wrong; both are wrong applied to all twenty-four stores, and that is what the clustering is for. Two stores sit on a boundary and are worth naming rather than hiding: Marsh Lane at $49K with a $46 basket and 29% collection, and Weavers Gate at $46K with a $45 basket and 31% collection.
Both look like middle-group stores and both are assigned to the collection group, because the tool puts the three columns on a common scale before it measures anything, and on that scale their low revenue and high collection share outweigh a basket that is only just above that group's. Those two are the stores drifting from one operating model to another, which is a more useful finding than the cluster label itself.
Rerun at k = 4 and the middle group splits by size, into five large stores averaging $76.8K and six smaller ones averaging $55.5K, with the two boundary stores rejoining the smaller half. Rerun at k = 2 and the destination and middle groups merge. There is no correct k, only the split you can write a plan against. An empty Seed box uses a fixed default, so this run reproduces as it stands, but put your own number in before you circulate anything so that a later change to the data is the only thing that can move the answer. To use your own estate, paste one store per row with numeric columns only and widen the range.
The model
It arrives on a tab called Template: Which Stores Behave the Same, carrying these columns:
- Weekly revenue ($K)
- Average basket ($)
- Click and collect share (%)
- Store
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Is unit cost driven by the price or by the volume?Will Unit Cost Land Where the Budget Says?
- The business case says yes. What are the odds?Is the New Machine Worth It?
- How far short could the season finish?Will the Season Hit Its Number?
- Which of your peak-season fixes actually pay?What Could Go Wrong at Peak?
- Tool up or keep paying the supplier?Make It or Buy It?
- Is the round your driver does now the shortest?Which Order of Stops Makes the Shortest Delivery Round?
Every model like this one, and the method behind them: Machine learning in Google Sheets.