Business intelligence prototype · Trucking & logistics
A practical BI framework for integrating TMS, telematics, fuel, maintenance, and financial data into reliable forecasting and operational intelligence.
Independent demonstration created by S. Scott Mattes using synthetic data. This prototype is not affiliated with Value Truck and contains no proprietary company information.
Section 01 · The data foundation
“A single shipment may appear in multiple systems with different identifiers, timestamps, mileage values, and cost allocations. Reliable business intelligence begins by resolving those records into one auditable load-level model.”
Transportation Management System
Orders, stops, rates, statuses
Telematics & ELD
GPS, mileage, hours of service
Fuel Cards
Gallons, price, location, unit
Maintenance
Work orders, parts, downtime
Financial ERP
Invoices, payments, GL postings
External APIs
Fuel indexes, weather, FX rates
One shipment, one record
Validated Load-Level
Data Model
Identity resolution · timestamp reconciliation · mileage validation · cost allocation · audit trail
Financial Intelligence
Margin, pricing, leakage
Fleet Intelligence
Utilization, deadhead, dwell
Customer Intelligence
Profitability, service, AR
Forecasting & Alerts
Scenarios, exceptions, risk
Section 02 · High-value analytics
Each use case runs on the same validated load-level model, so the finance view and the operations view can never drift apart.
Decision it supports: Which freight to keep, reprice, or walk away from — decided on measured margin instead of averaged rate-per-mile instinct.
Decision it supports: Where planners should reposition equipment and which lanes need paired backhauls before adding trucks.
Decision it supports: How hiring, fuel-hedging, and credit decisions change next quarter's cash position — tested before committing.
Decision it supports: Which loads, units, and invoices need intervention today — surfaced automatically instead of discovered at month-end.
Section 03 · Executive dashboard
Every figure below recomputes from load-level records as you filter — the same behavior a production semantic layer guarantees. Period Mar 16 – Jul 5, 2026, as of Jul 10, 2026.
Synthetic demonstration data726 of 726 loads selected
Revenue per Total Mile
$2.74
Linehaul + surcharge + detention ÷ all miles
Cost per Mile
$2.19
Variable operating cost ÷ all miles
Margin per Mile
$0.55
Contribution margin ÷ all miles
Deadhead
12.8%
Empty miles ÷ total miles
On-Time Delivery
94.2%
Delivered ≤ 30 min past appointment
Avg Border Dwell
3.7 h
Cross-border loads only
Revenue-leakage watch: 54 completed loads in this slice sat beyond the 2-hour free window with no detention billed — roughly $8K in unbilled accessorials. See the margin investigation.
Billed revenue vs. variable operating cost by pickup week
Margin compresses in the final six weeks as diesel rises faster than surcharge recovery — the gap the investigation below decomposes.
| Week of | Revenue | Operating cost | Contribution margin | Loads |
|---|---|---|---|---|
| Mar 16 | $70,213 | $56,743 | $13,471 | 40 |
| Mar 23 | $78,012 | $62,367 | $15,645 | 41 |
| Mar 30 | $76,647 | $61,399 | $15,247 | 42 |
| Apr 6 | $81,451 | $64,486 | $16,965 | 45 |
| Apr 13 | $81,249 | $64,143 | $17,106 | 44 |
| Apr 20 | $83,671 | $66,431 | $17,240 | 43 |
| Apr 27 | $84,276 | $66,680 | $17,596 | 45 |
| May 4 | $82,677 | $65,376 | $17,300 | 43 |
| May 11 | $92,161 | $73,102 | $19,059 | 48 |
| May 18 | $84,803 | $67,142 | $17,661 | 47 |
| May 25 | $85,673 | $68,923 | $16,751 | 46 |
| Jun 1 | $88,807 | $71,118 | $17,689 | 48 |
| Jun 8 | $80,172 | $64,024 | $16,148 | 44 |
| Jun 15 | $93,335 | $74,485 | $18,850 | 47 |
| Jun 22 | $93,402 | $76,295 | $17,107 | 50 |
| Jun 29 | $106,920 | $87,066 | $19,854 | 53 |
Revenue per total mile vs. contribution margin % · bubble size = load count
Lanes below the fleet line earn well per loaded mile yet trail on margin — usually empty repositioning, dwell, or border friction.
| Lane | Rev / total mile | Margin % | Loads | Total miles |
|---|---|---|---|---|
| Toronto, ON → Chicago, IL | $2.65 | 16.1% | 86 | 77,679 |
| Chicago, IL → Winnipeg, MB | $2.61 | 13.1% | 50 | 62,196 |
| Calgary, AB → Denver, CO | $3.23 | 29.7% | 34 | 41,085 |
| Chicago, IL → Toronto, ON | $2.65 | 15.6% | 54 | 48,104 |
| Denver, CO → Calgary, AB | $3.02 | 25.5% | 33 | 39,743 |
| Chicago, IL → Dallas, TX | $2.56 | 18.5% | 35 | 36,580 |
| Dallas, TX → Atlanta, GA | $2.80 | 27.1% | 37 | 31,676 |
| Atlanta, GA → Chicago, IL | $2.76 | 25.8% | 36 | 28,692 |
| Montreal, QC → Toronto, ON | $2.52 | 18.0% | 75 | 28,264 |
| Monterrey, MX → Laredo, TX | $2.91 | 18.2% | 72 | 22,283 |
| Laredo, TX → Dallas, TX | $2.42 | 17.6% | 39 | 20,826 |
| Toronto, ON → Montreal, QC | $2.56 | 19.1% | 49 | 18,452 |
| Dallas, TX → Laredo, TX | $2.54 | 21.0% | 36 | 17,021 |
| Toronto, ON → Detroit, MI | $3.17 | 23.0% | 53 | 13,385 |
| Laredo, TX → Monterrey, MX | $2.96 | 18.3% | 37 | 11,631 |
Ranked by billed revenue for the filtered period
A wide gap between the two bars flags a high-revenue, low-margin relationship worth a pricing or dwell conversation.
| Customer | Revenue | Contribution margin | Margin % | Loads |
|---|---|---|---|---|
| Northgate Retail Group | $388,379 | $62,921 | 16.2% | 159 |
| Prairie Foods Cooperative | $294,913 | $60,567 | 20.5% | 84 |
| Great Plains Agri Supply | $213,847 | $48,554 | 22.7% | 108 |
| Midwest Building Products | $167,902 | $44,442 | 26.5% | 73 |
| Lakeshore Beverage Co. | $82,967 | $15,215 | 18.3% | 87 |
| Cascadia Paper & Packaging | $73,947 | $14,185 | 19.2% | 53 |
| Dominion Auto Components | $72,950 | $14,191 | 19.5% | 89 |
| TransBorder Metals | $68,564 | $13,614 | 19.9% | 73 |
Empty miles as a share of total miles run
The highlighted terminal runs the most empty miles per mile — a repositioning and backhaul-planning target.
| Terminal | Deadhead % | Empty miles | Total miles | Loads |
|---|---|---|---|---|
| Laredo, TX | 19.9% | 10,868 | 54,740 | 148 |
| Chicago, IL | 17.0% | 24,939 | 146,880 | 139 |
| Calgary, AB | 10.2% | 8,227 | 80,828 | 67 |
| Dallas, TX | 9.7% | 7,470 | 77,389 | 109 |
| Toronto, ON | 8.8% | 12,186 | 137,780 | 263 |
Hours from border-queue arrival to release, cross-border loads
The long right tail is the planning problem: median crossings are fine, but 6+ hour outliers wreck downstream appointments.
| Dwell range | Loads |
|---|---|
| 0–1 h | 1 |
| 1–2 h | 35 |
| 2–3 h | 137 |
| 3–4 h | 122 |
| 4–5 h | 54 |
| 5–6 h | 26 |
| 6–8 h | 25 |
| 8+ h | 19 |
Share of loads delivered within 30 minutes of appointment
| Week of | On-time % | Loads |
|---|---|---|
| Mar 16 | 92.5% | 40 |
| Mar 23 | 97.6% | 41 |
| Mar 30 | 95.2% | 42 |
| Apr 6 | 93.3% | 45 |
| Apr 13 | 93.2% | 44 |
| Apr 20 | 95.3% | 43 |
| Apr 27 | 93.3% | 45 |
| May 4 | 93.0% | 43 |
| May 11 | 93.8% | 48 |
| May 18 | 91.5% | 47 |
| May 25 | 95.7% | 46 |
| Jun 1 | 97.9% | 48 |
| Jun 8 | 93.2% | 44 |
| Jun 15 | 93.6% | 47 |
| Jun 22 | 94.0% | 50 |
| Jun 29 | 94.3% | 53 |
Section 04 · Root-cause investigation
“Aggregate revenue can conceal the operational reasons margin changes. A useful BI system connects financial variance to operational drivers and identifies which causes are controllable.”
Synthetic demonstration dataIllustrative variance decomposition on synthetic data
Revenue grew $420K, but fuel, empty miles, dwell, and unbilled detention consumed $330K of it — leaving +$90K of net contribution improvement. Three of the four drags are controllable.
| Driver | Impact | Running total | What it captures |
|---|---|---|---|
| Revenue growth | +$420K | +$420K | Higher volume and rate mix vs. prior period |
| Fuel expense | −$145K | +$275K | Diesel ramp in the final six weeks, net of surcharge |
| Empty miles | −$96K | +$179K | Added repositioning, led by the Winnipeg lane |
| Dwell & waiting | −$58K | +$121K | Customer dock dwell and paid driver waiting |
| Unbilled detention | −$31K | +$90K | Detention incurred but never invoiced |
| Net change | +$90K | +$90K | Contribution improvement after operational drags |
Review pricing and detention terms for high-dwell customers
Dock dwell above the free window is a real cost — paid driver hours and lost capacity. Reprice it or contract it away.
Improve backhaul planning on lanes with persistent empty repositioning
A lane that pays well loaded can still destroy margin if the trailer comes home empty. Pair headhauls with committed return freight.
Automatically flag completed loads with incurred but unbilled detention
Detention that operations recorded but billing never invoiced is pure leakage — an exception feed catches it the day it happens.
Section 05 · Shared definitions
When finance, operations, and sales each compute “on-time” or “margin” their own way, every meeting starts with an argument about whose number is right. A governed glossary — grain, formula, source, owner — is the cheapest fix in analytics. Expand a row for its inclusion and exclusion rules.
| KPI | Business definition | Formula | Grain | Source systems | Refresh | Owner | Details |
|---|---|---|---|---|---|---|---|
| Revenue per total mile | Average revenue earned for every mile the fleet runs, loaded or empty. | (Linehaul + fuel surcharge + detention revenue) ÷ total miles | Load | TMS · Financial ERP | Daily, 06:00 | Finance BI | |
| Cost per mile | Variable operating cost for every mile the fleet runs. | Σ variable operating costs ÷ total miles | Load | Fuel cards · Payroll · Maintenance · ERP | Daily, 06:00 | Finance BI | |
| Contribution margin per load | Dollars each load contributes toward fixed cost and profit. | Load revenue − load variable cost | Load | TMS · Fuel cards · Payroll · ERP | Daily, 06:00 | Finance BI | |
| Deadhead percentage | Share of total miles run without revenue freight. | Empty miles ÷ total miles | Load, aggregated by terminal and lane | Telematics/ELD · TMS | Hourly | Operations BI | |
| On-time delivery | Share of loads delivered within the appointment tolerance. | On-time deliveries ÷ delivered loads | Load | TMS · Telematics | Hourly | Operations BI | |
| Border dwell | Hours a cross-border load waits from border-queue arrival to release. | Release timestamp − border-arrival timestamp | Border-crossing event | Telematics/ELD · Customs broker feed | Hourly | Operations BI | |
| Tractor utilization | Miles produced per active tractor each week. | Total miles ÷ active tractor-weeks | Tractor-week | Telematics · Maintenance · TMS | Weekly, Monday 05:00 | Fleet BI |
Section 06 · Dimensional model
Every measure lives once, at load grain, in the fact table; every business attribute lives once in a conformed dimension. Executive reports become consistent because “customer,” “lane,” and “week” mean exactly one thing — and fast because queries join one narrow fact table to small dimensions instead of scanning raw system extracts.
FactLoad
keys · measures · one row per load
joined to seven conformed dimensions
DimDate
DimCustomer
DimLocation
DimLane
DimEquipment
DimDriver
DimTerminal
Consistent
One conformed DimCustomer means finance and operations rank the same customers the same way.
Fast
Narrow fact rows and small dimensions keep executive dashboards interactive without exotic tuning.
Auditable
Every dashboard number traces back through the fact table to source-system records.
Section 07 · Forecasting & scenario planning
Every output below is plain arithmetic over the assumptions shown — no black-box model. Move a lever, or load a preset, and trace exactly why the margin changes.
Current network shape at trailing 16-week averages.
29,358 miles operated per week in this scenario.
A production forecast would combine historical seasonality, committed customer volume, spot-rate conditions, equipment availability, fuel prices, and accounts-receivable collection patterns — with backtested accuracy reported next to every number, so planners know how much to trust it.
Section 08 · Implementation principles
Operational dashboards should be traceable to financial results. If the margin dashboard can't tie to the P&L, one of them is wrong — and both get ignored.
A shared KPI glossary prevents contradictory reports. Grain, formula, inclusions, and owner are agreed once — then every chart inherits the definition.
Validate uniqueness, completeness, freshness, valid ranges, and referential integrity on every run. A dashboard is only as credible as its worst upstream join.
Surface late loads, margin leakage, missing invoices, and data-quality failures before they become month-end surprises. Reports describe; exceptions act.
Section 09 · About
Business Intelligence Analyst | Data Engineer
This prototype demonstrates the full path from fragmented operational systems to executive decisions: a governed data model, defined metrics, interactive analysis, root-cause investigation, and transparent forecasting — the working scope of a BI analyst in freight.