Transport

How to measure on-time pickups when trips have missing timestamps

A dispatcher has five trips and three measured pickups. Work out what the punctuality figure can tell you before changing the rota.

5 min readBy Mayur Kale

A dispatcher asks how often completed pickups occurred within five minutes of the promised time. The transport CSV records five dispatches. Four completed, one was cancelled, and one completed trip has no measured delay. The unit is one dispatch. For this exercise the service day and zone are already assigned and a delay of exactly five minutes qualifies.

The measurable completed trips have delays of two, eight and five minutes. Two of three meet the threshold, giving 66.7% punctuality among measurable completions. Measurement coverage is independently three of four completions, or 75%. Publishing only the first percentage conceals a decision about a missing observation. Across all four completions the true on-time rate could be as low as 50% or as high as 75%, depending on the unknown outcome.

Open the Transport worksheet and enter 9 in the blank delay cell for T5. Coverage becomes complete and punctuality becomes 50%. Clear the cell again and change T4 from 5 to 6. The measured rate falls to one third. These two experiments distinguish missing information from crossing a business threshold. For an operational report I would display the completion count, measured count and threshold alongside the rate, and give dispatch staff a queue for missing events.

For a real service, agree which timezone defines the service day and how a rescheduled promise is versioned. A dataset of actual taxi pickups can demonstrate distance or fare analysis. Measuring punctuality also requires a promised timestamp. Treating pickup time as both the promise and the actual would produce perfect punctuality by construction.

Download the example workbook. The formulas cover the supplied rows. Extend the ranges before adding new records.

Read the rate from the dispatcher's seat

Picture the shift handover. East has two completed pickups. One driver was two minutes behind the promise and another was eight minutes behind. West has a pickup exactly five minutes behind, one cancellation and one completion with no recorded delay. A supervisor deciding where to send a relief vehicle needs to know where a promise was missed and where the team simply lacks evidence. Put the trips on separate lines before drawing a chart. A blank cell beside a completed journey is a question someone can investigate.

A reasonable first report for this shift would say that East met one of two measured promises. West met its only measured promise, with a second completion awaiting a timestamp. Calling West the better performer would be premature. Recovering a single timestamp could bring West level with East. This is especially important when small teams appear in a league table. Put the counts next to the percentages so that a reader can see how much weight to give a one-trip difference.

Agree which promise survives a reschedule

A rider receives an appointment for ten o’clock. At five minutes to ten, dispatch advises that the vehicle will reach them at twenty past. The vehicle arrives at eighteen past. Against the first promise the pickup is eighteen minutes late. Against the revised promise it is two minutes early. A service can have legitimate reasons to measure both. The first measure describes what the customer experienced against the initial commitment. The second helps the controller assess the latest estimate given to the rider.

Store the promises separately and record when each was communicated. An edited appointment field can erase the evidence needed for the first calculation. During a pilot, ask a controller to reconstruct one reschedule from their normal dispatch screen. Find out whether an internal adjustment is visible to the rider. If the revised time was never communicated, describe it as an internal forecast. This distinction is easy to miss when two timestamps appear side by side in a data dictionary.

Also agree what it means to arrive. A vehicle can enter the pickup zone, park outside the door and register boarding at three different times. Pick the event that matches the service commitment. Test it with a real operational case, such as a driver who arrived promptly but waited for assistance before boarding the passenger. The supervisor and the customer service team may need separate measures to explain the same trip.

Work out what a missing observation could change

Take a piece of paper and put the four completed trips into three columns. Two met the threshold, one missed and one has an unknown outcome. Move the unknown trip into the missed column. Now move it into the met column instead. Those two arrangements give the lowest and highest possible rates consistent with the evidence. They require no assumption about why the timestamp is missing. A leader can compare that range with the threshold that would actually trigger an operational change.

Suppose the internal target for this example were 80%. Even the most favourable outcome for the unknown trip yields only 75%. The team already knows the target was missed and can investigate the late pickup in East while recovering the missing event in West. If the target were 60%, the missing timestamp could change the conclusion. Retrieve it before describing the shift as above or below target. The usefulness of extra data depends on the decision it can change.

Look at where the blanks occur over several shifts. A depot with poor mobile reception can appear unusually punctual if the journeys that disappear are the difficult ones. Compare the count of completed dispatches from the controller with the count of measured pickups from the vehicle system. Group the difference by depot and shift. Give someone a short list of trip references to check with the mobile team. A persuasive explanation should predict which trips will be affected on the next run.

Test a week before publishing the route table

Include a journey that crosses midnight, a cancellation that is subsequently reinstated and a pickup exactly on the allowed boundary. For a night route, agree whether the shift belongs to its starting service day or to the calendar date of the pickup. Save the source timestamps with their offsets. Ask the scheduler to check that a service spanning a clock change still appears against the intended shift. A chart can have immaculate daily totals while assigning a late-night journey to the wrong supervisor.

When combining routes, add the measured trip counts and the successful pickups first. For example, a route that meets its only measured promise and one that meets nine of nineteen have ten successes in twenty measurements. The network figure is 50%. Giving each route percentage equal weight would answer a different question about the typical route. The director choosing network capacity and the manager supporting a small route may legitimately want different summaries. Label the denominator in both places.

End the week with an action log. Record which route required intervention, which timestamps were recovered and which dispatch decisions changed. Include the cutoff used for the meeting. If an event arrives after that cutoff, save the revised result alongside the figure the supervisor actually saw. When someone asks why the staffing decision looked reasonable at the time, the original evidence will still be available.

Give customer services an answer they can use

Consider what happens when a passenger calls about a missed pickup. They remember a promised time and a vehicle that arrived late. A network average provides little help. Give the authorised service team a trip view showing the original commitment, any revised time communicated to the passenger, the recorded arrival and whether any of these events is missing. A short reason beside a missing event helps the colleague decide whether to contact dispatch or ask the passenger for more information.

Keep a second list for cancellations. A service could improve measured punctuality by cancelling the trips most at risk of being late. The sample contains one cancellation out of five dispatches. It is excluded from the punctuality calculation under the stated agreement, yet it clearly belongs in a review of whether people received the promised service. Ask the operations manager to look at cancellations and missing measurements whenever punctuality improves. Three small counts on the same page can prevent a confident but mistaken explanation for the movement.

Turn the trend into shipped software

I contract as a senior fullstack and data engineer, building the production systems your customers and your reporting depend on.

Explore services↗