How to Prepare Freight and 3PL Data for an Audit

Executive Summary

Freight audits fail before they start when the data arrives as disconnected PDFs and a rate card nobody has updated. The fix is not more analysis, it is preparation: a full 12 to 18 month invoice pull, the actual current carrier agreements including fuel surcharge tables, and a shipment-level file that can be joined to both. Get those three things into a common format and the audit itself becomes mechanical.

Rate cards and fuel tables get negotiated once, and invoices then get paid against whatever the carrier's system prints, with no independent check that the printed rate matches the signed one. Preparing the data is what makes that check possible at all.

What changes the outcome is joining invoice line items to shipment weight, mode, and lane at the same grain the contract was written at. Get that join wrong and every downstream finding is wrong with it.

1. What data does a freight and 3PL audit actually require?

A freight audit needs three linked datasets: carrier invoices at the line-item level for 12 to 18 months, the current signed rate agreements including fuel surcharge and accessorial tables, and a shipment record with origin, destination, weight, mode, and carrier for each move. Without all three joined together, an invoice can be read but not verified against what was actually contracted and shipped.

The invoice file is the starting point, but it answers only one question: what did the carrier bill. It cannot tell you whether that bill was correct. For that you need the rate agreement itself, not a summary of it, because a summary is where the sunset date on a fuel table can go missing.

The shipment record is the piece teams skip. It is also the one that makes the other two usable. A rate card states a price per hundredweight for a given lane and weight break. Without a shipment record showing the actual weight and lane, there is no way to know which line of the rate card should have applied.

Pull all three at the line-item grain, not the summary grain. A monthly carrier statement showing a total is not auditable. The individual invoice lines, each tied to a shipment number, are.

• Carrier invoices: Line-item detail, 12 to 18 months, including every accessorial charge as its own line rather than a bundled total.
• Rate agreements: The signed base rate tables, fuel surcharge schedules, and accessorial price lists currently in force, with effective dates.
• Shipment records: Origin, destination, weight, mode, and carrier for each shipment number referenced on an invoice.
• Amendment history: Any rate changes, addenda, or renewal letters issued after the base agreement, since these override the original terms.

2. How far back should the invoice pull go?

Pull 12 to 18 months of freight invoices. That window is long enough to capture a full seasonal cycle and at least one fuel surcharge table rollover, and it is what ValueXPA diagnostics use as the standard lookback. A shorter window risks missing a rate that drifted gradually after a single missed update, and a longer window adds volume without adding much diagnostic value once the rate structure has already turned over.

Fuel indices move month to month, and a surcharge table that was correct at one reading can be stale later if nobody re-publishes it against the updated index. A 12 to 18 month window is long enough to catch that kind of slow drift, where a single outdated table quietly overcharges every shipment until someone notices.

Going back further than 18 months tends to pull in a prior carrier agreement entirely, which complicates the match without adding much: rate terms already renegotiated are not something a current control can fix.

If your fuel surcharge is indexed to a published benchmark, keep the read date next to any figure you cite from it. A surcharge table that has not been updated against a moving index is worth checking first, and the carrier's own published tariff or the relevant government price index are the sources to check it against.

3. How should freight invoice data be structured before matching?

Structure each invoice as one row per charge line, not one row per invoice: base freight, fuel surcharge, and each accessorial get their own row, tagged with the shipment number, carrier, and charge type. That grain is what lets a matching process test the base rate against the rate card and the fuel surcharge against the surcharge table independently, instead of testing a bundled total against nothing in particular.

A common failure at this stage is working from an invoice PDF or a carrier portal export that presents a single line total per shipment. That format hides exactly the information an audit needs: whether the fuel surcharge was calculated on the correct index period, whether an accessorial was even contracted for, and whether the base rate matches the weight break actually shipped.

Assign a consistent charge-type taxonomy across carriers before you start. One carrier's invoice may label a charge "residential delivery" and another "non-commercial address," but if both map to the same accessorial in your structured data, they can be tested against the same contract clause.

Keep the shipment number as the join key throughout. It is the field that reliably ties an invoice line back to the weight, lane, and mode recorded in the shipment system, which is what n-way invoice matching depends on to work at the line-item level rather than the invoice-total level.

