Alert Detection Rules

All patterns the system monitors. Alerts fire when live data crosses these thresholds.

Store-Level Stockout Engine — 4-Signal Taxonomy (Daily, Live)

Source: tesco_daily_stock_data (SKU × Store × Day), latest day. One label per row by priority: Out of Stock › Low Stock › Phantom › Shelf Depth. Every signal is gated by store materiality (trailing-28d sales_value ≥ £200). Dynamic store-wise baselines (store_val_28d, expected_daily per store×SKU) are recomputed each run; the £ gate and shelf-depth £-tier cuts refresh monthly. Data caveats: CSL mirrors zero-sales (CSL=0 ⟺ sold=0) so it never gates the sold side; days_cover=0 means not-selling and is excluded; no Sales%VarLY column exists, so Shelf Depth uses same-£-tier peer rate-of-sale.

Pattern	Severity	Data Signal / Trigger	What It Means	Recommended Action
Out of Stock	CRITICAL	stock_volume ≤ 1 AND had demand	Shelf genuinely empty for a SKU with real demand. Sub-attributed to a national supplier/DC service-level gap (context) or this store under-ordering. Severity escalates with duration: out ≥ 3 consecutive days = RED, ≥ 7 days = CRITICAL.	Chase store/DC replenishment. If the national supplier or DC SL is low this week, escalate nationally — it is not store-specific.
Low Stock (Days to Cover)	CRITICAL	stock_volume > 1 AND 0 < days_cover < 3 AND units_sold > 0 (RED when days_cover < 7)	Still selling but under ~2 weeks of cover left at the current rate — pre-emptive, high OOS risk. The earliest warning, before the shelf empties. days_cover = 0 (frozen, stock present but not selling) is deliberately EXCLUDED — that is Phantom, not Low Stock. Severity rides days-of-cover, not lost £.	Raise an emergency replenishment order now, before the shelf empties.
Phantom Inventory (Ghost Stock)	HIGH	stock_volume > 1 AND 0 sales over the last 3 days AND expected_daily ≥ 0.5/day	Stock on hand but it has stopped selling versus the store×SKU’s own trailing-28d norm. Ghost stock / not reaching the shelf. The expected_daily ≥ 0.5/day floor stops naturally-slow movers tripping a 3-day zero.	Gap-scan + reset on-hand to true. Route to the Tesco store-systems team.
Shelf Depth (CSL-Masking)	MEDIUM	csl_pct ≥ 85 AND stock_volume > 1 AND units_sold < 40% of same-£-tier peer mean	Scorecard looks green (CSL healthy) but the store is selling far below same-size peers for this SKU. Stock is stuck in the backroom, not on the shelf — the silent killer. Peers are banded by store £-tier (LOW < £459 < MED < £814 ≤ HIGH) so a big store is not compared against a small one.	Flag store ops for backroom replenishment / shelf check.

Tesco — Service Level & Supply Chain

Source: Tesco portal export (TPNB × Store × Week). National-level rules run on aggregate rows. Store-level rules run per store.

