Supplier Offer Ingestion & Normalization

The problem

A business received supplier offers via email — as Excel spreadsheet attachments. Each supplier used different formats, column names, tab structures, and pricing conventions. Manually processing these into a standardized format was slow, error-prone, and didn't scale.

The challenge was building an automated pipeline that could:

1. Ingest email attachments from Outlook/Exchange
2. Parse inconsistent spreadsheet formats
3. Semantically map fields to a standard schema
4. Normalize pricing, product data, and supplier metadata
5. Report exceptions clearly when data didn't fit

My role

I designed and built the entire ingestion and normalization pipeline — from email API integration to structured output generation with exception reporting.

What I built

Email ingestion layer

• Microsoft Graph API integration — connected to Outlook/Exchange to automatically fetch incoming emails with attachments
• Attachment extraction — filtered for Excel files, downloaded and staged them for processing
• Metadata logging — captured sender, timestamp, subject, and attachment details for audit and debugging

Parsing engine

• Multi-format Excel handling — parsed workbooks with multiple tabs, inconsistent headers, and varying structures
• Column detection — identified key fields like EAN, product description, price, pieces per carton across different naming conventions
• Tab-aware processing — handled workbooks where relevant data might be on the first tab, a named tab, or spread across multiple tabs

Normalization rules

• Semantic field mapping — built rules to map inconsistent column names to a standardized schema (e.g., "Retail Price", "RRP", "Price incl. VAT" all mapping to the correct field)
• Price type detection — distinguished between net price, retail price, and promotional pricing across supplier formats
• Brand resolution with an operator override path — rule-based detection where a supplier names the brand explicitly, plus bulk correction stored per barcode so a human fix follows the product and survives rebuilds. Worth being honest about the split: the supplier-column rules resolve a few hundred offers, while operator corrections resolve the overwhelming majority. Measuring that is what justified building the bulk-correction path in the first place
• Unit normalization — standardized quantities, packaging units, and per-carton counts

Exception reporting

• Unmappable field detection — flagged columns that couldn't be automatically mapped to the standard schema
• Data quality alerts — identified missing EANs, impossible prices, duplicate entries, and format anomalies
• Structured exception output — generated clear reports showing what was processed, what was flagged, and what required manual review

Standardized output

• Clean normalized spreadsheets — output in a consistent format ready for downstream systems (inventory, pricing, analytics)
• Processing summary — per-supplier statistics showing coverage, exception rates, and confidence levels

Architecture

The pipeline follows a staged approach:

1. Fetch — MS Graph API polls for new emails, extracts attachments
2. Parse — Excel files are opened, tabs identified, headers detected
3. Map — Semantic rules match source columns to target schema
4. Normalize — Values are cleaned, prices categorized, brands identified
5. Validate — Data quality checks flag exceptions
6. Output — Clean data and exception reports are generated

Each stage is independent — the parser doesn't need to know about the email layer, and the normalizer doesn't care about the source format. This makes the system extensible when new supplier formats appear.

Why this project matters

This is one of my most advanced data pipeline efforts because it goes far beyond simple scraping or parsing:

• Email API integration — not just file processing but automated email-based ingestion
• Semantic mapping — rules that understand intent, not just exact column names
• Business domain complexity — pricing conventions, packaging standards, and brand hierarchies
• Exception-first design — built to report what it can't handle, not silently drop data
• Scalable architecture — each stage is modular and independently testable

Outcome

The platform went live on 25 July 2026 and has been in daily use by the client's own buying and customer-service teams since. Measured 31 July 2026, it holds:

• ~1,843 raw supplier files ingested, normalized into ~170,142 standardized rows
• ~188,000 scored offers, across 112 suppliers and ~123,000 distinct products
• 342,332 historical quotes to enrich against, spanning a 2022–2026 archive
• A full run end to end in about five and a half minutes, refreshed hourly

Throughput was never the interesting part. Three correctness results mattered more.

Whole categories of file had been failing silently

Spanish-language price lists were rejected outright: the price-column classifier recognized price, prix and cost and nothing else, so PRECIO matched nothing and the sheet was discarded. Separately, offers a supplier typed into the body of an email rather than attaching were skipped entirely.

Neither failure produced a log line. The files simply went nowhere, and nobody knew to look — the most expensive class of bug in an ingestion system, because it presents as an absence. Both now parse like any other offer.

The best-looking prices were the least reachable

870 offers had been priced from volume-tier columns — headers like OFFER PRICE>75K and 100K€+ PRICE, rates that only apply on orders of €75,000 or more. Read at face value, they were simply the lowest numbers available, so the 100K€+ group scored an average competitiveness of 89.9: the least reachable prices in the book rated as the strongest deals in it. Tier prices are now detected and excluded from face-value scoring. Verified 870 → 0.

A weight column was being read as a unit price

SIZE/WEIGHT ranked highly as a net-of-VAT price column because the string "weight" contains ht, the abbreviation for hors taxes. 281 offers therefore carried a product's millilitres as its price in euros — a 1500 ml shampoo booked at €1,500. Verified 281 → 0.

This is the failure mode that makes header matching hard: the heuristics that let you read 112 suppliers' formats without per-supplier configuration are the same heuristics that occasionally match something confidently and wrongly. The fix was not abandoning inference but scoring it against the resulting values, so a price column that produces implausible prices loses to one that does not.

Alongside

Infrastructure came down with it — the ingest volume from 3.5 GB to 444 MB, and the heaviest operator page from 27 MB to 2.5 MB. New supplier formats are onboarded by adding mapping rules rather than rewriting code, and exception reporting surfaces what the engine could not classify instead of dropping it.