Massachusetts General Hospital KPI Report

For this project I have 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:

• How many patients have been admitted or readmitted over time?
• How long are patients staying in the hospital, on average?
• How much is the average cost per visit?
• How many procedures are covered by insurance?

The dashboard will scale to accommodate new data over time, but the CEO has asked me to summarize any insights I can derive from the sample provided.

The Original dataset contained data from January 2011 to January 2022. However, as 2022 only had one month I only included the data up to the end of 2021. So, the report only shows information for complete years.

Patient ages are calculated to the last date in the dataset and not the current date. As we are reviewing information 2 years after the end of the dataset which will affect age groupings.

Despite one of the questions being to show patients have been admitted or readmitted over time. The report does not show admissions and readmission performance as I was unable to speak to stakeholders to clarify the rules of what encounters were an admission and what encounters were a readmission. Some encounters are marked as admit but there are many encounters spanning multiple days. If I received further clarification, then the report would be updated with the two KPIs using the correct rules. I did not wish to make an incorrect guess at the rules.

The data was provided in a set of csv files and did not require much work to make it suitable for data analysis. I separated the description columns from the transaction tables and created lookup tables. This created an optimal star schema data model.

The report comprises of:

Summary Page - A summary page showing key information from the report pages.

Hospital Visits Page - An analysis of the hospital encounters

Procedures Page - An analysis of the procedures the hospital recorded.

Patients Page - An analysis of patient demographics

Payers Page - An analysis of the medical insurance providers.

An icon-based navigation menu was created and is located in the top right-hand corner of each report page. Use this to navigate through the report.

Summary Page

This page contains a text summary and key visuals from pages within the report.

In the recent period, the hospital has generated a total revenue of $104.8 million from procedures. Of this, $40.4 million (38.6%) came from insured procedures, while $64.4 million (61.4%) was attributed to uninsured procedures.

The highest revenue-generating procedure was Normal Pregnancy, contributing $20.04 million to the total revenue. The year 2011 marked the lowest revenue with $4.4 million, while 2014 achieved the highest revenue of $12 million. A growth rate of 18.6% from the previous year.

Medicare emerged as the insurance provider with the highest number of encounters, accounting for 11,270 encounters or 40.7% of the total. The encounter class with the highest volume was Ambulatory, with 12,415 encounters. Insured encounters totalled 14,173 (51.22%), slightly exceeding uninsured encounters at 13,496 (48.78%).

The procedure with the highest average base cost was "Admit to ICU," with an average cost of $206,260. The year 2014 also stood out with the highest number of procedures at 6,291.

Among patients, those in their 90s represented the largest age group, with 216 patients (22.2%). Medicare was again the leading insurance provider for patients, covering 449 individuals.

In terms of gender distribution, 480 patients (49.3%) were female, and 494 patients (50.7%) were male. The encounter reason with the highest number of procedures was "Assessment of healthcare and social care needs," with 4,557 encounters, making up 9.6% of the total.

The year 2014 also saw the highest total claim cost at $12 million and the highest total payer coverage of $3.6 million.

Hospital Visits Page

The key insights for this page are that encounters generated $101 million in revenue which is on average a 10% year on year increase. Procedures generated $105 million in revenue. The discrepancy is due to procedure cost being calculated on base cost. Whilst encounter revenue is calculated on total claim cost. one encounter can have multiple procedures. The total procedure base cost may not be shown total the claim cost and would require discussions with stakeholders for further clarification.

Urgent care encounters raised $23M in revenue or 22.7% of total encounter revenue. The number of encounters where insurance contributed to the cost was 14,173 or 51.22% of encounters. White people visited the hospital the most with 19,315 encounters or 69.8% of the total encounters.

Encounters peaked in 2014 with $12 million in revenue raised and 2020 had a lower peak with $10.3 million in revenue.

Procedures Page

Procedures where insurance contributed totalled $40.4 million or 38.6% of the total procedure costs. Admit to ICU was the procedure with the highest average base cost of $206,260. Normal Pregnancy was the reason for the most revenue raised for procedures raising $20,041,802 in revenue. 2014 had the most procedures conducted with 6,291 procedures.

Patients Page

There are 974 registered patients in the provided dataset of which 353 have only used insurance to pay for treatment. Whilst 249 patients have never used insurance to pay for treatment. 372 patients have used both methods to pay for treatment. 783 or 80.4% of patients are non-Hispanic whist 191 or 19.6% are Hispanic. 680 or 69.8% of patients are white. People in their 90s are the largest age group with 216 or 22.2% of patients being in that age group. Most of the hospital patients (644 or 66.1%) live in in Suffolk County. Which is the county in which the hospital is located so is the most convenient for people who live in Suffolk County. Other counties are further away so visiting Massachusetts General Hospital may not be convenient as there could be other hospitals located nearer to them. Medicare is the top medical insurance provider with 449 patients using it for hospital treatment. It is noted that many patients have used more than one insurance company to pay for treatment. The most common procedure for patients is Assessment of health and social care needs with 4,557 or 9.6% of procedures.

Medical Insurance Providers Page

Medicare was used to pay for 11,270 (40.7%) hospital visits. Whilst 8,743 (31.6%) of hospital visits used no insurance. Assessment of health and social care needs raised $2M in revenue for the hospital. There is a substantial gap between the total claim cost and the coverage by the insurance companies. It would be beneficial to find ways to reduce or eliminate the gap. Reducing the costs to the patient would help but would require a substantial change for the hospital and would need to be thoroughly investigated. The insurance companies may be able to assist with increasing coverage it could be beneficial to discuss claim costs and reducing the gap with them.