Explain Fulfillment Delays Faster

See the Decision Intelligence Workflow in Action

This workflow handles a compound cognitive task that usually gets done by hand under time pressure: detect a material SLA drop, trace likely causes across labor, system reliability, and order mix, and assemble the findings into a usable explanation. It is built for recurring incident review work where consistency and defensibility matter as much as speed.

Data Warehouse Integration

The workflow depends on warehouse operational data that already exists in routine systems of record. It reads fulfillment performance, labor availability, downtime events, and order-priority patterns together so the manager is not manually reconciling separate reports during an incident.

That cross-domain join matters because fulfillment delays are rarely explained by a single metric; labor shortfalls, WMS downtime, and order-mix shifts have to be evaluated against the same time window to produce a defensible explanation.

• order_fulfillment
• labor_roster
• system_downtime

CREATE TABLE pura_vida_foods_dev.ops_dw.order_fulfillment (
  warehouse_id STRING,
  order_id STRING,
  order_date DATE,
  promised_date DATE,
  fulfilled_date DATE,
  priority STRING
);

WITH current_window AS (
  SELECT
    warehouse_id,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN fulfilled_date > promised_date THEN 1 ELSE 0 END) AS delayed_orders
  FROM pura_vida_foods_dev.ops_dw.order_fulfillment
  WHERE warehouse_id = string">'{warehouse_id}'
    AND fulfilled_date BETWEEN string">'{start_date}' AND string">'{end_date}'
  GROUP BY warehouse_id
),
prior_window AS (
  SELECT
    warehouse_id,
    COUNT(*) AS prior_total_orders,
    SUM(CASE WHEN fulfilled_date > promised_date THEN 1 ELSE 0 END) AS prior_delayed_orders
  FROM pura_vida_foods_dev.ops_dw.order_fulfillment
  WHERE warehouse_id = string">'{warehouse_id}'
    AND fulfilled_date BETWEEN string">'{prior_start_date}' AND string">'{prior_end_date}'
  GROUP BY warehouse_id
)
SELECT
  c.warehouse_id,
  c.total_orders,
  c.delayed_orders,
  p.prior_total_orders,
  p.prior_delayed_orders
FROM current_window c
LEFT JOIN prior_window p
  ON c.warehouse_id = p.warehouse_id;

CREATE TABLE pura_vida_foods_dev.ops_dw.labor_roster (
  warehouse_id STRING,
  roster_date DATE,
  shift STRING,
  scheduled_staff INT,
  present_staff INT
);

SELECT
  roster_date,
  shift,
  scheduled_staff,
  present_staff,
  (scheduled_staff - present_staff) AS staff_gap
FROM pura_vida_foods_dev.ops_dw.labor_roster
WHERE warehouse_id = string">'{warehouse_id}'
  AND roster_date BETWEEN string">'{start_date}' AND string">'{end_date}'
ORDER BY roster_date;

CREATE TABLE pura_vida_foods_dev.ops_dw.system_downtime (
  warehouse_id STRING,
  downtime_date DATE,
  duration_minutes INT,
  system_name STRING
);

SELECT
  downtime_date,
  system_name,
  duration_minutes
FROM pura_vida_foods_dev.ops_dw.system_downtime
WHERE warehouse_id = string">'{warehouse_id}'
  AND downtime_date BETWEEN string">'{start_date}' AND string">'{end_date}'
ORDER BY downtime_date;

The Business Case for Automation

Primary Valuation Metric: Reduction in average time to produce a root-cause narrative for fulfillment delays