__STYLES__
This report is a part of the Data challenges held by Maven Analytics every month on their website.
Challenge Objective
For the Maven Hospital Challenge, I’m playing the role of an Analytics Consultant for Massachusetts General Hospital (MGH).
I’ve been asked to build a high-level KPI report for the executive team, based on a subset of patient records. The purpose of the report is to give stakeholders visibility into the hospital’s recent performance, and answer the following questions:
The Stakeholder: The CEO of the hospital. Tools used: Excel and Power BI
About This Project
Maven Hospital Challenge
This project offers an in-depth analysis of patient encounters, procedures, and financial performance at Massachusetts General Hospital. Utilizing Power BI dashboards for data visualization, delivers valuable insights into key hospital metrics, including total encounters, procedures, patient demographics, and financial claims.
The analysis uncovers trends in patient encounters and procedures, identifies the most frequent and costly procedures, and examines the relationship between payer coverage and total claims. Additionally, it explores demographic factors such as age, race, and marital status that influence procedure frequency and hospital revenue. These findings are crucial for strategic planning, optimizing resource allocation, and enhancing patient care services at the hospital.
1. Patient Encounter Status and Financial Analysis
Overview: This dashboard provides a comprehensive view of patient encounters, procedures, financial claims, and patient demographics at Massachusetts General Hospital. Key metrics include total encounters, procedures, patients, claims, and the mortality rate.
Key Metrics: - Total Encounters: 27,891 - Total Procedures: 47,701 - Total Patients: 974 - Total Claims: $102 million - Mortality Rate: 15.81%
Patient Encounter and Procedures Trending:
Patient Class Analysis:
Revenue by Encounter Class:
Claim Payers: - Top Payers: — Medicare: 24.28% — Medicaid: 16.31% — Humana: 3.49% — Anthem: 2.94% — No Insurance: 48.52% of claims are from patients without insurance.
Top 100 Patients: - Services Received: — Kimberly627: 1,380 encounters, 1,167 procedures (Renal dialysis) — Mariano761: 1,261 encounters, 713 procedures (Renal dialysis) — Shani239: 887 encounters, 694 procedures (Renal dialysis) — Other Notable Services: Electrical cardioversion and hospice care.
2. Patient Procedure Analysis
Overview: This dashboard focuses on the analysis of patient procedures, including the number of procedures, costly procedures, demographic breakdowns, and procedure frequency.
Top 10 Procedures:
Procedures by Age Group:
Procedures by Race:
Procedures by Ethnic Group:
Marital Status:
3. Organizational Performance
Overview: This dashboard evaluates the relationship between payer coverage and total claims, average coverage by gender, encounter classes, and revenue from encounters and procedures.
Encounter Class Analysis: - Average Hospital Stay (Hours): — Inpatient: 36.83 hours — Ambulatory: 9.48 hours — Outpatient: 5.88 hours
Revenue by Encounter Class:
4. Patient Outcomes
Top 20 Procedures by Volume and Revenue:
Key Observations: - Assessment of Health and Social Care: 4.6K procedures, the most frequently performed procedure. - Hospice Care Service: Generates the highest revenue, significantly peaking at around $36 million, despite having 4.1K procedures. - Depression Screening: 3.6K procedures performed. - Renal Disease Screening and Other Assessments: Each with around 2.4K to 2.9K procedures. - Medication Reconciliation: 1.4K procedures, generating around $6 million in revenue. - Auscultation of Fetus: 1.1K procedures, with lower revenue. - Catheterization and Bone Density Scan: Lower volumes (0.5K to 0.2K) and correspondingly lower revenue.
Insight:
Class of Patients Encountered
Key Observations:
Insight:
Number of Encounters per Hour Over the Week
Heatmap Key Observations:
Insight:
Conclusion
The dashboards provide a comprehensive overview of patient encounters, procedures, financial claims, and organizational performance at Massachusetts General Hospital. Key insights include the high reliance on ambulatory services, significant revenue generation from prenatal and follow-up visits, and the positive correlation between payer coverage and total claims. This analysis can help the hospital in strategic planning and improving patient care services.
Interactive report link
Here is the Github Repo of the project
if you like this blog please follow me for more.