AI for Delivery Managers: A Daily Exceptions Report From Route Exports

Export the completed routes, define what counts as late or failed, then ask AI to produce a short list of exceptions with stop IDs, reasons and owners. Check its totals against the export before sending anything. The report should separate confirmed facts from missing data and draft customer updates only for stops that need one.

A route export is a snapshot, not proof of what happened at a door. A late arrival may still be a successful delivery; a failed status may be a scanning error. Keep the raw file, use a fixed cut-off time, and have dispatch verify each outward-facing statement against the driver's notes or proof of delivery.

Decide which stops deserve a line in the report

The manager's problem is attention, not route optimisation. At the end of a shift, a 12-van operation may have hundreds of ordinary completed stops. The report should identify the small set that needs an owner, a customer update, or a change to tomorrow's plan. If you need to redesign route order, use the separate small courier route-planning tutorial; an exceptions report works after dispatch has happened.

Write the rule before opening an AI chat. For example, mark a stop late when its actual arrival is more than 20 minutes after the end of its promised window. Mark a failed delivery when the final status is failed, attempted, returned or undelivered according to your own system's status list. Mark a repeat address when the same normalised address and customer reference appears twice in the chosen look-back period. These are illustrative management thresholds, not universal service standards. Your customer commitments may require a different grace period.

Distinguish a breach from a watch item. A 12-minute delay with a delivery confirmed at the door may be worth a weekly trend but not an urgent customer call. A failed stop with temperature-sensitive goods or a promised installation slot needs immediate human attention even if the system recorded it only moments ago. Give each exception a priority, but make the priority rule visible so a dispatcher can challenge it.

The report's columns can be simple: stop ID, van, customer reference, promised window, actual time, status, reason evidence, priority, proposed action, owner and next check time. Keep customer names and full addresses out of the AI upload where a stop ID and partial reference will do. Your team can resolve identities in the route system. Check the data terms and settings of the tool you use before uploading personal information.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Prepare one route export that can be audited

Export all routes for the same operating day, including completed, failed, cancelled and unscanned stops. A filter that exports only late stops prevents you from detecting status gaps. Retain the unedited source as a dated file, and create a working copy. Ask for one row per planned stop. OpenAI's data-analysis guidance recommends clear column names and one record per row for spreadsheet work; ChatGPT and Claude both document CSV uploads. This is a practical way to use either assistant, with your file and account controls checked first.

Start by checking whether the export contains a stable stop ID. If a route tool creates a new ID on redelivery, carry the original order or customer reference too. A common trap is to count a redelivery as a second customer complaint when it is actually the same order. Confirm the timezone used by promised and actual times; a spreadsheet can quietly mix a local timestamp with a system timestamp. Do not let the model infer a time offset from the pattern. Get the source-system setting from dispatch.

For an illustrative two-row excerpt, imagine stop R07-014 has window end 15:00, actual arrival 15:28 and final status delivered. It is a late completed stop by the 20-minute rule. Stop R09-031 has a 16:00 window end, no arrival time, final status attempted and note “gate code absent”. It is a failed stop, not a 0-minute late stop. AI should leave the time difference blank on R09-031; treating a blank as zero would hide it.

Remove duplicated header rows, totals embedded at the bottom, and merged cells before analysis. Check a handful of records against the dispatch screen. If the export splits each stop into several scan events, first build a stop-level table using a documented rule for choosing the final event. Keep the underlying scans so you can trace the result. An AI assistant can suggest this transformation, but you should confirm that the output has the expected count of unique stop IDs.

A useful pre-upload ledger has three numbers: total planned stops, unique stop IDs, and rows with missing final status. For example, 216 planned stops, 216 unique IDs and three missing statuses is coherent. If the file has 219 rows because three stops have duplicate scans, do not report 219 deliveries. Ask AI to identify the duplicates by ID, inspect them, then decide whether to aggregate. The messy-data cleaning tutorial covers the wider spreadsheet hygiene; here the aim is a reliable day-end route snapshot.

A 12-van day: from 216 rows to six actions

Here is an illustrative worked example. Twelve vans each receive 18 planned stops, so the expected total is 216. At the chosen cut-off, the export shows 207 delivered, six failed or returned, and three without a final status. Among the delivered stops, nine arrivals exceed the 20-minute grace period. Two of those nine belong to addresses that also had a late or failed stop earlier in the seven-day look-back. The categories overlap: nine late delivered stops are already inside the 207 delivered, so you must never add them to the status totals.

First reconcile the 207 + 6 + 3 = 216 status split. Next select the nine late completed rows and six failed rows. Then compare the seven-day address history to find repeat patterns. You have 15 current exceptions for review, plus three unknown-status records that need clarification. A manager may choose to put only six actions in the brief: two failed deliveries requiring redelivery decisions, one access problem recurring at the same address, one temperature-sensitive late stop needing a service review, and two vans whose missing scans need investigation. The other late completed stops can go into a trends appendix or a weekly review.

