How to Build a Logistics Reconciliation Tool Without Code

What was scheduled, what was delivered, and what was invoiced rarely tell the same story. The gaps between those three records are where money disappears - double billing, missed deliveries that still got invoiced, and partial shipments that no one followed up on. This tutorial builds a tool that lines those records up, flags the gaps, and gives you a queue to work through them.

Before you start

You need a Creatr account and a working understanding of where your three sources of truth live today. That might be a spreadsheet, a TMS, a carrier portal, or a mix of all three. You do not need to clean or format anything before you begin - just know where the data is. Connecting QuickBooks for invoice data and Google Calendar for scheduled runs will save significant manual work later; have those credentials ready if you want to wire them in during the build.

This tutorial is intermediate difficulty. You will be making judgment calls about matching rules and exception thresholds, so spend ten minutes writing down the reconciliation headaches that cost you the most time before you start.

Step 1 - Describe the three sources of truth

The most common mistake is asking for "a reconciliation tool" without explaining what you are reconciling. Creatr needs to know what your three record types look like and how they relate before it can build anything useful.

Write down the shape of each record as it actually exists in your operation. A scheduled run might have a carrier, a route, a pickup window, and an expected delivery date. A delivery confirmation might have a proof-of-delivery timestamp, an actual weight, and a note field. An invoice line item might have a reference number, a rate, an accessorial charge, and a payment status. The fields do not need to match across the three - that mismatch is the whole problem you are solving.

Then describe it to Creatr:

Build a logistics reconciliation tool for a regional freight operation. I have three types of records: scheduled shipments (carrier, origin, destination, promised delivery date, quoted rate), delivery confirmations (actual delivery date, delivered weight, proof-of-delivery reference, carrier code), and invoice line items from carriers (invoice number, invoice date, carrier, billed rate, billed weight, reference number). I want to match these three records together and flag anything that does not line up.

Step 2 - Model the data

Once Creatr understands the three record types, it will lay out a data model before building anything. Read this step carefully. The model determines what you can query, report on, and flag later - a field you miss here is a blind spot you live with for months.

At this point, tell Creatr about any fields that exist in your real data but were not in your initial description. Common ones that get dropped from the first prompt: accessorial charges (fuel surcharges, detention, re-delivery fees), multi-stop routes where a single truck touches several delivery records, and split invoices where one invoice covers multiple shipments.

Also add an accessorial charges field to invoice line items - it will hold a list of charge types and amounts. Add a route ID field to scheduled shipments that can link to multiple delivery confirmations when a truck makes several stops on a single run.

Creatr will update the model and show it back to you. Approve it before moving on.

Step 3 - Import or connect your sources

With the model in place, bring your data in. You have two paths: a one-time import from files, or a live connection to the systems that generate the records.

For imports, Creatr accepts CSV and will map columns to your fields with a short prompt:

Import this CSV of carrier invoices from the last 90 days. The column "REF#" maps to reference number, "INV DATE" maps to invoice date, "GROSS" maps to billed rate, and "FUEL" maps to the fuel surcharge entry in accessorial charges.

For live connections, wire them in now so the tool stays current without manual exports. If your invoices come through QuickBooks, connect it and tell Creatr which vendor transactions to pull. If your dispatch schedule lives in Google Calendar, connect it and describe which calendar and which event fields map to your shipment fields.

Connect QuickBooks and pull freight vendor invoices from the last 60 days. Vendor names that start with "Carrier -" are the relevant ones. Map the memo field to reference number and the amount to billed rate.

Connect Google Calendar, use the "Dispatch" calendar, and pull events from the last 60 days. Event title maps to carrier name, the description field contains origin and destination separated by a pipe character, and the start time maps to promised delivery date.

Do not worry about perfect coverage on the first import. Gaps in historical data are expected. The goal is enough records to test the matching logic in the next step.

Step 4 - Build the matching rules

This is the core of the tool. A matching rule is a set of conditions that tells the system two records from different sources belong to the same shipment.

The strongest match is usually a shared reference number - your carrier assigns a PRO number or BOL number that appears on both the delivery confirmation and the invoice. If you have that, start there:

Match a scheduled shipment, a delivery confirmation, and an invoice line item together when the carrier code matches across all three and the reference number on the delivery confirmation matches the reference number on the invoice. If there is no reference number match, fall back to matching on carrier code plus a delivery date within 2 days of the promised delivery date.

Your fallback rule matters. Reference numbers get mistyped, omitted, or reformatted by carriers. A date-window fallback catches most of those cases while keeping false positives low. Two days is a reasonable starting point - adjust it based on how much your carriers' delivery timing actually varies.

Tell Creatr what a matched group should look like in the interface:

When all three record types are matched, show them as a single reconciled shipment row. Show the quoted rate, the billed rate, and the difference. Show the promised delivery date and the actual delivery date and the difference in days.

Step 5 - Flag exceptions

Matching creates the paired groups. Exception rules define what counts as a problem within those groups - and what to do with records that never matched at all.

Start with the exceptions that cost you real money:

Flag a matched shipment as a billing discrepancy if the billed rate is more than 3% above the quoted rate. Flag it as a weight dispute if the billed weight is more than 5% above the delivered weight. Flag it as late delivery if the actual delivery date is more than 1 day after the promised delivery date. Use a different color or label for each exception type so I can filter by type.

Then handle the unmatched records:

Flag any invoice line item that has no matching delivery confirmation after 5 days as a potential ghost invoice - billed but no proof of delivery. Flag any scheduled shipment with a promised delivery date that has passed but no delivery confirmation linked to it as a missing delivery. Show these in a separate section at the top of the tool.

Ghost invoices and missing deliveries are your highest-value exceptions. Treat them as a separate category from rate discrepancies - they need different workflows to resolve.

Step 6 - Build the review queue

Flags are only useful if someone works through them. The review queue is a structured list of exceptions where your team can investigate, add notes, and mark items resolved.

Add a review queue that shows all flagged shipments sorted by exception type, with the highest-value billing discrepancies first within each type. Each row should be expandable to show the full matched records. Add a notes field where the reviewer can log what they found - carrier contacted, dispute filed, approved as correct, etc. Add a status field: Open, In Dispute, Resolved. Let reviewers change the status and filter the queue by status and exception type.

If multiple people handle reconciliation, add role separation:

Add two roles: Reviewer and Approver. Reviewers can add notes and move items to In Dispute. Approvers can mark items Resolved and see a history of who changed what.

Step 7 - Build the leakage dashboard

The review queue handles individual exceptions. The dashboard shows the pattern - which carriers overbill most often, which routes have the worst on-time rate, and how much is sitting in unresolved disputes at any point.

Add a dashboard with: total billed vs. total quoted for the current month with the variance amount and percentage; a bar chart of billing discrepancy count by carrier for the last 90 days; a line chart of on-time delivery rate by week for the last 12 weeks; total value of open disputes; and a count of ghost invoices and missing deliveries by week. Let me filter all of it by date range and carrier.

The dashboard should answer the question your current process cannot: which carrier relationship has the worst billing accuracy, and is it getting better or worse?

Step 8 - Set up notifications

A reconciliation tool that requires you to log in to find problems will get skipped when you are busy. Wire in alerts so the high-priority exceptions reach you without a manual check.

Creatr can send alerts via Gmail. Tell it what should trigger a message and who should receive it:

Send a Gmail alert to ops@example.com when a new ghost invoice is flagged - include the carrier name, the invoice amount, the invoice date, and a link to the record in the tool. Send a daily digest at 8am to the same address with a count of new exceptions by type from the previous day. Do not send the daily digest if there are no new exceptions.

Keep the alert criteria tight. Alerts that fire too often get ignored. Ghost invoices and missing deliveries are worth an immediate alert. Rate discrepancies can wait for the daily digest unless they exceed a threshold you set.

Also send an immediate alert if a single billing discrepancy exceeds $500.

Step 9 - Test with real data

Before you use this in a live month, run it against a closed period where you already know the answers. Pick a month you have already reconciled manually and import those records.

I am loading data from March 2026, which I have already reconciled. Flag everything as you normally would, then let me compare the flags against my known exceptions list.

Work through any flags that your manual process missed - decide whether the tool is being too aggressive or whether it found real leakage you previously overlooked. Work through any exceptions your manual process found that the tool did not flag - this tells you whether your matching rules or fallback windows need adjustment.

Two things to verify specifically: make sure the fallback date-window matching is not creating false positives where different shipments on the same carrier are being joined incorrectly, and make sure the ghost invoice detection threshold is giving the tool enough time for delivery confirmations to arrive before it flags a phantom billing.

When the test results look right, flip to the current period.

Step 10 - Ship and iterate

Deploy the tool and run it live for one full billing cycle before making structural changes. The first real month will surface patterns your test data did not - carrier behaviors, edge cases in your reference number format, accessorial charge types you forgot to include.

Every rough edge is a one-sentence prompt. Add a charge type, tighten a matching rule, change a threshold. The system knows what it built, so adjustments do not break historical data or existing matched records.

Add "Residential Delivery" as an accessorial charge type to invoice line items.

Change the billing discrepancy threshold from 3% to 5% for the carrier "FastFreight Co" - they have a rate adjustment clause in our contract.

Add a filter on the review queue for "Invoice Month" so I can look at one billing cycle at a time.

For teams handling significant freight volume, connect this to the Build an admin dashboard pattern to give leadership a read-only view of the leakage summary without access to the full review queue. If your ops team uses other internal tools, see Build an internal tool for your team for patterns around role structure and shared data.

Recap

You built a tool that pulls scheduled shipments, delivery confirmations, and carrier invoices into one place, matches them by shared identifiers, and surfaces the gaps as actionable exceptions. The dashboard shows where the leakage is concentrated. The review queue gives your team a structured way to work through it. Alerts make sure the high-value problems reach someone immediately rather than waiting for a monthly audit.

The tool did not require a developer. It required knowing what your three record types look like, being specific about your matching logic, and deciding upfront what counts as an exception worth flagging.