Threshold Tuning for Alerts

Static percentage triggers are the single most common reason a food cost alerting system gets muted and then ignored. Flag every menu item that crosses a fixed 3% theoretical-to-actual delta and you bury the two variances that actually matter under a hundred that are just normal kitchen physics — trim loss on a whole fish, moisture drift on a braise, scale drift on a busy Friday line. This page sits inside the broader Theoretical vs Actual Food Cost Calculation domain and scopes down to one specific control problem: deciding, per SKU and per period, what size of variance is signal versus noise, and routing only the signal to a human. Get the threshold wrong in one direction and staff stop reading the alerts; get it wrong in the other and real margin leakage never surfaces until the monthly close.

The problem is not “compare a number to a limit.” It is manufacturing a defensible, per-SKU limit from history that moves with the operation — widening during a volatile protein week, tightening around pre-portioned dry goods — and doing it deterministically so the same input data always yields the same alert. That is a rolling-statistics problem layered on top of the variance mapping methodologies that produce the raw deltas in the first place, and it is solved with a statistical baseline, a category calibration layer, a two-tier router, and idempotent evaluation, in that order.

Concept Definition and Data Contract

The tuning engine consumes one thing: a tidy table of daily, reconciled variance observations. Each row is one SKU on one day, with the percentage delta between what the recipe bill-of-materials roll-up said should have been consumed and what inventory reconciliation says was consumed. The contract is deliberately narrow — the engine does no reconciliation itself, it only reads the output of the upstream variance pipeline.

The expected input schema:

The output contract is equally strict: for every current-day SKU row the engine emits a rolling_mean, a rolling_std, a soft_threshold, a hard_threshold, and an alert_level in {OK, SOFT, HARD}. Nothing else. Downstream routers subscribe only to that enum; they never re-derive thresholds. This keeps the alerting decision in exactly one place, which is what makes it auditable — when someone asks “why did this page fire,” there is a single record with the mean, the deviation, and the multiplier that produced it.

Two non-negotiables sit in the contract. Variance percentages are statistical quantities, so they live as floats here — but the monetary figures they were derived from must already have been computed in exact arithmetic upstream, per the site-wide rule that any dollar amount uses Decimal or PostgreSQL NUMERIC. And variance_pct must never arrive as NaN on a COMPLETED row; a null variance is an upstream reconciliation bug, and letting it into a rolling window silently poisons the mean.

Architecture Decision Rationale

The core decision is adaptive thresholds over static ones, and it is worth stating why, because a fixed limit is genuinely simpler to ship. A static 3% band assumes every ingredient has the same natural variance, which is false by an order of magnitude. Raw proteins routinely swing 4–6% day to day from trim and moisture alone; vacuum-portioned sauces barely move. A single global band therefore has to be set loose enough to not scream about proteins, which makes it structurally blind to a dry-goods SKU quietly walking out the back door at 2.5%. Adaptive per-SKU bands dissolve that trade-off by learning each ingredient’s own baseline.

The second decision is rolling window over exponential smoothing. An exponentially weighted average reacts faster, but in a kitchen that is a liability: it lets a single bad prep day drag the baseline toward the anomaly, so tomorrow the same behavior looks normal. A flat rolling window (14–30 days) treats every day in the window equally and resists that drift — a genuine step-change in cost has to persist before it reshapes the baseline, which is the behavior an operator actually wants from a control limit rather than a forecast.

The third decision is category-weighted multipliers over per-SKU tuning. You could fit a bespoke sensitivity to every SKU, but that is thousands of hand-maintained parameters that rot the moment the menu changes. Grouping by category — proteins, produce, dry goods, dairy/fats — captures ~90% of the variance-behavior difference with a handful of numbers a culinary manager can reason about and version-control. This mirrors how the portion size standardization work already buckets execution tolerance by ingredient class, so the two systems stay consistent.

The final decision is two-tier routing over a single alert. Merging “watch this trend” and “act now” into one notification is what trains staff to dismiss the channel. Splitting a passive soft band (logged, never paged) from an active hard band (paged, 24-hour resolution SLA) keeps the paging channel scarce and therefore credible.

Phase 1 — Statistical Baseline and Rolling Window

The baseline is a rolling mean and standard deviation per SKU. The deterministic threshold formula is:

Threshold = Rolling_Mean + (Multiplier × Rolling_StdDev)

