Cost-to-serve chaos stalled pricing — Manhattan WMS, OTM & Salesforce unified in Snowflake

Overview

A national food distributor struggled to reconcile Warehouse Management System (WMS), Transportation Management System (TMS), and Customer Relationship Management (CRM) data, which stalled cost-to-serve analysis and muddied pricing decisions. Warehouse handling, accessorials, and route miles did not tie cleanly to orders or customers, and spreadsheets produced conflicting answers. Intelligex connected Manhattan WMS, Oracle Transportation Management (OTM), and Salesforce into Snowflake, harmonized customers and SKUs under a mastered ID, and stood up a pricing review workflow in ServiceNow. Executives saw clear profitability by segment and account, disputes declined because evidence was consistent, and pricing decisions moved faster with a shared view of cost drivers.

Client Profile

• Industry: Food distribution and wholesale
• Company size (range): Enterprise with regional distribution centers and dedicated fleets
• Stage: Established operator modernizing analytics and pricing governance
• Department owner: Strategy, Analytics & Executive Leadership (Corporate Strategy / Pricing Steering Committee)
• Other stakeholders: Supply Chain and DC Operations, Transportation, Sales & Account Management, Pricing, Finance/FP&A, Customer Service, IT/Data Engineering, Legal & Compliance

The Challenge

Operations and Pricing needed a coherent picture of what it cost to serve each customer and product family. In practice, Manhattan WMS tracked picks, cases, and temperature zones; Oracle OTM held stops, miles, and accessorials; and Salesforce captured deal terms, promotions, and service levels. Each system used different identifiers and units of measure. Promotions were separated from invoices, substitutions created SKU drift, and returns and credits were not consistently netted. Teams merged extracts by hand, and small differences in cut-offs or mappings yielded large swings in apparent profitability.

Disputes with customers and internal debates followed. Sales questioned freight adders and minimum order charges because costs were not tied back to specific behaviors. Pricing lacked a defensible view of accessorials by segment, and Finance could not trace from decision back to activity. Leaders did not want new systems; Manhattan WMS, Oracle OTM, Salesforce, and the data warehouse were already in place. They needed a harmonized model, repeatable allocation rules, and a governed workflow that turned analysis into pricing actions without adding another portal for frontline teams.

Why It Was Happening

Identifiers and calendars were fragmented. Customer hierarchies differed between CRM and TMS, SKUs changed over time, and units varied between eaches, cases, and pallets. A single shipment could serve multiple orders, but the linkage to invoice lines was incomplete. Without a mastered ID and consistent unit normalization, reconciliation required manual work that did not scale.

Allocation policy lived in spreadsheets. Warehouse handling, cold-chain, stop charges, and fuel surcharges were applied inconsistently across segments. Accessorials were booked at the route level and not attributed down to customer behavior. There was no audit trail for changes in mapping, and ownership of exceptions was unclear. The absence of a controlled workflow meant pricing conversations restarted from scratch each cycle.

The Solution

We implemented an end-to-end data pipeline into Snowflake with a master data layer that harmonized customers and SKUs, applied unit conversions, and encoded cost allocation rules. Manhattan WMS, Oracle OTM, and Salesforce feeds landed on a controlled schedule. Transformations in dbt built conformed tables that tied shipments and warehouse activities to orders and customers. A ServiceNow pricing review workflow routed exceptions and proposed price actions to owners with approvals. Dashboards in Power BI presented profitability by segment and account with drill-through to the underlying activities. Existing systems remained systems of record; the orchestration unified them into a governed decision flow.

• Data ingestion from Manhattan WMS for picks, cases, temperature zones, and labor activities (Manhattan WMS)
• Transportation data from Oracle Transportation Management for routes, miles, stops, and accessorials (Oracle Transportation Management)
• Deal terms and service commitments from Salesforce opportunities and accounts (Salesforce REST API)
• Snowflake as the analytic store with conformed models and time-stamped snapshots (Snowflake)
• dbt transformations for unit normalization, customer/SKU harmonization, and allocation logic (dbt)
• Mastered IDs for customers and SKUs, leveraging GS1 Global Trade Item Number (GTIN) where applicable (GS1 GTIN)
• Cost allocation engine that attributes warehouse handling, cold-chain, accessorials, and route costs to orders based on drivers (picks, cube, miles, stops, temperature)
• Validation layer for orphan shipments, unmapped SKUs/customers, inconsistent UOM, and missing returns/credits; items routed to human-in-the-loop review
• ServiceNow pricing review workflow with exception queues, proposed actions, reason codes, and approvals (ServiceNow)
• Power BI executive and practitioner dashboards with drill-through to order, route, and activity-level detail (Power BI)
• Role-based access and audit logs for mappings, allocation rule changes, and pricing approvals

Implementation

