From Quarterly Excel to Per-Shipment API: A 3PL Migration Story

I run the API platform at EcoFreight (background on my author page). Most of my week is spent looking at request logs, tuning the calculation engine, and answering integration questions from teams who are ripping a quarterly Excel workflow out of their reporting stack. This post is about one of those migrations — the same one we wrote up as a structured case study with all the metrics in one table.

The customer is a European 3PL. They run roughly 12,000 shipments a month across road, ocean, and a small bit of air. Their sustainability lead had been producing a quarterly CSRD-aligned emissions report in Excel — pulling shipment exports from their TMS, mapping modes to DEFRA 2019 factors in a lookup tab, and shipping a PDF to the auditor about ten weeks after quarter-end. They went live on our API at the start of Q1 2026. We have the numbers from the first full quarter of operation and I want to walk through what actually changed, what did not, and where the migration still hurts. If you want to read the same story through the lens of competing vendors, I covered that in the API comparison post.

Before and after, in real numbers

Their old workflow had a clear shape. One person, two to five working days at the end of every quarter, producing a single CO₂e total split by mode. The data was eight to ten weeks stale by the time anyone saw it. Lane-level breakdowns existed in theory but took another half-day to produce on request, so nobody asked.

The new workflow runs at shipment creation inside their TMS. A small middleware service listens for "shipment booked" events, builds the request payload, calls POST /api/v1/calculate against the schema documented at /docs, and writes the returned calculation_id and co2e_kg back onto the shipment record. The sustainability dashboard reads from that same column. The quarterly report is now a SQL aggregation over the table — about 30 minutes of work, almost all of it spent double-checking the cut-off date and signing the PDF. Their full request and response pair lives in the engine walkthrough if you want to see the field-by-field shape.

The latency numbers are not impressive in isolation — they are impressive because the alternative was ten weeks. A shipment booked at 14:02 has an emission number at 14:02 and change. The freight planner can see it before they confirm the carrier.

The API call, in full

People ask me what a real per-shipment request looks like. Here is one from a Munich to Milan articulated truck run, pulled straight out of our staging logs and lightly redacted. The customer's middleware sends this on every shipment-booked event — the request schema and the GLEC Framework v3.2 factor selection rules are public:

POST /api/v1/calculate
Content-Type: application/json
X-Request-Id: 0c4f9a2e-9b3a-4a55-b80f-1f1e3b6cf7b1

{
  "mode": "road",
  "vehicle_class": "articulated_truck_gt32t",
  "origin":      { "lat": 48.1351, "lon": 11.5820 },  // Munich
  "destination": { "lat": 45.4642, "lon":  9.1900 },  // Milan
  "cargo_mass_tonnes": 12.0,
  "load_factor": 0.78,
  "scope": "WTW",
  "factor_set": "glec_v3_2"
}

And the response:

200 OK
Content-Type: application/json

{
  "calculation_id": "calc_2RkX8b3qLnPv9Yt",
  "request_id":     "0c4f9a2e-9b3a-4a55-b80f-1f1e3b6cf7b1",
  "factor_set":     "glec_v3_2",
  "scope":          "WTW",
  "distance_km":    490.3,
  "cargo_mass_tonnes": 12.0,
  "emission_factor_g_per_tkm": 62.0,
  "co2e_kg": 364.78,
  "split_kg": { "ttw": 286.6, "wtt": 78.2 },
  "data_quality_tier": 1,
  "computed_at_utc": "2026-05-22T08:14:02Z",
  "engine_version": "calc-engine@4.7.1"
}

Two fields deserve a note. The calculation_id is stable across the lifetime of a calculation — if you ask the auditor in 2028 how a 2026 number was produced, the ID dereferences to the exact inputs, factor set, and engine version that produced it. The engine code that ran the calculation is pinned in engine_version. When we ship a new GLEC factor revision, old IDs continue to resolve against the old engine. No silent recalculation.

The request_id is the customer's tracer. If something looks wrong, that ID is the single thing I need to find the request in our logs and tell them what happened. Every TMS or ERP integration I have built — Blue Yonder via their event bus, SAP TM through an SAP-PI wrapper, Oracle TMS via REST adapters — gets a strong recommendation to persist this on the shipment record. I cannot help you without it.

The reconciliation problem

Here is the part nobody warns you about. When you have ten weeks of API-calculated per-shipment numbers and ten weeks of Excel-calculated quarterly numbers, they do not match. Not even close, the first time.

In this customer's case the API total was about 8.4% lower than the Excel total for the same quarter. The sustainability lead's first instinct was that the API was wrong. It was not. We sat down with their Excel workbook for a morning and found four causes, roughly in order of size:

• The Excel used DEFRA 2019 factors for road; we use GLEC v3.2. DEFRA 2019 runs hotter on articulated trucks. That alone was about 5% of the gap.
• The Excel rounded distances to the nearest 50 km off a static lane-distance lookup table. We use the actual routed great-circle for ocean and the routed road distance from the geocoder. About 2% of the gap, mostly on short hauls.
• The Excel had ghost shipments. A handful of cancelled bookings made it into the export but not into the API stream — the API only sees confirmed events. Less than 1%.
• The Excel had load-factor assumptions baked into its factors implicitly. Ours are explicit in the request. The customer was assuming 65% load factor in their Excel; their actual telemetry says 78%. Better utilisation, lower per-tonne-km emissions.

That last one is the interesting one. The Excel number was not wrong because of bad math. It was wrong because the assumptions had drifted four years from reality and nobody had revisited them. The API call forces those assumptions to be explicit on every shipment. That is half the value, frankly. The other half is the dashboard.

When you should not switch

I get asked to scope this kind of migration about once a week. About one in four times, my honest answer is: do not bother yet. The signals that point to "stay in Excel":

• Under ~200 shipments a month. The integration cost outweighs the reporting cost. A spreadsheet maintained by one person, refreshed monthly, is fine.
• Single-mode, single-lane fleets. If you run twelve trucks on three routes, your emission factor varies by less than 5% across the fleet. A static lookup is genuinely accurate enough.
• No live TMS. If your shipments live in email and PDFs, the missing piece is the TMS, not the emissions layer. Solve that first.
• Reporting-only, no operational use case. If the number only ever shows up in an annual PDF, real-time gives you nothing. CSRD and SEC compliance will run fine on quarterly data, as will most Scope 3 Category 4 CDP submissions.

Quarterly emissions reports are a compliance exercise. Per-shipment data is an operational tool. Don't let a sustainability team buy you the first when you needed the second.

What I would build next

The 3PL migration shipped what we had. Here is what is open on my roadmap that would have made it smoother — in rough priority order:

• A reconciliation endpoint. Feed it a CSV of legacy Excel totals; it returns a per-row diff against the API numbers with attribution to the four causes above. Right now I do this by hand in a Jupyter notebook.
• Webhook delivery of recalcs. If GLEC v3.3 lands and we re-run a customer's last 24 months, they need a push, not a polling job. We do polling today.
• Tier 2 factor support, ergonomically. When a carrier finally hands over actual fuel-consumption data, plugging it into the request should be a single additional field. The schema supports it; the docs do not yet explain it well.
• SDK clients for SAP TM and Blue Yonder. The integrations are straightforward but every customer rebuilds them. A thin client per platform would shave a week off each migration.

None of these are blockers. They are the difference between a migration that takes four weeks and one that takes one.