where Rolling_Mean is the historical average delta between theoretical and actual usage, Rolling_StdDev is dispersion computed with the unbiased estimator (ddof=1, so a sample of n divides by n-1), and Multiplier is the sensitivity that sets how many deviations of headroom a SKU gets before it trips. The window is configurable — 14 days for high-churn produce, closer to 30 for stable dry goods — and it is computed with a vectorized grouped rolling call, never a Python row loop, so the whole estate reprices in one pass.

from __future__ import annotations

from dataclasses import dataclass, field

import numpy as np
import pandas as pd


@dataclass(frozen=True)
class ThresholdConfig:
    """Immutable, version-controllable tuning parameters."""

    window_days: int = 21
    min_periods: int = 7
    soft_multiplier: float = 1.0
    hard_multiplier: float = 2.0
    category_multipliers: dict[str, float] = field(
        default_factory=lambda: {
            "protein": 2.3,
            "produce": 1.9,
            "dry_goods": 1.3,
            "dairy": 1.7,
        }
    )
    default_multiplier: float = 1.5


def compute_adaptive_thresholds(
    daily_variance: pd.DataFrame,
    config: ThresholdConfig,
) -> pd.DataFrame:
    """Compute rolling mean, std, and dynamic soft/hard thresholds per SKU.

    Expects columns: ['date', 'sku_id', 'category', 'variance_pct'].
    Vectorized: one grouped rolling pass, no per-row iteration.
    """
    df = daily_variance.sort_values(["sku_id", "date"]).reset_index(drop=True)

    grouped = df.groupby("sku_id")["variance_pct"]
    df["rolling_mean"] = grouped.transform(
        lambda s: s.rolling(config.window_days, min_periods=config.min_periods).mean()
    )
    df["rolling_std"] = grouped.transform(
        lambda s: s.rolling(config.window_days, min_periods=config.min_periods).std(ddof=1)
    )

    df["cat_mult"] = (
        df["category"].map(config.category_multipliers).fillna(config.default_multiplier)
    )

    df["soft_threshold"] = df["rolling_mean"] + (
        config.soft_multiplier * df["rolling_std"] * df["cat_mult"]
    )
    df["hard_threshold"] = df["rolling_mean"] + (
        config.hard_multiplier * df["rolling_std"] * df["cat_mult"]
    )
    return df

Freezing the config as a dataclass(frozen=True) is deliberate: the multiplier matrix is an operational control, and letting it be mutated mid-run is how two locations end up on silently different thresholds. Store it as a version-controlled file so a culinary manager can adjust sensitivity through a reviewed change, not a live edit. Python’s own statistics module and the pandas rolling API document the exact estimator semantics these calls rely on.

Phase 2 — Category Calibration and Alert Evaluation

Uniform multipliers fail across heterogeneous inventory. High-turnover proteins carry wide natural variance from trim, moisture evaporation, and portioning drift; packaged goods hold tight distributions. The calibration layer above already applied a category multiplier; the evaluation step now compares the current day’s variance_pct against the two derived bands and assigns the routing enum. The comparison uses np.select so it stays vectorized and the precedence (hard beats soft beats OK) is explicit rather than implied by if/else ordering.

def evaluate_alerts(df: pd.DataFrame, variance_col: str = "variance_pct") -> pd.DataFrame:
    """Assign OK / SOFT / HARD by comparing current variance to dynamic bands.

    Rows without an established baseline (NaN thresholds during cold start)
    resolve to 'OK' and are flagged separately so they are never silently
    treated as breaches.
    """
    baseline_ready = df["hard_threshold"].notna() & df["soft_threshold"].notna()

    conditions = [
        baseline_ready & (df[variance_col] > df["hard_threshold"]),
        baseline_ready & (df[variance_col] > df["soft_threshold"]),
    ]
    df["alert_level"] = np.select(conditions, ["HARD", "SOFT"], default="OK")
    df["baseline_ready"] = baseline_ready
    return df

Sensitivity multipliers by category class, expressed as ranges so a manager can dial within a band without re-reasoning the model:

The baseline_ready flag is the important detail here. During a cold start the rolling std is NaN until min_periods days accumulate, which makes both thresholds NaN. Comparing anything to NaN returns False, so without the explicit guard every un-baselined SKU would resolve to OK and look reassuringly clean when in fact it is simply un-monitored. Surfacing baseline_ready=False as its own state lets the operator distinguish “verified within tolerance” from “not yet watched.”