Suppose van V04 has three late stops, but all three were delivered before customers complained and all sit after a single loading delay. The daily report should say “V04: three late completed stops; loading departure 32 minutes behind plan; dispatcher to check loading hand-off”. It should not assign three separate driver faults. If van V09 has two attempts at the same building and both notes say “no access code”, the action belongs with the order-entry or customer-contact process. That distinction makes the report useful rather than merely a list of red cells.

Here is a compact illustrative management summary: “216 planned; 207 delivered, six failed, three status unknown. Nine delivered stops passed the 20-minute lateness threshold. Priority today: confirm three unknown statuses on two vans, arrange two redeliveries, correct one access instruction, and review the temperature-sensitive late stop.” Each number is traceable to a row set. A customer-facing message would be narrower and would not disclose fleet-wide figures.

To test the AI's arithmetic, use a spreadsheet filter or pivot table to count status values and late rows independently. Check that each report line carries a source stop ID. If the model says there are 10 late stops but the filter returns nine, ask it to show the exact IDs and its formula. Do not accept an explanation such as “a few records were ambiguous” without seeing the rows. The second count is especially valuable because AI may include a failed stop with no arrival timestamp in the late tally.

Give the assistant rules, not a vague request for insights

Attach the cleaned CSV in ChatGPT or Claude and paste a brief that names columns and forbids invented details. OpenAI's data-analysis help says to specify the columns, calculations and groupings, and to review code and assumptions when the assistant uses them. Ask for row-level evidence before prose. The assistant's most useful job is sorting and wording: a fixed spreadsheet formula is usually a better authority for the counts.

Use the attached route export for the operating day shown in the file.
One row is one planned stop. Do not infer a missing timestamp.
Use stop_id as the unique key. Report duplicate keys separately.
Late = delivered and actual_arrival more than 20 minutes after window_end.
Failed = final_status in [failed, attempted, returned, undelivered].
Unknown = blank final_status or an unrecognised value.
Show: planned unique stops, counts by final status, late delivered IDs,
failed IDs, unknown IDs, and any repeated address plus customer reference
in the seven-day history file. Do not add overlapping categories.
For each action, give stop_id, evidence column, proposed owner and next check.
Then draft a manager summary of at most 120 words.
If a column is missing, stop and ask me rather than guessing.

A first answer from the assistant (illustrative) might read: “15 exceptions: nine late and six failed; V09 address repeated twice; send apology and confirm delivery tomorrow.” The first two counts may be correct, but “delivery tomorrow” is not supported unless a new slot has been booked. Edit it to “dispatcher to check available redelivery slots and contact the customer after confirmation”. The model has improved the queue, yet a person must supply the operational commitment.

Ask a second prompt for quality control: “List every statement in the summary that cannot be traced to a supplied field or note. For each, provide the unsupported phrase and the stop ID.” This can catch phrases such as “driver delayed by traffic” when the export merely shows a late timestamp. It cannot certify truth, because the source itself may be wrong. The dispatcher still checks the live route record.

Draft updates that a person can actually approve

Customer updates should be drafted only after the dispatch owner has established what happened and what the business can offer. Create a small approved vocabulary: “delivery attempted”, “we are checking the status”, “your order was delivered at [time]”, “we will confirm the next available slot”. Exclude blame, compensation promises, exact ETAs and stock claims unless the responsible person has verified them. The separate delivery-update tutorial goes deeper into ongoing parcel messages.

For the failed R09-031 stop, an illustrative draft is: “Hello [first name], we could not complete the delivery linked to reference [reference] today because the driver could not access the delivery point. Please reply with an access instruction or a suitable contact route. We will confirm a new delivery option once dispatch has checked availability.” Before sending, the dispatcher must verify that the access note is accurate, the reference belongs to the right customer, and asking for an instruction is appropriate.

A different case needs different wording. Stop R03-006 has no final scan and no driver note. The draft should say: “Hello [first name], we are checking the status of your delivery and will update you after our dispatch team has confirmed it.” It must not say the delivery failed or is on the way. A missing scan is a data problem until someone checks. This is a useful test of whether the assistant respects the difference between an exception and an unknown.

Consider a delivered but late business-to-business stop whose customer had already received a delay notice. An automatic second apology can create confusion. Add a column `customer_update_sent_at` or check the message history before drafting. The report may record “late, already advised, no further message” and keep a follow-up for the account owner if the contract requires one. AI can identify candidates; it does not know the customer conversation unless you provide that record.

Do not upload a file containing every recipient's phone number or email address merely to write six drafts. Use the stop ID and a placeholder in the prompt. The dispatcher can paste the approved copy into the authorised communication channel. If a tool is connected directly to messages, set it to create drafts for approval rather than sending automatically. A mistaken message about a lost parcel has a higher cost than a slow draft.

Find repeat addresses without accusing the wrong customer