• Discovery: Mapped current extracts from Manhattan, Oracle OTM, and Salesforce; inventoried customer and SKU taxonomies; documented units of measure and temperature zones; collected existing allocation rules; and reviewed dispute cases to identify recurring gaps in evidence.
• Design: Defined the mastered customer and SKU ID model and mapping stewardship; authored unit normalization rules; designed the allocation engine with agreed drivers by cost type; specified validations and exception categories; and outlined the ServiceNow pricing review workflow with approval paths and reason codes.
• Build: Stood up scheduled feeds into Snowflake; implemented dbt models for conformed order-shipment-activity joins, harmonization, and cost allocation; created exception queues and mapping tools; built Power BI models and dashboards; and configured ServiceNow forms, queues, and approvals tied to pricing actions.
• Testing and QA: Replayed historical periods to reconcile results with Finance baselines; pressure-tested unit conversions and allocation drivers across ambient, chilled, and frozen paths; validated customer hierarchy roll-ups; and exercised exception routing and approvals with Pricing and Sales.
• Rollout: Launched read-only dashboards alongside legacy spreadsheets; after validation, enabled the ServiceNow workflow for targeted segments; then expanded coverage across regions and product families. Kept a manual override path for sensitive strategic accounts with documented rationale.
• Training and hand-off: Delivered guides for analysts on mappings and allocations; trained Pricing and Sales on the review workflow and reason codes; briefed Operations on addressing exception flags; and assigned stewardship for master data and allocation rules with a change-control cadence.

Results

Profitability views aligned across Strategy, Pricing, and Operations. Executives and account teams saw cost-to-serve by segment and customer, with clear attribution to warehouse and transportation drivers. Disputes were resolved with evidence rather than debate, and pricing proposals carried traceable links to activities and allocation rules. The conversation shifted from reconciling extracts to selecting actions on freight, minimums, and service commitments.

Decision speed improved because data arrived harmonized and validated on a schedule, with exceptions resolved in flow. ServiceNow captured approvals and rationale, and Snowflake preserved snapshots so results could be reproduced later. Sales, Pricing, and Operations worked from the same definitions, reducing rework and enabling targeted negotiations where costs were truly out of line.

What Changed for the Team

• Before: Spreadsheets stitched WMS, TMS, and CRM with inconsistent units and hierarchies. After: Snowflake hosted conformed models with mastered IDs and unit normalization.
• Before: Allocation rules varied by analyst and deck. After: An encoded cost allocation engine applied agreed drivers with an audit trail.
• Before: Exceptions were discovered during pricing meetings. After: Validation flags routed orphan records and mapping gaps to owners ahead of review.
• Before: Pricing actions were emailed without lineage. After: ServiceNow captured proposed actions, approvals, and reasons tied to underlying cost drivers.
• Before: Debates hinged on screenshots. After: Power BI dashboards drilled from segment profitability to order, route, and activity detail.

Key Takeaways

• Harmonize customers and SKUs under mastered IDs; reconciliations fail without consistent identity and units.
• Encode allocation policy in the data model; cost drivers should be transparent, repeatable, and auditable.
• Route exceptions to a human-in-the-loop queue; fixing mapping and orphan issues upstream protects pricing forums.
• Keep WMS, TMS, CRM, and the warehouse; orchestrate them with schedules, transformations, and a pricing workflow rather than replatforming.
• Present profitability where decisions are made; drill-through from segment to activity builds trust and speeds action.

FAQ

What tools did this integrate with?
We ingested warehouse activity from Manhattan WMS, transportation routes and accessorials from Oracle Transportation Management, and deal context from Salesforce. Data landed in Snowflake and was transformed with dbt. Pricing actions and approvals ran through ServiceNow, and decision views were delivered in Power BI.

How did you handle quality control and governance?
We established mastered IDs and mapping stewardship for customers and SKUs, normalized units, and encoded allocation rules in dbt. Validations flagged orphan shipments, unmapped entities, UOM mismatches, and missing returns/credits. Exceptions flowed to owners with reason codes. ServiceNow captured pricing approvals and rationale, and Snowflake snapshots preserved the state behind each decision with change logs.

How did you roll this out without disruption?
We ran dashboards in read-only mode alongside existing spreadsheets while teams validated mappings and allocations. Once confidence was established, we enabled the ServiceNow review flow for selected segments, then broadened coverage. Core systems remained; the orchestration layered harmonization, allocation, and workflow on top.

How were SKUs and customer hierarchies harmonized?
A master data layer assigned stable customer and SKU IDs, mapped legacy codes and historical changes, and aligned to reference standards such as GS1 GTIN where applicable. Mapping ownership sat with defined stewards, and updates were versioned so historical reporting remained interpretable.

How did you allocate warehouse and transportation costs to orders?
Allocation used drivers appropriate to each cost type: picks and cube for handling, temperature zone for cold-chain, miles and stops for transportation, and explicit linkage for accessorials. Rules were encoded in dbt models with notes and owners, and results were traceable from segment roll-ups down to order and stop-level activities.