Phase 3 — Two-Tier Routing and Downstream Handoff

Evaluation produces the enum; routing decides what a human experiences. The two tiers decouple calculation from notification so operational teams get action items, not a firehose.

1. Soft warning band (mean + 1.0 × std × cat_mult) — logs to the operational dashboard for trend monitoring and can trigger an automated prep-recalibration suggestion. It never pages a person.
2. Hard alert band (mean + 2.0 × std × cat_mult) — dispatches an immediate notification to Slack, email, or an ITSM ticket, with a documented 24-hour resolution SLA.

The critical handoff rule is that a hard alert must not fire until the day’s inventory count is final. Real-time point-of-sale deltas are useful for populating soft warnings, but they lag receiving and never see unlogged waste, so a hard page off a PENDING reconciliation is a false positive waiting to happen. The router therefore gates hard dispatch on recon_status == "COMPLETED" and cross-references the breach against logged spoilage before escalating — a check that belongs to the waste tracking and routing systems layer, so a variance already explained by a recorded comp or spill is downgraded rather than paged.

from typing import Protocol


class AlertSink(Protocol):
    def emit(self, sku_id: str, level: str, payload: dict[str, float]) -> None: ...


def route_alerts(
    df: pd.DataFrame,
    dashboard: AlertSink,
    pager: AlertSink,
    *,
    recon_col: str = "recon_status",
) -> None:
    """Route evaluated rows to sinks. HARD gates on finalized reconciliation."""
    soft = df[df["alert_level"] == "SOFT"]
    for row in soft.itertuples(index=False):
        dashboard.emit(row.sku_id, "SOFT", {"variance_pct": row.variance_pct})

    hard = df[(df["alert_level"] == "HARD") & (df[recon_col] == "COMPLETED")]
    for row in hard.itertuples(index=False):
        pager.emit(
            row.sku_id,
            "HARD",
            {
                "variance_pct": row.variance_pct,
                "hard_threshold": row.hard_threshold,
                "rolling_mean": row.rolling_mean,
            },
        )

Iterating the filtered alert frame is acceptable here — it is a handful of breaches, not the estate — and the payload deliberately carries the mean and threshold that justified the page so the on-call resolver sees the full arithmetic without a second query. Hard alerts that fall on a PENDING day are simply held, not dropped; the next run after reconciliation completes will re-evaluate and route them if the breach persists.

Production Hardening

Deploying this across a multi-unit estate turns four operational details into the difference between a trusted system and a muted one.

• Cold-start seeding. Seed the rolling window with 14–30 days of reconciled history before enabling paging. A brand-new SKU or a new location has NaN thresholds until min_periods is met; the baseline_ready flag keeps those from masquerading as clean, and paging should stay suppressed for that SKU until the flag flips true.
• Idempotent evaluation. The whole pipeline is a pure function of (daily_variance, config), so re-running a day produces identical output. Key each dispatched alert on (sku_id, date, alert_level) and deduplicate at the sink, so a retried batch or a re-run after a late invoice never double-pages. This is the same idempotency discipline the ingestion layer uses upstream, and it is what makes retries safe.
• Predictive multiplier widening. Feed seasonal yield curves into the category_multipliers. When a forecast flags an upcoming harvest shift or a supplier change, temporarily widen the affected class by 0.2–0.3 to absorb expected variance rather than paging on a known, transient swing — then revert on a scheduled date so the widening never becomes permanent slack.
• Config versioning and memory. Persist the frozen ThresholdConfig per period so every alert can be replayed against the exact multipliers that produced it. The rolling computation holds only the window in memory per SKU, so the footprint is flat regardless of how many months of history the warehouse retains — scan only the trailing window_days + buffer when the table gets large rather than materializing the whole history.

For location-specific multiplier overrides — a coastal unit with structurally higher seafood variance, say — layer the multi-location cost center architecture on top of the base matrix rather than forking the config, so a global change still propagates and only the genuine regional exception is overridden.

Failure Modes and Troubleshooting

Most threshold failures are silent — the job reports success while quietly training staff to ignore it or hiding a real loss. These are the patterns to detect deliberately.