A. Charge-type taxonomy

Build one list of charge types (base rate, fuel surcharge, residential delivery, liftgate, detention, redelivery) and map every carrier's own labels onto it before any comparison begins. Without this step, the same accessorial charged by two carriers looks like two different findings instead of one control gap.

B. Join keys

Shipment number is the primary key between invoice and shipment record. Where a carrier does not return it cleanly, fall back to a composite key of ship date, origin zip, destination zip, and weight, accepting that the composite key produces more ambiguous matches than a clean shipment number.

4. What should the rate card and fuel surcharge file contain?

The rate card file needs the base rate table by lane and weight break, the fuel surcharge schedule with its indexing formula and reset frequency, and every accessorial with its price and the conditions under which it applies. Each entry needs an effective date and, where superseded, an end date, because an invoice has to be tested against the table that was actually in force on the ship date.

Carrier rate agreements are frequently signed as a base document with a fuel surcharge addendum that gets updated separately, sometimes annually, sometimes on a rolling formula tied to a diesel index. Both pieces have to be captured, and captured with their effective dates, because a surcharge tested against the wrong period's table will look wrong even when it was billed correctly.

Where your own fuel surcharge is formula-driven, the formula itself needs to be recorded, not just the current output. A formula reading as an indexed weekly adjustment is testable. A remembered percentage is not.

Accessorial definitions deserve the same treatment as rates: many disputes come down to a condition, a liftgate fee that only applies below a certain dock height, a detention charge that only starts after a stated free time, rather than the price itself. Capture the condition, not just the number.

A. Effective dating

Record a start date and, once superseded, an end date for every rate, surcharge, and accessorial entry. This is the same discipline described in surcharge sunset dating as a control, and it applies to the source file just as much as to the enforcement process built on top of it.

5. How do you reconcile carrier invoice data with shipment records?

Join invoice lines to shipment records on shipment number where available, and on a composite of ship date, origin, destination, and weight where it is not. Flag any invoice line that cannot be matched to a shipment record at all: an unmatched line means either the shipment data is incomplete or the invoice references a shipment that was never authorized.

This step surfaces two different problems that look similar until you separate them. The first is a data quality gap: a shipment record exists but a field is missing or misspelled, and the match can be repaired manually. The second is a genuine control gap: no shipment record exists at all for a billed shipment, which raises a question about authorization that data cleanup cannot answer.

Keep a running log of unmatched lines rather than discarding them once matched manually, since a manually resolved match still indicates a data feed that is not capturing what it should. If the same field is missing across dozens of shipments from one carrier, raise that with the carrier or the TMS administrator independent of the audit itself.

Weight and mode deserve particular attention in this reconciliation, since both directly determine which rate card line applies. A shipment billed at a truckload rate but recorded in the shipment system as an LTL move is either a data error or a rate applied to the wrong service level, and either way it needs resolution before the invoice can be tested.

• Match on shipment number: Join every invoice line to its shipment record using the shipment number where the carrier provides one cleanly.
• Fall back to composite key: Where shipment number is missing, join on ship date, origin, destination, and weight, accepting a higher rate of manual review.
• Log unmatched lines: Keep every unmatched invoice line in a visible queue rather than dropping it, and record why it failed to match.
• Check weight and mode: Confirm the billed service level matches the recorded mode before testing the rate, since a mode mismatch invalidates the rate lookup entirely.

6. What data quality checks should run before the audit starts?

Before matching begins, check for duplicate invoice numbers, blank or zero weights, missing carrier codes, and accessorial charges with no corresponding contract line at all. Each of these breaks the audit differently: a duplicate invoice number risks double-counting a finding, while an accessorial with no contract line means the audit needs a decision on whether the charge was even authorized before it can be tested for accuracy.

Duplicate invoice numbers are the simplest check and the most consequential to skip. If a carrier resubmits an invoice under the same number after a correction, and both versions land in your pull, an unadjusted count shows the charge twice.

Blank or zero weight fields make a rate lookup meaningless, since rate cards are structured by weight break. Flag these for correction against the shipping system rather than guessing a weight from the charge amount, which reverses the logic the audit exists to check.

