The client is a global consumer packaged goods (CPG) company with a diversified portfolio spanning oral care, personal care, and home care brands, sold across multiple countries. The company partners with a third-party receipt-scanning and rewards platform to capture point-of-sale shopper data, which feeds its customer relationship management, loyalty, and marketing attribution programs.
Implemented a prioritized matching sequence — exact barcode match, then a leading-zero-normalized barcode match, then a dedicated fallback for the unmapped barcode range — so the most reliable source always took precedence.
Built a maintainable brand-to-category/subcategory reference for products with no master-data coverage, combined with description-based rules (toothbrush type, floss, whitening, etc.) to standardize subcategories to the client's controlled vocabulary.
Redesigned identity resolution to match consumer emails against CRM records using the correct brand code — derived dynamically from the CRM brand reference table rather than hardcoded — with a defined fallback for transactions where no brand-specific match exists, eliminating duplicate and cross-brand subscriber assignments.
Added explicit dedup logic at every identity and matching join, guaranteeing one clean match per transaction and preventing row multiplication.
Refactored the transformation from a multi-stage, layered structure into a single-pass query, computing repeated business rules once and reusing them for a model that's easier to read, audit, and maintain.
