Pharma Sales Intelligence &
Demand Forecasting Dashboard
A hypothetical case study demonstrating how 6 years of real pharmaceutical dispensing data (sourced from a public Kaggle dataset) across 8 drug categories can be transformed into actionable business intelligence — from raw CSV exports to interactive, filterable dashboards for inventory planning and revenue optimization.
Overview
This project demonstrates how a regional pharmacy chain could move beyond spreadsheet-based reporting to understand dispensing patterns, seasonal demand shifts, and category-level performance across their product portfolio. The hypothetical scenario assumes the existing process relies on manual CSV exports from a POS system with no automated analysis pipeline.
Using a publicly available pharmaceutical sales dataset from Kaggle, I built an end-to-end analytics system that ingests multi-granularity sales data (hourly, daily, weekly, monthly), processes it through a structured data pipeline, and surfaces insights through interactive dashboards with filterable KPIs, trend analysis, seasonality detection, and category-level drill-downs.
Note: All data in this case study comes from a publicly available Kaggle dataset. The business scenario, client context, and projected outcomes are hypothetical — designed to showcase the analytical approach and dashboard-building methodology.
The Challenge
In this scenario, the pharmacy chain would be operating with significant blind spots in their sales data. Category managers would have no visibility into cross-drug trends, seasonal patterns, or hourly demand curves — leading to stockouts during peak periods and overstock during slow months.
Projected Business Impact: In a real-world scenario, these issues could result in an estimated $180K/year in lost revenue from stockouts during peak demand periods, plus $45K/year in carrying costs from overstocked slow-moving categories.
The Solution
I designed and implemented a modern analytics pipeline that transforms raw pharmaceutical sales data into interactive, filterable dashboards — enabling real-time decision-making across inventory, staffing, and category management.
Data Ingestion & Processing
Built a Python-based ETL pipeline to ingest 4 CSV datasets (50K+ records across hourly, daily, weekly, and monthly granularities), clean anomalies, and transform into structured JSON for the analytics layer.
KPI Engine & Aggregation
Computed key performance indicators including total volume, category market share, year-over-year growth rates, seasonality indices, and weekday distribution patterns — all pre-calculated for instant dashboard rendering.
Interactive Dashboard Development
Built responsive, filterable dashboards using React and Recharts with drug category toggles, time granularity switching (weekly/monthly), year filters, and multiple visualization types (area charts, bar charts, radar, heatmaps).
Insight Delivery & Recommendations
Surfaced actionable insights: identified Oct–Dec as peak demand quarter (20% above average), Saturday as highest-volume day, and N02BE (Anilides) as the dominant category at 49% market share — enabling data-driven inventory and staffing decisions.
Tech Stack
Live Interactive Dashboard
Explore the working dashboard built from the public Kaggle pharmaceutical sales dataset. Use the filters to drill into specific drug categories, time periods, and granularities. All data is loaded dynamically from pre-processed JSON — no hardcoded values.
Total Units Sold
126.6K
2014-01 to 2019-10
Avg Monthly Volume
1.8K
Across 70 months
Top Category
N02BE
Analgesics (Anilides)
Peak Season
Oct–Dec
Highest demand quarter
- M01AB
- M01AE
- N02BA
- N02BE
- N05B
- N05C
- R03
- R06
The Results
Based on the analysis of the real dataset, this type of analytics system could transform how a pharmacy chain manages inventory, staffing, and category strategy — replacing gut-feel decisions with data-driven insights. Below are projected outcomes.
Projected Time Saved
12 hrs/week
Automating what would otherwise be manual Excel analysis
Projected Stockout Reduction
-35%
Seasonal demand forecasting could prevent key category shortages
Projected Savings
$120K
Potential annual savings from right-sizing stock levels per category
Decision Speed
Real-time
From weekly batch reports to instant interactive dashboards
About This Analysis
This case study was built using a publicly available pharmaceutical sales dataset from Kaggle, containing real dispensing data across 8 ATC drug categories from 2014–2019. The business scenario and projected outcomes are hypothetical, but the data analysis, dashboard design, and technical implementation are fully functional and representative of the approach I would take for a real client engagement.
Key Insights from the Data
N02BE Dominance
Analgesics (Anilides) represent 49% of total volume — a single category driving nearly half of all dispensing activity. This concentration creates both opportunity and risk.
Q4 Demand Surge
October through December consistently shows 15–20% above-average demand across most categories, driven by seasonal illness patterns and year-end insurance utilization.
Saturday Peak
Saturday is the highest-volume day (65.7 avg units vs. 57.2 on Thursday), suggesting staffing optimization opportunities for weekend coverage.
Bimodal Hourly Pattern
Dispensing shows two daily peaks: late morning (11–12) and evening (18–20), indicating both lunch-break and after-work pharmacy visits.
Learnings & Takeaways
Pre-processing data into JSON at build time eliminates API latency and enables instant dashboard rendering — critical for executive-facing tools.
Multi-granularity data (hourly → monthly) serves different stakeholders: operations teams need hourly patterns, while executives need monthly trends.
Interactive filters are essential — static reports can't answer the follow-up questions that always come in stakeholder meetings.
Pharma data has strong seasonality that must be accounted for in any demand forecasting model. Simple year-over-year comparisons can be misleading without seasonal adjustment.