Retail media taxonomy drift to Snowflake–mParticle mapping with Tableau—targeting aligned

Overview

A global consumer packaged goods brand’s retail media campaigns underperformed because product taxonomies did not match across retailers, leading to inconsistent targeting and ongoing troubleshooting. Intelligex built a mapping layer in Snowflake with automated checks, synchronized product attributes to ad platforms through mParticle, and surfaced inconsistencies in a Tableau monitoring dashboard. Targeting and exclusions aligned across retailers, campaign setup became steadier, and the team spent less time reconciling spreadsheets and chasing errors.

Client Profile

• Industry: Consumer packaged goods (CPG)
• Company size: Global portfolio with multi-brand categories
• Stage: Scaling retail media operations across multiple marketplaces
• Department owner: Marketing & Customer Engagement (Retail Media and Performance)
• Other stakeholders: eCommerce Sales, Category Management, Product Information Management (PIM) team, Data Engineering, Agency Partners, Legal/Compliance

The Challenge

Each retailer used a different product taxonomy and set of category IDs. The brand’s internal PIM and analytics models used yet another structure. Campaign managers and agency partners translated categories by hand using spreadsheets, then built audiences and negative matches inside retailer ad consoles. Even careful work produced drift: a snack that counted as “chips” in one retailer and “salty snacks” in another sometimes landed in an unrelated segment, and size or pack variations were handled inconsistently. As a result, targeting rules behaved differently across retailers, creative rotations misfired, and budgets were spent against the wrong shelves.

Catalog files arrived on different cadences and used conflicting identifiers: internal SKU, GTIN/UPC, retailer-specific item IDs, and marketplace listings. New launches and reformulations made the problem worse, since attributes like flavor, pack count, and dietary tags often lagged in some systems. When campaigns underperformed, teams combed through exports and console views to find mismatches. The cycle repeated with each reset, pulling time away from planning and creative testing.

Replacing platforms or asking partners to switch tools was not an option. The brand relied on Snowflake as its warehouse, mParticle as the customer and catalog data conduit, and Tableau for reporting. Retailer ad platforms and agency workflows were established. The team needed a governed way to reconcile taxonomies, keep mappings fresh, and publish consistent attributes to every destination.

Why It Was Happening

Taxonomies evolved independently. The brand’s hierarchy was optimized for product development and supply chain, while retailers optimized for shopper navigation and ad inventory. Without a shared mapping layer and validation, last-mile translations lived in spreadsheets that drifted. Ambiguous or missing attributes, such as pack size or variant, forced ad hoc decisions that no one else could see later.

Identifiers did not line up reliably. Internal SKUs, GTINs, and retailer item IDs were all in play. Ingest jobs from the PIM and retailer feeds landed on different schedules, so a product could look eligible in one dataset and absent in another. There was no automated gate to flag unmapped products, conflicting category assignments, or stale mappings before campaigns were built.

The Solution

Intelligex created a centralized product-to-taxonomy mapping layer in Snowflake, backed by automated checks and human stewardship. The layer reconciled canonical product attributes to each retailer’s taxonomy and surfaced conflicts for review. Mappings and enriched attributes were synchronized to ad platforms through mParticle, and a Tableau dashboard provided a shared view of coverage, conflicts, and freshness. The approach integrated with existing systems and agency workflows, introduced a small number of review gates, and replaced manual spreadsheets with governed tables.

• Snowflake as the authoritative store for mappings, with curated tables for retailer taxonomies, translation rules, and SKU-to-category assignments. Reference: Snowflake documentation.
• Identity resolution across internal SKU, Global Trade Item Number (GTIN), and retailer item identifiers to ensure consistent joins. Background: GS1 GTIN.
• Automated validation rules that flagged unmapped products, many-to-many category conflicts, deprecated retailer categories, and missing key attributes like size, flavor, or pack count.
• Stewardship workflow that queued conflicts for Category Management to review, with an approval history, rationale, and effective dates captured at the record level.
• Versioned mapping dictionaries with roll-forward and rollback paths, so campaigns could reference stable snapshots.
• mParticle integration that consumed curated product attributes from Snowflake and forwarded retailer-aligned categories and traits to ad platforms. Reference: mParticle documentation.
• Tableau dashboards showing mapping coverage by brand and retailer, freshness of source feeds, validation failures, and items pending review. Reference: Tableau Help.
• Data contracts for product attributes and taxonomy fields, with schema checks to catch drift before publication to partners and ad consoles.
• Audit trail of mapping changes, owners, and downstream publish events to support compliance and partner communications.
• Graceful fallbacks that defaulted unmapped items to conservative categories and excluded them from sensitive tactics until reviewed.

Implementation

