Helped 3rd largest Pharmacy chain in India Boost Sales Effectiveness with Redshift DataWarehouse

About the Customer:

Customer is a leading retail pharmacy and wellness products chain in India, operating a large omnichannel and hyperlocal retail network of 24x7 stores. The brand focuses on providing customers with a diverse range of pharmaceutical, nutraceutical, lifestyle, and wellness products with their catalog having over 1,00,000+ products, including curative & preventive medicines.

‍

Customer Challenge

• The customer doesn’t have a unified platform to manage and drive analytics from their existing data across various data sources like store databases, Sheets, CRM systems, transaction systems
• Difficulty in combining data from across the sources and making data-driven decisions to improve sales performance, enhance marketing campaigns, manage inventory
• 3x increase in data volumes year on year has made the decision-making complex prompting the need for an efficient system to handle the workload

‍

Risks if the above challenges are not implemented

• The absence of a centralized data platform adversely affected the decision-making process
• Lack of visibility into sales performance based on marketing campaigns and inventory details as per multiple metrics related to brands, stores, manufacturers, and so on
• Challenges in deriving store-wise purchases and managing stores with the right inventory

‍

Solution Implemented

• Built a centralized data warehouse platform leveraging Redshift where data from across different data sources resides for performing analytics
• Developed a Self Serve ETL pipeline using Managed Airflow, EMR Serverless, S3, and Redshift where data is exported and transformed from 300+ tables from across 5+ data sources including Sybase, Strapi, MySQL, Google Sheets
• Scheduled complex analytical queries on data stored in Redshift that help customers combine data from all sources and make informed decisions 
• Utilized open source Apache Superset visualization tool and built 15+ dashboards to manage sales as per categories, and stores and perform discount and purchase analysis

‍

‍Results and Benefits:

• Seamlessly perform complex analytics and build dynamic reports and ML models across disparate data sources through data warehouse implementation
• Reduced data processing time from 6 hours to 30 minutes
• Improved decision-making by 80%

‍