Part one argued that AI in FMCG analytics is not one thing, and gave a ten-rung ladder for saying which thing you are doing. This part builds the machine.
The machine is not a model. It is three workflows sharing one fact table and one queue, and the interesting engineering is almost entirely in the sharing.
The grain decides everything
Before any of the three workflows: Store × SKU × Week, with sales, price, promotion, display, availability, distribution, region, retailer and - where you can get it - competitor promotion. Plus a baseline, and a sales gap defined against it.
This sounds like plumbing and it is the highest-leverage decision in the whole programme. Get the grain wrong and every subsequent model learns the wrong thing with perfect technical correctness. Analyse at retailer-month and the model discovers, with high confidence, that large retailers sell more than small ones. That is not a finding. That is the grain talking.
The baseline is the second half of the same decision. Almost every question below is really a question about a residual - actual minus expected - and “expected” is a modelling choice you are making whether or not you admit it. Teams that skip the baseline end up comparing to last year, which silently assumes last year was normal.
Workflow one: the Issue Finder
Top-down. Something happened, and you are narrowing.
- Observe - variance against baseline or target, anomaly detection → an event
- Localize - contribution tree, top segments → the concentrated slices
- Associate - key influencers, regression, SHAP, lagged checks → probable drivers
- Size - sales residual, missed sales → order of magnitude
- Recommend-lite - rules plus a score plus feasibility → action queue
- Validate or escalate - holdout, difference-in-differences, BSTS, test-and-learn → validated action or a causal estimate
Note the shape: five cheap steps and one expensive one, and the expensive one is optional and comes last. Most organisations attempt step 6 first, on the largest question they have, and discover eight weeks later that the effect was not identifiable in observational data. Running 1–5 on everything and 6 on the three most expensive decisions per year is a far better allocation of the same money.
Workflow two: the Opportunity Finder
Bottom-up. Nothing is broken, and you are scanning.
- Define the universe - Store × SKU, Customer × SKU, Retailer × Segment
- Benchmark - similar stores, fair share, competitor presence, best internal comparator
- Gap - whitespace, assortment gap, fair-share gap → candidates
- Contextualise - distribution, availability, price index, competitor, shopper fit → reason codes
- Size - expected velocity × gap × margin × feasibility → potential £
- Score and queue - value × confidence × feasibility × strategic priority → backlog
This is the workflow that does not exist in most companies, and it is the one that most justifies automation. Issue investigation happens anyway - someone notices the number and asks. Nobody notices an opportunity that was never realised, which is exactly why a machine has to go looking. The absence of a complaint is not evidence of health.
The benchmark step is where this succeeds or fails. “Similar stores” has to mean something defensible - format, catchment, footfall band, retailer - or the fair-share gap is just noise with a currency symbol in front of it.
Workflow three: the Missed Sales Finder
Demand existed. It was not served.
- Detect the availability issue - availability or out-of-stock flags, stock at zero, lost distribution, no scans where scans were expected
- Expected sales - baseline velocity, similar stores, seasonality → expected
- Lost sales - expected minus observed, winsorised at the extremes → missed-sales estimate
- Associate a root reason - stock, listing, replenishment, promotion, reporting lag → reason code
- Prioritise - lost sales × margin × fixability × strategic weight → action queue
- Feedback - before and after, ideally against a holdout → recovery evidence
Missed sales are not recoverable sales. This deserves saying loudly and early in any deck, because the number is large and attractive and gets treated as a promise. The shopper may have bought the competitor’s pack, or bought nothing and not missed it, or the season may have closed. Missed sales size the problem. They do not size the prize, and the gap between those two is where analytics credibility goes to die.
The exact limits of what a BI tool will do
A great deal of the above runs inside Power BI, which surprises people on both sides of the argument. Decomposition Tree does localization; Key Influencers does association; Top Segments does slice finding; Forecast does prediction; Anomalies does diagnostic flagging; What-if does scenarios; DAX rules do prescriptive-lite. That is rungs 1 through 7 of part one’s ladder, in a tool the business already owns.
So it is worth being precise about where it stops.
Key Influencers ranks factors associated with a target - logistic regression for a categorical target, linear regression for a numeric one. Top Segments is the different thing on the second tab: it finds combinations of conditions where the target is unusually high or low, using a FastTree decision tree, and it is the feature closest in spirit to a slice finder. Continuous factors are automatically cut into at most five supervised bins, and the analysis runs on a sample of up to 10,000 observations.
Can it compare sales and availability? Yes, in three different ways, and they are three different questions:
- Level association - analyze
Sales, explain byAvailability, at Store × SKU × Week: “high availability tends to accompany high sales.” - Change against change - precompute
SalesGapandAvailabilityGapand analyse the gaps: “the drop in sales coincided with the drop in availability.” - Lead and lag - Key Influencers has no concept of time order. You must precompute
Availability(t−1),Availability(t−2), rolling windows. And even then you have predictive information, not causality.
Three traps, in the order they actually catch people:
- Grain. The wrong grain means the model is learning chain size or mix, and reporting it as insight.
- Target leakage. You cannot explain sales with a measure derived from the same sales. This happens constantly and quietly - stock-cover and rate-of-sale metrics are the usual culprits.
- Multicollinearity. Promo flag, discount depth, temporary price reduction and display will split importance between them in ways that vary run to run. Group them into driver groups before you interpret anything.
And the question everyone eventually asks: will different algorithms return the same list of influencers? Almost certainly not, and that is not a defect. Linear and logistic models look for average conditional relationships; trees find non-linear thresholds; SHAP explains one specific predictive model; a slice finder hunts unusually poor subgroups; a decomposition tree traces a contribution path. An honest benchmark - Jaccard overlap, Spearman rank correlation, bootstrap stability, holdout validation - is not run to crown a winner. It is run to separate stable findings from method-specific artefacts, which is a different and much more useful output.
When you actually need causal design
Naming the factors is not enough. Drawing a DAG is not enough either, if the DAG is a picture rather than a commitment. It becomes enough only when eleven things exist:
- Treatment - what precisely does the business change?
- Outcome - what are we measuring?
- Unit - Store × SKU × Week, Customer × SKU, Region × Week
- Timing - what is before, what is after
- DAG - who influences whom
- Confounders - what affects both treatment and outcome
- Mediators - what the effect travels through
- Colliders and leakage - what must not be controlled for
- Identification strategy - why this comparison is valid
- Estimator - what computes the number
- Validation - placebo, pre-trends, sensitivity, holdout
The canonical FMCG example is availability, and it is instructive because it can occupy three different positions in the same diagram. It can be a cause (availability falls → sales fall). It can be a mediator (promotion → demand rises → out-of-stock → sales fall). Or it can be a post-hoc artefact (sales rise → replenishment triggers → stock is measured after the selling happened). Those three require different controls and yield different numbers. Nothing in the data tells you which one you are in. Only the business process does.
Which is why “I’ll name the factors” is raw material for a DAG workshop, not a causal design - and why the last stretch of this work cannot be handed to a tool. AI will not check your causal design for you. A language model can propose a DAG, surface missing confounders and generate the DoWhy or EconML setup. It does not know the real data-generating process, it cannot make an untestable assumption true, and it will draw wrong arrows with complete confidence.
What part two buys you
Three workflows, one grain, and a defensible line between what the BI tool can answer and what needs a design. That is a year of work for a small team and most of the realisable value in the domain.
Part three is the uncomfortable part: who does this, what it costs, and why the answer is smaller and cheaper than the vendor proposal on your desk.
Public FMCG and retail cases referenced in the working notes behind this series - PepsiCo Store DNA, McKinsey trade promotion, NIQ availability recovery, Kroger/84.51° causal impact testing - are vendor or customer claims and are not independently verified effects.
← AI in FMCG analytics, part one — the frame · AI in FMCG analytics, part three — the capability →