Join the fuel and tracking exports through a vehicle crosswalk, keep the original timestamps, and compare distance across the same two confirmed fill events. Calculate fuel consumption across a full-to-full interval. Investigate disagreement between the records before interpreting a change in consumption.
Build a vehicle crosswalk before joining exports
Create a reference sheet with one row per effective assignment. Include fleet ID, registration, fuel-card ID or masked reference, tracker asset ID, and the dates when the assignment started and ended. The same registration can appear with different spacing in a fuel export and a driver app. A tracker or card can also be moved to another vehicle, which makes a lookup based on today's assignment misleading.
Import the fuel and trip exports into separate raw tabs. Keep each source row and its original identifier. In working tabs, standardise registrations, convert timestamps to SAST, and keep the card transaction time alongside the statement posting date. Use transaction time to compare activity with a trip. Use posting date to reconcile the statement period.
Calculate consumption between confirmed full tanks
- Find a starting fill with a confirmed full tank and a credible odometer. Record its timestamp and odometer as the start of the interval.
- Accumulate all litres dispensed after the starting fill, including partial fills and the next confirmed full fill.
- Subtract the starting odometer from the ending odometer. Sum the tracker distance for that vehicle across the same window, taking care with trips that overlap either boundary.
- Calculate L/100 km = litres dispensed ÷ kilometres travelled × 100. Use the verified odometer distance as the denominator and record the tracker comparison alongside it.
Worked example for one bakkie
Vehicle V-06 starts full at 105,000 km. The next receipt shows a 25 L top-up at 105,280 km. The following 55 L receipt brings the vehicle back to full at 105,710 km. The fuel dispensed after the first fill totals 80 L. The vehicle travelled 710 km, giving 11.27 L/100 km. The tracker export totals 702 km for the same period, a difference of 8 km, about 1.1% of the odometer distance.
Keep the 8 km as a separately named distance discrepancy. Whether it warrants a review depends on the vehicle, the tracker configuration and the completeness of the export. A 25 L receipt divided by the first 280 km would show 8.93 L/100 km for that partial fill, even though the tank ended that leg at an unknown level.
Investigate a mismatch in this order
- Check the join. Compare the mapped tracker and fuel-card IDs with the assignments valid on the transaction date. Confirm the receipt vehicle if a card serves several assets.
- Check the window. Compare the export time zone, date filters and trips that cross midnight or the beginning and end of the fill interval.
- Check distance coverage. Look for missing trips, telemetry gaps and tracker replacement events. Compare a documented odometer photo or signed vehicle log against the export before correcting an entry.
- Check the fuel. Match each receipt to its fuel row, confirm the fuel product, exclude refunds from quantities only after matching the original receipt, and flag intervals with missing fills.
Record a decision for the weekly fleet review
Use one row per interval. Record vehicle, start and end transaction IDs, both odometers, litres, tracker kilometres, coverage status, difference, the evidence needed and a review owner. Mark unclear intervals as awaiting evidence. Use confirmed, comparable intervals in your fleet average and report the kilometres covered so the team can see how representative that figure is.
Start with one vehicle and one complete interval. The Basic Fuel Record workbook gives you a place to log receipts and odometers. For a backward jump, offset or repeated reading, follow the odometer discrepancy guide. The sample Fleet Exception Review shows how to hand the evidence and next check to an owner.