Explain OTIF Declines Faster

See the Decision Intelligence Workflow in Action

This workflow handles a compound cognitive task: quantify where OTIF moved, test which operating signals best explain the change, and assemble the result into a defensible escalation narrative. It starts with shipment-level performance by lane, carrier, and mode, then checks whether the decline aligns with tender lead time, dwell, carrier SLA deterioration, or broader market capacity pressure.

Data Warehouse Integration

The workflow is designed for warehouse-resident operational data that already exists across transportation and partner reporting systems. It queries shipment execution data, carrier SLA scorecards, and market transport indices together so the analysis can be reproduced without rebuilding the same spreadsheet package for every exception review.

That cross-domain join matters because OTIF declines are rarely visible in one source alone: shipment timing lives in the TMS, carrier performance lives in the scorecard, and capacity pressure shows up in external market indices.

• tms_shipments
• carrier_sla_scorecard
• market_transport_indices

CREATE TABLE pura_vida_foods_dev.logistics_dw.tms_shipments (
  shipment_id STRING,
  region_id STRING,
  origin_dc_id STRING,
  dest_city STRING,
  lane_id STRING,
  carrier_id STRING,
  mode STRING,
  ship_date DATE,
  promised_delivery_date DATE,
  actual_delivery_date DATE,
  on_time_flag BOOLEAN,
  tender_lead_hours DOUBLE,
  dwell_hours DOUBLE
);

SELECT
  region_id,
  lane_id,
  carrier_id,
  mode,
  SUM(CASE WHEN ship_date BETWEEN string">'{start_date}' AND string">'{end_date}' THEN 1 ELSE 0 END) AS curr_shipments,
  AVG(CASE WHEN ship_date BETWEEN string">'{start_date}' AND string">'{end_date}' THEN CAST(on_time_flag AS INT) END) AS curr_otif,
  SUM(CASE WHEN ship_date BETWEEN string">'{compare_start_date}' AND string">'{compare_end_date}' THEN 1 ELSE 0 END) AS base_shipments,
  AVG(CASE WHEN ship_date BETWEEN string">'{compare_start_date}' AND string">'{compare_end_date}' THEN CAST(on_time_flag AS INT) END) AS base_otif,
  AVG(CASE WHEN ship_date BETWEEN string">'{start_date}' AND string">'{end_date}' THEN tender_lead_hours END) AS curr_tender_lead_hrs,
  AVG(CASE WHEN ship_date BETWEEN string">'{start_date}' AND string">'{end_date}' THEN dwell_hours END) AS curr_dwell_hrs
FROM pura_vida_foods_dev.logistics_dw.tms_shipments
WHERE region_id = string">'{region_id}'
  AND ship_date BETWEEN string">'{compare_start_date}' AND string">'{end_date}'
GROUP BY region_id, lane_id, carrier_id, mode
HAVING curr_shipments > 0;

CREATE TABLE pura_vida_foods_dev.logistics_dw.carrier_sla_scorecard (
  carrier_id STRING,
  region_id STRING,
  lane_id STRING,
  report_date DATE,
  tender_accept_rate DOUBLE,
  on_time_pickup_rate DOUBLE,
  on_time_delivery_rate DOUBLE,
  claims_rate DOUBLE
);

CREATE TABLE pura_vida_foods_dev.logistics_dw.market_transport_indices (
  region_id STRING,
  index_date DATE,
  capacity_index DOUBLE,
  fuel_index DOUBLE
);

SELECT
  s.carrier_id,
  s.lane_id,
  s.region_id,
  s.report_date,
  s.tender_accept_rate,
  s.on_time_pickup_rate,
  s.on_time_delivery_rate,
  m.capacity_index,
  m.fuel_index
FROM pura_vida_foods_dev.logistics_dw.carrier_sla_scorecard s
JOIN pura_vida_foods_dev.logistics_dw.market_transport_indices m
  ON m.region_id = s.region_id
 AND m.index_date = s.report_date
WHERE s.region_id = string">'{region_id}'
  AND s.report_date IN (string">'{compare_end_date}',string">'{end_date}');

The Business Case for Automation

Primary Valuation Metric: % of OTIF exception reviews where the brief is used as the single source of truth for the escalation narrative