What should hospitals work on to improve patient's perspectives on hospital care?

Maven Healthcare Challenge:

I am working as a Data Analyst for the American Hospital Association (AHA), a national organization that represents hospitals and their patients, and acts as a source of information on health care issues and trends.

As part of my role, I've been asked to analyze the Hospital Consumer Assessment of Healthcare Providers and Systems (HCAHPS) survey for the last 9 years. The intent of the HCAHPS initiative is to provide a standardized survey instrument for measuring patients' perspectives on hospital care, and one of its 3 main goals is to "create incentives for hospitals to improve their quality of care."

I am tasked with evaluating whether the HCAHPS survey has been successful in accomplishing this goal by answering the following questions:

1. Have hospitals' HCAHPS scores improved over the past 9 years?
2. Are there specific areas where hospitals have made more progress than others?
3. Are there any major areas of opportunity remaining?
4. What recommendations can you make to hospitals to help them further improve the patient experience?

First, I looked at at the raw data, turning all the data into tables in Excel to make the data easier to navigate. To answer the questions, I was exploring, minimal data cleaning was required. In order to make a dynamic dashboard to help answer the questions of the challenge, I first made various filters using formulas. I filtered the MeasureIDs to show the various Measure (some of which encompass multiple questions). I associated the format of the release year into an actual year format. I made a filter for Top-Box, Middle-Box, and Bottom-Box answers, as well as filters for states, and number of facilities vs. response percentage for the states.

From the filters, I made dynamic tables with formulas to show dynamic line graphs showing Top-Box, Middle-Box, and Bottom-Box across the 9 years of the survey dynamically changing based on Measure. I also developed a table based on formulas to show dynamic line graphs across the 9 years, based on the Measure, showing the 3 box levels, based on a state selection. Formulas were also used for 2 map graphs, one with dynamic selections for Measure and box-level and the other showing dynamic number of facilities in each state or the percentage of responses returned.

The types of formulas used include: INDEX, MATCH, FILTER, VLOOKUP, MAX, MIN, UNIQUE, IF, IFERROR, COUNT, and, MAXIFS.

1. Have hospitals' HCAHPS scores improved over the past 9 years? No, all measures have either stayed relatively static or declined in the last 1-3 years.
2. Are there specific areas where hospitals have made more progress than others? Some measures increased initially, but then either remained static or declined.
3. Are there any major areas of opportunity remaining? Most areas have opportunities for improvement, however, the measures with the greatest declines are Communication about Medications, Care Transition, Overall Hospital Rating, and Willingness to Recommend the Hospital
4. What recommendations can you make to hospitals to help them further improve the patient experience? My recommendations include improving care, protocols, and staffing to improve in particular these Measure areas: Communication about Medications, Care Transition, Overall Hospital Rating, and Willingness to Recommend the Hospital.

Since I discovered that all of the measures Top-Box responses declined in the last few years of the survey, I reasoned that a large factor (given there is a lag between when the survey is conducted and when it is released), that the decline is likely due to the COVID-19 pandemic and the increase in hospital volume and limited staffing levels during that time and since.

My recommendations include improving care, protocols, and staffing to improve these Measure areas in particular: Communication about Medications, Care Transition, Overall Hospital Rating, and Willingness to Recommend the Hospital. If possible, higher staffing levels may improve patients' perspectives on hospital care.

To view the dynamic dashboard: Maven Challenge HCAHPS Project