Pricing Data Integration

Rebuilding a promotional pricing pipeline that consolidates competing offer sources into a single, auditable weekly price.

Overview

Grocery retailers run promotions across multiple channels at once, including campaigns, store-level offers, and standing discount programs. Each source has different date ranges, offer types, and price levels. The real challenge isn't ingestion, but that these sources frequently conflict, and without a clear resolution rule, different teams end up working from different "correct" prices. This project builds a weekly pricing pipeline that produces a single source of truth: one validated price per SKU per menu week, with a documented, auditable trail of which source drove it. The pipeline:

• Ingests and cleans four source tables (Campaign Offers, Non-Campaign Offers, Special Discounts, Product List Price)
• Generates a SKU × menu week grid covering a defined promotional window
• Resolves price conflicts using a priority ruleset
• Outputs a clean, validated pricing file ready for downstream use

The Problem

Without a centralized pricing source, teams working from different offer tables arrive at different prices for the same SKU in the same week. Promotional commitments get overridden silently. Special discounts get missed. The result is pricing inconsistency that surfaces as errors at execution time, often too late to fix cleanly.

Pricing Priority Logic

The core business rule: Campaign offers represent intentional promotional commitments. When one exists, it takes precedence and Non-Campaign offers are ignored entirely. Special Discounts, however, are always active and always considered alongside whichever primary source applies.

1. Campaign offer exists for that week:
   → Ignore NonCampaign
   → final price = min(Campaign price, Special Discount price)

2. No Campaign offer:
   → final price = min(NonCampaign price, Special Discount price)

3. No offers at all:
   → final price = List Price  |  promo_type = LIST_PRICE

BOGO offers are halved before any comparison to ensure a fair per-unit basis across offer types.

Process

Data Cleaning

Issue	Fix
Trailing empty rows	Dropped with dropna(how='all')
sales_price stored as $X.XX string	Stripped $, cast to numeric
Date columns as object type	Converted with pd.to_datetime

Approach

Step 1 — Generate menu weeks Menu weeks run Friday to Thursday. pd.date_range with freq='W-FRI' generates all Fridays from 2024-03-01 to 2024-04-25 (8 weeks in total).

Step 2 — Build base grid & join sources A full cross-join of all SKUs from ProductListPrice against all menu weeks creates the base. Each offer source is joined by SKU and filtered to rows where the offer's date range overlaps the menu week (promo_start ≤ week_end and promo_end ≥ menu_week). Where multiple offers exist for the same SKU and week, the lowest price is selected before any priority logic is applied.

Step 3 — Apply priority rules Boolean flags (has_camp, has_non, has_spec) indicate whether each source produced a price for that row. np.select evaluates conditions top-down in the order described above.

Output

integrated_data.csv — one row per SKU per menu week

Column	Description
menu_week	Week start date (Friday)
sku_id	Product identifier
sku_name	Product name
promo_type	Source and offer type applied (CAMPAIGN, NON_CAMPAIGN, BOGO, LIST_PRICE, etc.)
end_price	Final resolved price for the week

Technologies Used

• Python 3.x
• Pandas & NumPy

made with ✨ + 🌿 + 💛 by Chia Chang