From Job Sheets to Cost per Service: Data for Managing a Fleet

A fleet leaves behind two accounts of the same working day. One is the job sheet: what the driver or technician writes at the end of the shift, with their jobs, their incidents and their handwriting. The other is the one the vehicle leaves without anyone writing it: positions, stops, kilometres, engine speed, driving times. Both describe the same thing and they almost never match, because one reports what was done and the other records what happened. Building BI for industrial fleets is, to a large extent, about building the model where those two accounts meet and deciding, case by case, what to do when they disagree. This article describes that model as we have built it for a company in the industrial sector and in food distribution: the entities it is made of, what the vehicle reports, what has to be estimated, how a stop is matched to a customer and which inconsistencies always turn up.

The job sheet and the telemetry tell the same day in two different ways

The job sheet is a statement of intent. It records what was planned and what the person remembers when closing the day, grouped however suits them: sometimes one line per customer, sometimes one per type of job, sometimes a single line for the whole day. It is valuable because it contains what no sensor knows — what was done, with what materials, why it took longer — and at the same time it is the least structured source in the entire system.

Telemetry is the opposite: fully structured and entirely silent about purpose. It knows the vehicle was stationary for twenty minutes at a given point, but not whether that was a delivery, a breakdown or a coffee. That is why neither source works on its own. A job sheet without telemetry cannot be verified; telemetry without a job sheet cannot be interpreted.

The management decision this enables comes before any KPI: with both sources joined up, operations stops arguing about whether a job was done and can start discussing what it cost. They are different conversations, and only the second one goes anywhere.

The service as the unit of the model

The model is built around one central entity, the service: a piece of work carried out for a customer at a location and at a point in time. Everything else hangs off it. The service is linked to the vehicle and the person who carried it out, to the service point where it took place, to the period it belongs to and to the documents that support it.

Four blocks sit around that entity:

• Job sheets. What is declared: services, times, incidents and materials used, normalised to one line per service even if it arrives grouped differently.
• Telemetry. What the vehicle records: journeys, stops, kilometres, engine running time and idling time.
• Maintenance. Preventive and corrective work per vehicle, with its cost and its downtime — which is almost always more expensive than the repair itself.
• Costs. Fuel, tyres, insurance, depreciation and staff, allocated by vehicle and period so they can then be spread across the services that vehicle carried out.

Defining the service as the smallest unit is what turns the question "how much does it cost to serve this customer?" into something with an answer rather than a debate. Without that unit, costs can only be viewed by vehicle or by month — precisely the level at which nothing concrete can be decided.

What the vehicle reports: CAN bus and tachograph

Telemetry is captured with Movildata, and two of its sources deserve a separate mention because they are what turn a tracking system into a management system.

The CAN bus is the vehicle's internal network, carrying the data its control units exchange with one another. Reading it gives you figures from the vehicle itself rather than inferred from GPS: actual odometer kilometres, engine speed, running and idling times. The difference from calculating these from positions is not trivial: a GPS-estimated journey can come up short in areas with poor coverage, and idling simply cannot be seen from outside, because a stationary vehicle with the engine running and one with the engine off occupy exactly the same position.

The tachograph adds the legal dimension of time: driving, breaks and rest periods per driver. Integrating it is not just a compliance matter; it is what allows you to distinguish a stop that is part of the job from one that is part of a mandatory rest, and therefore not count it as unproductive time. Without that distinction, any shift-utilisation KPI penalises drivers for obeying the law.

Estimated consumption when the vehicle does not report litres

Not every vehicle reports fuel consumption. When it does not, the alternative is not to give up on the figure but to estimate it — and say clearly that it is an estimate. The calculation relies on two figures that are available: the distance travelled as reported by the CAN bus, and idling hours, which burn fuel without moving the vehicle.

The result is an estimated consumption per kilometre per vehicle, comparable across vehicles and periods. That comparability is what makes it useful: it lets you rank the fleet, spot a vehicle drifting away from its own usual values and flag routes with a lot of stationary engine time. What it does not allow is reconciling a refuelling receipt or replacing direct measurement, and that is how it is presented in the dashboard, labelled as an estimate. Publishing an estimate without saying so discredits the whole system the first time someone checks it against a fuel receipt.

