Dashboard 2: Retail Inventory & Store Operations — Omni Build Spec
Omni Topic: retail_inventory_operations
Audience: Will/Russell (Retail), Jason/Brian (Supply Chain)
Build order: Pages 1-2 first (answer pilot questions), then Page 3.
Global dashboard settings
- Title: “LMNT — Retail Inventory & Store Operations”
- Default date filter: Last 30 days
- Default retailer filter: All
- Header element: As-of timestamp showing
MAX(business_date)fromretail_fct_inventory, plus label: “Inventory snapshot date. On-order and in-transit available for Walmart only.”
Page 1 — Inventory Pipeline
Section 1a: Pipeline Summary Scorecards
Type: Scorecard row (4 cards), filtered to latest date
| Card | Measure | Format |
|---|---|---|
| On-Hand | on_hand_quantity | #,##0 units |
| On-Order | on_order_quantity | #,##0 units (Walmart only) |
| In-Transit | in_transit_quantity | #,##0 units (Walmart only) |
| Total Pipeline | total_pipeline_quantity | #,##0 units |
Section 1b: Pipeline by Retailer × SKU
Type: Table Rows: Retailer, SKU (nested) Columns:
| Column | Format | Notes |
|---|---|---|
| On-Hand | #,##0 | |
| On-Order | #,##0 | NULL for non-Walmart; display ”—“ |
| In-Transit | #,##0 | NULL for non-Walmart; display ”—“ |
| Total Pipeline | #,##0 | |
| On-Hand Value ($) | $#,##0 | |
| In-Stock Rate (%) | 0.0% | From weekly inventory summary |
| OOS Rate (%) | 0.0% | |
| Weeks of Supply | 0.0 | Only when validated with Supply Chain |
Filter: Latest date (auto-applied); Retailer; Category Sort: Default by Retailer ASC, then On-Hand DESC
Source: retail_fct_inventory joined to retail_dim_products and retail_rpt_weekly_inventory_summary
Section 1c: Inventory Trend
Type: Line chart
X-axis: business_date (last 30 days)
Y-axis: on_hand_quantity (total across all stores)
Series: By retailer
Purpose: Show whether inventory is building or depleting over time. A declining line heading toward zero is a stockout signal.
Data freshness note
Add a text annotation per retailer:
- Walmart: “Daily via Emerson Snowflake share”
- Target: “Daily via Emerson”
- Vitamin Shoppe: “Weekly spreadsheet — may be stale”
- Costco CA: “Ad hoc — not consistent; use with caution”
Page 2 — Are Stores Stocking Out?
Purpose: Directly answers “Are retailers stocking out? (individual stores?)” This page is action-oriented — it surfaces stores by name where inventory is at zero or critically low, alongside their recent sales to show revenue risk.
Section 2a: Stocked-Out Stores Right Now
This is the lead section — visible without scrolling.
Type: Alert table with conditional formatting
Default filter: on_hand_quantity = 0 (i.e. actively stocked out). User can change threshold (e.g. on_hand <= 5).
Rows: One row per Store × SKU that is currently out of stock
Sort: Recent POS Revenue DESC — highest-revenue stockouts at top (biggest risk first)
| Column | Format | Notes |
|---|---|---|
| Store Name | text | Walmart only; Target = Location ID |
| Store ID | text | |
| Retailer | text | |
| State | text | Walmart only |
| SKU | text | |
| Category | text | |
| On-Hand (latest) | #,##0 | Red highlight when 0 |
| Days OOS | #,##0 | DATEDIFF from last date on_hand > 0 to today |
| Recent POS Revenue (7d) | $#,##0 | Sales in the last 7 days at this store-SKU |
| Recent POS Units (7d) | #,##0 | |
| Revenue at Risk | flag | ”High” if 7d revenue > 100 |
Text above table: “These stores are currently stocked out. Sorted by revenue at risk — act on High first.”
Section 2b: Stockout Summary by Retailer
Type: Summary scorecard row
| Card | Measure | Format |
|---|---|---|
| Total OOS Store-SKUs | COUNT rows where on_hand = 0 | #,##0 |
| Revenue at Risk ($) | SUM(7d POS revenue) for OOS store-SKUs | $#,##0 |
| OOS Rate (%) | OOS store-SKUs / total store-SKUs | 0.0% |
| Avg Days OOS | AVG(days_oos) | 0.0 |
Followed by a small breakdown table (rows = Retailer):
| Retailer | OOS Store-SKUs | Revenue at Risk ($) | OOS Rate (%) |
|---|---|---|---|
| Walmart | … | … | … |
| Target | … | … | … |
| Vitamin Shoppe | … | … | … |
| Costco CA | … | … | … |
Section 2c: Low-Inventory Risk Stores (Forward Warning)
Type: Table — separate from 2a (shows stores not yet at zero but approaching it)
Filter: on_hand_quantity > 0 AND on_hand_quantity < safety_stock_threshold (configurable; default: on_hand ⇐ 10 units)
Rows: Store × SKU
Columns:
| Column | Format | Notes |
|---|---|---|
| Store Name / ID | text | |
| Retailer | text | |
| SKU | text | |
| On-Hand | #,##0 | Yellow highlight (low but not zero) |
| Days Until OOS (est.) | 0.0 | on_hand_quantity / avg_daily_units; requires velocity |
| OOS Rate (past 30d) | 0.0% | Historical pattern |
| Recent POS Revenue (7d) | $#,##0 |
Label: “Approaching stockout — act before these stores run out.”
Section 2d: Stockout Scatter (Diagnostic View)
Type: Scatter plot X-axis: Recent POS Revenue ($) for the store-SKU (last 7 days) Y-axis: Latest On-Hand Quantity Color: Retailer Size: Recent POS Units
Bottom-right quadrant = high revenue, zero/low inventory = priority action. Filters: Retailer, Category
Page 3 — Store-Level Detail
Section 3a: Store Performance Table
Type: Flat table Rows: One row per store per day per product Columns:
| Column | Format |
|---|---|
| Date | date |
| Store ID | text |
| Store Name | text (Walmart only, from store dim) |
| Retailer | text |
| SKU | text |
| Category | text |
| POS Sales ($) | $#,##0 |
| POS Units | #,##0 |
| On-Hand Inventory | #,##0 |
| State | text (Walmart only) |
| City | text (Walmart only) |
Filters: Retailer, Category, State, Date range, Store ID (search) Sort: Default by Date DESC, POS Sales DESC
Section 3b: Velocity Ranking by Store
Type: Table Rows: Store ID, Store Name, Retailer, State Columns:
| Column | Format |
|---|---|
| Total Units (period) | #,##0 |
| Days With Sales | #,##0 |
| Avg Daily Units (velocity) | 0.0 |
| Total Sales ($) | $#,##0 |
| Avg On-Hand | #,##0 |
| Current OOS Status | text |
Sort: Avg Daily Units DESC (highest velocity first) Filters: Retailer, Category, Date range
The OOS Status column connects velocity back to inventory: a high-velocity store that is out of stock is the highest-priority item.
Answers pilot question 1 at store grain: “Which stores have the highest velocity?”
Layout notes
- Table 3a is the main detail view; designed for account-level review conversations
- Table 3b is a leaderboard; can be placed side-by-side or below 3a
- Page title: “Store-Level Detail”