Idowu Aluko Portfolio Case Study

Truck Express
Analysis Dashboard

A full-stack logistics analytics project from raw relational schema to interactive Tableau dashboards uncovering $262M in revenue patterns, fleet performance, and operational efficiency across 3 years of freight data. This dashboard uses a logistics datasets from Kaggle.com.

Period 2022 – 2024
Data Stack MySQL + Tableau
Tables Processed 14 → 10 Views
Dashboards 7 Pages
View Dashboards Key Findings
$262M
Total Revenue
38.56%
Operating Ratio
85,410
Total Loads
124
Active Drivers
120
Fleet Trucks
44.60%
Avg On-Time Rate
7
Dashboard Pages
Project Overview

The Business Problem

Truck Express operates a large-scale freight network across the United States, managing over 120 trucks, 150 drivers, and thousands of monthly loads across dozens of origin-destination lanes. Despite generating over $262M in revenue across 2022–2024, the company lacked a unified analytics layer to understand where that revenue was coming from, and more critically, where costs were eroding it.

The operational data existed but was fragmented across 14 relational database tables spanning drivers, trucks, trailers, customers, routes, loads, trips, fuel purchases, maintenance records, delivery events, safety incidents, facilities, driver monthly metrics and truck utilization metrics. No single view tied these together into actionable insight.

This project was designed to bridge that gap — transforming raw relational data into a 7-page interactive intelligence platform that operations, finance, and executive teams can actually use.

Project Objectives

  • Model and consolidate 14 raw tables into 10 optimized MySQL views
  • Identify the most and least profitable routes by revenue per mile
  • Build a driver performance scorecard covering revenue, MPG, on-time rate, and safety
  • Quantify fleet idle time and its direct cost to the business
  • Surface fuel and maintenance cost trends to support cost reduction initiatives
  • Detect seasonal demand patterns to support workforce and capacity planning
  • Deliver a visually professional Tableau dashboard
Why It Matters

Logistics analytics is not a nice-to-have

Revenue Leakage

Underpriced lanes and high-cost routes silently drain margin. Analytics makes the invisible visible.

Fuel Is the Largest Variable Cost

Fuel accounted for $95.59M of operating cost, a 1% efficiency gain saves ~$1M annually.

Fleet Idle Time

23% of the fleet sits idle at any given time, each idle truck is lost revenue that can be recovered.

Driver Quality Variance

Top drivers generate 2x the revenue of bottom-tier drivers. Knowing who is who changes HR decisions.

MySQL Workbench SQL Views Tableau Public CTEs Window Functions NTILE LEFT JOIN GROUP BY

Schema → Views Architecture

14 Raw Tables

drivers trucks trailers customers facilities routes loads trips fuel_purchases maintenance_records delivery_events safety_incidents driver_monthly_metrics truck_utilization_metrics
↓ MySQL Views ↓

7 Optimized Views

executive_overview route_customer_profitability truck_performance revenue_per_truck trailer_utilization driver_performance_safety fuel_cost maintenance safety_metrics_compliance seasonal_patterns_demand_trends
Data Engineering

From 14 Tables to 10 Production Views

The raw schema was a normalized relational database built for transactional integrity, not analytical consumption. Each table served a specific operational purpose but had no awareness of the broader business picture. The first engineering challenge was designing a transformation layer that preserved data accuracy while making it analytically usable.

MySQL views were chosen over flat tables to ensure the analytical layer always reflects the most current underlying data.

1

Schema Analysis & Relationship Mapping

Mapped all 14 table relationships — one-to-one, one-to-many, and many-to-one — to identify correct join paths and avoid row duplication in aggregated views.

2

Pre-Aggregation of High-Cardinality Tables

Tables like fuel_purchases and maintenance_records were aggregated via subqueries and CTEs before joining to base tables, preventing row multiplication in merged views.

3

Calculated Fields & Derived Metrics

Revenue per mile, fuel cost per mile, operating ratio, incident rate per 100 trips, driver tenure, and performance tiers were all computed directly in SQL using arithmetic expressions, DATEDIFF, and NTILE window functions.

