Trucking Cost & Revenue Intelligence Dashboard

Problem

Trucking is one of the larger controllable line items in air cargo, but "are we spending well?" is not a question a cost total can answer. A station can be cheap per pound and still be a poor use of the network if the freight it moves is low-value; another can look expensive until you see it's carrying the highest-revenue product on the system. Cost has to be read against the revenue it enables, and at the level where decisions actually get made: the station and the lane.

The monthly reporting that existed answered the total but not the judgment. I wanted a single artifact that a manager could open and, within a minute, see which stations to look at and why.

Approach

The core idea is a two-axis read on every station: cost efficiency on one axis, revenue enablement on the other. Each station is plotted against the system average — cheaper-than-average to the left, higher-value freight toward the top — which turns a flat cost table into four quadrants with different meanings. The upper-left quadrant (low cost, high-value freight) is the goal; the lower-right (costly, low-value) is where the attention goes.

That single view is backed by four more, each answering the next question a reader asks:

• Cost & Revenue — the quadrant scatter plus a sortable per-station detail grid.
• TPI — a Truck Productivity Index (volume per truck, indexed), at both station and lane level, plotted against rate variance so utilization and price are visible together.
• Prior Months — the same system metrics tracked over time, with a library of saved monthly builds you can jump between.
• What-If — a live simulator: adjust trucking volume, rate, or scope with sliders and watch the scenario KPIs and quadrant positions recompute in the browser against a fixed year-to-date baseline.
• Y/Y Changes — the reporting month against the same calendar month a year prior, so seasonality doesn't masquerade as improvement.

Architecture

The whole thing is a Python build step that emits a single self-contained HTML file — no server, no database, nothing to keep running. That was a deliberate constraint: a report that depends on infrastructure is a report that eventually breaks when nobody's watching.

source data (cost + logistics export)
        │
        ▼
   build_dashboard.py
   ├─ load + reconcile (pandas)
   ├─ derive metrics — cost/lb, rev/lb, rev-less-cost, TPI, variances
   ├─ segment stations + lanes into quadrants
   ├─ render figures (Plotly)
   └─ template the page + embed the what-if engine (Jinja2 → HTML/JS)
        │
        ▼
   one portable .html per month  ──►  saved to the month library

Because each month is a frozen file, the historical record is immutable — a past dashboard shows exactly what it showed when it was generated, which is the property internal reporting usually loses first.

Data

The pipeline joins an internal cost export with a logistics/volume export, keyed by station and lane. Getting that join right is most of the real work: reconciling station identifiers between two systems, handling lanes that appear in one export but not the other, and deciding how to treat rows with volume but no matching cost (and the reverse). The sample build spans 137 stations and 4,633 truck movements in the reporting month.

The metrics are all derived at load time rather than precomputed upstream, so the definitions live in one place: cost per pound, revenue per pound, revenue-less-cost per pound, TPI, and each station's variance against the system average.

Results

The reporting month in the sample build covers 372.6M lb moved, $78.6M in trucking spend against $300.0M in trucking-enabled revenue, a blended cost of $0.2110/lb and revenue of $0.8053/lb. But the deliverable isn't the totals — it's that the quadrant view surfaces the handful of stations that are both costly and low-value without anyone reading a 137-row table, and the what-if tab lets someone test "what if we flexed rate here" and see it before proposing it.

The reframing, and why it mattered

The project did not end up answering the question it started with. The original framing was a profitability assessment: cost against margin, station by station. The trucking revenue data isn't segmented cleanly enough to carry that, and forcing it would have produced a confident answer resting on a join the data couldn't support.

So the question changed: not how profitable is this station but how much revenue does this trucking spend enable. That is a different claim with different owners. Revenue enablement is a commercial and network-planning conversation, where profitability is a finance one, and the shift changes which decisions the dashboard can legitimately inform.

What made the shift cheap was that almost none of the work had to be thrown out. The cost derivations, the station and lane joins, the quadrant machinery, and the month-over-month structure were all indifferent to which revenue definition sat on the second axis. Swapping the framing meant changing what the axis measured and what the quadrants were called, not rebuilding the pipeline. That is the practical argument for keeping the analysis simple and the metric definitions in one place: when the business question moves — and in a year where both profitability and revenue generation are under pressure, it does — the analysis moves with it instead of starting over.

What it identifies today

In its current form the analysis surfaces high performers across four cuts: lanes, stations, vendors, and service types. That last cut is the mode and handling profile by which freight actually moves, which is where the revenue differences concentrate.

How it's being used

The output supports vendor negotiations. Trucking companies bid on schedules and lanes on roughly a six-month cycle, so there is a recurring window where vendor performance, the revenue a lane enables, and the gaps worth pressing on all need to be on the table at once. The dashboard is what that conversation is prepared from. The timing matters: in an unfavorable fuel-price environment for aviation, cost pressure raises the value of knowing precisely which lanes and vendors are carrying revenue rather than just volume.

The procurement team is using the dashboard to renegotiate terms on the lanes it flags for investigation. That is the clearest signal the framing shift was the right one: the quadrant view doesn't just describe where cost sits, it produces a specific, defensible list of lanes to take back to a carrier — and that list is what the renegotiation is built on.

I'm not publishing a dollar figure for negotiated outcomes — that's internal. The honest claim is narrower and still worth making: the analysis is in active use in a recurring commercial process, and it replaced a view that couldn't answer the question at the level the decision is made.

What changed in v2

The demo panel above switches between the two builds. v1 is the original sample; v2 is the current build, and the differences are mostly about making the analysis trustworthy over time rather than adding charts.

A persistent actuals store, which fixed a real bug. v1 could only read trucking actuals for months that still had a raw TLM export sitting in the inputs folder. For any month without one — which is every prior-year month — it silently fell back to a different cost column in the source workbook. The comparison still rendered; it was just quietly comparing two different definitions of cost. v2 moves actuals into a centralized persistent store so a month's figures stay correct after its raw export is gone, and the fallback no longer happens without anyone noticing. This is the kind of defect I care most about catching: nothing errors, the output looks fine, and the number is wrong.

Stage length as its own view. Trucking cost per pound is not comparable across trips of different length, so v2 adds a Stage Length tab and bands each movement, which makes a short high-cost haul and a long one legible on the same screen instead of the short one always looking worse.

Fuel-adjusted cost. v2 separates burdened cost from cost excluding fuel surcharge, driven by a fuel-surcharge assumptions input. In a volatile fuel environment, a station can look like it's degrading when the only thing that changed was the surcharge; splitting the two keeps the carrier conversation about the part the carrier actually controls.

Financial region mapping. Stations now roll up to financial regions from a maintained mapping rather than ad-hoc grouping, so the filters match how the business actually reports.

A longer history. The month library grew from four prior months to seven, and both versions let you switch months from the dropdown at the top of the dashboard — the month-over-month view was the point of freezing each build as its own file in the first place.

What I'd do differently

• The what-if engine recomputes in vanilla JS embedded in the page; past a certain complexity that logic wants to be a small typed module with its own tests rather than template-injected script.
• Segment thresholds (what counts as "high value") are currently fixed constants — they'd be better as configuration surfaced in the UI so a reader can move the line and see the quadrants reshuffle.
• The month library is filesystem convention; a light manifest file would make the history self-describing instead of relying on filenames.

Next

• Parameterize the segmentation so the definition of each quadrant is adjustable rather than hard-coded.
• Add a data-quality gate that blocks a build when the two source exports disagree beyond a tolerance, so a bad month can't silently render.
• Factor the shared metric definitions into an importable module the other analytics projects can reuse.