• Alert fatigue from an under-seeded baseline. A window that is too short (or a min_periods that is too low) yields a jittery std that snaps on every ordinary day. Symptom: hard alerts on a stable SKU with no operational cause. Fix by lengthening the window or raising the category multiplier, and confirm the std is being computed with ddof=1 — a population std (ddof=0) understates dispersion on small samples and over-fires.
• The self-masking anomaly. If a genuine cost step-change persists inside the rolling window, the mean creeps toward it and the SKU stops alerting while the loss continues. Detect it by running a monthly audit that compares each SKU’s current baseline against a longer trailing period; a baseline that has drifted upward without a documented cause is masked leakage, not improvement.
• NaN poisoning of the mean. One variance_pct of NaN inside the window makes the entire rolling mean and std NaN for that span, collapsing every threshold to NaN and every evaluation to OK. Never let a NaN variance reach the window — reject it at the contract boundary and treat it as an upstream reconciliation bug.
• Premature hard pages off pending counts. A hard alert fired before inventory is COMPLETED almost always resolves to a pending delivery or an unlogged transfer. Symptom: pages that self-clear the next morning. The recon_status gate is the fix; if pages still leak, the reconciliation flag is being set before counts are truly final.
• Category drift after a menu change. A SKU re-categorized (or a new ingredient defaulting to default_multiplier) inherits the wrong sensitivity band. Reconcile the category map against the ingredient registry on every menu update, and alert on any SKU still falling through to the default multiplier after N days.
• Divide-by-tiny-std over-sensitivity. A SKU with near-zero historical variance (a pre-portioned, machine-dispensed item) has a std so small that even the multiplier cannot open a usable band, so a one-cent rounding wobble trips it. Floor the effective std at a category minimum so perfectly stable items get an absolute tolerance rather than a mathematically zero one.

Frequently Asked Questions

Why adaptive thresholds instead of a single fixed percentage?

A fixed band assumes every ingredient has the same natural variance, which is false by roughly an order of magnitude — raw proteins swing several percent a day from trim and moisture while vacuum-portioned goods barely move. A global limit therefore has to be loose enough to tolerate proteins, which makes it structurally blind to a stable SKU leaking at 2.5%. Per-SKU adaptive bands learn each ingredient’s own baseline, so both cases are caught at their own appropriate sensitivity.

Why a flat rolling window rather than an exponentially weighted average?

An exponential average reacts faster, but for a control limit that is a defect: it lets one bad prep day drag the baseline toward the anomaly, so the same behavior looks normal tomorrow. A flat window weights every day equally and resists that drift — a real cost step-change has to persist before it reshapes the baseline, which is exactly what you want from a threshold whose job is to catch persistent leakage.

Should thresholds be tuned per SKU or per category?

Per category. Bespoke per-SKU sensitivity means thousands of hand-maintained parameters that rot the instant the menu changes. Grouping by class — protein, produce, dry goods, dairy — captures the great majority of the variance-behavior difference with a handful of numbers a culinary manager can reason about and version-control, and it keeps the tuning consistent with how portion tolerance is already bucketed.

Why gate hard alerts on completed reconciliation?

Real-time point-of-sale deltas lag receiving and never see unlogged waste, so a hard page off a pending count is a false positive waiting to happen — it typically resolves to a delivery or transfer that lands the next morning. Gating hard dispatch on recon_status == "COMPLETED" (soft warnings can still populate from live data) keeps the paging channel scarce and therefore credible.

What prevents the same variance from paging twice on a re-run?

Idempotency. The pipeline is a pure function of the input variance and the frozen config, so re-running a day is deterministic. Each dispatched alert is keyed on (sku_id, date, alert_level) and deduplicated at the sink, so a retried batch or a re-evaluation after a late invoice converges to the same set of pages instead of firing them again.

Related Pages

• Setting Dynamic Variance Thresholds — the step-by-step implementation of the multiplier and decay logic sketched here.
• Variance Mapping Methodologies — the upstream reconciliation that produces the daily deltas this engine consumes.
• Waste Tracking & Routing Systems — cross-references a breach against logged spoilage before it escalates.
• Portion Size Standardization — the execution tolerances that explain protein and garnish variance.
• Multi-Location Cost Center Architecture — layering regional multiplier overrides on the base matrix.

Up one level: Theoretical vs Actual Food Cost Calculation.

For the estimator semantics behind these calculations, consult the official Python statistics module and the pandas rolling API documentation.