4

View Creation & Tableau Connection

Each view was scoped to a specific dashboard page — one view per page — keeping Tableau queries lean, fast, and purpose-built for their visualization context.

5

Data Validation

Row counts, revenue totals, and cost sums were cross-validated against base table aggregates to confirm the views produced accurate, non-duplicated results before connecting to Tableau.

View SQL Merge Queries on GitHub
Analytics Development

From SQL Views to Tableau Intelligence

Once the 7 views were validated in MySQL, they were connected directly to Tableau Public as live data sources. Each view fed a dedicated dashboard page, keeping the workbook clean and the visualization logic separated from the data transformation logic.

133+ SQL Queries

Every metric on every dashboard page is backed by a purpose-written query — from single KPI aggregates to multi-join ranked lists and time-series trend tables.

KPI Card Design

Each dashboard page opens with 4–5 headline KPIs — total revenue, operating ratio, on-time rate, incident rate — giving stakeholders an instant top-line read before diving into charts.

Calculated Fields in Tableau

Revenue per mile, performance tier classification, operating ratio, and on-time rate were computed as Tableau calculated fields layered on top of the SQL views for interactive flexibility.

Dark Theme Design System

A consistent dark navy palette (#1A1A2E background, #03C4A1 primary, #C62B88 accent) was applied across all 7 pages, with color encoding carrying analytical meaning throughout.

Interactive Filters & Actions

Year, booking type, route, driver tier, and incident type filters are connected across sheets via Tableau dashboard actions, enabling drill-down exploration without leaving the page.

Geographic Visualization

Fuel cost per location is visualized on a US state-level filled map, giving operations teams an immediate geographic view of where fuel spend is concentrated.

Dashboard Showcase

7 Pages. One Complete Picture.

Each dashboard page answers a specific business question, from executive revenue oversight to safety compliance designed to serve different organizational stakeholders.

1

Executive Summary

Revenue, cost, and operational health at a glance
Executive Summary Dashboard

Key KPIs

$262.53M Revenue $101.23M Operating Cost 38.56% Operating Ratio 85,410 Total Loads

Visualizations

  • Dual-line chart showing monthly Revenue vs Operating Cost from Dec 2021 to Dec 2024 — reveals consistent cost discipline with revenue staying well above cost throughout the period
  • Two donut charts breaking down total loads and revenue by booking type: Contract, Spot, and Dedicated — Dedicated loads represent 49.6% of revenue at $130.24M
  • Sparkline KPI cards with mini trend lines for at-a-glance directional context

Key Insights

  • Operating ratio of 38.56% is exceptionally healthy — industry benchmark is below 95%, meaning the company spends just $0.39 for every $1.00 earned
  • Dedicated contract loads dominate revenue ($130.24M) but spot loads (21,528) and contract loads (21,545) are near-equally split by volume, suggesting pricing power on spot remains strong
  • Revenue shows no sustained decline over 3 years, a sign of stable demand and consistent customer relationships
2

Segment Profitability

Route performance and customer revenue analysis
Segment Profitability Dashboard

Key KPIs

58 Total Routes $2.20 Avg Rev/Mile PA-NY Best Lane NV-NY Worst Lane

Visualizations

  • Side-by-side horizontal bar charts showing Top 10 Performing Routes and Top 10 Underperforming Routes by revenue per mile
  • Top 10 Customers by Revenue and Top 10 Customers by Load Count — two separate rankings revealing that high-frequency shippers are not always the highest-revenue accounts

Key Insights

  • PA-NY leads all lanes at $2.78/mile — 26% above the fleet average of $2.20/mile — a lane worth protecting and potentially expanding capacity on
  • NV-NY sits at the bottom at $1.53/mile — 30% below average — a strong candidate for rate renegotiation or load optimization
  • First Group leads customers by revenue at $9.14M and load count at 2,116 — suggesting First groups ships more frequently at high per-load rates
3

Fleet Utilization

Asset performance, idle analysis, and trailer breakdown
Fleet Utilization Dashboard

Key KPIs

120 Trucks $21.85M Fleet Revenue $79.16K Avg Rev/Truck 23% Idle Rate 37K Avg Miles

Visualizations

  • Active vs Idle donut — 77% active, 23% idle — 28 trucks generating zero revenue at any given time
  • Revenue Per Truck and Miles Per Truck bar charts — identifies top and bottom performing assets
  • Revenue & Trip by Trailer Type donut — Dry Van leads at $133.83M across 43,526 trips; Refrigerated at $123.50M across 40,204 trips
  • Truck Age vs Maintenance Cost scatter — older trucks cluster at higher maintenance cost, quantifying the aging fleet risk

Key Insights

  • 23% idle rate means roughly 28 trucks are not generating revenue — at the fleet average of $79.16K per truck, recovering even half of that idle capacity adds ~$1.1M annually
  • Dry Van and Refrigerated trailers handle nearly 99% of trip volume — the "Others" category ($5.20M, 1,680 trips) may represent specialty underutilized assets
  • Truck age shows a clear positive correlation with maintenance cost — a data-backed case for fleet renewal planning
4

Driver Performance

Revenue, safety, MPG, and on-time rate per driver
Driver Performance Dashboard Driver Performance Scoreboard

Key KPIs

124 Active Drivers out of 150 6,084 Incidents 7% Incident Rate 6.50 Avg MPG 44.60% On-Time Rate

Visualizations

  • Performance Tier donut: 124 drivers segmented into Top Performer (42), Mid Performer (41), and Low Performer (41) tiers — near-even distribution across tiers
  • Top 10 Drivers by Revenue — horizontal bar showing the top revenue contributors, led by Linda Davis at ~$2.31M each
  • MPG vs Incident Rate scatter plot — all 124 drivers plotted, revealing whether fuel-efficient drivers correlate with safer driving behavior
  • Revenue vs On-Time Rate scatter — surfaces whether high-revenue drivers are also reliable on delivery schedules

Key Insights

  • Top performers (42 drivers) are generating disproportionate revenue — investing in this cohort through incentives and preferred route allocation would maximize fleet ROI
  • 44.60% average on-time rate is critically low — industry standard is 95%+ — signaling systemic issues with scheduling, routing, or driver planning
  • The Driver Score Board (separate sheet) provides full individual-level drill-down across revenue, MPG, on-time rate, hire date, and incident rate for all 124 drivers
5

Fuel & Maintenance

Cost control, efficiency trends, and downtime analysis
Fuel and Maintenance Dashboard Fuel Cost by Location

Key KPIs

$95.59M Fuel Cost $0.68 Fuel/Mile $5.73M Maintenance Cost 3,010 Downtime Days

Visualizations

  • Fuel vs Maintenance Cost by Month dual-line chart — both cost lines show volatility, with fuel peaking at $2.90M in individual months
  • US state-level filled map showing Fuel Cost per Location — Texas ($7.64M) and Tennessee ($7.62M) are the highest-cost fuel states
  • Top 10 Most Expensive Trucks to Maintain — TRK00003 leads at $90.16K total maintenance spend
  • Maintenance Cost by Type bar chart — Preventive ($963K), Repair ($945K), Tire ($919K), Brake ($912K), Engine ($891K), Transmission ($880K) — a remarkably even distribution

Key Insights

  • Fuel at $95.59M represents 94.3% of total operating costs — making fuel efficiency the single highest-leverage cost reduction target in the entire business
  • Texas and Tennessee fuel spend likely correlate with high trip density through those states — route optimization through cheaper-fuel corridors could yield meaningful savings
  • 3,010 downtime days over 3 years equates to roughly 8.2 truck-years of lost capacity — a quantifiable cost of deferred maintenance decisions
6

Safety Metrics

Incident analysis, compliance, and risk by route and driver
Safety Metrics Dashboard

Key KPIs

170 Total Incidents $2.65M Incident Cost 37.65% Preventable Rate 64 Preventable

Visualizations

  • Incident Cost Trend line chart — monthly incident costs are highly volatile, spiking to $176.5K in one month — identifying high-risk periods for targeted intervention
  • Incident by Type bar — DOT Violations (39) and Equipment Damage (35) lead, followed by Accidents (35) and Customer Complaints (34)
  • Top 10 Dangerous Routes — OH-OR leads with 10 incidents; WA-TX and MN-GA at 6 each — geographic safety hotspots that demand operational review
  • Preventable Flag donut — 64 preventable vs 106 unpreventable — 37.65% of incidents were avoidable with better training or protocols

Key Insights

  • DOT Violations (39) are the most frequent incident type — a direct regulatory and insurance risk that targeted compliance training could address immediately
  • $2.65M in incident costs over 3 years averages $883K/year — preventable incidents at 37.65% represent ~$332K/year in avoidable cost
  • OH-OR route with 10 incidents warrants an immediate operational audit — whether it's the terrain, schedule pressure, or specific drivers on that lane
7

Seasonal Patterns

Demand trends, quarterly splits, and route seasonality
Seasonal Patterns Dashboard

Key KPIs

August 2022 Peak February 2023 Slowest $7.72M Peak Revenue $6.57M Off-Peak 2,373 Avg Monthly Loads

Visualizations

  • Monthly Load Volume vs Revenue dual-axis line chart — 3-year view showing the relationship between volume fluctuations and revenue response
  • Booking Type Quarterly Load Split table — Contract, Dedicated, and Spot loads are remarkably stable across all four quarters, suggesting demand is relatively non-seasonal
  • Route Demand Heatmap by quarter — color-encoded table showing which lanes experience highest demand in which quarters

Key Insights

  • Demand is more stable than seasonal — the $1.15M gap between peak and off-peak revenue is relatively modest for a $262M/year operation, suggesting strong contract-based demand smoothing
  • Booking type splits are near-identical across all four quarters — Dedicated loads maintain ~10,500 per quarter regardless of season — a highly predictable revenue base
  • February consistently underperforms — capacity and cost management during this window (reduced headcount, deferred non-critical maintenance) could improve off-peak margin
Key Findings

What the Data Actually Says

Six findings that cut through the noise and tell the real story of this freight operation.

Revenue Health Is Strong — But Margin Needs Work

Total revenue of $262.53M against $101.23M in operating costs produces a strong headline. However, the 44.60% on-time delivery rate suggests significant service-level risk — customers experiencing late deliveries may negotiate lower rates or leave, threatening future revenue without immediately showing in cost metrics.

38.56% Operating Ratio — best-in-class performance

Route Profitability Has a Wide Spread

The revenue per mile spread between the best lane (PA-NY at $2.78) and worst lane (NV-NY at $1.53) is 82% — meaning the worst lane generates almost half the revenue per mile of the best. With 58 routes in the network, standardizing lane pricing to even approach the median would materially improve total revenue.

$1.25 Revenue per mile gap between best and worst lane

Fuel Is the Dominant Cost Variable

At $95.59M, fuel represents 94.3% of total operating cost — dwarfing maintenance at $5.73M. This concentration means fleet-wide MPG improvement, smarter fueling location selection, and route optimization against fuel-cost corridors are far more impactful than any maintenance efficiency program.

$0.68 Average fuel cost per mile — industry benchmark is $0.55–$0.70

23% Fleet Idle Rate Is a Hidden Revenue Leak

With 28 trucks sitting idle at any given time and an average revenue per truck of $79.16K, the theoretical revenue opportunity from recovering idle capacity is over $2.2M — without hiring a single new driver or acquiring a single new truck. Idle reduction is the fastest path to revenue growth in this fleet.

28 Idle trucks at any given time — recoverable capacity

Driver Tier Concentration Drives Revenue Risk

With 124 drivers split near-evenly across three performance tiers, one-third of the driver base (the low performer tier) is generating significantly below-average revenue. The top 10 drivers alone contribute an estimated $23M+ — roughly 8.7% of total revenue from fewer than 10% of drivers. This concentration creates operational fragility.

$2.31M Revenue from top individual driver — Linda Davis

37.65% of Incidents Were Preventable

64 of 170 incidents over 3 years were classified as preventable — meaning they could have been avoided with better training, protocol adherence, or vehicle maintenance. At the average incident cost of $15.6K per preventable incident, eliminating them entirely saves roughly $332K per year in damage, legal, and compliance costs.

$332K Annual savings from eliminating preventable incidents
Business Impact

How These Insights Move the Needle

Here is how each finding translates into concrete operational and financial outcomes.

Revenue Growth Opportunities

  • Repricing underperforming lanes (bottom 10 routes) to the fleet average revenue per mile of $2.20 could add an estimated $8–12M in annual revenue from existing loads
  • Recovering 50% of idle fleet capacity (14 trucks) at average revenue per truck adds ~$1.1M without capital expenditure
  • Shifting volume toward high-revenue lanes like PA-NY ($2.78/mile) through customer incentives or broker negotiations increases per-mile yield
  • Top customer concentration analysis enables targeted upselling of Dedicated contracts to the high-frequency, lower-rate accounts like First Logistics

Cost Reduction Targets

  • A 5% improvement in fleet-wide MPG (from 6.50 to 6.83) would reduce fuel consumption and save approximately $4.8M annually at current fuel prices
  • Eliminating preventable incidents saves $332K/year in damage and compliance costs with no capital investment — only training
  • Proactive retirement of the top 10 highest-maintenance trucks (averaging $75K+ in maintenance spend each) reduces unplanned downtime and frees budget for fleet renewal
  • Optimizing fueling locations away from Texas and Tennessee high-cost states where operationally feasible could reduce per-gallon costs across high-frequency routes

Delivery Performance

  • Addressing the 44.60% on-time rate is the single highest-urgency operational priority — below 50% is a customer retention risk for all contract customers
  • Identifying the specific routes and drivers with the lowest on-time rates allows targeted scheduling intervention rather than fleet-wide policy changes
  • February historically underperforms — pre-positioning drivers and trucks in high-demand corridors before Q1 begins could lift the off-peak on-time rate measurably
  • Reducing 3,010 downtime days through predictive maintenance scheduling would directly add available trucks back to service during peak demand periods

Fleet Optimization

  • The Truck Age vs Maintenance Cost scatter plot provides a data-driven retirement schedule — trucks above the cost inflection point are candidates for immediate replacement
  • Trailer type analysis shows Dry Van and Refrigerated trailers handle 99%+ of volume — the "Others" category represents potential asset reallocation or disposal candidates
  • Monthly fleet utilization trends by route reveal which lanes consistently require more trucks — informing targeted capacity deployment rather than blanket fleet expansion
  • Driver-truck pairing optimization using MPG data can reduce fuel spend by assigning the most fuel-efficient drivers to the highest-mileage routes
Strategic Recommendations

What Should the Company Do Next?

Six data-backed strategic decisions, each with a clear verdict and rationale drawn directly from the analysis.

✗ Not Yet

Should They Acquire More Trucks?

No — not before addressing the 23% idle rate. With 28 trucks already generating zero revenue, adding trucks without solving dispatch inefficiency and route coverage gaps would only increase fixed costs. The priority is maximizing utilization of the existing 120-truck fleet first. Revisit acquisition when idle rate drops below 10%.

✗ Not Yet

Should They Hire More Drivers?

No — not before addressing the on-time rate. The 44.60% on-time rate is not a capacity problem; it is a scheduling, routing, or driver quality problem. Adding drivers to a broken process amplifies the problem. The focus should be upskilling the Low Performer tier (41 drivers) and addressing the root causes of late deliveries before expanding headcount.

✓ Immediate Priority

Should They Optimize Routes?

Yes — this is the highest-ROI initiative available. The $1.25/mile gap between the best and worst lanes, combined with the geographic concentration of high-cost fueling states, means route optimization delivers both revenue uplift (better lane pricing) and cost reduction (lower fuel spend) simultaneously. Start with the bottom 10 underperforming lanes.

✓ Immediate Priority

Should They Reduce Idle Time?

Yes — this is effectively free revenue. The 28 idle trucks represent over $2.2M in recoverable revenue capacity with no additional capital expenditure. Implementing dynamic load matching, reducing empty mile runs, and tightening dispatch scheduling are all operational levers that pay back immediately.

✓ High Impact

Should They Invest in Safety Training?

Yes — 37.65% preventable incident rate is both a cost problem ($332K/year) and a regulatory risk. DOT Violations are the most frequent incident type, which carries insurance premium implications beyond direct damage costs. A targeted compliance and defensive driving program for the highest-risk driver cohort has a highly favorable cost-benefit ratio.

◎ Plan Within 12 Months

Should They Renew the Aging Fleet?

Yes — but on a data-driven schedule. The Truck Age vs Maintenance Cost scatter identifies specific trucks above the cost inflection point. Rather than a blanket fleet renewal, a targeted retirement of the top 10–15 highest-maintenance trucks in the next 12–18 months reduces unplanned downtime, lowers per-mile maintenance cost, and improves driver satisfaction on newer equipment.

Forward-Looking Analysis

What the Trends Suggest About 2025 and Beyond

Based on 3 years of observed patterns, here is what the data signals about where this business is heading.

Revenue Will Remain Stable With Modest Growth

The near-flat monthly revenue trend across 2022–2024 — oscillating between $6.57M and $7.72M — suggests a mature, stable demand base. Without new lane expansion or customer acquisition, 2025 revenue is projected to remain in the $7.0–7.5M/month range. Meaningful growth requires either new customer wins or rate increases on existing lanes.

Dedicated Contract Volume Will Continue Dominating

Dedicated loads have maintained ~10,500 per quarter across all four quarters for 3 years with zero seasonal degradation. This segment will continue to anchor revenue. The strategic play is converting high-frequency spot customers (like First Logistics at 2,116 loads) to dedicated contracts — locking in volume and improving planning certainty.

Maintenance Costs Will Rise if Deferred

The truck age distribution indicates a meaningful portion of the fleet is entering the 7–11 year range — historically the zone where maintenance cost acceleration begins. Without a structured retirement and renewal plan, maintenance spend (currently $5.73M) could increase 20–35% over the next 3 years, compressing margins.

Fuel Cost Volatility Remains the Largest Earnings Risk

Fuel prices showed significant month-to-month volatility throughout the analysis period. With fuel at 94.3% of operating costs, a 10% fuel price increase translates to approximately $9.6M in additional annual cost. Fuel hedging or surcharge pass-through mechanisms with customers deserve serious consideration as a risk management strategy.

⚠ Key Risk Areas to Monitor in 2025

  • On-time rate deterioration below 40% would signal systemic scheduling failure and could trigger customer contract reviews — monitor monthly and set a 45% floor as an early warning trigger
  • DOT Violation frequency is the most actionable safety risk — a spike in violations creates regulatory scrutiny that can ground trucks and interrupt revenue operations with no warning
  • Driver attrition in the Top Performer tier (42 drivers generating disproportionate revenue) is the highest-impact human capital risk — a 10% departure rate from this cohort could represent $20M+ in revenue exposure
  • OH-OR route with 10 incidents needs immediate operational review — if the root cause is structural (terrain, schedule pressure), continuing operations on this lane without intervention compounds both safety and insurance risk
  • Fleet idle rate trending upward above 25% would indicate a capacity-demand imbalance that requires either load acquisition or fleet rightsizing before it permanently erodes asset ROI

Growth Projections

$275–285M
Projected 2025 Revenue (conservative, no new customers)
$8–12M
Revenue uplift potential from route optimization alone
$6–8M
Fuel savings potential from 5–7% MPG fleet improvement
View the Work

Explore the Full Project

From the raw MySQL schema to the interactive Tableau dashboards — every layer of this project is available to explore. Connect with me if you want to discuss the methodology, the findings, or how this kind of analysis applies to your operation.

View Interactive Tableau Dashboard View other Logistics Dashboard Project Made with Excel Visit My Portfolio