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 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.
Auto-Stale & Audit Trail: Auto-dismissed alerts are marked with auto_stale = true and logged in auto_dismiss_audit_logs with exact proof text, confidence scores (High/Medium/Low), and trigger metrics. Viewed under Dismissed filter in Alert History.
Thresholds: All configurable per brand+retailer in the alert_thresholds table. Defaults shown above.