Transit agencies are spending more on data than ever, and most of them still see their network in two halves. One half lives in the planning vendor's tools: stops, routes, schedules, blocks. The other half lives in the real-time tracking vendor's tools: vehicle positions, journey detection, on-time performance. The questions that drive operating decisions — whether the network being run matches the network that was designed, and where the two diverge between those two halves.
A recent joint analysis between Optibus and Mosaiq, drawing on data from Mosaiq’s Global Public Transit Index (GPTI) combined with Optibus planning, showed what becomes possible once that join is in place.
This article goes a layer deeper: what it actually takes, from a data engineering perspective, to make the join credible. We’ve been doing this work for some time. Both the methodology and what it surfaces in real cities are worth sharing.
Public transportation data sits in two architectural tiers. The planning layer describes the network as designed: every stop, every route variant, every scheduled trip, every block of work assigned to a vehicle and driver. It's the system of record for what's supposed to happen. The execution layer describes what the buses actually did - vehicle positions arriving in seconds, journey detection inferring which scheduled trip a vehicle is serving, on-time performance calculated against the plan but generated from independent observation.
These layers are almost always built by different vendors, owned by different agency teams, and refreshed on different cadences. Each one is engineered, in isolation, to be coherent. The trouble starts when you ask them to be coherent with each other. The cost of that incoherence is operational: decisions that would benefit from comparing plan against execution get made on one side of the data only.
The intuitive picture of joining planning and execution data is a key match: each trip in the plan carries a trip ID, each tracked journey carries that same ID, you join on it and the rest is dashboard styling. In our experience that picture is wrong roughly nine times out of ten.
Planning data is dense at the stop level; execution data is dense at the trip and journey level. You can't join them at one grain without aggregating, and you can't aggregate without committing to a question. The choice of grain decides what operational questions you can ask, and which ones you've quietly closed off.
Names of cities, operators, depots, and routes drift across systems. "City of Bolton" in one source becomes "Bolton MBC" in another. A new operator wins a contract and the same physical route gets re-keyed in one system but not the other. The most expensive single component of the join, measured in hours of work, is the reconciliation table that maps every operator and locality across both sources, kept current as the underlying systems change. This is unglamorous work. It is also what quietly determines whether your dashboards can be trusted.
Planning data updates on schedule cycles - weeks, sometimes months. Execution data updates by the second. When the time windows don't match, the join produces answers that look fine and aren't. The cost shows up later, in decisions that didn't land.
None of this shows up in a dashboard. All of it shows up in the trust placed in the dashboard.
Once the identity layer is in place, the same network looks different. As a worked example, consider three of the UK bus networks analysed based on the Mosaiq Global Public Transit Index (GPTI) data.
Bolton has 1,518 stops in its planning footprint and recorded 66,623 trips in the comparison period, a trips-per-stop ratio of roughly 44. The planning side is dense, and the execution side reflects that density. With the join in place, operators can ask schedule-versus-actual questions at stop grain across the whole network. That is a different conversation from the one a single-vendor dashboard supports.
Sheffield has a similar shape: 1,390 stops, 78,954 trips, ratio of roughly 57. The join produces stop-level performance questions that mirror the network's own planning resolution.
Bournemouth is the case that makes the methodology point. 33 stops in the planning footprint, 64,877 trips in the execution data, a ratio of 1,966. That number alone doesn't say much. Depending on what's happening on the ground, it could indicate a trunk-corridor design with high frequency on few stops, a demand-responsive layer running through limited formal stops, or a planning footprint that under-represents the operational reality.
The join surfaces the discrepancy. It doesn't resolve it.
The ambiguity is where the join earns its keep. Cross-source integration is good at flagging the cases where a single-source view would have given a confident-but-wrong answer. The work that follows - investigation, conversation with operations teams, sometimes re-modelling the planning footprint - is where the operating decision actually gets made.
Transit dashboards are usually shaped by their data source. If you have a planning vendor, you see your network through planning. If you have a performance vendor, you see it through execution. Most agencies have both, and most still see them in parallel rather than joined.
The lift required to bring those views together is not glamorous. It's an identity table, a
join-grain decision, and a time-alignment rule. None of that work is publishable on its own. All of it is required to ask the question planning and operations teams have been asking forever: does the network we designed match the network we're running?
The data engineering disciplines required for this kind of join are not transit-specific. Careful grain modelling, identity reconciliation, time alignment - they're how any organisation that runs on data builds credibility across multiple sources of truth. What is specific to transit is the partnership model underneath. When planning and performance vendors design their outputs with reconciliation in mind, and when their data engineering teams treat the join as a shared interface rather than a downstream problem, the work of bridging them becomes lower-friction by an order of magnitude. The Optibus-Mosaiq analysis is one example of that partnership maturing. There will be more.
For agency leaders, the takeaway is short. Cross-source identity reconciliation is
infrastructure, not a project. A bridge metric like trips-per-stop is one starting question; there are others. The metric matters less than the underlying capability to ask questions that span both layers. Agencies that invest in that capability close the planning-to-performance loop. The ones that don't keep running two halves of one network in parallel.
The interesting work in transit data increasingly happens between systems, not inside them. As planning and performance vendors mature their own products, the marginal insight comes from what happens when their outputs meet. The continuous improvement cycle - plan, operate, observe, adjust - is real, and the joint Optibus-Mosaiq analysis showed what that cycle looks like when it's closed. This article has been about the engineering underneath it: the data work that doesn't usually get written up, but that most cycles depend on.
About the authors:
Barak Ben Kimhy is the Head of Data Operations at Optibus. He leads the data architecture and analytics layer supporting ARR reporting, pipeline analytics, and cross-source business intelligence across the Salesforce, Snowflake, and ThoughtSpot stack, and is an early adopter of embedding machine learning and predictive analytics into production data pipelines.
Bosmat Levi-Hevroni is a Data Architect at Optibus. She owns the Finance ARR cycle, pipeline reporting, and the data accuracy layer between Salesforce, Snowflake, and ThoughtSpot.
Further Reading: