Enhanced Customer Engagement through Unified Data Model and Power BI Dashboard

Executive Summary

One of the largest food and drug retailers in the United States faced challenges in obtaining a complete view of customer engagement due to data fragmentation across various communication channels. To address this, they sought a unified data model and Power BI dashboard to assess channels’ performance and campaign effectiveness.

Factspan team designed a data model to provide a single source of truth. By deploying ETL processes, the team consolidated data from SMS, App & email push notifications, In-app messages, Email and Ecommerce platforms into a unified data warehouse. The robust data model enabled an enhanced analytical approach by enriching CRM data with customer segmentation and company’s banners, providing a comprehensive view of customer profiles and sentiment analysis.

Leveraging Power BI, the team created an interactive dashboard showcasing customer engagement metrics, channel breakdowns, customer segmentation, campaign & banner performance, time analysis, and sentiment analysis. As a result, customer engagement increased by 15% with the new engagement strategies and positive sentiment in customer feedback improved by 20% following the implementation of the initiatives.

About the Client

The client is one of the largest food and drug retailers in the United States. The company operates over 2,200 stores in 35 states and the District of Columbia. It is committed to sustainability and reducing its environmental footprint and giving back to its communities.

Business Challenge

The company faced challenges in getting a complete view of customer engagement because each communication channel (SMS, App & email push notifications, In-App messages, Email and Ecom mobile & website) had its own data sources. This made it difficult to assess the performance of each channel and marketing campaign from the aggregated customer’s perspective.

To address this issue, the company aimed to develop a unified data model that can accommodate data from all communication channels, from their respective sources. By organizing the data based on campaigns, channels, and banners, the CRM team intended to improve customer engagement tracking. This unified approach will allow them to evaluate channel performance, campaign effectiveness, and identify trends by banners and customer segments all within a single dashboard.

Our Solution

Factspan team started by conducting in-depth workshops with the stakeholders to understand their specific requirements, pain points, and key performance indicators (KPIs). This process helped them tailor the data model and dashboard to align with their business objectives. The team utilized Extract, Transform, Load (ETL) processes to cleanse, transform, and load the data into a unified data warehouse as per the data governance policies. A robust data model was designed to serve as the foundation for the Power BI dashboard to lay on top.

To enhance the analytical capabilities of the dashboard, the team enriched the CRM data with relevant external data, such as customer segments, banners’ location data, and social media sentiment scores. This enrichment provided a comprehensive view of customer profiles and sentiment analysis. Leveraging Power BI’s powerful visualization capabilities, they created an interactive and user-friendly dashboard with various components, including:

• Overview Section: A summary of customer engagement metrics, including interactions, trends, and sentiment.
• Channel Breakdown: Visualizations showcasing engagement data by channels, helping identify effective communication channels.
• Customer Segmentation: Insights on customer segments based on demographics and engagement history, enabling targeted marketing.
• Banner Breakdown: Visualizations showcasing engagement data by channels, helping identify effective communication channels.
• Campaign Performance: Analysis of campaignspecific interactions and customer feedback for better product understanding.
• Time Analysis: Trends and patterns of engagement over time, identifying peak periods for resource optimization.
• Sentiment Analysis: Visualizations depicting customer sentiment changes and factors influencing it.

Business Impact

• Customer engagement increased by 15% after implementing the new engagement strategies.
• Positive sentiment in customer feedback improved by 20% following the implementation of the engagement initiatives.
• The new marketing campaign resulted in a 25% increase in the conversion rate of leads to customers