From unstructured JSON to a single-view shipment dashboard
A cargo SaaS platform held everything about a shipment and could show none of it in one place — an analyst answered each question by hand, query by query. We built the transformation and a single-view dashboard covering flights, events, SLAs and shipment search, ready to surface to their own customers.
1 analyst
of manual query work turned into a self-serve view
3
data streams unified into one shipment view
₹0
in new software licences or per-seat fees
Product-ready
built to plug in and surface to their own customers
The challenge
Shipment data arriving as raw, unstructured records that no one could read at a glance. The client runs a SaaS platform for the cross-border air-cargo supply chain, and during product testing they needed to see what their own system held. The data came as nested JSON — flight movements, handling events and consignment detail in one payload, with fields that appeared, disappeared or changed shape depending on the source and the leg. Nothing about it was tabular. Answering an ordinary question like "where is this shipment, how long has each step taken, and did we hit the SLA?" meant a full-time analyst writing a query against the raw data, one question at a time. That worked while the questions were rare. It did not scale to product testing, and it could never be shown to a customer.
Our approach
We started on the raw payloads rather than with a platform proposal. The transformation was the real work: a rules-based clean-up that handles the conditional shape of the data, resolves fields that arrive under different names on different legs, and stitches a consignment back together across every stream it touches — including part loads, where one shipment is split and moves on more than one flight. The clean output lands in Google Sheets, and a Looker Studio dashboard reads it as a single view: flights, events, and a shipment search, with time taken at each step, quality metrics, and SLA monitoring, all filterable. The client runs the transformation on their own data. Because the logic is separate from where the output lands, the same mapping can sit behind an API and surface to their end customers as a product feature.
Why they called us
The client was mid-way through product testing. Their platform tracks cross-border air cargo, and it already captured a great deal about every consignment — flight movements, handling events, status changes, timings at each step. On paper, they had the data.
In practice they could not look at it. Anything anyone wanted to know went to an analyst, who wrote a query against the raw payloads and came back with an answer. One question, one query. That is a workable arrangement when questions are occasional and internal. It falls apart during product testing, when the whole point is to interrogate the data repeatedly and from different angles — and it is not something you can ever put in front of a customer.
So the brief was not “build us a dashboard.” It was closer to: the information exists; make it usable without a person in the loop.
What we found in the data
We began with the raw payloads and a working session, not a proposal deck. Three things came out of it, and they set the shape of everything after.
It was not tabular, and pretending otherwise would fail. The data arrived as nested JSON. Structure varied by source and by leg — fields present in one payload absent in the next, the same concept named differently depending on which system in the chain emitted the update, values arriving in more than one format. There was no clean row to read. Any tool pointed at the raw data naively would have produced confident, wrong numbers.
A shipment is not one record. This was the finding that mattered most. A single consignment leaves a trail across several streams — its own detail, the flights it rides, the events logged against it — and those do not line up neatly. Part loads make it harder: a shipment split across more than one flight generates parallel trails that both belong to the same consignment and have to be reassembled to see the real journey. Get that stitching wrong and every downstream number — transit time, step duration, SLA compliance — is wrong with it.
The interesting questions were about time, not counts. How long did a consignment sit between arrival and handover? Which step is consistently slow? Which shipments breached the SLA and where did the delay actually occur? None of that is visible in a payload. All of it is derivable once the streams are stitched and the timestamps are trustworthy.
That third point reframed the job. The deliverable was not a set of charts. It was a clean, correctly-joined, time-aware model of a shipment, with charts as the visible surface.
What we built
Two parts: a transformation, and a single-view dashboard on top of it.
The transformation
This is where the effort went, and it is the part that lasts. The script takes raw payloads and:
- Flattens the nested JSON into consistent tables, applying conditional rules for the structural variation rather than assuming one shape.
- Harmonises field names, formats and timestamps so a concept has one name regardless of which system in the chain produced it.
- Stitches each consignment back together across flights, events and shipment detail — including part loads, so a split shipment reads as one journey rather than two unrelated ones.
- Derives the things nobody could see before: elapsed time at each step, quality flags where records are incomplete or contradictory, and SLA status against expected durations.
- Writes clean tables the dashboard reads directly.
Steps 3 and 4 are the ones that turn data into answers. Everything before them is plumbing.
The single view
The dashboard is built in Looker Studio and does what the analyst was doing, without the analyst:
- Flights — movements, schedules and actuals, with delays visible against plan.
- Events — the handling trail for a consignment, in order, with the time between each step shown rather than left to be worked out.
- Shipment search — enter an air waybill and see its whole journey in one screen, part loads included.
- Quality and SLA monitoring — which records are incomplete or internally contradictory, which shipments breached, and at which step the time actually went.
- Filtering across the parameters people reach for in testing: route, carrier, date range, status, service level.
The shift is from ask someone and wait to open it and look. That is the entire value of the engagement, and it is why the transformation mattered more than the visuals.
Why Sheets underneath and Looker Studio on top
Two layers doing two different jobs, deliberately kept apart.
Sheets is the data layer because that is where the client already worked. The cleaned tables sit in a tab anyone can sort, filter and copy from. When a tester wants to check one odd consignment against the raw payload, they do it directly — no request, no waiting.
Looker Studio is the viewing layer because a spreadsheet is a poor place to read hundreds of shipments at once. It gives filters and pivots that survive being shared with people who should not be editing the data underneath, at no per-seat cost — which matters when the people looking at a feed during testing are not the same three people each week.
We said the ceiling out loud at the start: at much larger volumes, or with many concurrent viewers, the data layer has to move to a proper warehouse. Looker Studio would then point at that instead and the dashboard itself would barely change. That was the right trade for the job in front of them, and we built for the exit rather than pretending it wasn’t coming.
Built as a product capability, not a report
This is the part that outlived the engagement. The transformation logic is independent of where its output lands — today, Sheets. Point it at an API instead and the same stitching, derived timings and SLA logic can surface inside the client’s own platform, to their customers, as a feature they sell.
That was not an accident of good design; it was the intent from the first session. A client asking for an internal dashboard during product testing is usually one step away from wanting the same view in their product, and hard-coding the output into a spreadsheet throws that away. Keeping the layers separate cost very little during the build and saved the entire rewrite later.
What we deliberately didn’t do
We did not build a live integration into their platform. They were validating a product feed, not operating a reporting business, and a live pipe would have added scope, review cycles and a dependency on their release schedule — to answer a question they wanted answered that month.
We also did not propose a retainer to run it. The transformation and the dashboard were handed over in full, and the client runs their own refreshes. That is less recurring revenue for us, and it is the right call: a tool the client cannot run without calling us is not a handover.
What we’d tell a similar business
Three things carry over to almost any company sitting on data it cannot read.
If a person is answering your questions one query at a time, that is a modelling problem, not a staffing one. Hiring a second analyst doubles the throughput of a bottleneck instead of removing it. Modelling the data once removes it.
Get the joins right before you get the charts right. In any operation where one real-world thing — a shipment, an order, a job — leaves traces in several systems, the hard part is reassembling it correctly. Part loads, split orders and partial deliveries are where this breaks. Every metric you build sits on top of that join, so it is the last place to be approximate.
Measure duration, not just volume. Counts are easy to produce and rarely the thing anyone acts on. Time between steps is what exposes where the process actually fails, and it is what an SLA is written against.
If you are sitting on data only one person can answer questions from, that is the work we do — see analytics and dashboards, or tell us what your data looks like.
What this means for your business
If your business is facing similar challenges — whether it's shipment data arriving as raw, unstructured records that no one could read at a glance — the same approach can work for you.