Pattern	Severity	Data Signal / Trigger	What It Means	Recommended Action
Customer Service Level	CRITICAL	csl_volwt < 90%	Volume-weighted CSL below RED threshold. More than 10% of demand unfulfilled from shelf. OOS stores contributing zero to weighted average (survivor bias). If csl_volwt and csl_simple diverge by > 5 points, failure is concentrated in high-volume stores. Treat as more urgent.	Chase DC/store replenishment. Check oos_store_count — if > 20 stores, escalate to depot.
Customer Service Level	AMBER	csl_volwt ≥ 90% and < 95%	Below target availability but not critical. Monitor for deterioration.	Monitor replenishment cycle. Prepare store-level breakdown for NAM if trend worsens.
OOS Store Count	CRITICAL	oos_store_count ≥ 50 stores	50+ stores fully OOS (CSL=0%). Volume-weighted CSL may still read AMBER because OOS stores contribute zero weight. This catches what weighted CSL hides. Validated: 203 stores fully OOS on one SKU while csl_volwt read 92.8% AMBER.	Store-ops escalation with store ID list. Request gap scans if depot SLs are healthy.
OOS Store Count	AMBER	oos_store_count ≥ 20 stores	20–49 stores fully OOS. Monitor and prepare store list.	Attach store ID list to next NAM call.
DC to Store Service Level	CRITICAL	depot dc_to_store_sl_pct < 85%	Tesco's DC failed to forward > 15% of ordered cases to stores. Stock sitting at depot, not reaching shelf. Source: depot row (not store averages).	Chase distribution/RDC team. Stock at DC needs to move.
DC to Store Service Level	AMBER	depot dc_to_store_sl_pct ≥ 85% and < 92%	DC delivery below normal. Monitor replenishment cycle.	Monitor and escalate if trend below 85%.
Supplier to DC Service Level	CRITICAL	depot supplier_to_dc_sl_pct < 80%	Supplier delivered < 80% of ordered cases to Tesco DC. Root cause sits with the supplier.	Raise urgent PO. Escalate to Category Supply Manager.
Full Supply Chain Failure	CRITICAL	supplier_sl < 80% AND dc_sl < 85% (same week)	Both supplier and DC failed simultaneously. Complete supply chain breakdown.	Escalate to supplier AND distribution simultaneously. Root cause is supplier; DC failure is downstream.
Buffer-Burn Warning (Pre-OOS)	CRITICAL	supplier_sl < 50% while csl_volwt ≥ 95%	CSL looks healthy but supplier has almost stopped delivering. Stores selling through buffer stock. OOS expected within 1–2 weeks. This is the pre-OOS early warning. Without it, 886 stores would go GREEN → RED with no AMBER warning.	Fix supplier deliveries immediately. Escalate to head of supply.
Buffer-Burn Warning (Pre-OOS)	AMBER	supplier_sl ≥ 50% and < 80% while csl_volwt ≥ 95%	Supplier delivery below normal while CSL still healthy. Buffer stock being consumed.	Chase supplier now. OOS within 1–2 weeks if not resolved.

Tesco — Waste Alerts

Source: Tesco export waste columns. National weekly sums.

Pattern	Severity	Data Signal / Trigger	What It Means	Recommended Action
RTC Waste (Reduce-to-Clear)	CRITICAL	national rtc_waste_value > £500/week	Near-expiry markdowns exceed £500. Tesco marked product down to clear before expiry. Signals overstock or short shelf-life issue. If > 50% of stores are on promotion, markdown waste is partially expected. Compare to prior promo weeks before escalating.	Review pricing/promo mechanic urgently.
RTC Waste (Reduce-to-Clear)	AMBER	national rtc_waste_value > £100/week	Elevated near-expiry markdowns. Monitor trend.	Monitor. Consider promotional support.
OOC Waste (Expired Stock)	CRITICAL	national ooc_waste_value > £50/week	Product expired without being sold or cleared. Always a process failure — no AMBER level.	Investigate supplier date coding and supply chain date management.
DAM Waste (Damaged)	CRITICAL	national dam_waste_value > £2,000/week	Damaged stock write-off above £2,000. Likely packaging or handling failure at DC/distribution. Validated: £25,981 DAM in one real week (3× RTC, 175× OOC) with no rule previously.	Review packaging/handling with supply chain. Request store-level breakdown to identify DC concentration.
DAM Waste (Damaged)	AMBER	national dam_waste_value > £500/week	Elevated damaged write-off. Monitor trend.	Monitor.

Tesco — Commercial Alerts (Phase 1)

Week-on-week change detection. Fires when demand, price, or waste shifts significantly vs previous week. Requires 2+ weeks of data.

Pattern	Severity	Data Signal / Trigger	What It Means	Recommended Action
Estimated Lost Sales	CRITICAL	lost_sales_value > £500/week	Imputed revenue lost due to OOS stores. Formula: avg units sold by in-stock stores × OOS store count × avg selling price. Converts "shelf gaps" into £ number for buyer conversations.	Escalate to NAM with £ figure. Quantify gap for buyer review.
Estimated Lost Sales	AMBER	lost_sales_value > £100/week	Moderate imputed lost sales. Prepare store-level gap report.	Monitor and prepare store-level gap report.
Sales Volume Change	CRITICAL	WoW change in units_sold > ±25%	Significant week-on-week demand shift. Could indicate promo effect, distribution change, seasonality, or data quality issue.	Investigate root cause. Check if promo is running, distribution changed, or competitor activity.
Sales Volume Change	AMBER	WoW change in units_sold > ±15%	Notable demand movement. Monitor trend.	Monitor over next 2 weeks.
Sales Value Change	CRITICAL	WoW change in sales_value > ±25%	Combined volume + price movement. Larger swing than volume alone suggests pricing change.	Check if pricing is correct. Validate with ASP alert.
Avg Selling Price Change	CRITICAL	WoW change in avg_selling_price > ±10%	Unexpected price movement. Could indicate promotional pricing, markdown activity, or pricing error.	Verify intended price with commercial team. Check Tesco portal for active TPR.
Avg Selling Price Change	AMBER	WoW change in avg_selling_price > ±5%	Minor price movement. Monitor.	Confirm pricing is as planned.
Total Waste Change	CRITICAL	WoW change in (RTC + OOC + DAM) > ±30%	Sudden spike in total waste. Absolute waste alerts catch high levels — this catches the moment a waste problem starts worsening.	Investigate waste root cause. Check RTC/OOC/DAM breakdown for which type is driving the change.

