Multi-Location Benchmark Dashboard (seed KPIs and layout)

Purpose

This dashboard helps operators, area managers, and support teams quickly see which locations are performing well, which are slipping, and where to send coaching or resources. It pairs clear comparative KPIs with drill-ins to audits, inventory, and best-practice notes so teams can move from detection to action.

Audience & Use

Primary users: regional managers, operations leads, finance partners, quality and food-safety leads. Use cases include daily morning reviews, weekly performance huddles, and quarterly portfolio reviews to decide where to allocate coaching or capital.

High-level Layout

1. Top filters: date range, rolling 7/28/90-day windows, region/format/brand, unit type (franchise/owned), shift-level toggle.
2. Topline metrics row: portfolio-level aggregates for revenue, contribution margin, avg check, covers, labor %, food %.
3. Ranked table of locations: sortable by any KPI, shows sparkline trend, current value, delta vs prior period, and anomaly flag (R/A/G).
4. Detail pane / drill-in: click a location to open tabs for sales & mix, labor & productivity, inventory & shrink, audits & sanitation, best-practice notes and assigned actions.
5. Actions and insights panel: suggested coaching items, recent corrective actions, and link to submit a location improvement note or schedule a huddle.

KPI Seed List (definition, calculation, visualization)

• Contribution margin — (Net Sales - Cost of Goods Sold - Variable Labor) / Net Sales. Visual: sparkline + delta % vs last period. Frequency: daily, rolling 28d.
• Comp / Remake rate — Number of comps + remakes / total checks. Visual: bar trend by day/shift. Use for guest experience and waste monitoring.
• Covers per labor hour — Total covers / paid labor hours. Visual: scatter vs average check to find efficiency sweet spots.
• Server throughput — Average checks handled per server per shift or covers per server hour. Visual: distribution chart across locations.
• Average check — Average receipt value. Visual: contribution to revenue by item category.
• Shrink % — (Recorded inventory loss + adjustments) / total food purchases or beginning inventory. Use to flag theft/spoilage.
• Food cost % — Cost of Goods Sold / Net Sales. Visual: stacked decomposition by major categories.
• Labor % — Total labor cost / Net Sales. Visual: compare to forecasted labor and to covers per labor hour.
• Forecast accuracy — 1 - (|Actual Sales - Forecast| / Actual Sales). Visual: heatmap by daypart and location.

Anomaly Flags & Rules

Use simple, explainable rules first. Example thresholds (customize per brand):

• Red: KPI > 2 standard deviations from rolling 28-day mean or absolute threshold (e.g., Food Cost > 38%).
• Amber: KPI between 1 and 2 standard deviations or trending deteriorating > 5% vs prior period.
• Green: within expected range or improving.

Include a confidence score that factors in data freshness and sample size. Show the rule used for each flag so users understand why a location was flagged.

Drill-ins & Linked Resources

• Inventory details: recent receipts, variances, top loss items.
• Audit scores: sanitation, temperature logs, checklist failures (link to corrective actions).
• POS sales mix: top items, price elasticity signals, voids and comps.
• Labor details: schedule vs actual, overtime occurrences, shift-level productivity.
• Best-practice notes: playbooks, short video demos, photos, assigned coach and status.

Data Model Guidance (recommended sources & grain)

Keep the dashboard calculations transparent and traceable to source tables. Suggested canonical tables:

• sales_lines (one row per sold item; keys: location_id, check_id, item_id, qty, price, sale_time)
• sales_checks (check-level totals; keys: check_id, location_id, check_total, check_time, server_id)
• labor_shifts (payroll/shift hours & cost; keys: shift_id, location_id, employee_id, hours, labor_cost)
• inventory_movements (purchases, usages, adjustments; keys: txn_id, location_id, item_id, qty, cost, txn_date)
• forecasts (predicted sales by location, datepart)
• audits (checklists and scores with timestamps and photos)

Keep event grain at the transaction or shift level; aggregate in ETL/semantic layer to daily/shift rollups used by the dashboard.

Sample SQL Snippets (generic)

Contribution margin (daily, per location):

SELECT
  location_id,
  date(check_time) AS dt,
  SUM(check_total) AS net_sales,
  SUM(cogs) AS cogs,
  SUM(variable_labor_cost) AS var_labor,
  (SUM(check_total) - SUM(cogs) - SUM(variable_labor_cost)) / SUM(check_total) AS contribution_margin
FROM sales_checks sc
JOIN cost_view cv ON sc.check_id = cv.check_id
GROUP BY location_id, date(check_time);

Comp / Remake rate:

SELECT location_id, dt,
  SUM(CASE WHEN is_comp OR is_remake THEN 1 ELSE 0 END) * 1.0 / COUNT(DISTINCT check_id) AS comp_remake_rate
FROM sales_lines
WHERE date(check_time) BETWEEN :start AND :end
GROUP BY location_id, dt;

Forecast accuracy (28-day):

SELECT f.location_id, f.dt,
  1 - (ABS(f.forecast_amount - a.actual_amount) / NULLIF(a.actual_amount,0)) AS accuracy
FROM forecasts f
JOIN (SELECT location_id, dt, SUM(check_total) AS actual_amount FROM sales_checks GROUP BY location_id, dt) a
  ON f.location_id = a.location_id AND f.dt = a.dt;

Adjust column/table names to your semantic layer. Use NULL-safe division and annotate assumptions.

Best-Practice Sharing Workflow

1. When a location is flagged, regional manager opens the drill-in and reviews supporting evidence (audit score, inventory loss, sales mix).
2. Manager creates a short best-practice note: what was done at a high-performing location (recipe change, scheduling tweak, training video). Attach photos or a short how-to clip.
3. Assign an owner and expected completion date. Track adoption per location (status: proposed, piloting, adopted, retired).
4. Collect post-adoption KPIs for 28 days and auto-compare to prior period to estimate lift. Store these observations as searchable institutional knowledge.

Operational Considerations

• Data freshness: daily ETL is minimum. For morning huddles, include a near-real-time sales feed for same-day situational awareness.
• Timezone handling: rollups should use location local date for ease of interpretation.
• Permissions: regional views limited to their locations; corporate view can see portfolio-wide comparisons.
• Explainability: surface formulas and data lineage for every KPI so users can trust and validate numbers.

Tailoring, Reuse & Packaging

Design the dashboard as a reusable toolkit that teams can copy and tailor per brand, region, or franchise group. Provide a packaged version that includes:

• Standard KPI definitions and SQL templates
• Sample ETL mappings from common POS and labor systems
• Audit and best-practice templates
• Configuration settings for thresholds and business rules

Next-step Capabilities to Add

• Interactive forms to capture best-practice notes, corrective actions, and post-adoption results (store submissions for history and analytics).
• Ability to assign and track coaching tasks from within a location drill-in.
• Automated alerts (email/Slack) for new red flags and for sustained amber conditions.
• Agent-assisted suggestions that propose likely causes (e.g., paired high shrink + low forecast accuracy => receiving/ordering issue) — label as experimental insights.

Governance & Next Steps

Start with a minimal deployable dashboard: the ranked table, three to five KPIs, and drill-ins to raw sales and audit records. Run a 60-day pilot with two regions, gather feedback, then iterate. Keep KPI definitions versioned and record any changes so historical comparisons remain valid.