Insights from 11 years of MGH Hospital Encounters [Finalist]

Goal

The task was to play the role of an Analytics Consultant, asked by Massachusetts General Hospital (MGH) to build a high-level KPI report based on a subset of patient records. The purpose of the report is to help a hospital's executive team quickly understand its recent performance in key areas. The dashboard should scale to accommodate new data over time, and the CEO has asked to summarize any insights from the sample provided.

The data consists of synthetic records from nearly 1’000 MGH patients from 2011-2022, including information on patient demographics, insurance coverage, and medical encounters and procedures. For example, each row in the encounters table includes details such as the start and end date of the encounter, the total cost, the type of encounter, and the insurance coverage.

Given the assignment, I decided to create a two-page report:

• The first page is a high-level KPI overview for the executive team that scales with new data
• The second page is an explanatory overview of the provided data sample, offering recommendations to the executive team.

Data Cleaning

Minimal data cleaning was necessary. Some null values from categorical data were easily filled with suitable names, while the rest of the data was well-recorded, with no misspellings or missing values.

Report Structure

The assignment this time was wonderful, allowing me to create two reports in one:

Because the report is for the executive team and should consist of high-level KPIs, I decided to make the first page very simple, featuring just five KPI’s:

• The count of admissions
• The average duration per visit
• The average cost per visit
• The percentage of costs covered by insurances
• The occupancy of the hospital beds

This approach allows executives to receive the most relevant information in just a few seconds.

I chose to only focus on encounters because they include all information, from simple check-ups to complex operations, providing an unfiltered overview of the hospital.

The only interaction on this page is the month and year filter, enabling executives to view monthly, yearly, and year-to-date performance if the year is incomplete. The chart titles and texts change accordingly.

Four of the Five KPI’s follow this structure:

• The value for the selected time period (e.g., average cost per visit)
• To provide context: The comparison value for the previous time period (e.g., average cost per visit last month)
• To visualize the difference: The delta between these two values
• A bar chart showing the value for the selected time period (first bullet point) broken down by encounter class vs. previous time period (e.g., average cost per visit per encounter class vs. average cost per visit per encounter class last month)

The fifth KPI, hospital bed occupancy, is visualized in a column chart (with zero space) to show if capacity utilization is at its limit. Since the number of hospital beds was not provided, I defined the maximum bed capacity as three. I also decided encounters with class inpatient that last more than four hours are considered bed utilization. The capacity utilization is calculated with the following measure:

Capacity Bed Utilization =

VAR currentDate =
    MAX(Calendar_Table[Date])

VAR cumulatedValue =
    CALCULATE(
        [Count Admissions],
        encounters[ENCOUNTERCLASS] = "inpatient",
        encounters[Duration Encounter mins] > 240,
        ALL(Calendar_Table),
        encounters[Date START Encounter] <= currentDate,
        encounters[Date STOP Encounter] > currentDate || ISBLANK(encounters[Date STOP Encounter])
    )

VAR result =
    IF(
        cumulatedValue = BLANK(),
        0,
        cumulatedValue
    )

RETURN
result

This measure calculation also allows to reflect capacity utilization for new data, where the encounter is not finished yet.

The second page presents three major considerations for the executive team based on the provided data visualized as an Explanatory Dashboard:

• Five of the nine recorded insurance providers have not paid a single dollar for their customers
• In nearly every month over the last ten years, more than 50% of all procedure costs were related to pregnancy and Atrial Fibrillation
• Patients undergoing these two procedures are often uninsured or have insurance providers known not to cover their costs.

Design

KPI Overview Page

The KPI Overview Page primarily uses a turquoise tone, reflecting MGH’s brand color. The bar charts for the KPIs Count Admissions, Average Duration per Visit and Average Cost per Visit have no predefined goals. The KPI for % Cost covered by Insurance has a self-set goal of covering at least 45% of expenses; bars below this threshold are highlighted in red. The same applies to Bed occupancy, where overcapacity is shown in red.

Explanatory Analysis

The Explanatory Analysis page has its own colors, generated with a color palette tool. The Gestalt principles of visual perception, primarily Proximity and Similarity, are used to enhance visual comprehensibility. Each visual starts with its main message in plain text for clarity. The column chart of the share of procedure cost shows all years to visualize the recurring high amount of costs related to Atrial Fibrillation and Common Pregnancy. A little hack with the datatypes I learned from Wyn Hopkins allowed me to display all years without any scrollbar.

Insights

The assignment also includes four questions, answered here:

• How many patients have been admitted or readmitted over time? Total Patients: 974, Readmitted: 854 (88%), Average 2’500 patients per year
• How long are patients staying in the hospital, on average? Average stay: a little over 7 hours, skewed by outliers. The median is 15 minutes.
• How much is the average cost per visit? Average cost: $3,600, skewed by outliers. The median is $280.
• How many procedures are covered by insurance? 68% of procedure costs are covered by insurance (however, only 31% of overall encounter costs are covered)

Especially interesting was the general univariate and multivariate analysis of the dataset, which provided additional insights for the Explanatory Dashboard. I decided to communicate the cost aspect only, as it might be the most important for the executives.

Two additional interesting topics might be:

• The 6-month rolling average of hospital admissions is stable at roughly 180-205, excluding special events like the "Administration of vaccine to produce active immunity (procedure)" because of COVID-19 in the first half of 2021, "Patient encounter procedure (procedure)" & "Follow-up encounter (procedure)" in early 2014 as well as the frequent "Telemedicine consultation with patient" encounters not using physical hospital space.
• Around 40% of all recorded encounters are from people aged 78-90 years, mostly for ambulatory encounters.