Retail store manager to retail analyst: basket and shrinkage data

Mistake 1: using a generic public dataset instead of your own trade

Many career-switchers open a well-known sample superstore dataset, build a sales-by-region chart and stop. For a store manager this wastes the one thing you have that other applicants do not: operational knowledge. You know that a Sunday evening bill looks different from a Tuesday morning bill, that a stock count after a festive sale shows more variance, and that a promotion endcap changes what sells beside it.

Fix: build a synthetic dataset shaped like your store's actual records, and say plainly that it is synthetic. The records worth mirroring:

• POS bill-level data — bill number, time, items, quantity, value, payment mode.
• Daily sales report (DSR) — sales, bills, average bill value, units per bill.
• Stock count and shrinkage report — book stock versus physical count by category.
• Footfall counter log — entries by hour, if your store has a counter.
• Markdown and damage register — what was cleared, at what discount, why.
• Staff roster — coverage by hour, which explains conversion dips.

Mistake 2: reporting pairs bought together without checking lift

The classic first retail analysis is "what is bought together". The mistake is ranking pairs by raw count, which just rediscovers your best sellers. Milk appears with everything because milk appears in most bills. Lift corrects for that by comparing how often two items appear together with how often they would if purchases were independent.

from itertools import combinations
from collections import Counter

# Synthetic grocery bills: each set is one bill
bills = [
    {"milk", "bread", "eggs"}, {"milk", "bread"}, {"milk", "tea"},
    {"tea", "biscuits"}, {"tea", "biscuits", "milk"}, {"bread", "butter"},
    {"bread", "butter", "milk"}, {"milk", "eggs"}, {"tea", "biscuits"},
    {"milk", "bread", "butter"}, {"milk"}, {"biscuits", "tea", "sugar"},
]
n = len(bills)
item_count = Counter(item for b in bills for item in b)
pair_count = Counter(pair for b in bills for pair in combinations(sorted(b), 2))

print(f"{'pair':<22}{'bills':>6}{'support':>9}{'lift':>7}")
rows = []
for (a, b), c in pair_count.items():
    if c < 2:            # ignore pairs seen only once
        continue
    support = c / n
    lift = support / ((item_count[a] / n) * (item_count[b] / n))
    rows.append((a + " + " + b, c, support, lift))

for name, c, support, lift in sorted(rows, key=lambda r: r[3], reverse=True):
    print(f"{name:<22}{c:>6}{support:>9.2f}{lift:>7.2f}")

On these twelve synthetic bills, bread with milk is among the most frequent pairs, but its lift is close to 1 — they co-occur roughly as often as chance predicts, because milk is in most bills. Tea with biscuits and bread with butter have lift well above 1, which is the pattern worth discussing with a category manager. The c < 2 filter matters too: on small data, a pair seen once can show an enormous lift that means nothing.

Mistake 3: calculating shrinkage against the wrong base

Store teams often quote shrinkage as the value of missing stock divided by closing stock value. Retail finance usually expresses it against sales for the period. Neither is universally "right", but mixing them across months makes a trend meaningless. In your write-up, state the formula once, for example shrinkage % = (book stock value − physical stock value) / net sales for the period, and apply it consistently by category.

Mistake 4: a conversion rate without footfall context

Conversion is bills divided by footfall. If the counter double-counts staff or groups, conversion looks worse than it is. A good project checks the counter against hours when the store was closed (entries at 7 a.m. before opening are a red flag), and shows conversion by hour next to the staff roster, because coverage gaps explain more dips than merchandise does.

Mistake 5: the dashboard with fifteen visuals

A store manager reads a DSR in thirty seconds. Respect that. A portfolio dashboard should have one page with three or four visuals — sales versus last week, conversion by hour, top lift pairs, shrinkage by category — and one written recommendation.

The real gap for store managers

You manage people, stock and customers well. What you usually have not done:

1. 1SQL — joining bills to an item master and a calendar table, and grouping by week or hour.
2. 2Careful definitions — writing down exactly how each metric is computed.
3. 3Python or a BI tool — enough to reproduce your analysis next month without copying and pasting.
4. 4Statistical caution — especially with small stores where one bulk order distorts a week.

Category or merchandising analyst first

A retail analyst, category or merchandising analyst, or store operations MIS analyst role is the natural first step: your floor experience makes your analysis credible to the teams who act on it. A general junior data analyst role in consumer businesses is a sensible second target. Check which analyst skills your resume already shows with the free skill check.

Why a store manager wants to sit behind the data

"I managed a grocery store for five years and read the DSR every morning, but we always talked about what sold, not what sold together. I learned Python and ran a basket analysis on synthetic bills shaped like ours. The most frequent pair was bread and milk, but its lift was about one — milk is in most bills — while tea and biscuits had real lift. That's the kind of correction I want to be making for a category team, and I understand how the numbers are produced on the floor."

If you are weighing whether to go further into modelling later, data analytics vs data science sets out the difference. For guided practice with SQL, Power BI and verified AI-assisted analysis, see the Data Analyst programme.