SQL Data Mining & Analytics

Data rich, answer poor

Most carriers, brokers and private fleets sit on a remarkable amount of data. Every order, dispatch, check call, trip event, fuel purchase, invoice, payment and driver settlement lives somewhere in a SQL database behind the TMS and the accounting system. Yet when the owner asks a simple question, such as "Which of our top twenty customers are we actually making money on?", the answer takes a week, comes from three spreadsheets, and nobody fully trusts it.

The usual reasons:

• Canned reports don't ask the right questions. The standard reports that ship with the system show activity, not profitability.
• The data is spread out. Revenue is in billing, driver cost is in settlements, fuel is in fuel card imports, and maintenance cost is in another module or another system entirely.
• The data is dirty. The same shipper exists under four names. Locations are keyed inconsistently. Old rate tables were never retired.
• Nobody knows the schema. The tables and fields behind the screens were never documented, and the person who wrote the old queries has moved on.

SQL data mining fixes this by going straight to the source. With the right queries, the data your system has already collected becomes the most honest picture of your business you'll ever get.

The questions we answer

Strong Tower Consulting focuses on the measures that drive margin and cash in freight. Every analysis is built from your own data, not industry averages.

Lane profitability

Revenue and cost by origin and destination pair, by region, and by direction. Which head-haul lanes carry the network, which backhauls barely cover fuel, and which lanes you'd be better off repricing or walking away from. We factor in loaded and empty miles, driver pay, fuel, tolls, accessorials and, where the data supports it, equipment and maintenance cost.

Revenue per mile and cost per mile

By customer, lane, terminal, division, tractor, driver and time period. Revenue per total mile and per loaded mile tell different stories, and we show both. Paired with cost per mile, they reveal where the margin is really earned.

Deadhead and utilization

Empty miles as a share of total miles, by terminal, region, driver and customer. Where trucks wait for freight, where repositioning costs pile up, and how tractor and trailer utilization changes week to week. For private fleets, the same analysis shows backhaul opportunities and true cost per delivery.

Customer profitability

Margin by customer after accessorials, fuel surcharge recovery, detention, claims, and the time your team spends on them. Some of your largest customers by revenue may be among the least profitable once everything is counted. Some smaller ones may deserve more capacity.

Revenue leakage

Loads delivered but not invoiced, detention recorded in check calls but never billed, fuel surcharge applied at the wrong rate, accessorials waived without approval, and rate overrides below tariff. Leakage hides in plain sight until someone queries for it.

AR aging and cash

Aging by customer, bill-to, terminal and salesperson. Days sales outstanding trends, short-pays and deductions by reason, and the gap between delivery date and invoice date that quietly stretches your cash cycle. Collections teams get a focused list instead of a full aging report.

Driver and settlement analysis

Pay as a share of revenue by driver, pay type and lane. Settlement corrections and their causes. Miles per driver per week and where hours are being lost. The data behind driver retention conversations.

Data cleanup: the first win

Analysis is only as good as the data under it. Before we build profitability reports, we usually clean up what's there. It's also the step that makes billing faster and conversions safer.

• Duplicate customers, consignees and vendors merged so history and AR roll up correctly
• Location standardization for addresses, cities, states and ZIP codes, so lane analysis groups correctly
• Stale rate tables and tariffs identified and retired
• Orphaned and incomplete records such as loads without invoices, invoices without loads, and drivers or equipment marked active long after they left
• Code cleanup for accessorial, commodity and equipment codes that drifted over the years

Every change is scripted, tested on a copy, backed up and approved by you before it touches production. Clean data also makes any future migration much easier (see Legacy TMS Advisory & Migration).

One definition for every number

Half the arguments in a freight company's management meeting come from the same metric calculated two different ways. Before we report anything, we agree on definitions with you and write them down:

• Revenue per mile: Linehaul only, or including fuel surcharge and accessorials? Per loaded mile or per total mile? We usually show more than one, clearly labeled.
• Deadhead percentage: Empty miles divided by total miles, with an agreed rule for bobtail, repositioning and moves to the shop.
• Load margin: Revenue less driver or carrier pay, fuel, tolls and other direct costs, with a clear statement of which costs are included and which are allocated.
• Load count and period: Counted by pickup date, delivery date or invoice date, applied the same way in every report.
• DSO and aging: Measured from invoice date or delivery date, so the gap between the two is visible instead of hidden.

Once definitions are set, every report, query and dashboard uses them. The numbers stop being debated and start being used.

How a data mining engagement runs

1. Start with the questions. We sit down with the owner, CFO or operations manager and agree on the business questions that matter most. Data work without a question becomes a science project.
2. Secure access. Read-only access to a copy or reporting replica of your TMS and accounting databases, set up with your IT team or provider.
3. Map the data. We document the tables and fields that matter: loads, stops, movements, invoices, settlements, fuel, equipment and payments, and how they relate.
4. Clean and validate. Fix or flag the data problems that would distort the answers, and tie totals back to your financial statements so leadership trusts the numbers.
5. Analyze. Build the queries, run the analysis and test the findings with the people who know the freight.
6. Deliver and operationalize. Findings in plain language, plus the queries, views or reports so you can rerun them every month (see Reporting & Business Documents).

What you get

Putting the answers to work

Data mining earns its keep when the findings change decisions. Typical uses:

• Pricing and bid season. Lane-level cost and margin history before you answer a customer's RFP or a rate increase request.
• Network planning. Where to add or reduce capacity, which terminals feed each other, and where backhaul freight is worth chasing.
• Customer reviews. Facts on detention, accessorials and payment behavior when you sit down with a shipper.
• Process improvement. Baselines for billing cycle time and settlement corrections before and after a change (see Business Process Optimization).
• System decisions. Real volumes and data quality facts for a TMS selection or migration plan.
• Acquisitions. A data-backed look at a target company's lanes, customers and margins.

Who this is for

Owners, CFOs, controllers and operations managers at truckload and LTL carriers, brokerages, intermodal and drayage operators, and private fleets who:

• Suspect some lanes or customers are losing money but can't prove which
• Are heading into bid season or contract renewals
• Spend days assembling monthly numbers from spreadsheets
• Have a TMS database nobody on staff fully understands
• Want clean data before an upgrade, migration or acquisition

Curious how a data engagement fits into a wider assessment? See how we work.

Frequently asked questions

TMW is a trademark of its respective owner (Trimble Inc.). Strong Tower Consulting is an independent consulting firm and is not affiliated with or endorsed by Trimble or TMW Systems.