Matching stops to customers

This is the hard part. The vehicle produces stops; the business needs customer visits. Turning one into the other looks like a distance problem, and it is not.

The model separates two concepts that are usually mixed up in the ERP master data: the customer, who signs the contract and gets invoiced, and the service point, the physical place where the work is done. A customer can have several points. One point can correspond to several customers, as happens on an industrial estate, in a food market or in a shopping centre where several businesses share a loading bay. With that separation, a stop is matched first to a point, which is a geographical question, and the customer is resolved afterwards by cross-referencing the documentation for the period, which is a business question.

Matching to a point requires two conditions at once: being within a radius of the known location, and staying there for a minimum time. The radius cannot be the same for the whole fleet — an industrial loading bay is not a narrow street — and is usually set by point type. For operations, the result is being able to answer with hard data how many times a customer was actually visited in a month, which is the basis for any review of a route or a tariff.

Cross-referencing ERP delivery notes

The third source is the ERP. Delivery notes provide what neither the telemetry nor the job sheet contains: what was delivered or collected, in what quantity and at what value. Cross-referencing the three sets — matched stop, job sheet and delivery note — produces a table of reconciled services and, more usefully, three exception lists: visits without a delivery note, delivery notes without a visit, and declared services with no trace in either of the other two sources.

Those lists are not a reproach to anyone. They are almost always broken processes: a delivery note entered the following day, a delivery documented against head office instead of the branch, a service carried out by a vehicle that was not carrying its usual equipment that day. What matters is that the lists exist and that someone reviews them on a fixed schedule. This kind of integration with the management system is what we describe in ERP integration with dashboards (in Spanish), and when the delivery note arrives on paper or as a PDF, it is read by document AI applications rather than by a person.

The inconsistencies that always turn up

There is a set of problems that recurs in every fleet and is best dealt with explicitly, because left untreated they contaminate every indicator built on top:

• Stops lasting seconds that look like visits. A traffic light or a give-way junction next to a customer's gate generates a stop inside the radius. These are discarded by minimum dwell time and by recurrence: a point the vehicle drives past almost every day is not a destination.
• Customers with several addresses. Solved by separating customer from service point, and by keeping a master list of points as data in its own right rather than deriving it from the billing address.
• Visits without a delivery note. These may be undocumented deliveries, unbilled services, legitimate operational stops or delivery notes assigned to another customer. The system classifies them separately and does not decide which they are: that is for operations.
• Badly geocoded addresses. An address the geocoder places in the centre of the town turns every stop in the area into a visit to that customer. Points are validated against the actual stops observed and corrected once.
• Overlaps between vehicles. Two vehicles at the same point at the same time usually indicate either a service duplicated on the job sheet or undeclared assistance; either way, it is worth seeing.

Dealing with these five issues is what separates a pretty map from a control system. The decision it enables is a concrete one: reviewing a route or a tariff with a list of verified facts in hand, instead of a vague impression that a particular customer "creates a lot of work".

Dashboards, alerts and cost per service

With the model reconciled, the Power BI dashboards are built on an analytical data store kept separate from the operational systems, with incremental, hourly loads. It is not real time, and it is worth being clear about that: telemetry arrives continuously from the vehicle, but its consolidation with job sheets, delivery notes and costs runs every hour. This is the layer we explain in data warehouse and dashboards.

Two different things sit on top. Dashboards answer stable questions: cost per service, per customer and per point; shift utilisation net of tachograph rest periods; estimated consumption per vehicle against its own history; maintenance and downtime cost per unit. Alerts do the opposite: rather than waiting for someone to ask, they flag when something breaks its pattern — a vehicle whose estimated consumption drifts, a point that has gone too long without a visit, a declared service with no supporting document.

That is where our work ends: cost per service calculated on reconciled facts, and deviations flagged in time. What comes next — which routes to redesign, which tariffs to review, which vehicle no longer pays its way — is a management conversation, and our colleagues at CAIC cover it from that side in their article on cost control and profitability per service (in Spanish).