Medtrackpro insights

MedTrack Pro is an integrated data analytics system for managing pharmacies and clinics. It bridges clinical data (patients, doctors, prescriptions) and operational data (inventory, sales) into a single relational database to generate actionable insights.

Technologies Used

SQL Server — Relational database with 11 interconnected tables

Python (pandas, matplotlib, seaborn) — Data analysis and chart generation

SQLAlchemy + pyodbc — Connecting Python to the database

Interactive Dashboard — Interactive HTML dashboard built with Plotly.js

Data Structure

The database covers two core dimensions:

Clinical Data: Clinics, doctors, patients, and prescriptions (60 patients, 7 doctors, 150 prescriptions)

Operational Data: Medications, pharmacies, inventory batches, and sales (10 medications, 81 inventory batches, 715 sales transactions, 1,786 sale line items)

Core Analyses

Expiration Risk Classification — SQL CASE logic categorizes each inventory batch into 4 states (Expired / Critical / Warning / Safe) based on remaining days.

Inventory Health Assessment — Compares current stock levels against the reorder point for each medication (Healthy / Watch / Reorder Now).

Sales & Revenue Analysis — Daily revenue trends and top-selling medications.

Inventory Distribution — Heatmap showing stock quantities by branch and category.