Skip to content

Weekly Fleet Utilisation Report

Example prompt: "Every Monday at 8am, roll up last week's cost per mile, MPG, drop count, downtime, and driver infringements by vehicle and by driver. Draft a one-page summary to me with the worst five on cost per mile and the vehicles below 60% utilisation."

The Problem

The data is all there — telematics has the miles and the engine hours, the fuel-card CSV has the litres and the spend, the workshop has the downtime, the tachograph analysis has the infringements, the PDA scans have the drop counts. None of it is in one place. Most weeks the fleet manager opens four browser tabs at 8:30am, spends an hour pulling numbers into a sheet, and then never quite gets to the per-driver cut. The cases that need attention — the van quietly running at 22mpg when its baseline is 28, the driver with three working-time infringements last week, the vehicle that did 60 hours in the workshop and nobody flagged — sit invisible in someone else's system.

How GloriaMundo Solves It

We build a workflow that runs every Monday at 8am. Integration steps pull last week's exports from the telematics export folder, the fuel-card reconciliation log, the workshop job-card sheet, and the tachograph analysis CSV. A code step joins the four sources on (vehicle_reg, week_start_date) and on (driver_id, week_start_date), and computes cost per mile, utilisation percentage, MPG vs make/model baseline, drop count per shift, downtime hours, and infringement count. An LLM step composes a one-page summary highlighting the worst five on cost per mile, the vehicles below 60% utilisation, the drivers with three or more infringements, and the week-on-week deltas. An integration step saves the summary as a Gmail draft and posts a short top-line to #fleet on Slack. Glass Box preview shows the table, the chart, and the draft before anything reaches the manager's inbox.

Example Workflow Steps

  1. Trigger (scheduled): Every Monday at 8am.
  2. Step 1 (integration): Read last week's telematics export (Microlise, Webfleet, Samsara, or Quartix — CSV in the configured Drive folder), the Fuel Card Reconciliation Log tab, the Workshop Job Cards tab, and the Tachograph Analysis CSV (Tachomaster or TruTac export).
  3. Step 2 (code): For each (vehicle_reg, week_start_date), aggregate revenue-miles, engine-on hours, total fuel spend, total litres, drop count, and workshop hours. Compute cost_per_mile = (fuel + tolls + workshop + tyre wear estimate) / miles, utilisation_pct = engine-on hours / available hours, mpg_vs_baseline = current MPG / eight-week rolling baseline.
  4. Step 3 (code): For each (driver_id, week_start_date), aggregate infringement count by class (4.5h driving without break, 9h daily, 56h weekly), harsh-events per 100 miles from telematics, and walk-around defect submissions completed.
  5. Step 4 (code, gate): Dedupe on (report_week_start, depot_id) against the 'Fleet Reports Log' tab. A row at 'Sent' halts; a provisional row resumes; no row allocates a fresh report_id and writes a provisional row before drafting.
  6. Step 5 (code): Sort vehicles by cost_per_mile descending and pick the top five. Filter to vehicles where utilisation_pct < 60. Filter to drivers with infringement_count ≥ 3. Compute week-on-week deltas against the previous Monday's report row.
  7. Step 6 (llm): Compose a one-page summary — top-line totals, the worst-five table on cost per mile, the below-60%-utilisation list, the driver infringement watchlist, and the week-on-week delta on each headline KPI.
  8. Step 7 (integration): Save the summary as a Gmail draft to the fleet manager with the underlying joined CSV attached. Persist gmail_draft_link to the provisional Fleet Reports Log row.
  9. Step 8 (integration): Post a four-line summary in #fleet on Slack — cost per mile, average MPG, top utilisation laggards count, and infringement count.
  10. Step 9 (integration): Flip the provisional Fleet Reports Log row's status from 'Allocating' to 'Sent'.

Integrations Used

  • Google Drive — the inbox folder for the telematics and tachograph CSVs
  • Google Sheets — the Fuel Card Reconciliation Log, Workshop Job Cards, and Fleet Reports Log
  • Gmail — the Monday morning summary draft to the fleet manager
  • Slack — the four-line top-line in #fleet

Who This Is For

Fleet managers of 10 to 50 vehicle operations — last-mile couriers, palletline depots, regional haulage — where telematics, tacho, fuel cards, and workshop data each live in their own system and where the Monday morning roll-up is the difference between catching a quiet 6mpg drift in week one or the quarterly review.

Time & Cost Saved

Pulling Monday morning's KPI roll-up by hand against four systems typically takes 90 minutes and the per-driver cut tends to get skipped. This workflow turns it into a 10-minute review of the draft summary before the 9am stand-up; the per-driver infringement watchlist surfaces cases that used to fall through to the quarterly tacho review; and the worst-five-on-cost cut is in front of the manager every Monday rather than once a year.