An accessorial charge with no matching line anywhere in the rate agreement is a different kind of finding than a miscalculated one. It does not necessarily mean the charge is wrong, but it does mean nobody has confirmed it is right, which is a governance gap distinct from a pricing error and worth tracking separately once the audit moves into review.

A. Duplicate and orphan checks

Run a duplicate check on invoice number plus amount before any other analysis, and a separate check for invoice lines referencing a carrier code not present in your rate agreement file. Both are quick checks that prevent hours of downstream rework.

7. Who should own the freight data pull inside the company?

AP or accounting typically holds the invoice history, transportation or logistics holds the shipment records and carrier relationships, and procurement or the contract owner holds the signed rate agreements. A freight data pull needs coordinated access to all three, and naming one internal owner to assemble the joined file prevents the audit from stalling on whoever is slowest to respond.

These three functions rarely report to the same person, and the systems they use rarely talk to each other directly. AP's ERP holds the paid invoice history. The transportation management system holds shipment-level detail. The signed rate agreements often live in a shared drive or with whoever negotiated them, not in either system.

Name one internal coordinator before the pull starts, with authority to chase all three sources on a timeline. Without that, each function tends to assume another has already sent the file, and the pull stalls on nothing more than unclear ownership.

Where the diagnostic runs as a bounded, fixed-scope engagement, this coordination step is part of what determines whether it lands inside the stated 2 to 4 week window across ValueXPA diagnostics: the analysis itself moves quickly once the data exists in a matched, structured form.

For the wider pattern this sits inside, start with the margin drift guide.

For the wider pattern this sits inside, start with the margin drift guide.

8. Frequently Asked Questions (People Also Ask)

What file format should the freight invoice pull be delivered in?

A flat file such as CSV or Excel, with one row per charge line rather than one row per invoice, is easiest to work with. Whatever format the source system exports, it needs to preserve the shipment number, carrier, charge type, and amount as separate fields rather than a single combined description.

Do we need the original signed contract or is a rate summary enough?

The signed agreement itself, including any fuel surcharge addendum and accessorial schedule. A summary can omit an effective date, a superseded clause, or a condition attached to a fee, and any of those gaps will make a correct invoice look wrong or a wrong one look correct.

What if our carrier does not provide shipment numbers on invoices?

Fall back to a composite key of ship date, origin, destination, and weight to join the invoice line to the shipment record. It produces a higher rate of ambiguous matches than a clean shipment number, so budget extra time for manual review on that carrier's invoices.

Can this data pull be done inside our ERP without exporting anything?

ERPs typically hold the invoice history but not the shipment-level detail or the current signed rate agreements, which usually live in a transportation management system and with whoever negotiated the contract. An export that joins all three is generally still necessary.

How do we handle carriers who changed their invoice format mid-period?

Map both formats to the same charge-type taxonomy before combining them into one file. Treat the format change itself as a data point worth noting, since it can coincide with a change in how a fee is calculated or labeled.

Should freight class or NMFC codes be part of the shipment record?

Yes, where the mode is LTL. Freight class affects which rate applies, and a class mismatch between what was quoted and what was billed is a separate check from a weight or lane mismatch.

What counts as an accessorial charge for this purpose?

Any charge beyond the base linehaul rate and fuel surcharge: liftgate, residential delivery, detention, redelivery, inside pickup, and similar fees. Each should be its own row in the structured file, tagged to the contract clause that authorizes it.

How much of this can be automated versus done manually?

The join itself, matching invoice lines to shipment records and rate tables, can be automated once the source files are structured consistently. Assembling those source files from disconnected systems and confirming which rate version was in force on a given date typically still needs manual review.

Do we need data from every carrier or just the largest ones?

Every carrier billed against a contract should be included, since a smaller carrier's rate card can be just as out of date as a larger one's. Scope by contract coverage, not by invoice volume alone.

What happens if the shipment record and invoice disagree on weight?

Flag it before testing the rate, since the rate lookup depends on weight break. Confirm which figure is correct against the bill of lading or scale ticket before deciding whether the invoice was overbilled.