Delivery constraints and capacity scenarios

Business question

Where is delivery capacity constrained, and how would additional capacity change unmet demand? The intended user is an operations manager investigating regional bottlenecks and comparing explicit capacity scenarios.

Data model

364 rows cover July 2–September 30, 2026, across four regions. Demand equals delivered plus unserved parcels; delivered parcels cannot exceed available capacity. Costs are stored as integer USD cents.

Metric definitions

Observed constraints

The North region accounts for 652 unserved deliveries, or 54.6% of the total shortfall. It is the first region to investigate.

Compare scenarios at the same demand

Adding 15% capacity reduces modeled unserved demand from 2,766 to 842, enabling 1,924 additional deliveries at the same modeled demand.

The assumed capacity cost is $2.50 per extra daily unit. Under this model, $8,550 / 1,924 = $4.44 per additional delivery. This excludes variable delivery cost and revenue, so it is not ROI or total incremental delivery cost.

This is sensitivity analysis over historical days, not a forecast or an optimization algorithm. Each row is rounded separately. Capacity cannot transfer between regions. Demand, service quality and unit capacity cost are assumptions rather than predictions.

SQL and evidence

SELECT f.region, SUM(demand) AS demand, SUM(capacity) AS capacity,
       SUM(delivered) AS delivered, SUM(late) AS late,
       SUM(unserved) AS unserved, SUM(cost_cents) / 100.0 AS cost_usd,
       (SUM(delivered)-SUM(late)) * 1.0 / NULLIF(SUM(delivered),0) AS on_time_rate,
       SUM(cost_cents) / (100.0 * NULLIF(SUM(delivered),0)) AS cost_per_delivery_usd,
       SUM(unserved) * 1.0 / NULLIF(SUM(SUM(unserved)) OVER (),0) AS unserved_share
FROM fact_delivery_daily f JOIN dim_date d USING (date)
WHERE (:month IS NULL OR d.month = :month)
  AND (:region IS NULL OR f.region = :region)
GROUP BY f.region ORDER BY unserved DESC;

-- A sensitivity model, not a forecast. No transfer of capacity across regions/days.
-- Units: parcels/day; :unit_capacity_cost_usd = 2.50 USD per extra daily unit.
-- SQLite ROUND matches JavaScript Math.round for these nonnegative inputs.
WITH adjusted AS (
  SELECT f.*, CAST(ROUND(demand * (1 + :demand_growth_pct / 100.0)) AS INTEGER) AS new_demand,
         CAST(ROUND(capacity * (1 + :capacity_growth_pct / 100.0)) AS INTEGER) AS new_capacity
  FROM fact_delivery_daily f JOIN dim_date d USING (date)
  WHERE (:month IS NULL OR d.month = :month)
    AND (:region IS NULL OR f.region = :region)
), modeled AS (
  SELECT *, MIN(new_demand,new_capacity) AS modeled_delivered,
         MAX(0,new_demand-new_capacity) AS modeled_unserved,
         (new_capacity-capacity) * :unit_capacity_cost_usd AS extra_cost_usd
  FROM adjusted
)
SELECT SUM(delivered) AS baseline_delivered, SUM(unserved) AS baseline_unserved,
       SUM(new_demand) AS modeled_demand, SUM(modeled_delivered) AS modeled_delivered,
       SUM(modeled_unserved) AS modeled_unserved,
       SUM(extra_cost_usd) AS extra_cost_usd
FROM modeled;

Validation

Primary keys, foreign keys and CHECK constraints prevent invalid loads. SQL rules are in 08_quality_checks.sql; Python adds independent reconciliation checks.

These checks establish internal consistency of the synthetic fixture. They do not establish the completeness of real business events or a causal effect.

Reproduce the analysis

python3 analytics/run_analysis.py

Python 3 only; no external dependencies or network requests. The script rebuilds the local database and result files in analytics/results/.

The source contains prepared aggregates, not raw transactions or user events. Source dimensions retain their original labels; English display labels are mapped without changing the numerical data. The website loads verified extracts and recalculates filters in the browser, without a live SQL server.

Skills demonstrated

Exception monitoring, operational drill-downs, scenario modeling, explicit cost assumptions and comparing capacity choices at equal demand.

Created with Codex. Charts and controls use the standard Data Analytics runtime. The data model, SQL, metric definitions and analytical logic are available for inspection and independent adaptation.