Data Cleaning for Commercial Fleet Maintenance Databases: Vehicle Asset Record Reconciliation, Maintenance History Normalization, Parts Inventory Data Deduplication, Vendor and Supplier Contact Verification, DOT Compliance Record Maintenance and Warranty Tracking Data Cleanup
On this page
The Maintenance System Where Every Technician Spells Things Differently
Fleet maintenance management systems -- Fleetio, RTA Fleet Management, Dossier, AssetWorks FleetFocus, Chevin FleetWave, Whip Around, or custom-built systems -- accumulate data quality problems at a rate proportional to the number of people entering data and the length of time the system has been in use. A fleet maintenance database that has been collecting repair orders for ten years from a dozen technicians at three shop locations contains thousands of maintenance records with inconsistent descriptions of the same repair.
One technician writes "replaced alternator." Another writes "alt replacement." A third writes "R&R alternator 12V 160A." A fourth writes "electrical - charging system repair." All four describe the same work, but the database treats them as four different types of repairs, making it impossible to query the system for a simple question like "how many alternators have we replaced across the fleet in the last two years?" Without that query capability, the fleet manager cannot identify vehicles with chronic alternator failures, cannot negotiate volume pricing with the alternator supplier, and cannot evaluate whether a different alternator brand or specification would reduce failure rates.
The same inconsistency problem applies to part numbers. The alternator in the example above might appear in the parts inventory under the OEM part number (from the vehicle manufacturer's parts catalog), the remanufacturer's part number, the aftermarket cross-reference number (from the parts supplier's catalog), and an internal stock number assigned by the fleet's parts room. The same physical part occupies four inventory records, and the fleet management system shows four different parts with four different quantities on hand, four different reorder points, and four different purchase histories.
Vehicle Asset Record Reconciliation
The fleet management system's vehicle asset records -- the master list of every vehicle in the fleet, with its VIN, unit number, year, make, model, assigned location, status, and maintenance schedule -- drift from reality as vehicles are acquired, sold, transferred, totaled, and retired. A vehicle that was sold at auction but not removed from the fleet management system continues to generate scheduled maintenance reminders, distorts fleet utilization and cost-per-mile calculations, and counts as an active asset in fleet size reporting. A vehicle that was transferred from the east region to the west region without a system update shows as available in the east (where it is not) and does not appear in the west (where it is).
Reconciliation requires comparing the fleet management system's active vehicle list against the actual fleet. The actual fleet is documented through vehicle registration records (DMV registration renewals confirm which vehicles are currently registered to the company), insurance policy vehicle schedules (the insurance carrier's list of covered vehicles reflects what the company told them it operates), fuel card transaction records (vehicles that have not had a fuel transaction in 90+ days are likely no longer in active service), and GPS/telematics system active device lists (vehicles with active telematics devices are currently in the fleet; vehicles whose devices have been deactivated may have been disposed of).
Export the fleet management system's active vehicle list and the comparison data sources. Upload to Email Extractor to extract VIN numbers and unit identifiers from each data source for cross-referencing. Vehicles that appear in the fleet management system but not in registration, insurance, or telematics records are candidates for removal from the active fleet list. Vehicles that appear in registration, insurance, or telematics records but not in the fleet management system are candidates for addition.
Maintenance History Normalization
Normalizing maintenance history requires establishing a standard repair coding system -- a controlled vocabulary of repair descriptions, failure codes, and component identifiers that every technician uses when entering repair orders. The industry standard is the VMRS (Vehicle Maintenance Reporting Standards) coding system developed by the American Trucking Associations' Technology and Maintenance Council (TMC). VMRS provides a hierarchical coding structure with system codes (e.g., 013 for cooling system), assembly codes (e.g., 013-001 for radiator), component codes (e.g., 013-001-001 for radiator core), and reason-for-repair codes (e.g., scheduled maintenance, breakdown, accident damage, campaign/recall).
Retroactively coding ten years of free-text maintenance descriptions into VMRS codes is a significant data cleanup effort, but it is necessary to make the maintenance history usable for analysis. The process involves exporting the full repair order history, categorizing each repair description into its VMRS system, assembly, and component codes, standardizing part numbers to a single primary identifier (typically the OEM part number, with cross-references to aftermarket and distributor numbers), and reimporting the standardized data.
Export the repair order history from the fleet management system. The export includes repair order number, vehicle unit number, date, mileage/hours, repair description, technician, parts used (with part numbers and quantities), and labor hours. Upload to Email Extractor to extract vendor and supplier email addresses that appear in repair order records (for parts ordered, outside repairs, and warranty claims). This extraction identifies the full universe of vendors the fleet has done business with, including vendors whose contact information may have changed since the repair was performed.
DOT Compliance Record Maintenance
Fleets operating commercial motor vehicles (CMVs) under FMCSA jurisdiction must maintain specific records that are subject to audit. Driver qualification (DQ) files must contain the driver's application, driving record (MVR), road test certificate or equivalent, medical certificate, and annual review of driving record. Annual vehicle inspection records (per FMCSA 396.17) must document the inspection date, inspector, vehicle identification, and each component inspected with its pass/fail status. Drug and alcohol testing records must document pre-employment, random, post-accident, reasonable suspicion, return-to-duty, and follow-up testing for every CDL driver.
These records often exist in a mix of paper files, scanned documents, fleet management system entries, and third-party service provider databases (the drug testing consortium, the MVR provider, the medical examiner). When FMCSA conducts a compliance review, the fleet must produce these records in an organized, complete format. Missing or expired records result in violations that affect the fleet's safety rating and can result in out-of-service orders.
Export driver qualification records, vehicle inspection records, and testing records from all sources. Upload to Email Extractor to extract driver, inspector, medical examiner, and testing provider email addresses for contact verification. Drivers whose medical certificates are approaching expiration need to be notified. Inspectors whose certifications have lapsed need to be replaced. Testing providers whose contact information has changed need to be updated in the fleet's records.
