Commodity & Gas Index Mapping

2023 · Python · NLP · Snowflake · Public Gas & Commodities APIs

Impact

• Used as a manual verification tool during active supplier negotiations; useful for specific moments when a supplier cited production cost increases as justification for a price change
• Not productionized, but validated the core idea: public indices move ahead of supplier price requests, and the lag and magnitude are measurable

Business Problem

During the Ukraine war, gas prices surged and suppliers started citing increased production costs as justification for price increases. The problem: there was no way to verify these claims independently, and no systematic tracking of the input costs that actually drive supplier pricing: gas indices, flour, sugar, cocoa, wheat.

If a supplier claims their costs went up, you need the data to check.

Solution Design

Public gas and commodity price indices were pulled via external APIs and loaded into Snowflake. An NLP mapping layer links each product's ingredients and cost components to the relevant commodity and energy indexes, bridging the vocabulary gap between index naming conventions (e.g. "EU natural gas TTF front-month"), the terminology suppliers use in contracts, and internal product category labels.

Once the mapping is in place, index movements are tracked per product: if a supplier claims their costs went up, the tool shows whether the underlying commodity or energy index actually moved, and by how much. This gives the procurement team a factual baseline to verify or push back on supplier cost claims during negotiation.

Technical Challenges

Mapping commodity names to product categories. Standard commodity index names (e.g. "EU natural gas TTF front-month") don't match how products or suppliers describe their inputs. The NLP mapping step needed to bridge trade terminology, index naming conventions, and internal product category labels, none of which use consistent vocabulary.

Index source heterogeneity. Different commodities come from different API providers with different update frequencies, units, and historical coverage. Gas indices update daily; agricultural commodity indices update weekly or monthly. Normalization had to handle gaps, unit conversions, and differing base periods before any comparison was meaningful.

Lag between input costs and supplier price requests. Commodity price changes don't immediately translate into supplier price increase requests. Understanding the typical lag for a given commodity, and whether the current request is within that window, was the key analytical question. Without a corpus of historical (commodity movement, price request) pairs it was hard to calibrate this systematically.

Status

• Proof-of-concept, used manually during 2023 supplier negotiations
• Covered gas indices and key agricultural commodities: wheat, flour, sugar, cocoa
• Not productionized; the correlation analysis was run ad hoc per negotiation rather than as an automated workflow
• A natural follow-on to the Supplier Price Intelligence margin tracking work

Next Steps

• Productionize the index view: automate the weekly data pull so the mapped index movements are current for each negotiation round rather than run ad hoc
• Expand commodity coverage: add metals and packaging materials to cover a wider range of supplier cost structures
• Tighten the ingredient mapping: improve coverage of edge cases where index names and product ingredients share no vocabulary, currently handled manually