# Scheduling Recommendation Engine ## Overview The Scheduling Recommendation Engine analyzes daily staffing data per location and produces an actionable recommendation. It compares actual shifts worked against demand-based targets and flags mismatches as scheduling opportunities. The engine runs as a custom calculation in Metabase, evaluated against aggregated daily metrics per location. *** ### How It Works ``` Raw Data (orders, shifts, targets) │ ▼ Gap Metrics Computed ├── Under Utilization Gap └── Over Utilization Gap │ ▼ Case Logic Evaluated (top → bottom) │ ▼ Recommendation Returned ``` #### Input Metrics | Metric | Description | | ------------------------- | ---------------------------------------------------------------------------------------------------- | | **Under Utilization Gap** | Measures how far below target efficiency the location is running. Higher values = more overstaffed. | | **Over Utilization Gap** | Measures how far above target efficiency the location is running. Higher values = more understaffed. | > **Key insight:** These two gap metrics are the primary decision drivers. They directly encode the magnitude and direction of the staffing mismatch. #### Decision Logic The engine evaluates conditions **top-to-bottom** and returns the **first match**. This means more severe conditions are checked first.
PriorityConditionRecommendation
🔴 HighOver Utilization Gap ≥ 0.75Materially understaffed — add coverage on peak periods first.
🔴 HighUnder Utilization Gap ≥ 0.50Materially overstaffed — reduce coverage on low-demand periods first.
🔴 HighOver Utilization Gap ≥ 0.40 AND Under Utilization Gap ≥ 0.20Demand is uneven — rebalance hours from low to high-demand periods.
🟡 MediumOver Utilization Gap ≥ 0.30Add a small amount of coverage where demand spikes.
🟡 MediumUnder Utilization Gap ≥ 0.25Trim coverage modestly in low-demand periods.
🟢 On Track(default)Staffing is generally aligned to demand.
*** ### reportlab Implementation ```sql case( [Average of Over Utilization Gap] >= 0.75, "High Priority: You are materially understaffed. Add coverage on peak periods first.", [Average of Under Utilization Gap] >= 0.50, "High Priority: You are materially overstaffed. Reduce coverage on low-demand periods first.", [Average of Over Utilization Gap] >= 0.40 AND [Average of Under Utilization Gap] >= 0.20, "High Priority: Demand is uneven. Rebalance hours from low-demand to high-demand periods.", [Average of Over Utilization Gap] >= 0.30, "Medium Priority: Add a small amount of coverage where demand spikes.", [Average of Under Utilization Gap] >= 0.25, "Medium Priority: Trim coverage modestly in low-demand periods.", "On Track: Staffing is generally aligned to demand." ) ``` *** ### Design Principles #### Optimize for false positives The engine is intentionally tuned to **flag aggressively**. A missed opportunity (false negative) costs real margin. A false alarm costs a manager 30 seconds of review. The thresholds reflect that asymmetry. #### Gap metrics as primary signals Earlier versions of this logic gated every branch behind an **Opportunity Score** threshold. This created a problem: locations with clear directional signals (e.g., a 0.57 under-utilization gap) were classified as "On Track" because their composite score fell below the entry threshold. The current version uses gap metrics directly, removing the redundant gate. #### Evaluation order matters The `case()` statement returns the first matching condition. The ordering encodes priority: 1. **Understaffing** is checked before overstaffing at the High tier — this can be swapped if cost savings should take priority over coverage risk. 2. **Rebalance** (both gaps present) is checked after pure directional signals — a location that is clearly understaffed shouldn't get a "rebalance" label. 3. **Medium** conditions use lower thresholds and catch softer signals that don't rise to High priority. *** ### Threshold Reference | Threshold | Value | Rationale | | -------------------- | ------------------------------ | ------------------------------------------ | | High understaffing | `Over Util ≥ 0.75` | Demand significantly exceeds capacity. | | High overstaffing | `Under Util ≥ 0.50` | At least half the capacity is underused. | | High rebalance | `Over ≥ 0.40` + `Under ≥ 0.20` | Both signals present simultaneously. | | Medium understaffing | `Over Util ≥ 0.30` | Noticeable but manageable demand pressure. | | Medium overstaffing | `Under Util ≥ 0.25` | Modest excess capacity worth reviewing. | > These thresholds are starting points. After running in production, review the distribution of recommendations and adjust if a particular tier fires too often or too rarely. *** ### Worked Example **Location:** Lynnfield · **Date:** Saturday, Mar 14 2026 | Metric | Value | | --------------------- | ----------------- | | Shifts Worked | 7 | | Daily Orders | 31 | | Scheduling Ratio | 4.43 orders/shift | | Target Range | 5–7 orders/shift | | Shift Delta vs Mid | -1 | | Under Utilization Gap | 0.57 | | Over Utilization Gap | 0.00 | **Evaluation trace:** 1. `Over Utilization Gap (0.00) ≥ 0.75` → ❌ Skip 2. `Under Utilization Gap (0.57) ≥ 0.50` → ✅ **Match** **Result:** *High Priority: You are materially overstaffed. Reduce coverage on low-demand periods first.* **Action:** Cut \~1 shift (or equivalent hours) to reduce waste. *** ### Changelog | Date | Change | | ---------- | ------------------------------------------------------------------------------------------------------------------------------------ | | 2026-03-13 | **v2.0** — Removed Opportunity Score gates. Simplified to gap-metric-only logic. Lowered thresholds to optimize for false positives. | | — | **v1.0** — Original logic with Opportunity Score ≥ 5/10 gates on all branches. |