Case study · Logistics
On-time, in-full, measured every day
Logistics companies are judged by customers on on-time in-full delivery, yet often measure it monthly from spreadsheets. This is how we built Power BI analytics from transport, warehouse and telematics data that show delivery performance and cost every day.
Project overview
Delivery performance analytics on Power BI.
- Industry
- Logistics
- Client
- Confidential
- Solution
- Delivery performance analytics
- Platform
- Power BI + Fabric
- Timeline
- 8–14 weeks
- Team
- 3 people
- Sources
- TMS, WMS, telematics
- Refresh
- Daily, intraday optional
- Users
- Operations, account managers, customers
Expected business outcomes
What the client set out to gain
Daily visibility lets operations fix problems before customers notice.
-
Daily
OTIF by customer and depot
On-time in-full performance every morning.
From TMS and proof-of-delivery data
-
10–20%
Fewer failed deliveries targeted
Failure reasons visible by route, driver and customer.
Target range when operations act on the reasons
-
Per drop
Delivery cost visibility
Cost per drop and per route from telematics and payroll data.
Where cost data is integrated
Business challenges
How delivery reporting worked before
Data exists in several systems; performance is reported monthly.
Transport
The TMS knows planned deliveries; drivers' devices know what happened.
Failures
Failure reasons recorded as free text or not at all.
Costs
Fuel, wages and vehicle costs known by total, not by route.
Customers
Customer performance reports built by hand for account reviews.
Timing
Problems discovered weeks after they started.
Proposed solution
What the Power BI build covers
A delivery data model joining plans, actuals and costs.
-
Data pipelines
TMS, proof of delivery and telematics data loaded daily.
-
OTIF
On-time and in-full by customer, depot, route and driver.
-
Failed deliveries
Failure reasons categorised and trended.
-
Cost to serve
Cost per drop, per mile and per customer.
-
Customer reports
Shareable performance reports for account reviews.
-
Exception alerts
Alerts when OTIF drops for a customer or depot.
Project requirements
What the client asked us for
The request was a daily OTIF figure. The detail matters: 'on time' needs agreed windows per customer, and 'in full' needs line-level delivery data.
Structured failure reasons are introduced on drivers' devices, because free text can't be analysed.
What the project had to deliver
- 01Daily OTIF by customer
- 02Structured failure reasons
- 03Cost per drop
- 04Customer-ready reports
Key challenges & considerations
What made it harder, and what we did
-
Different delivery windows per customer
What we did
Delivery windows were held per customer, so on-time was measured against what each customer was promised.
-
Proof of delivery data gaps
What we did
Missing proof-of-delivery data was reported as its own measure until capture improved.
-
Subcontracted carriers' data
What we did
Subcontracted carriers supplied data in a standard template that was loaded automatically.
-
Cost allocation rules
What we did
Cost allocation rules were agreed with finance and documented in the model.
What we watched for
- Agree OTIF windows per customer
- Replace free-text failure reasons
- Include subcontractor data
- Alert on trends, not single failures
Development process
Definitions, data, dashboards
OTIF is defined with customers' contracts in hand.
-
Definitions
OTIF windows and failure categories agreed.
-
Pipelines
TMS, POD and telematics connected.
-
OTIF dashboard
Daily OTIF live for operations.
-
Cost and customers
Cost to serve and customer reports added.
-
Alerts
Exception alerts and review routines set up.
Technology stack
Power BI for logistics
Fabric for volume, Power BI for everyone who needs the numbers.
Reporting
- Power BI
- Power BI mobile
- Paginated reports
Data platform
- Microsoft Fabric
- Azure SQL
- Dataflows
Governance
- Row-level security
- Deployment pipelines
- Measure documentation
Delivery
- Git integration
- Automated refresh monitoring
Quality assurance & deployment
Every figure reconciled before anyone relied on it
A dashboard is only useful if its numbers are believed. Each measure was reconciled to its source, and to finance where money was involved, before the report was released.
- Each measure reconciled to its source system
- Financial figures signed off by finance
- Data quality checks on every refresh: completeness and freshness
- Row-level security tested with real user accounts
- Report performance tested at full data volume
Deployment
- 01Cost and customersCost to serve and customer reports added.
- 02AlertsException alerts and review routines set up.
- 03Deployment pipelinesReports moved from development to test to production through controlled pipelines.
- 04Adoption and warrantyTraining for report users and builders, usage reviewed, and six months of free bug fixing.
Final deliverables
Problems fixed before customers call
Operations see OTIF and failures every morning and act on them the same day, and account managers bring accurate reports to customer reviews.
Cost per drop shows which routes and customers are profitable.
- Daily OTIF
- Categorised failures
- Cost to serve
- Customer performance reports
Handed over to the client
- 01Data model and pipelinesDocumented and scheduled
- 02Reports and dashboardsPublished with security roles
- 03Measure definitionsEvery figure documented
- 04TrainingFor report users and report builders
- 05Six-month warrantyFree bug fixing after delivery
Success metrics
How success is measured
| Measure | Outcome | How it is measured |
|---|---|---|
| OTIF by customer and depot | Daily | From TMS and proof-of-delivery data |
| Fewer failed deliveries targeted | 10–20% | Target range when operations act on the reasons |
| Delivery cost visibility | Per drop | Where cost data is integrated |
Verified reviews
What clients say about our custom software work
Verified reviews from our clients on the same kind of work, published on Clutch.
-
Custom Software Development
Web-Based Coupon App Development
The work produced by the team was of exceptional quality.
Jon Thies
CTO, Model Rocket Feb 2022 – Mar 2023
-
Custom Software Development
Mobile & Web Platform Development & Design
Silver Scintilla was responsive, respectful, and enjoyable to work with, quickly addressing any questions or requests.
DeShawn Brown
CEO & Founder, Lithios Jun 2021 – Nov 2022
-
Custom Software Development
Web-Based HRM System Development
Silver Scintilla provided the best solution with scalable functionality, personalized features, and robust security.
Suheb Khan
Director, Collaborative Insight Technologies Jul 2020 – Jun 2022
Free discovery call
Have an idea? Let's turn it into AI-powered software.
Book a free discovery call with our experts. Share your idea and we will help you shape the scope, timeline and budget, under NDA.
- Free consultation
- NDA before we talk
- Transparent estimate