Stockout Risk Intelligence

04 — MODEL PERFORMANCE

Logistic regression, chosen on purpose

Selected over random forest on ROC-AUC, with a threshold deliberately tuned for recall over precision.

Why recall over precision: missing a real stockout costs more than reviewing a SKU that turns out fine. The threshold was tuned to guarantee recall ≥ 0.75 on the positive class, then maximize precision within that constraint — not picked for the best-looking single accuracy number.

Train/test split is time-based, not random — the model is evaluated on dates it has never seen, because SKU/warehouse series are correlated across time and a random split would leak future information into training.

05 — PIPELINE ARCHITECTURE

Six stages, six real artifacts

Each stage hands the next a file it actually consumes — this can be re-run end to end.

generate_data.py

Seeded daily simulation — demand, seasonality, lead times, a real disruption event.

→

schema.sql

Relational warehouse — products, suppliers, warehouses, sales, snapshots, POs.

→

queries.sql

8 business-logic queries — days-of-supply, anomaly detection, ABC analysis.

→

feature_engineering.py

Rolling 7/30-day features + forward-looking stockout label.

→

model.py

Trains, evaluates, scores the latest snapshot → model_output.csv.

→

Power BI

Weekly action dashboard — this table is the dashboard's main visual.

06 — ENGINEERING

The code underneath the table

Real excerpts — one per layer of the stack.

sql/queries.sql — days of supply
sql/queries.sql — anomaly detection
python/model.py
powerbi/dax_measures.md
// the core operational metric: how many days until this SKU runs out
WITH last_date AS (SELECT MAX(date) AS d FROM inventory_snapshots),
recent_sales AS (
    SELECT sku, warehouse_id, AVG(units_sold) AS avg_daily_sales_30d
    FROM daily_sales
    WHERE date > (SELECT date(d, '-30 days') FROM last_date)
    GROUP BY sku, warehouse_id
)
SELECT
    c.sku, c.warehouse_id,
    ROUND(c.on_hand_qty / NULLIF(r.avg_daily_sales_30d, 0), 1) AS days_of_supply,
    s.base_lead_time_days
FROM current_stock c
JOIN recent_sales r ON r.sku = c.sku AND r.warehouse_id = c.warehouse_id
WHERE c.on_hand_qty / NULLIF(r.avg_daily_sales_30d, 0) < s.base_lead_time_days
ORDER BY days_of_supply ASC;
-- Finding: any row here is at risk — stock will likely run out before
-- a reorder placed today would even arrive.
// the disruption-finder — flags a sustained lead-time blowout automatically
WITH monthly AS (
    SELECT
        po.supplier_id,
        strftime('%Y-%m', po.order_date) AS order_month,
        AVG(julianday(po.actual_delivery_date) - julianday(po.order_date)) AS avg_lead_time,
        s.base_lead_time_days
    FROM purchase_orders po
    JOIN suppliers s ON s.supplier_id = po.supplier_id
    GROUP BY po.supplier_id, order_month
)
SELECT
    supplier_id, order_month,
    ROUND(avg_lead_time / base_lead_time_days, 2) AS lead_time_ratio
FROM monthly
WHERE avg_lead_time > base_lead_time_days * 1.8   -- flag: 80%+ above baseline
ORDER BY supplier_id, order_month;
-- Finding: exactly 3 suppliers flagged, exactly during a Jul-Sep window.
// threshold selection — tuned for recall, not raw accuracy
# time-based split — SKU/warehouse series are correlated across time,
# a random split would leak the future into training
cutoff = df["date"].quantile(0.75)
train_mask = df["date"] <= cutoff

model = LogisticRegression(max_iter=1000, class_weight="balanced")
model.fit(X_train_s, y_train)
proba = model.predict_proba(X_test_s)[:, 1]

# pick the lowest threshold that still guarantees recall >= 0.75,
# then take whatever precision comes with it
thresholds = np.linspace(0.05, 0.95, 19)
best = max(
    thresholds,
    key=lambda t: (recall_score(y_test, proba >= t) >= 0.75,
                  precision_score(y_test, proba >= t, zero_division=0))
)
// the same exposure formula this page computes live, as a Power BI measure
Estimated Exposure ($) =
SUMX(
    FILTER(model_output, model_output[risk_tier] = "High"),
    model_output[unit_cost] * model_output[avg_sales_7d] * model_output[base_lead_time_days]
)
// estimates the $ value of demand that could go unfulfilled if a
// High-risk SKU stocks out for its full lead-time window

Risk Color =
SWITCH(
    TRUE(),
    model_output[risk_tier] = "High",   "#D9534F",
    model_output[risk_tier] = "Medium", "#F0AD4E",
    "#5CB85C"
)

Recommended actions this week

Reorder the 92 High-risk pairs first — sorted by days of supply ascending, the table above already ranks them.
Treat Electronics & Toys as a seasonal policy review, not a one-off fire drill — both sit at 4–5 days of supply heading into peak demand.
Run the anomaly query monthly against live PO data — it caught the supplier disruption precisely; it should run automatically, not get found in a quarterly review.
Revisit reorder points seasonally — Garden's 83 days of excess supply is a policy mismatch, not a risk.

Honest limitations

Data is synthetic — internally consistent, but without the missing values and schema drift real production data has.
The model is intentionally simple — the natural next step is an orchestrated weekly retrain (Airflow/Prefect) on a rolling window.
Demand isn't modeled with a time-series-specific method (e.g. Prophet) — a worthwhile comparison for the demand side specifically.
Estimated exposure assumes a full lead-time stockout — a real ops team would size this against partial-fulfillment scenarios too.

Right now, 92 of 160 SKU–warehouse pairs are flagged High risk — $2.14M of demand exposed if they aren't reordered in time.

The model's actual output — filter it yourself

Why these SKUs, why now

Risk tier distribution

Avg days of supply, by category

What the model actually weights

The anomaly the SQL layer was built to catch