Data Cleaning for Commercial Logistics and Freight Broker Databases: Carrier Contact and Authority Record Reconciliation, Shipper Account and Lane History Deduplication, FMCSA Authority and Insurance Verification, Load Board and TMS Data Normalization, Accessorial Charge and Rate Agreement Standardization, and Carrier Safety and Performance Score Maintenance
On this page
The Broker's Database Is Their Business
A freight brokerage is fundamentally a data business. The brokerage matches shippers who need freight moved with carriers who have available truck capacity. The quality of the match -- the right carrier at the right price at the right time with the right equipment -- depends entirely on the quality of the data in the brokerage's database. A carrier database with outdated phone numbers, expired insurance certificates, revoked operating authorities, and duplicate profiles produces failed loads, compliance violations, and customer churn. A shipper database with fragmented account records, inconsistent lane data, and inaccurate volume history produces bad pricing, misallocated sales effort, and lost accounts.
The typical freight brokerage accumulates data from dozens of sources: carrier onboarding forms (paper and digital), load board carrier profiles (DAT, Truckstop, Convoy, Uber Freight), TMS system records (MercuryGate, TMW, McLeod, Aljex), email correspondence (carrier rate confirmations, shipper rate quotes, POD documents), FMCSA SAFER database queries, insurance certificate tracking systems, carrier safety monitoring platforms (Highway, RMIS, MyCarrierPackets, Carrier411), and manual data entry by brokers and dispatchers during phone calls and load bookings.
Each of these sources produces records in different formats, with different field definitions, different data quality levels, and different update frequencies. Without systematic data cleaning, the brokerage's database degrades over time: carrier records accumulate duplicates (the same carrier registered under slightly different company name spellings), shipper records fragment (the same shipper account split across multiple records because different brokers entered the account separately), and critical compliance data goes stale (insurance certificates expire, FMCSA authorities are revoked, safety scores change).
Carrier Authority and Insurance Verification
The most critical data quality issue in a freight brokerage's carrier database is whether each carrier holds a currently active FMCSA operating authority (MC number for property brokers and carriers, FF number for freight forwarders) and carries the minimum insurance coverage required by federal regulations and the brokerage's own requirements (typically $1 million in auto liability and $100,000 in cargo insurance, though many shippers require higher limits).
The FMCSA's SAFER (Safety and Fitness Electronic Records) system is the authoritative source for carrier operating authority status and insurance filing information. The SAFER system shows whether a carrier's authority is active, inactive, or revoked, the carrier's insurance filing status (whether the carrier has filed the required proof of financial responsibility with the FMCSA), the carrier's safety rating (satisfactory, conditional, or unsatisfactory), and the carrier's inspection and crash data.
Export the carrier database from the TMS or carrier management system. Upload to Email Extractor to extract carrier company names, MC numbers, DOT numbers, and contact email addresses from the export files. Cross-reference extracted MC and DOT numbers against the FMCSA SAFER database to verify that each carrier's authority is currently active. Flag carriers with inactive or revoked authority, carriers with no insurance on file, and carriers with unsatisfactory safety ratings for removal from the approved carrier list.
Shipper Account Deduplication
Shipper account fragmentation is a common problem in freight brokerages that have grown through acquisitions, that have multiple office locations with separate sales teams, or that have experienced high broker turnover. When a new broker joins the company and contacts a shipper that another broker was already working with, the new broker often creates a new shipper account record rather than finding and updating the existing record. Over time, a single shipper customer can have three, five, or ten separate account records in the TMS, each associated with a different broker, a different set of lane history, and different rate agreements.
Account fragmentation causes several business problems: the brokerage cannot see the shipper's total volume and lane mix (because the data is spread across multiple records), rate agreements conflict (different records show different contracted rates for the same lane), multiple brokers contact the same shipper (creating confusion and unprofessional impressions), and revenue attribution is inaccurate (because the shipper's revenue is split across multiple records associated with different brokers).
Deduplication requires exporting the full shipper database and comparing records by company name (using fuzzy matching to identify variations -- "ABC Manufacturing" vs. "ABC Mfg" vs. "ABC Manufacturing Inc."), billing address, ship-from and ship-to locations, and primary contact names and email addresses. Upload the shipper database export to Email Extractor to extract and standardize email addresses and contact names across all shipper records, then use the standardized contact data as a matching key to identify duplicate records that share the same contact person.
Carrier Safety Score Maintenance
A freight brokerage's carrier selection process increasingly incorporates carrier safety data from the FMCSA's Compliance, Safety, Accountability (CSA) program. CSA assigns each carrier scores in seven Behavioral Analysis and Safety Improvement Categories (BASICs): unsafe driving, hours-of-service compliance, driver fitness, controlled substances/alcohol, vehicle maintenance, hazardous materials compliance, and crash indicator. Carriers with high BASIC scores (indicating higher violation rates relative to their peer group) represent elevated risk for the brokerage.
Maintaining current CSA BASIC scores in the carrier database requires periodic data refresh because scores change monthly as new inspection and crash data enters the FMCSA system. A carrier with an acceptable safety profile at the time of onboarding may develop elevated scores over time as inspection violations accumulate. The brokerage that does not regularly refresh its carrier safety data may be dispatching loads with carriers whose safety performance has deteriorated below the brokerage's minimum standards.
Carrier safety monitoring platforms (Highway, RMIS, Carrier411, CarrierWatch) provide automated monitoring that alerts the brokerage when a carrier's authority status, insurance status, or safety scores change. These platforms maintain continuous connections to the FMCSA databases and issue alerts when a monitored carrier's data changes. Integrating a safety monitoring platform with the brokerage's TMS ensures that carrier safety data in the TMS reflects current FMCSA records.
