Revolutionising Prescription Operations for a Multi-Facility Healthcare Network Through an Enterprise Analytics Intelligence Platform
Success Metrics
70 %
Reduction in manual cross-facility reporting time
85 %
Improvement in leadership visibility into prescription fulfilment outcomes across the network
3 x
Faster identification of operational bottlenecks at facility and channel level
Introduction
The project focused on building a unified prescription intelligence platform for a multi-facility healthcare network. Leveraging a purpose-built Snowflake data warehouse integrated with a comprehensive analytics application, the objective was to provide leadership, operations, and finance teams with a single, consistent view of the complete prescription lifecycle, from prescriptions written through to fulfilment outcomes and financial results, eliminating fragmented, manual reporting across the facility network.
Business Challenges
- Prescription and fulfilment data was siloed across individual facilities, making reliable network-wide reporting practically impossible
- No unified view of dispensing outcomes across channels; same-day fulfilled, subsequently fulfilled, and unfulfilled could not be tracked consistently
- Channel-level financial performance including revenue, cost, and margin by dispensing route required time-consuming manual data extraction and reconciliation
- Cross-facility performance comparison was not operationally feasible, preventing leadership from benchmarking sites or directing resources with confidence
- Drug and therapeutic category-level financial analysis was entirely unavailable, limiting formulary and portfolio decision-making
ARCHITECTURE OVERVIEW
The platform is built on a three-layer Snowflake data warehouse that enforces a strict separation between raw source data, business logic, and reporting outputs, ensuring every metric is calculated once, consistently, and served to all modules from a single verified source.
Architecture Flow:
- Raw Layer: Source prescription, hospital, drug, and channel data ingested in its original form. No transformation applied; full traceability to source preserved.
- Intermediate Layer: Cleaned, standardised, and enriched models with business logic applied once and dimensions aligned across all datasets.
- Analytics Layer: Purpose-built reporting views, pre-aggregated for consistent query performance, serving all seven platform modules.
- Interactive Analytics Platform: Seven modules drawing exclusively from the analytics layer, covering Executive Overview, Prescription Volume, Fulfilment Performance, Hospital Scorecard, Financial Performance, Drug Performance, and Regional Performance.
Solution
We designed and built Healthcare Rx, a seven-module enterprise analytics platform anchored by a structured Snowflake data warehouse. Each module is backed by a purpose-built reporting view and delivers role-appropriate intelligence to operations, finance, and executive teams. All modules draw from the same verified data foundation, ensuring that every team across every facility works from identical, consistent figures.
- Executive Overview: Real-time command centre presenting network-wide KPIs across the complete prescription lifecycle, covering volume, fulfilment rates, dispensing speed, refill patterns, and financial summary.
- Prescription Volume: Month-by-month trends tracking prescriptions written versus filled, same-day versus subsequent-day dispensing rates, and new initiations versus refill continuations.
- Fulfilment Performance:Channel-by-channel breakdown of all three fulfilment outcomes with stacked area trend analysis and unfulfilled rate monitoring.
- Hospital Scorecard: Comprehensive peer-comparison module covering every facility, including prescription demand, fill rates, turnaround time, unfulfilled exposure, and financial contribution.
- Financial Performance: Monthly revenue, cost, and gross margin disaggregated by fulfilment channel with profitability trend analysis and quarterly summaries.
- Drug Performance: Real-time command centre presenting network-wide KPIs across the complete prescription lifecycle, covering volume, fulfilment rates, dispensing speed, refill patterns, and financial summary.
- Regional Performance: Operational and financial outcomes by geography with multi-metric comparison to guide regional investment decisions.
Business Impact
Healthcare Rx gave the network a single authoritative view of its prescription operations for the first time. Operations teams could identify fulfilment bottlenecks by channel and facility without any manual data extraction. Financial teams gained channel-level profitability visibility that directly informed formulary and resourcing decisions. Leadership replaced time-consuming quarterly manual reports with continuous, self-service intelligence, accessible to every team from the same consistent data foundation.
Customer Feedback
“For the first time, every team is working from the same numbers. The visibility into fulfilment performance and margin by channel has fundamentally changed how we make decisions across the network.” Director of Healthcare Operations
