Strong Tower Consulting, LLC was founded in Minnesota in 2008 by Scott Elliott. For more than 18 years we have helped asset-based motor carriers transform fragmented financial data into precise, actionable lane-level intelligence. Our sister company Exspeedite LLC builds the complementary software tools that many of our clients choose to implement after our strategic architecture work is complete. Strong Tower Consulting and Exspeedite LLC are separately organized sister companies under common ownership.
Modern trucking operations generate enormous volumes of transactional data across Truckmate, McLeod, TMW, or custom TMS platforms. Yet most carriers still rely on static monthly operating ratio reports that mask the true contribution margin of individual lanes, customers, and equipment types. This guide presents the production-grade SQL cost allocation architecture we deploy for carriers running 75 to 750 power units.
The Problem With Traditional Carrier Financial Reporting
Standard income statements aggregate expenses into broad categories: fuel, driver wages, maintenance, insurance, and overhead. These reports answer “Are we profitable this month?” but fail to answer the far more important question: “Which lanes, customers, and equipment configurations are actually subsidizing the rest of the network?”
Without dynamic lane profitability models, carriers routinely accept freight that destroys contribution margin, misprice deadhead repositioning, and allocate fixed equipment overhead using arbitrary percentages rather than actual utilization.
Core Components of the Dynamic Lane Profitability SQL Engine
1. Unified Transaction Grain
All analytics begin with a single, conformed fact table that joins EDI 204 tender acceptance, EDI 214 status events, EDI 210 freight invoices, driver settlement records, fuel purchase and tax (IFTA) data, and equipment utilization logs. The grain is one row per loaded movement with all associated empty (deadhead) miles attributed using a configurable attribution rule set.
2. Cost Allocation Models
We maintain two parallel cost layers:
- Variable (direct) costs: fuel, driver pay (including per-diem and accessorials), tolls, and maintenance consumed on the specific movement.
- Equipment and terminal overhead: tractor and trailer depreciation, insurance, licenses, and yard costs allocated using actual utilization (revenue miles, loaded hours, or a hybrid model).
| Lane | Loaded Miles | Deadhead % | Revenue | Variable Cost | Contribution Margin | OR % | Equip. Overhead | Net Profit |
|---|---|---|---|---|---|---|---|---|
| ATL - CHI | 18,420 | 11.4% | $1,284,700 | $682,300 | $602,400 | 47.1% | $218,900 | $383,500 |
| CHI - MSP | 9,850 | 28.7% | $487,200 | $391,400 | $95,800 | 80.3% | $142,600 | ($46,800) |
| DAL - HOU | 14,110 | 6.9% | $792,400 | $371,900 | $420,500 | 46.9% | $167,300 | $253,200 |
| MEM - ATL | 7,340 | 34.2% | $312,800 | $289,100 | $23,700 | 92.4% | $98,400 | ($74,700) |
3. Dynamic Operating Ratio Calculation
The SQL engine calculates operating ratio at three levels:
- Lane OR: Total cost (variable + allocated fixed) divided by lane revenue.
- Customer Contribution OR: Excludes equipment overhead to isolate sales and pricing performance.
- Network OR with Deadhead Attribution: Uses a mileage-proportional or optimization-based model to assign empty miles back to the preceding or following loaded movement.
Deadhead attribution logic is particularly important. Many carriers simply spread all empty miles across the entire fleet. Our model attributes deadhead using business rules such as “nearest preceding loaded movement by driver” or “optimization-based repositioning cost” derived from historical patterns.
4. Fuel Surcharge and Detention Reconciliation
The engine automatically reconciles billed fuel surcharges (based on DOE or other indexes) against actual fuel consumed per movement. Detention and accessorial revenue is matched to the exact stop using EDI 214 event timestamps and driver ELD records.
Implementation in Truckmate, McLeod, or TMW Environments
Most of our clients run either Truckmate, McLeod Transportation Management, or TMW Systems. The SQL engine is built as a set of scheduled ETL procedures that pull from the native SQL Server or Oracle schemas. Key tables include:
- Orders / Dispatches
- Stops / Events
- Driver Settlements
- Fuel / IFTA transactions
- Equipment utilization and maintenance
- EDI transaction archives (204, 214, 210)
We never require clients to replace their core TMS. The analytics layer sits alongside the operational system and refreshes nightly or intra-day depending on requirements.
Strong Tower Consulting provides strategic architecture, data modeling, and process optimization services. You never have to buy our sister company Exspeedite LLC software to benefit from our work. Many carriers choose to implement the models we design using their existing BI tools (Power BI, Tableau, or custom SQL Server Reporting Services).
Customer Yield Management Dashboard Architecture
Once the lane profitability engine is running, we layer a yield management dashboard that surfaces:
- Top 50 lanes by contribution margin per mile
- Customers ranked by fully-allocated operating ratio
- Equipment type profitability (reefer vs dry van vs flatbed)
- Driver-specific contribution after settlement and escrow accounting
This intelligence feeds directly into the carrier rate matrix optimization process and into automated load acceptance rules within the TMS.
Next Steps for Motor Carriers
If your current financial reporting cannot isolate lane-level contribution margin or dynamically calculate operating ratios that properly attribute deadhead and equipment costs, your pricing and network decisions are being made with incomplete information.
Strong Tower Consulting delivers production-ready SQL cost allocation and lane profitability engines that integrate with Truckmate, McLeod, TMW and other TMS platforms. Our work is used daily by carriers to reject unprofitable freight, renegotiate customer rates, and optimize network balance.
Request a Lane Profitability Assessment or contact us at 763-244-1373 to discuss your specific requirements.
Related Practices
- Carrier Rate Matrix Optimization
- SQL Data Mining & Analytics
- Transportation Accounting Integration
- Business Process Optimization
Related Guides
- Carrier Driver Settlements and Escrow Accounting Engine Guide
- Carrier Dynamic Rate Matrix and Tariff Engine Architecture
- McLeod LoadMaster Carrier Database Performance Tuning Playbook
- Trucking Fuel Tax (IFTA) Automated GPS State Mileage Audit
Frequently Asked Questions
How do you properly attribute deadhead mileage in lane profitability calculations?
We use a combination of driver-specific trip chaining and network optimization logic. The SQL model identifies the preceding or following loaded movement for each empty segment and attributes the deadhead cost proportionally or in full depending on the business rule chosen by the carrier. This prevents the common error of spreading all empty miles evenly across the entire fleet, which distorts true lane profitability.
Can this SQL engine work with both Truckmate and McLeod simultaneously?
Yes. The architecture is TMS-agnostic at the semantic layer. We build separate extraction procedures for each system but feed the data into a conformed star schema. Carriers running multiple TMS platforms (common after acquisitions) receive unified profitability reporting across all operating companies.
What is the difference between contribution margin and fully-allocated operating ratio?
Contribution margin typically includes only variable costs (fuel, driver pay, tolls) and is used to evaluate sales and pricing decisions. Fully-allocated operating ratio adds equipment depreciation, insurance, maintenance overhead, and terminal costs. Both metrics are calculated automatically by the engine and presented side-by-side so management can make appropriate tactical and strategic decisions.
Do we have to buy Exspeedite software to use these models?
No. Strong Tower Consulting, LLC and Exspeedite LLC are separately organized sister companies under common ownership. Our consulting deliverables include complete, documented SQL, data models, and dashboard specifications that you can implement with your existing BI team or any software provider. The “Independent by design” philosophy ensures you retain full control of your data and technology decisions.