Turning 14 daily spreadsheets into one live operations dashboard

The challenge

Fleet, fuel, and delivery data lived in separate exports maintained by three teams. Reconciling them took two analysts a full day, so managers only ever saw stale numbers and could not spot loss-making routes.

Research

• Shadowed dispatch and billing teams for a week to map the real workflow.
• Catalogued every source file, owner, refresh cadence, and known error.
• Defined 11 KPIs with the operations head before modelling anything.

Strategy

• Single source of truth first; visuals second.
• Automate ingestion so no analyst has to touch a spreadsheet daily.
• Design for the floor manager on a wall screen, not for a data analyst.

Design decisions

• Wall-screen layout readable from four metres away.
• Exception-first views that surface late and loss-making routes at the top.
• Drill-through from route to trip to individual consignment.

Development

• Scheduled Python ingestion with schema validation and failure alerts.
• Incremental refresh keeping the model responsive as history grew.
• Row-level security so branch managers see only their own region.

How we worked

1. 1
   Discovery

   Source mapping and KPI definition workshop.
2. 2
   Modelling

   Star schema, validation rules, and reconciliation logic.
3. 3
   Dashboard

   Role-based views for dispatch, finance, and leadership.
4. 4
   Handover

   Documentation plus two training sessions.