This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Announcing Alteryx + Snowflake | Alteryx and Snowflake make analytics and data science fundamentally easier. With the new integrated starter kit, you can push down data prep transformations and more into Snowflake for faster data quality and analytics output. Learn More
Collaborators: Diane Rossi MacKay, Dominick Kuljis, Ashley Baker, Don Stader
Overview of Use Case
The institutions that heal us can also unintentionally be the gateway into opioid abuse. Pain is one of the most common reasons for admission into the emergency department (ED) in the United States, and traditionally, opioids have been used as the primary way to treat acute pain in the ED. With the goal of improving appropriate use of opioids in EDs across Colorado, CHA developed key strategic partnerships with the Colorado Chapter of the American College of Emergency Physicians (Colorado ACEP), the Colorado Consortium for Prescription Drug Abuse Prevention, Telligen and the Colorado Emergency Nurses Association (Colorado ENA). Together the organizations formed the Colorado Opioid Safety Collaborative to improve opioid safety in Colorado EDs. The first project was the Colorado Opioid Safety Pilot Project, with an aim to reduce administration of opioids by 15 percent, measured in morphine equivalent units (MEUs), over the 2017 six-month pilot, as compared with the same six-month baseline period in 2016.
Describe the business challenge or problem you needed to solve
In order to measure success, CHA collected monthly data from all participating EDs. The challenge was to accurately process, analyze and report the data back to the hospitals through interactive Power BI visualizations that were updated with every new submission. Every hospital EHR (electronic health record) system can be different, leading to no standardized drug names, routes, dosing structures or even date formats. We were also trying to link the data to a separate claims database to provide additional information on diagnoses of the patients treated at the ED.
Describe your working solution
A series of six linked workflows provided the solution.
Step 1: Prep the aggregated ED counts for integration with the EHR data (to provide a denominator to standardize rates).
Step 2: Structure the EHR data into a standard columns, parsing out needed variables with consistent identifiers for each participating hospital location and date formats. The parse tool, multi-row formula tool, and datetime parse tool were essential here. Clean and verify the data.
Step 3: Join submitted data to CHA Discharge Claims dataset. Check unmatched records against any records with discharge dates from the three previous days of the unmatched records' discharge dates. I love DateTimeAdd 🙂
Step 4. Process finalized raw data to calculate all needed variables for analysis and visualization. Yay for the summarize tool!
Step 5. Produce data files for data accuracy checks from team and the hospitals.
Step 6: Process HCAHPS patient satisfaction scores to ensure that pain control and overall satisfaction are not reduced during pilot period.
I also designed several Power BI reports to share the data back to the hospitals and to our team. After all six workflows were complete, I loaded the processed data into those Power BI reports. None of the individual data problems I solved in Alteryx were in and of themselves hugely challenging--any proficient Alteryx user could have done this. But I think there were two key elements that Alteryx offered: firstly, Alteryx lets me do this kind of in-depth analysis even though I am a scientist, not a programmer. As such, I knew exactly what kind of data and analysis we needed at the end to create an impactful program--and Alteryx provided it. Secondly, the significantly reduced amount of time it took to build and manage the flows made this entire process possible. Essentially every month, a hospital would submit something in the data that broke the flow, but I could hop in and easily identify the problem, and fix it.
Describe the benefits you have achieved
EDs during the pilot period administered 36% fewer opioids (as measured by morphine equivalent units) than during the baseline period. The data were able to show how administration of opioids varied by condition, including an exploration of the five clinical pathways discussed in the "Colorado ACEP 2017 Opioid Prescribing & Treatment Guidelines" used in the pilot hospitals to reduce reliance on opioids. Furthermore, the robust findings helped demonstrate the success of the pilot project to a level that has received national attention. Hospitals across Colorado, and now the nation, are working towards implementing the ACEP guidelines to reduce ED opioid administration in their own facilities.