Explain Daily Route Changes

See the Decision Intelligence Workflow in Action

This workflow handles a compound communication task that planners usually do manually under time pressure. It identifies which stops changed from the original plan, links those changes to observable operating factors, and assembles a plain-language explanation that a driver can act on immediately. The result is a repeatable narrative instead of one-off calls, texts, and ad hoc notes.

Data Warehouse Integration

The workflow depends on joining route plan data with stop-level operating signals for the same service date. It retrieves planned versus adjusted sequence and assignment changes, then connects those changes to measurable factors so the narrative is traceable to actual operating data.

That means the explanation is built across route planning data and stop-level factor data, not from a single operational table.

• route_plans
• stop_factors

CREATE TABLE retail_dev.last_mile_ops.route_plans (
  plan_id VARCHAR,
  stop_id VARCHAR,
  plan_date DATE,
  driver_id VARCHAR,
  planned_sequence INTEGER,
  adjusted_sequence INTEGER,
  status VARCHAR
);

SELECT
  plan_id,
  plan_date,
  stop_id,
  driver_id,
  planned_sequence,
  adjusted_sequence,
  status,
  CASE
    WHEN planned_sequence <> adjusted_sequence THEN 1
    WHEN status <> string">'On Time' THEN 1
    ELSE 0
  END AS is_changed
FROM retail_dev.last_mile_ops.route_plans
WHERE plan_id = string">'{plan_id}'
  AND plan_date = string">'{plan_date}';

CREATE TABLE retail_dev.last_mile_ops.stop_factors (
  stop_id VARCHAR,
  factor_date DATE,
  traffic_index INTEGER,
  customer_available BOOLEAN
);

SELECT
  stop_id,
  factor_date AS plan_date,
  traffic_index,
  customer_available
FROM retail_dev.last_mile_ops.stop_factors
WHERE factor_date = string">'{plan_date}'
  AND stop_id IN (
    SELECT stop_id
    FROM retail_dev.last_mile_ops.route_plans
    WHERE plan_id = string">'{plan_id}'
      AND plan_date = string">'{plan_date}'
  );

The Business Case for Automation

Primary Valuation Metric: Reduction in average time spent by planners on daily route plan explanations per driver.