Repeated address analysis is useful because some failed deliveries arise from recurring access, opening hours or unit-number issues. Yet address matching is messy. “Unit 2, 14 Market Road” and “14 Market Rd, Unit 2” might be the same location. “14 Market Road” and “Unit 2, 14 Market Road” might be different receiving points. Make the model propose likely matches, then have staff confirm them against order IDs and delivery instructions. Never merge records on street name alone.

Use a seven-day look-back as an illustrative starting window, with a rule for what qualifies: same normalised address plus customer reference, two or more failed or late stops, and at least two distinct planned-stop IDs. Show each matching row. A single failed stop re-exported twice is not a repeat address. If there are many recurring deliveries to one shop, denominator matters: two late stops out of 30 is different from two out of two. Include the total scheduled stops for that address before ranking it as a chronic problem.

Imagine a trade customer has four drops in a week; two are failed because the receiving door was closed before a stated closing time. That pattern merits a check of promised windows and receiving hours. Another customer has three failed records, all on the same order, because a scan was duplicated during redelivery. Correct the data first. A third has two “no answer” notes but a signed proof image for one; ask dispatch to resolve the conflicting evidence instead of putting the account on a repeat-failure list.

If the route software already provides a reliable exceptions dashboard, export-based AI may still help with the narrative and next actions, but it need not recreate the dashboard. Compare the AI's list with the system's alerts for a week. Record differences by type: missed failure, false duplicate, unsupported reason or stale status. The report is valuable only if it leads to faster, better decisions than the current handover.

Make the handover small enough to use tomorrow

A practical daily output has a brief header, a priority action table and a small reconciliation footer. The header states the cut-off time and the planned-stop total. Each priority line names one owner and a next check time. The footer states delivered, failed, unknown and late-delivered counts, with a reminder that the late count overlaps delivered. Put the full ID list in a retained working sheet, not in a long email. If dispatch cannot find the source row within seconds, the summary is too detached from the evidence.

For an illustrative line, write “R09-031 | failed attempt | gate code absent in driver note | dispatch | confirm access detail before offering new slot | check 09:00”. The time is an internal check time, not a promise to the customer. Another line might be “R03-006 | no final scan | contact V03 driver, then update customer | dispatch | check before shift handover”. These are actions, not AI conclusions. The manager can close each with a short note and preserve that decision for audit.

Set a correction loop. On the next morning, compare yesterday's unknowns with final route statuses and mark whether the report was right. If two of three unknowns later show delivered, record that the data was late, not that the driver failed. After several days, review whether the chosen 20-minute threshold floods the report or misses serious complaints. Change the rule only with a dated note; otherwise comparisons across weeks become meaningless.

Measure the workflow itself with three simple checks: did status totals reconcile, did every priority line have an owner, and did any customer message need correction after sending? A useful weekly measure is the number of repeat exceptions that received a root-cause action, not merely the number of AI summaries produced. For the broader connection between routing decisions and customer messages, see the small-firm logistics tutorial. Keep this daily report focused on yesterday's evidence and today's owners.

Further reads

• AI Route Planning for Small Delivery Fleets and Mobile Teams — Plan tomorrow's routes after reviewing today's exceptions.
• Cost per Drop: Work Out Delivery Cost From Route and Fuel Data — Measure the cost of recurring failed stops.
• How Can AI Help Courier Services? Quotes, Dispatch and Proof — Connect the exception queue with dispatch hand-offs.
• Can AI Read Delivery Notes and Update Stock Automatically? — Handle delivery paperwork as a separate workflow.
• AI in Delivery and Courier Services: Handling Damage Claims and Disputes — Triage damage evidence without mixing it into delays.
• AI Dispatch Automation for Small Delivery Firms: Where to Begin — A small delivery firm's first dispatch pilot: capture jobs, suggest assignments, handle exceptions and measure results.
• AI Tools for Exporters: Answer Overseas Buyer Enquiries and Draft Quotes — Reply to overseas buyer enquiries and draft quotes from your price list with AI, plus a document cross-check and the sign-off steps before anything is filed.
• AI Tools for Fleet Management: Spot Fuel-Card Misuse in a Spreadsheet — A worked example: a fuel-card CSV and a location export, five flag rules, a sample exceptions list and how to verify each flag with the driver.
• AI Tools for Operations Management: Weekly Exceptions Report From Exports — A worked example with three exports, a review prompt, a sample exceptions list and the checks that catch a wrong join before it misleads you.
• Cold Chain Temperature Logs: AI Flags Breaches in Logger Exports — How a small food distributor can use AI on logger exports to find breaches, spot gaps and faulty sensors, and draft the corrective-action note.
• How Does AI Help With Transport? Dispatch and Delay Alerts for 10 Vans — A 10-van firm's day modelled step by step: AI-built routes, a dispatch board that flags drift, and delay messages that go out before customers ring.
• How Is AI Used in Warehouses? Small-Warehouse Examples That Work — Four warehouse jobs a small operation can give to AI today, with numbers, and a plain test for when robots might pay.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: OpenAI documentation, Data analysis with ChatGPT and Uploading files and audio to ChatGPT; Anthropic documentation, Upload files to Claude (checked November 2026).