The Challenge
This specialty retailer operated 300+ physical stores across the US and a Shopify Plus e-commerce platform generating $1.2B in annual revenue. Customer data was fragmented across the POS system (NCR), Shopify, a loyalty program (custom), email marketing (Klaviyo), and customer service (Zendesk). The marketing team could not identify customers who shopped both in-store and online. Promotional campaigns were not personalized, resulting in low response rates and high unsubscribe rates. The executive team wanted a unified customer view to drive personalization, but previous attempts with their internal IT team had stalled.
Our Solution
Designed a customer identity resolution engine in Microsoft Fabric that matches customer records across POS, Shopify, loyalty, email, and support systems using email, phone, and address matching with fuzzy logic for near-matches.
Built a Fabric Data Warehouse with a customer-centric star schema supporting RFM (Recency, Frequency, Monetary) segmentation, customer lifetime value calculation, churn prediction scoring, and cross-channel journey mapping.
Created 25+ Power BI dashboards including: executive customer health scorecard, segment performance, store-level customer analytics, e-commerce funnel analysis, loyalty program effectiveness, and marketing campaign ROI.
Deployed Power BI Embedded within the retailer's internal portal so store managers access customer insights alongside their existing tools. Embedded reports auto-filter to the manager's store location using RLS.
Implemented automated customer segment feeds that push high-value customer lists from Power BI to Klaviyo for personalized email campaigns and to the POS loyalty system for in-store offers.
Results
Personalized recommendations based on unified customer profiles drove higher conversion rates.
Unified customer identity across POS, e-commerce, loyalty, email, and support for the first time.
Cross-sell recommendations based on purchase history increased average order value.
Early churn detection models triggered automated retention campaigns for at-risk customers.
Every store manager has access to customer insights through embedded Power BI reports.
Targeted segments based on RFM analysis tripled marketing campaign return on investment.
Implementation Methodology
Phase 1 (Weeks 1-3): Data integration and identity resolution. Connected all customer data sources and built the identity matching engine to create unified customer profiles.
Phase 2 (Weeks 4-7): Data warehouse and analytics. Built customer-centric data warehouse, RFM segmentation, CLV models, and churn prediction scoring.
Phase 3 (Weeks 8-10): Dashboard development and embedding. Created executive and store-level dashboards, deployed Power BI Embedded in the internal portal.
Phase 4 (Weeks 11-12): Activation and training. Set up automated segment feeds to marketing tools, trained marketing and store teams, and established success metrics.