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%