Use Google Sheets to Find Exception Clusters in Your Delivery Data

What This Does

Export your failed delivery and exception data from your TMS into Google Sheets, then use Gemini AI to identify which drivers, zones, days, and times have the most problems — so you can fix root causes instead of just reacting to individual exceptions.

Before You Start

• You have a Google account
• You can export exception/failed delivery data from your TMS (Onfleet, Routific, or carrier portal) as a CSV file
• Your export includes: driver name, date, zone or stop address, exception reason
• Time needed: 15 minutes first time; 5 minutes monthly after
• Cost: Free

Steps

1. Export your exception data from your TMS

In your routing/delivery software, find the reporting or analytics section. Look for "Failed Deliveries," "Exception Report," or "Undelivered Stops." Export as CSV for the last 30 days.

What you should see: A downloaded CSV file with one row per failed stop. Troubleshooting: If your TMS doesn't have an export button, screenshot the data and type the key columns manually — even a rough export is useful.

2. Import the CSV into Google Sheets

Go to sheets.google.com → New sheet → File → Import → Upload your CSV file. Select "Replace spreadsheet" and click Import.

What you should see: Your exception data appears as a table with headers.

3. Ask Gemini to find patterns

Open the Gemini sidebar (sparkle icon or Extensions → Gemini). Ask:

"This is my failed delivery data for the last 30 days. Which driver has the most exceptions? Which day of the week has the most failures? Which zone or area shows the most problems? Summarize the top 3 patterns I should investigate."

What you should see: A written summary identifying the top exception contributors.

4. Add conditional formatting to visualize problem drivers

Select your "Exception Count" column → Format → Conditional formatting. Set: cells greater than [your threshold, e.g., 10] → fill with red. Cells 5–10 → yellow. Below 5 → green.

What you should see: An instant visual heat map of driver exception rates.

Real Example

Scenario: You've noticed exception rates seem high but can't pinpoint why. You export 30 days of data.

What you type into Gemini: "Columns are: Driver, Date, Zone, Exception Reason. 30 days of failed delivery data. What are the top 3 patterns?"

What you get: "1. Driver Thompson accounts for 31% of all exceptions (47 of 152). 2. Thursday has 28% more exceptions than any other day — likely related to volume spike. 3. Zone 4 (downtown commercial) has the highest exception rate at 18% — possible access or parking issues. Recommend investigating Thompson's routes and checking Thursday staffing levels."

Tips

• Run this analysis monthly — seasonal patterns (holiday volume, construction zones) become visible over time
• Share the output with your manager as part of your monthly review instead of writing it from scratch
• If Gemini isn't available, paste the summary stats into ChatGPT with the same question

Tool interfaces change — if the Gemini button has moved, look for AI, Gemini, or Smart options in the Extensions or sidebar menu.