Dashboard 1: Retail Sales & Performance — Omni Build Spec
Omni Topic: retail_sales_performance
Audience: Phil (BizOps/exec), Will/Russell (Retail), leadership
Build order: Pages 1-2 first, then 3, 4, 5.
Global dashboard settings
- Title: “LMNT — Retail Sales & Performance”
- Default date filter: Last 90 days (adjustable)
- Default retailer filter: All
- Header element: As-of timestamp showing
MAX(business_date)fromretail_rpt_daily_sales_summary, plus label “Data refreshed daily. Week = Sat–Fri EST.”
Page 1 — Executive Pulse
Phil’s daily/weekly comparison view. Tables only, no charts.
Section 1a: Daily — By Retailer (Sales)
Type: Table (pivot) Rows: Retailer (Walmart, Target, Vitamin Shoppe, Costco CA) Columns:
| Column | Field | Format | Notes |
|---|---|---|---|
| Latest Date | total_sales_amount (filtered to MAX date) | 4.2k”) | |
| SDLW | sdlw_sales | $k | |
| WoW % | wow_pct_sales | % with 1 decimal | Conditional: green if positive, red if negative |
| SDLM | sdlm_sales | $k | |
| MoM % | mom_pct_sales | % | Same conditional formatting |
| SDLY | sdly_sales | $k | |
| YoY % | yoy_pct_sales | % | Same conditional formatting |
Filter: business_date = MAX(business_date)
Source: retail_rpt_daily_comparisons joined to retail_rpt_daily_sales_summary
Section 1b: Daily — By Retailer (Units)
Same structure as 1a but using total_units, sdlw_units, wow_pct_units, etc.
Format: Full integers (no $k); e.g. “180”, “1,250”
Section 1c: Daily — By Category (Sales)
Rows: Category (Drink Mix, Sparkling, Variety Packs) Same columns as 1a.
Section 1d: Daily — By Category (Units)
Same structure as 1c with unit fields.
Section 1e: Daily — By Retailer × Category (Sales)
Rows: Retailer, Category (nested / grouped) Same columns as 1a.
Section 1f: Daily — By Retailer × Category (Units)
Same structure as 1e with unit fields.
Section 1g: Weekly — By Retailer (Sales)
Rows: Retailer Columns:
| Column | Field | Format |
|---|---|---|
| Latest Week | weekly_sales (filtered to MAX week_end_date) | $k |
| Week Prior | prior_week_sales | $k |
| WoW % | wow_pct_sales (weekly) | % |
| SWLY | swly_sales | $k |
| YoY % | yoy_pct_sales (weekly) | % |
Source: retail_rpt_weekly_comparisons
Section 1h: Weekly — By Retailer (Units)
Same with unit fields.
Section 1i–1l: Weekly by Category, by Retailer × Category (Sales & Units)
Same patterns as daily sections 1c–1f but using weekly comparison fields.
Layout notes
- Each section is a separate Omni table tile
- No charts on this page
- Sections stacked vertically in the order above
- Page title: “Executive Pulse — Daily & Weekly”
- Subtitle: “Latest Date: {MAX date} | Latest Full Week: {MAX week_end_date}“
Page 2 — Walmart vs Target & Performance Summary
Trend view for retail leadership. The top section answers the two primary pilot questions — how Walmart compares to Target, and what the reporting differences between them are — before descending to broader performance trends.
Section 2a: Walmart vs Target — Head-to-Head
Purpose: Directly answers pilot questions “How is Target performing vs Walmart?” and “What were sales across Walmart + Target for a given day or week?” This is the first visible section on the page.
Type: Side-by-side scorecard pair + comparison table
Scorecards (2 columns, Walmart | Target):
| Metric | Walmart | Target |
|---|---|---|
| POS Sales — Latest Day | total_sales_amount filtered to retailer + MAX date | same |
| POS Units — Latest Day | total_units | same |
| POS Sales — Latest Week | weekly_sales | same |
| Active Stores | active_stores | same |
| Avg Sales / Store | avg_sales_per_store | same |
Comparison table (rows = time periods, columns = Walmart / Target / Combined / Δ):
| Period | Walmart ($) | Target ($) | Combined ($) | Walmart share % | Target share % |
|---|---|---|---|---|---|
| Latest Day | |||||
| Latest Week | |||||
| MTD | |||||
| YTD |
Source: retail_rpt_daily_sales_summary and retail_rpt_weekly_sales_summary, filtered to retailer IN ('Walmart', 'Target')
Section 2b: Reporting Differences Callout
Purpose: Answers “What is the delta between how Walmart and Target report (e.g. week-ending date, rev-rec timing)—and can we show both in one view with clear labels?” This is a transparency panel, not a KPI tile.
Type: Text/table callout (static metadata, updated if definitions change)
| Dimension | Walmart | Target |
|---|---|---|
| Data source | Emerson Snowflake share (WALMART_STORESALES, WALMART_OMNISALES) | Emerson share + Target direct (TARGET_DAILY_SALES_TCIN_LOC) |
| Week end date | Saturday (Walmart fiscal calendar via WM_WK_END_DT) | No standard Walmart fiscal week; uses SALES_DATE only |
| Revenue recognition timing | Authorized-based (AUTH_BASED_NET_SALES_AMT) for Omni; POS scan date for store sales | POS sale date (SALES_DATE) |
| Returns / refunds | In WALMART_STORESALES if available; refund timing TBD | RETURN_GUEST_AMOUNT on same sale record (day of transaction) |
| Promo split | Not available from Emerson share | Available: PROMO_SALE_AMOUNT, REGULAR_SALE_AMOUNT, CIRCLE_SALE_AMOUNT |
| Store geography | Available: city, state, lat/lon via WALMART_STOREDIMENSIONS | Store dimension not in Emerson share — location_id only |
Implementation note: Render as a pinned text tile or a collapsed “Data Definitions” panel at the top of the page. Update this whenever data engineering resolves the Walmart + Target join (currently in progress per the BI Tool Memo).
Section 2c: Store Health Block
Type: Scorecard row (5 cards)
| Card | Measure | Description |
|---|---|---|
| Total Stores | num_stores | Cumulative ever |
| Active Stores | active_stores | Sale in last 365 days |
| Churned Stores | churned_stores | Active last week, not this week |
| New Stores | new_stores | First sale this week |
| Stores With Sales | stores_with_sales | Sold today/this week |
Breakout: Add a small table below cards showing the same metrics by retailer.
Source: retail_rpt_weekly_sales_summary
Section 2d: POS by Retailer — Period Trends
Type: Table + spark line
| Column | Description |
|---|---|
| MTD Sales ($) | SUM(total_sales_amount) where business_date in current month |
| Prior Month | SUM for prior full month |
| MoM % | Change |
| QTD Sales ($) | SUM for current quarter |
| Prior Quarter | SUM for prior full quarter |
| QoQ % | Change |
| YTD Sales ($) | SUM for current year |
| Prior YTD | SUM for same period last year |
| YoY % | Change |
Rows: Retailer. Add a spark line column showing daily POS trend for the last 30 days per retailer.
Section 2e: POS by Category
Same structure as 2d but rows = Category (Drink Mix, Sparkling, Other, Unknown).
Section 2f: Net Revenue Bridge
Purpose: Show the walk from gross POS sales down to Net Revenue — building the full bridge structure now so users understand what is and isn’t tracked yet.
Type: Waterfall table (or table with + / − rows) Rows: Each deduction line item Columns: Current Month (), Change ($)
| Row | Measure | Data Status |
|---|---|---|
| Gross POS Sales | total_sales_amount | Available |
| Returns | return_amount (negative) | Available |
| = Net Sales | net_sales_amount | Available |
| Discounts | discount_amount | Post-pilot scope (per BI Tool Memo — requires additional data and modeling) |
| Chargebacks | chargeback_amount | Post-pilot scope (trade spend, chargebacks, slotting explicitly deferred) |
| Trade Spend / Slotting | trade_spend_amount | Post-pilot scope |
| = Net Revenue | net_revenue_amount | Post-pilot scope — unblocks when deductions above are sourced |
Implementation note: Build the table structure now with all rows in place. Post-pilot rows should display ”—” with a tooltip: “Post-pilot scope — requires trade spend and chargeback data from LMNT finance systems.” Do NOT suppress the rows — the full structure shows users what the bridge will eventually look like, builds trust, and requires no rebuild when data arrives.
Source: retail_rpt_daily_sales_summary (returns/net sales). Post-pilot deductions will source from retail_fct_chargebacks and retail_fct_trade_spend (see Gap 7 in model audit).
Layout notes
- Walmart vs Target head-to-head (2a) + Reporting Differences (2b) at top
- Store Health (2c) below
- Trend tables (2d, 2e) in main body
- Net Revenue Bridge (2f) at bottom as a “coming soon” reference structure
- Page title: “Walmart vs Target & Performance Summary”
Page 3 — Geography & Regional Sales
Answers pilot questions: “Retail sales by week by region (e.g. NE vs CA)” and “What is revenue by state for retail?”
Section 3a: Revenue by Region (NE vs CA etc.)
Purpose: Directly answers the pilot question about regional breakdown. Shows whether LMNT’s retail performance varies by region — a key question for understanding expansion opportunities and geographic concentration.
Type: Table (regions as rows) Rows: US Region (defined below) Columns: Total Sales ($), Total Units, # Stores, Avg Sales/Store, WoW %, YoY %
Region groupings (implement as a Omni calculated dimension us_region on walmart_store_dimensions.state_prov_cd):
| Region | States |
|---|---|
| Northeast (NE) | CT, ME, MA, NH, NJ, NY, PA, RI, VT |
| Southeast (SE) | AL, AR, FL, GA, KY, LA, MS, NC, SC, TN, VA, WV |
| Midwest | IL, IN, IA, KS, MI, MN, MO, NE, ND, OH, SD, WI |
| Southwest | AZ, NM, OK, TX |
| West | AK, CA, CO, HI, ID, MT, NV, OR, UT, WA, WY |
Filters: Category, Date range, Retailer (default Walmart — only retailer with geography; label clearly)
Section 3b: Revenue by State Map
Type: Choropleth map (if Omni supports) or ranked table
Dimension: state (from walmart_store_dimensions.state_prov_cd)
Measure: SUM(total_sales_amount)
Color scale: Revenue intensity (light → dark)
Filters: Retailer (default: Walmart — only retailer with state data until Target geo is available), Category, Date range.
Note: Add a text annotation: “Geographic data available for Walmart stores only. Target store geography not in Emerson share — coming post-pilot.”
Section 3c: State Detail Table
Type: Table Rows: State (sorted by revenue descending) Columns: Region, Total Sales ($), Total Units, # Stores, Avg Sales/Store
Drill: Click a state → filtered store list showing individual stores in that state with sales and units.
Answers pilot question 3: “What is revenue by state for retail?”
Page 4 — POS Velocity by SKU & Retailer
Purpose: Directly answers the client question “What is POS velocity by SKU and retailer?” Velocity (units sold per store per day) is the leading indicator of retail health — it tells you which SKUs are actually moving off shelves.
Section 4a: Velocity Leaderboard
Type: Ranked table Rows: SKU (one row per product) Columns:
| Column | Measure | Format | Notes |
|---|---|---|---|
| SKU | item_description | text | |
| Category | category | text | |
| Retailer | retailer | text | |
| Avg Daily Units / Store | pos_velocity_per_store | 0.00 | Primary sort column |
| Total Units (period) | total_quantity | #,##0 | |
| Total POS Sales ($) | total_sales_amount | $#,##0 | |
| # Stores Selling | stores_with_sales | #,##0 | |
| # Stores Carrying | stores_with_inventory | #,##0 | |
| % Stores Selling | selling_store_pct | 0.0% | stores_with_sales / stores_with_inventory |
| Avg Daily Units (overall) | avg_daily_units | 0.00 |
Sort: pos_velocity_per_store DESC by default
Filters: Retailer, Category, Date range
Source: retail_rpt_product_performance + retail_fct_sales
Section 4b: Velocity by Retailer × SKU (Heat Table)
Type: Pivot table
Rows: SKU
Columns: Retailer (Walmart, Target, Vitamin Shoppe, Costco CA)
Values: avg_daily_units (velocity) at that SKU × Retailer intersection
Color: Heat map — low velocity = light, high velocity = dark green
Purpose: Immediately shows which SKUs are performing differently across retailers — e.g. a SKU doing 8 units/store/day at Walmart but only 2 at Target warrants investigation.
Source: retail_fct_sales aggregated to SKU × Retailer grain
Section 4c: Velocity Trend
Type: Line chart
X-axis: Week (last 12 weeks)
Y-axis: avg_daily_units (per store)
Series: Top 5 SKUs by velocity (auto-selected, or filterable)
Filter: Retailer; Category
Purpose: Shows whether velocity is accelerating or declining over time.
Layout notes
- Leaderboard (4a) is the primary view; place at top
- Heat table (4b) below leaderboard
- Trend chart (4c) at bottom
- Page title: “POS Velocity by SKU & Retailer”
- Default date range: Last 30 days (more recent = more actionable velocity signal)
Page 5 — Retailer Purchasing & Order Cadence
Purpose: Answers “How much product is each retailer purchasing and how frequently?” This page uses Sales data (what LMNT ships to retailers) and Walmart Omni (authorized orders) — distinct from POS (what consumers buy). Shows reorder patterns and purchasing frequency by retailer.
Section 5a: Retailer Purchasing Summary
Type: Table Rows: Retailer Columns:
| Column | Measure | Format | Notes |
|---|---|---|---|
| Units Purchased (period) | auth_based_qty (Walmart Omni) / total_quantity (Sales fallback) | #,##0 | Walmart: use auth_based_qty; others: use shipped units |
| Sales Value ($) | auth_net_sales_amount (Walmart) / total_sales_amount (others) | $#,##0 | |
| # Purchase Orders (period) | purchase_order_count | #,##0 | Gap — see note below |
| Avg Order Size (units) | auth_based_qty / purchase_order_count | #,##0 | Available when PO count is available |
| Avg Weeks Between Orders | avg_order_cadence_weeks | 0.0 | Gap — needs PO date data |
| Last Order Date | last_order_date | date | |
| MTD Purchases ($) | SUM current month | $#,##0 | |
| Prior Month ($) | SUM prior month | $#,##0 | |
| MoM % | Change | % |
Data notes:
- Walmart:
WALMART_OMNISALES.AUTH_BASED_NET_SALES_AMTandAUTH_BASED_QTYrepresent authorized (ordered) amounts — the closest proxy for “how much Walmart is purchasing.” UseORDER_CHNL_NMto split Store vs Ecomm. - Target: Use
retail_fct_sales(POS = sales = what Target purchased) as proxy until explicit PO data is available. - Purchase order count and cadence: Not currently available in the Emerson share. Flag as “Gap 8” in model audit and note that LMNT’s OMS or Emerson purchase order data is needed to compute precise order frequency.
Section 5b: Purchasing Frequency Trend
Type: Bar chart (weekly grain) X-axis: Week (last 52 weeks) Y-axis: Units purchased per retailer Series: Retailer (stacked or grouped)
Purpose: Visual cadence — shows seasonality, reorder spikes, and whether a retailer has gone silent (gap in bars = potential stockout risk or lost distribution).
Section 5c: Order Cadence by SKU
Type: Table Rows: Retailer, SKU (nested) Columns:
| Column | Measure | Format |
|---|---|---|
| Units Purchased (period) | auth_based_qty or total_quantity | #,##0 |
| Avg Weekly Run Rate | SUM(units) / COUNT(weeks) | 0.0 |
| Last Week With Activity | MAX(business_date or week_end_date) | date |
| Weeks Since Last Order | DATEDIFF(today, last_week_with_activity) / 7 | 0 |
| Status | CASE: Active (<4 wks) / Slowing (4-8 wks) / Stalled (>8 wks) | text with color |
Color rules: Active = green, Slowing = yellow, Stalled = red. Filter: Retailer, Category, Date range
Section 5d: Discounts Against Sales
Type: Table Rows: Retailer Columns:
| Column | Measure | Format | Notes |
|---|---|---|---|
| Gross Sales ($) | total_sales_amount | $#,##0 | Available |
| Promo Sales ($) | promo_sale_amount | $#,##0 | Target only (from PROMO_SALE_AMOUNT) |
| Regular Sales ($) | regular_sale_amount | $#,##0 | Target only |
| Promo % of Sales | promo_sales_pct | 0.0% | Target only |
| Discounts ($) | discount_amount | $#,##0 | Post-pilot scope — display ”—“ |
| Chargebacks ($) | chargeback_amount | $#,##0 | Post-pilot scope — display ”—“ |
| Trade Spend / Slotting ($) | trade_spend_amount | $#,##0 | Post-pilot scope — display ”—” |
Note: Promo/Regular split is available for Target today (from Emerson TARGET_DAILY_SALES_TCIN_LOC). Retail payback metrics — discounts, chargebacks, trade spend, and slotting — are explicitly deferred to post-pilot per the BI Tool Memo (require additional data and modeling). Populate columns with ”—” and a tooltip: “Post-pilot scope.” Build the columns now so the table structure is stable; no rebuild needed when data is added.
Layout notes
- Summary table (5a) at top
- Trend chart (5b) below
- SKU cadence table (5c) and Discounts table (5d) side-by-side or stacked
- Page title: “Retailer Purchasing & Order Cadence”
- Add text note at top of page: “Purchasing data: Walmart uses authorized order amounts (Omni channel). Other retailers use shipped POS as proxy. Purchase order count and exact cadence pending OMS data.”
Page 6 — Category & SKU Drill-down
Section 6a: SKU Daily Table
Type: Flat table (matches Phil’s SKU_Daily wireframe)
Columns (in order):
| Column | Format |
|---|---|
| Date | date |
| SKU | text |
| Retailer | text |
| Category | text |
| Sales Latest Date | $k |
| SDLW (Sales) | $k |
| WoW % (Sales) | % |
| SDLM (Sales) | $k |
| MoM % (Sales) | % |
| SDLY (Sales) | $k |
| YoY % (Sales) | % |
| Units Latest Date | full # |
| SDLW (Units) | full # |
| WoW % (Units) | % |
| SDLM (Units) | full # |
| MoM % (Units) | % |
| SDLY (Units) | full # |
| YoY % (Units) | % |
| Sales Latest Week | $k |
| Week Prior (Sales) | $k |
| WoW % Weekly (Sales) | % |
| SWLY (Sales) | $k |
| YoY % Weekly (Sales) | % |
| Units Latest Week | full # |
| Week Prior (Units) | full # |
| WoW % Weekly (Units) | % |
| SWLY (Units) | full # |
| YoY % Weekly (Units) | % |
Filters: Retailer, Category, Date range (all in page-level filter bar) Default sort: Date DESC, then by Retailer, SKU
Source: retail_rpt_sku_daily_comparisons (Part 3 of comparison logic)
Layout notes
- Single wide table; horizontal scroll expected
- Conditional formatting on all % columns (green positive, red negative)
- Page title: “Category & SKU Drill-down”