• Discovery: Cataloged retailer taxonomies, identified required attributes for targeting and exclusions, and mapped identity keys across PIM, warehouse, and retailer feeds. Collected the most common mismatch patterns from campaign retrospectives to guide rules and checks.
• Design: Defined the canonical taxonomy and translation tables, including how to handle ambiguous products and multi-category placements. Established the stewardship model, approval criteria, and data contracts for attributes and IDs. Planned snapshot strategies for stable campaign references.
• Build: Implemented Snowflake schemas for source, staging, curated, and published layers. Authored transformations and validation rules, and created stewardship queues with approval logging. Connected mParticle to consume curated attributes and forward mapped traits to ad platforms.
• Testing and QA: Ran the mapping layer in shadow mode, comparing proposed categories to current campaign setups and retailer console views. Injected edge cases like variant merges and seasonal items. Verified that validation failures blocked publication and raised actionable notifications.
• Rollout: Started with priority categories and high-volume retailers while leaving existing campaign structures in place. Enabled dashboards for read-only monitoring, then switched targeting references to mapped attributes as confidence grew. Kept rollback snapshots and partner coordination plans ready.
• Training and hand-off: Delivered role-based walk-throughs for Category Management stewards, media planners, and agency partners. Provided a playbook for reviewing conflicts, requesting new mappings, and interpreting dashboard signals. Set up a standing forum to tune rules based on live findings.
• Human-in-the-loop review: All conflicts and ambiguous assignments flowed to stewards for approval. Automation suggested likely matches, but human reviewers made the final call, with decisions and rationale logged for future reference.

Results

Campaign setup aligned across retailers because targeting referenced a single, governed mapping layer rather than ad hoc translations. Media planners and agency teams pulled the same retailer-ready categories, and exclusions behaved consistently. The validation rules caught gaps early, and stewardship addressed edge cases once rather than repeatedly in separate spreadsheets.

Troubleshooting dropped because dashboards showed where mismatches existed and how to fix them. New product launches and reformulations flowed into the mapping queue predictably, and partners saw stable, documented categories rather than shifting interpretations. The team regained time for strategy and creative testing, and leadership could view mapping coverage and readiness without asking for manual reports.

What Changed for the Team

• Before: Spreadsheet mappings by retailer; After: Centralized, versioned mappings in Snowflake with approvals and audit trail.
• Before: Inconsistent identifiers across datasets; After: Standardized joins across SKU, GTIN, and retailer item IDs.
• Before: Late discovery of missing attributes; After: Automated validations and alerts before publication.
• Before: Campaign resets to fix taxonomy drift; After: Stable snapshots and consistent targeting across retailers.
• Before: Blind spots on coverage; After: Tableau dashboards showing status, freshness, and conflicts to resolve.
• Before: Repeated case-by-case decisions; After: Stewardship queue with documented rationale and effective dates.

Key Takeaways

• Create a neutral mapping layer that translates internal and retailer taxonomies, rather than forcing any party to conform.
• Validate product attributes and mappings before they reach ad platforms; surface conflicts with clear owners and resolution paths.
• Use stable snapshots for campaigns so targeting does not shift mid-flight, while keeping mappings fresh upstream.
• Synchronize curated attributes through existing data pipes like mParticle to avoid rebuilding partner integrations.
• Make coverage and conflicts visible to both internal teams and agency partners with simple, shared dashboards.
• Keep humans in the loop for ambiguous products, and capture rationale to prevent repeating the same decisions.

FAQ

What tools did this integrate with?
Snowflake hosted the mapping layer and curated attributes, mParticle distributed mapped traits and categories to retail media platforms, the PIM supplied canonical product data, and Tableau provided monitoring dashboards. Retailer ad consoles consumed the attributes through existing partner connections or catalog feeds, avoiding new platforms for planners and agencies.

How did you handle quality control and governance?
Automated validations flagged unmapped products, conflicting assignments, deprecated categories, and missing attributes. A stewardship queue routed issues to Category Management with required fields for rationale and effective dates. Mappings were versioned, and snapshots supported audits and rollbacks. Data contracts enforced schema stability between PIM, warehouse, and downstream systems.

How did you roll this out without disruption?
The mapping layer ran in shadow mode while teams continued using existing setups. Dashboards exposed gaps and conflicts without changing campaigns. Priority categories moved first, with rollback snapshots ready. Agency partners kept their current workflows; only the attribute source changed from spreadsheets to curated fields.

How were retailer-specific nuances handled?
Translation tables captured retailer taxonomies, synonyms, and category hierarchies. Identity resolution aligned internal SKUs with GTINs and retailer item IDs, and special cases like multipacks or seasonal variants were flagged for human review. Where retailers allowed multi-category placement, rules and approvals documented intended behavior.

What about new products and refreshes?
Ingestion jobs watched for new or changed products, added them to the mapping queue, and applied conservative defaults until reviewed. Alerts notified stewards and media planners, and dashboards highlighted pending items so launches did not stall. Once approved, mappings flowed automatically to downstream platforms through mParticle.