Tesco — Store-Level Patterns

Chain attribution using national depot SLs combined with per-store CSL. Store rows carry no SL data.

Pattern	Severity	Data Signal / Trigger	What It Means	Recommended Action
Store Fully OOS	CRITICAL	store csl_pct = 0	Complete OOS at this store. CSL 0% means zero demand fulfilled.	Verify system stock at store. Chase immediate replenishment.
Supplier Failure (Full Chain)	CRITICAL	depot supplier_sl < 80% AND (dc_sl < 85% OR csl < 90%)	Supplier failed to deliver to DC. Both the supplier and DC are struggling. Root cause: supplier delivery failure.	Raise urgent PO with supplier. Escalate to Category Supply Manager.
DC / RDC Failure	HIGH	depot supplier_sl ≥ 80% AND depot dc_sl < 85% AND store csl < 90%	Supplier delivering but DC not forwarding to stores. Stock sitting at DC.	Chase DC/RDC. Stock at depot needs to be dispatched.
In-Store Failure	HIGH	supplier_sl ≥ 80% AND dc_sl ≥ 85% AND store csl < 90%	Supply chain healthy. Failure is in-store. Phantom stock or shelf replenishment not happening.	Request gap scan from store manager. Check physical vs system stock.
Low CSL — Chain Data Incomplete	AMBER	store csl < 90% AND depot row absent	CSL low but no depot SL data available to identify root cause.	Investigate both supplier and DC. Data quality flag.
Multi-SKU Store Outage	CRITICAL	Same store: csl_pct = 0 on ≥ 3 SKUs in same week	Structural store-level failure across multiple SKUs. One fix recovers all. Could be: missed delivery, ranging change, or security removal of whole range. Validated: 9 stores in Wk13, one store with 6 OOS SKUs simultaneously.	Single store call. Check for missed delivery, ranging change, or security removal of whole range.

Standard Rules

Applied to all retailers. Source: national stock data.

Pattern	Severity	Data Signal / Trigger	What It Means	Recommended Action
Low Stock (WOC)	CRITICAL	Weeks of cover < 2.0	Less than 2 weeks of stock remaining at current sales rate. High OOS risk.	Raise emergency replenishment order immediately.
Low Stock (WOC)	AMBER	Weeks of cover ≥ 2.0 and < 4.0	Stock buffer thinning. Risk of stockout within 2–4 weeks.	Review next order cycle and consider forward buy.
Low Availability	CRITICAL	Availability rate < 80%	More than 1-in-5 facing stores not holding the SKU. Significant lost sales.	Investigate supply chain. Check retailer ordering parameters.
Low Availability	AMBER	Availability rate ≥ 80% and < 90%	Partial distribution failure. Some stores underserved.	Monitor trend. Raise if deteriorating.
Low Sell-Through	HIGH	Sell-through rate < 25%	SKU moving very slowly vs. stock held. Shelf space at risk.	Review promotional mechanics or markdown strategy.
Low Sell-Through	AMBER	Sell-through rate ≥ 25% and < 40%	Below-par velocity. May indicate pricing or ranging issue.	Investigate promotional compliance and display placement.
Overstock	AMBER	WOC > 16.0 AND sell-through rate < 25%	Excess stock tied up with slow movement. Working capital risk.	Pause next order. Consider promotional push to clear stock.

Tesco Supplier Signals

Derived from Tesco's Supplier Hub data (On Hand, In Transit, On Order, EPOS, Days of Cover).

