Freight Cost Distortion Agent (Reference)

The Freight Cost Distortion Agent helps identify products with disproportionately high freight costs relative to their price or margin, making hidden margin leakage visible. It benchmarks products against category peers and highlights where freight cost correction or pricing action is needed.

List of Required Fields

The following table lists the set of transaction fields required for the Freight Cost Distortion Agent to compute its metrics and detection rules. Ensure these fields or equivalent are available and consistently populated in your source Transaction Datamart before deploying the Agent. Those exact field names are not required, but a similar fields with that data is required.

Definition

Series 1 (Revenue and Margin by Product)

Group By

Measures

Filters

Filters are criteria applied to transactional data to ensure only valid records are analyzed, commonly excluding zero-value transactions and constraining invoice dates using relative ranges computed from a configurable anchor date, specified as N months ago.

Example

The rule applies a relative date filter, keeping only transactions whose pricing date falls within the last three months, so the analysis reflects current freight cost behavior rather than legacy data

Pricing Date > custom N months ago 3 (1/22/2026)

Summary Table

• Product Id – The top-level grouping dimension selected in the Group By definition. Rows are organized by Product ID.

• Product Name – The product name used as the next grouping level within the table hierarchy.

• Product Group – The product category grouping products by type, business line, or segment.

• Revenue – The aggregated sum of invoice price for all included transactions in the group.

• Profit – The aggregated sum of gross margin (revenue minus cost) for all included transactions in the group.

• Avg Margin % – The weighted average gross margin percentage for the group, computed as total margin divided by total revenue.

• Current Freight Cost Rate – The average freight cost rate for the group, representing freight cost relative to revenue (or quantity) over the selected period.

• Freight To Margin Ratio – The ratio of freight cost to gross margin, highlighting how much of the margin is consumed by freight cost for each product.

Series 2 (Averages by Product Group)

Group By

Measures

Filters

Filters are criteria applied to transactional data to ensure only valid records are analyzed, commonly excluding zero-value transactions and constraining invoice dates using relative ranges computed from a configurable anchor date, specified as N months ago.

• Date window – Include transactions where the Invoice Date falls between the chosen anchor date, to isolate a three‑month historical window.

• Value sanity checks – Include only records, so that analyses consider meaningful, billable transactions.

• Cost-focused analyses – When evaluating cost–price alignment, it is reasonable to exclude records with missing or zero cost.

Example

Improves data quality and consistency by preventing incomplete or non-commercial records from biasing freight cost distortion metrics, removes zero-value or null-price rows.

(Pricing Date > custom N months ago 3 (1/22/2026)) AND (Invoice Price > 0) AND (Invoice Price ≠ null)

Summary Table

• Product Group – The top-level grouping dimension selected in the Group By definition. Rows are organized by product category.

• Avg Margin % – Weighted average gross margin percentage for the product group.

• Avg Freight Cost % – Average freight cost rate for the product group (freight cost relative to revenue or quantity).

• Average Freight To Margin Ratio – Average freight-to-margin ratio within the group, serving as a reference level.

• IQR Freight To Margin – Interquartile range (Q3–Q1) of freight-to-margin ratios, describing the spread of typical freight burden in the group.

• Q3 Freight To Margin Ratio – Third quartile of freight-to-margin ratios; many detection thresholds use this as an upper bound of expected freight distortion.

• Freight To Margin 90th Percentile – 90th percentile of freight-to-margin ratios, indicating extreme but still typical high-freight cases.

• Q3 Freight % – Third quartile of freight cost rate within the group, used as a benchmark for acceptable freight %.

Join Series (Join Series)

Definition

Set up the relationship between the series by mapping the matching data columns/dimensions that you would like to include in the definition.

Measures

Summary Table

Detection Rules

Detection Rules defines the Agent’s alert conditions and includes scheduling. When conditions are met, actions are triggered during the next Agent run.

Default thresholds are flexible, adjust as necessary.

Example

Flags products that either have a freight-to-margin ratio above their product-group average (positive spread), indicating disproportionately high freight burden, or are already margin-negative while their freight % is even lower than the group benchmark, highlighting structurally unprofitable items where prices do not cover costs even before fully recovering freight.

(Spread to AVG of Freight To Margin >  0) OR (Product Margin % < 0) AND (Freight % Variance To Group < 0)

Schedule

Set the preferred start date and frequency that you want the Agent to run.

Example

This rule creates a recurring monthly schedule.

Start Date – The date when the scheduled task will run for the first time.
Period – Period which represents the offset between each run.
Interval – Interval which represents the number of repetitions in a selected period. Allowed characters are 0-9. 0 means one-off run.

Action Definition

Example

Similar Case Handling

• Period – Defines the time period between potential similar action. Similar case will not be recreated before the defined period. Time unit for the duplicate-prevention window.

• Interval – Number of periods between similar actions. Similar case will not be recreated before the defined interval. Prevents creating a very similar case for the same context within the defined interval.

Example

Prevents creating a very similar case for the same context within a 3-month period.

Impact Calculation

You can define specific metrics to compute the foreseen impact of the actions. Please only use total absolute value (and not relative values) as those metrics will be aggregated.

Impact Definition

Summary

Review the setup of the Agent, here you can see all the set parameters in one place.

If you are happy with the setup, click Submit for Approval. Once the Agent is approved, it becomes active and starts monitoring your data based on the schedule.