Catching stockouts before they happen, for a leading EU skincare brand

The problem

A leading EU skincare and cosmetics brand with around 70 SKUs across eight categories, sold through retailers in several countries. Raw materials take two to four months to procure, and a deposit on every production order is paid upfront — so a missed reorder is expensive and slow to undo.

"Someone in the chain forgets to order something on time, and we run out of stock six weeks later."

Until this system went live, the analysis to catch that — joining live warehouse stock with sales velocity, projecting three months of demand, flagging the SKUs at risk — was done by hand in spreadsheets every week. Slow, error-prone, and easy to drop a row. When a row was missed, the order didn't happen; when the order didn't happen, production missed the procurement window; and the SKU went out of stock for two months.

The system

Every Monday morning, an automation bound to the team's master sheet runs end-to-end, before anyone is at their desk:

1. Reads the week's exports — warehouse stock from the ERP, plus 7-day and 30-day sell-out windows from the sales BI tool.
2. Cleans the data: drops expired lots, non-canonical categories, and discontinued SKUs from a maintained list.
3. Joins stock against sell-out velocity by exact product name — a contract guaranteed at the data layer, so no fuzzy matching.
4. Forecasts three months of demand two ways in parallel (7-day × 12 and 30-day × 3). The gap between the two is itself a signal: acceleration, deceleration, or a one-week noise spike.
5. Classifies each SKU on a surplus scale and takes the worst tier — and stays null-safe, so a SKU with no sell-out match is never falsely flagged.
6. Writes a styled weekly tab to the master sheet. One tab per week accumulates as a free, per-row audit trail.
7. Sends an email digest to the founder and the production lead, action rows first, ordered by deficit. Quiet weeks send nothing — so the alert is never trained into noise.

The only manual step left is five minutes on Sunday night: drop three export files into a shared folder. Monday morning, the action list is already in the inbox.

How it was built

The architecture and code were built by a Claude Code agent under my direction. My job was the commercial work the agent can't do alone: scoping the problem with the founder, sourcing the data contracts (what fields exist, how they join, what gets dropped), running the feedback loop that simplified the output from 16 columns to 12 and tuned the alert tiers, and integrating it into the team's existing workflow so nothing about how they already operate had to change.

That's the Mayer Commercial pattern: the technical heavy lifting is automated; the commercial judgement — what to measure, who needs to act, what gets flagged versus ignored — is what I bring.

What's operational today

• A passing unit-test suite over the pure logic — cleaning, joining, forecasting, status assignment.
• A live Monday-morning run: writes the weekly tab and sends the digest only when action is needed.
• Zero infrastructure cost — it sits inside the team's existing Google stack. No new platform, no new licence.
• A weekly tab history building up as a free audit trail: after a year, ~50 tabs of "what was true when, and what we did about it."