Pattern	Severity	Data Signal / Trigger	What It Means	Recommended Action
Phantom Inventory	CRITICAL	On Hand > avg_daily × 2 AND EPOS = 0 AND In Transit = 0 AND On Order = 0	Stock shows on Tesco's system but zero EPOS sales and zero replenishment activity. Ghost stock - system stopped ordering but product is not on shelf. If phantom persists 2+ consecutive weeks → CRITICAL REPEAT → escalate to area loss prevention.	Request Gap Scan + inventory reset from store manager. Escalate to Tesco Replenishment contact.
RDC Logistics Delay	MEDIUM	On Hand = 0 AND In Transit > 0 AND EPOS = 0 AND Days of Cover = 0	Stock confirmed in transit from RDC but not yet delivered. Store is OOS and losing sales daily while stock sits in the supply chain.	Contact distribution / logistics team to track shipment and confirm ETA.
RDC Rounding Error	HIGH	On Hand = 0 AND In Transit = 0 AND On Order = 0 AND avg_daily > 0 AND EPOS = 0	Zero stock at all supply chain stages despite active sales history. Caused by pallet weight rounding at RDC - stock not dispatched.	Escalate to Category Supply Manager - request manual Min/Max uplift.
Algorithmic Under-Forecast	HIGH	On Hand = 0 AND In Transit = 0 AND On Order = 0 AND EPOS ≥ avg_daily × 1.2	Stock crashed during a demand spike. Algorithm failed to anticipate uplift, resulting in lost sales during peak period.	Escalate to Category Supply Manager - increase Min/Max safety stock and review forecast parameters.
Shelf Depth Failure	MEDIUM	On Hand > avg_daily × 1.5 AND EPOS < avg_daily × 0.4 AND EPOS > 0 AND Days of Cover > 2	Stock on system, residual EPOS scanning, but EPOS 60%+ below baseline. Product trapped in backroom - shelf not being replenished.	Flag to store operations for shelf replenishment audit. No supply chain action required.

Asda Supplier Signals

Derived from Asda's Supplier Hub data. Similar signal structure to Tesco.

Pattern	Severity	Data Signal / Trigger	What It Means	Recommended Action
Phantom Inventory	CRITICAL	On Hand > avg_daily × 2 AND EPOS = 0 AND In Transit = 0 AND On Order = 0	Same as Tesco pattern - ghost stock on system with no sales activity and system-frozen replenishment.	Request inventory audit and reset via Asda Replenishment contact.
Shelf Depth Failure	MEDIUM	On Hand > avg_daily × 1.5 AND EPOS < avg_daily × 0.4 AND EPOS > 0 AND Days of Cover > 2	Stock held but sales well below baseline. Backroom fill issue at store level.	Flag to Asda store operations. No supply chain action required.
Depot Disruption (OOS)	HIGH	≥ 2 stores with On Hand = 0 AND EPOS = 0 AND In Transit = 0	Multiple stores simultaneously OOS with no inbound stock. Points to depot-level supply failure rather than individual store issue.	Escalate to Asda Supply Chain / depot contacts to investigate regional distribution failure.
Ranging Ambiguity	AMBER	No stock or sales recorded in full history for this SKU + retailer combination	Product may not be ranging at this retailer, or data feed not yet established. Cannot assess supply health.	Verify ranging status with Asda buying contact. Confirm data feed is active.

Distributor Signals

Applied when distributor depot stock data is available.

Pattern	Severity	Data Signal / Trigger	What It Means	Recommended Action
Depletion Stall	MEDIUM	On Hand > avg_daily × 7 × 1.5 AND depletion_daily < avg_daily × 0.4	Depot holds 1.5× normal week's stock but outbound depletion to stores has dropped 60%+ below baseline. Stock not flowing through.	Investigate depot outbound orders. Check if stores are ordering or if depot's delivery schedule is disrupted.
Depot OOS	HIGH	On Hand = 0 AND depletion_weekly = 0 AND In Transit = 0	Depot completely out of stock with no inbound supply. All downstream stores at risk of OOS.	Escalate to supply planning team. Raise emergency order to depot immediately.

Tesco data note: SL% columns only populated on the depot row — all store rows show SL%=0.0 meaning "not reported". The system detects the depot row dynamically (store with zero total sales + non-zero SL%). Never hardcoded to a specific store ID.

Lost Sales model: Uses SKUtrak-equivalent imputation — avg rate-of-sale of in-stock stores × OOS store count × avg selling price. Requires sales_value column in uploaded file.

WoW alerts: Require at least 2 weeks of data for the same SKU. Comparing week N vs week N-1 in the temporal loop.

Thresholds: All configurable per brand+retailer in the alert_thresholds table. Defaults shown above.