FreightMath Data Schema

The FreightMath Data Schema defines the fields, relationships, and minimum business rules required for order-level and segment-level data exchanged between trucking company transportation management systems (TMS) and FreightMath. It ensures a consistent, auditable structure for all FreightMath analytics, pricing tools, and dashboards.

Download Schema & Sample Data

Download the current FreightMath Data Schema workbook, which includes both the Order and Segment schemas plus sample data for each.

• FreightMath Data Schema & Samples (Excel) — Download Data Schema Workbook

Tier 1 — Conceptual Overview

1. Entities & Relationships

The FreightMath schema is organized around two core entities:

• Orders — a single customer shipment or load, including who tendered it, who is billed, high-level revenue and cost fields, and key geography.
• Segments — the operational building blocks that describe how an order is physically executed (e.g., linehaul, repositioning, local pickup/delivery segments).

Conceptually:

• Each Order must have a unique identifier (ORDER_ID) and core commercial attributes (Customer, Bill To, dates, revenue, cost).
• Each Segment must have a unique identifier (SEGMENT_ID) and a link back to its parent Order (e.g., ORDER_ID or equivalent foreign key).
• An Order typically has one or more Segments; together, those segments cover the physical activity and timing of the load from origin to final destination.

2. Data Categories

Fields are grouped into logical Data Categories to make mapping and validation simpler:

• Order / Segment — key identifiers, links, and flags.
• Chronology — dates and times (arrival, departure, appointments).
• Location & Geography — city, state/province, postal code, country, FreightMath Area codes, and related fields.
• Assets (Segments only) — tractors, trailers, drivers, and related attributes.
• Financial — revenue, accessorials, and cost fields used for FreightMath costing.
• Distance — loaded and empty miles used for costing and performance metrics.

3. Required vs. Optional Fields

Each schema field in the Excel workbook is tagged with a REQ flag:

• Y = required — FreightMath expects a valid, non-null value for every record.
• O = optional — useful and ingested when present, but not required for processing.

For implementation planning, prioritize mapping all Y fields first, then expand into optional fields as data quality, availability, and use cases mature.

4. Joining Orders and Segments

The schema assumes a stable, unique Order key with one-to-many related Segment records:

• Every Segment must reference the Order it belongs to.
• Order-level financials (e.g., total revenue) are associated with the Order, while operational activity (e.g., driver, tractor, trailer, miles, timestamps) is associated with Segments.
• FreightMath reconciles Segments to their parent Orders to compute lane-level P&L, network profitability, and driver/asset economics.

5. Time & Location Rules (Chronology & Geography)

To ensure consistent chronology, Segments carry “From” and “To” timestamps and locations. The following business rules apply at a high level:

• Within a Segment, arrival times cannot occur after departure at a given stop.
• Across Segments within a given Order, Segment start times should not overlap in ways that are physically impossible for the same driver or tractor.
• Location fields (city, state, postal code, country) should be valid, parseable, and consistent with the carrier’s TMS and FreightMath’s area definitions.

6. Financial & Distance Fields

Financial fields captured in the schema are used to compute FreightMath’s Operating Ratio (OR), margin, and profitability metrics. At a minimum:

• Revenue and cost fields should be in consistent currency (typically USD).
• Miles fields should distinguish loaded vs. empty mileage where possible.
• Accessorials (detention, layover, stop charges, etc.) should be mapped to their closest FreightMath fields.

For many clients, initial implementations map a subset of financial fields and evolve over time toward full coverage, especially as MapLedger and GL-based costing are introduced.

Tier 2 — Technical Appendix (Field-by-Field Specification)

This appendix mirrors the FreightMath Order and Segment field schema workbooks. It is intended for data engineers and technical implementers who are building or maintaining the TMS → FreightMath integration.

TMS-Specific Mapping Guides

The core FreightMath Data Schema is TMS-agnostic. However, most clients prefer prescriptive, system-specific guidance. The following sections are reserved for detailed mapping patterns, code snippets, and export templates for major TMS platforms. These will be maintained as separate Knowledge Base pages, with links surfaced here.

Note: Separate Knowledge Base pages will provide TMS-specific mapping examples (e.g., McLeod, TMW, MercuryGate, etc.), including code snippets and ETL patterns that map native TMS fields into this FreightMath Data Schema.