Vehicle Telematics Analytics: Trips from GPS Logs, Distance & Speed, Idling, Fuel per 100 km, Utilisation & Fleet Exception Reports

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn a raw ping log into information: minutes between pings, Start and End flags where the ignition changes, the number of trips and the minutes the engine was switched on.
• Compute the distance between consecutive pings with the haversine formula, compare it with the flat-grid approximation and add up trip distances from the unit's segment log with SUMIFS.
• Compute the speed of each segment, label it Moving or Stopped against a threshold, and measure the share of time moving and the average speed while moving.
• Apply an idle threshold to each standing episode, add the idle hours per vehicle, price them with fictional idle fuel rates and extrapolate the fleet's idle cost for a year.
• Compute litres per 100 km per van, the fleet median, a pooled figure per route type, and flag the vans whose consumption deviates too far from their own route type.
• Add up the hours each van was used, turn them into utilisation against the available hours, build a weekday × hour heat map of trip starts with COUNTIFS and count the underused vans.
• Label each flagged event with its ISO week, normalise event counts per 100 km for each vehicle and compare two weeks with date-range COUNTIFS.
• Flag each vehicle with an IFS rule set, build a top-5 list of idle share with LARGE and INDEX/MATCH and chart the idle share by vehicle for the monthly fleet review.