Less-Than-Truckload Costing Utility

Overview

The Less Than Truckload Costing utility solves the challenge of pricing less-than-truckload shipments when carrier rate data is complex and varies by service level, distance, and weight. Rather than manually looking up rates in carrier tariff tables, this workflow automates the entire process using FedEx Express Freight standard list rates. The utility expects a lanes-to-cost table containing shipment details including origin, destination, distance, weight, and desired service level. After running the utility, users receive a fully costed table with calculated transportation costs.

The Less Than Truckload Costing Utility is available on the Resource Library, from which you can download it or copy it to your Optilogic account. Learn more about the Resource Library in this How to use the Resource Library help center article.

What's Included

Sample Data

• Sample lanes_to_cost CSV file (5,000 lanes across the US)

System Utility

• Less Than Truckload Costing utility accessible via the "Run Utility" task in DataStar
• Built-in FedEx Express Freight rate tables (automatically loaded by the utility)

How To Use

The steps to use this utility are as follows. These are illustrated with screenshots below.

1. Prepare your input data:
   • Create a lanes_to_cost table with all required columns (see Input Requirements)
   • Ensure distances are calculated in miles
   • Assign appropriate service levels to each shipment
   • Set the destination_rural flag correctly for Alaska/Hawaii destinations, see also the Tups & Notes section
2. Upload your data to a database in DataStar
3. Run the utility:
   • Open the "Run Utility" task in DataStar
   • Select "Less Than Truckload Costing" from the available utilities
   • Configure the parameters:
     • Database: Enter your database name
     • Schema: Select the schema containing your table
     • Lanes Table: Select your lanes_to_cost table
     • Output Table: Specify the output table name
     • Keep Working Tables: Toggle on to retain intermediate tables for debugging
     • Verbose: Toggle on for detailed logging
4. Review the output table containing costed lanes

Screenshots of the steps:

Input Requirements

Lanes to Cost Table (Required)

Key Constraints:

• Each combination of (service_level, origin_id, destination_id, shipment_weight) must be unique
• No NULL values allowed in any required column
• Service level values are case-sensitive and must match exactly
• Weights must be positive numbers in pounds
• Distances must be in miles

Output Description

The utility produces an output table containing all lanes from the input with the following columns populated:

Zone Determination

Zones are determined automatically based on the following priority:

Special Zones (for Alaska/Hawaii):

1. Destination AK/HI + rural=True: Zone "11 (To AK rural)"
2. Destination AK/HI + rural=False: Zone "9-10 (To AK/HI metro)"
3. Origin AK/HI: Zone "13-16 (From AK/HI)"

Standard Distance-Based Zones:

Cost Calculation

Costs are calculated using the following formula:

• ‍base_charge = shipment_weight x price_per_lb
• final_cost = MAX(base_charge, minimum_charge)

Effective Weight: If the shipment weight is below the minimum weight for a service/zone combination, the utility uses the minimum weight band's rate but calculates the charge based on the actual shipment weight.

Service Levels

Tips & Notes

• The workflow is fully deterministic - given the same inputs, it will always produce the same outputs.
• Original input tables are never modified. The utility creates working copies for all processing.
• The FedEx rate tables are built into the utility and are automatically loaded. You do not need to provide rate data.
• For Alaska and Hawaii destinations, the destination_rural flag is important. Set it to True for rural Alaska locations, False for metro areas. This determines whether the "11 (To AK rural)" or "9-10 (To AK/HI metro)" zone rates apply.
• Service level values must match exactly (case-sensitive). Use the exact strings: "1Day Freight", "2Day Freight", "3Day Freight", or "First Overnight Freight".
• The minimum charge floor ensures that very light shipments still meet the carrier's minimum pricing requirements.
• If a shipment weight is below the minimum weight threshold for a service/zone, the rate from the lowest weight band is used, but the cost is still calculated using the actual shipment weight (not the minimum weight).
• This utility currently supports US domestic less-than-truckload shipments only. The rate structure includes special handling for Alaska and Hawaii.

Other Helpful Resources

• Learn more about Cosmic Frog
• Learn more about DataStar
• An overview of Optilogic's AI Agents and Utilities
• The Full Truckload Costing Utility, also available from within DataStar