In the world of healthcare business intelligence, patient-level profitability is considered an ‘advanced’ analytical tool. Not something that can be built in a day or a week. To produce patient-level profitability numbers that have any bearing on reality, you need a staggering amount of information about every single patient encounter.
At the beginning, we weren’t looking to measure profitability. We needed to be able to answer much more straightforward questions (How many x happened in y time period?). We found the data we needed to answer these simpler questions in our hospital billing system and our physician network electronic health record systems. System by system, table by table, we put the data into our data warehouse and got it flowing automatically. We started building models. We didn’t know exactly what we were building toward. We just knew we needed to answer questions for senior leadership.
We wanted to look good in front of our bosses. To do that, we needed to design our data models to help our analysts find the information they needed, find it quickly, and be confident it was accurate. Those data models became the foundation of our patient profitability model.
The purpose of this post is to share how we thought about organizing that foundational information.
(We did NOT purpose-build this foundation for patient-level profitability reporting. We had basic questions we wanted to answer about what was going on in our hospital and in our physician network. We built data models to answer those basic questions, and expanded from there.)
Imagine you’re starting from a blank slate. You know a patient was in the hospital. What do you want to know about their stay?
- WHO – Who was the patient? Who was the admitting provider? Who was the attending provider? If the patient had a procedure, who was the provider who performed it?
- WHAT – What was the patient’s diagnosis? What medical or surgical procedures did they have done? What labs and imaging did they have done?
- WHERE – Where did the patient come from? What nursing floor were they admitted to? What floor were they discharged from? Where did they go after their stay?
- WHEN – When was the patient admitted? When were they discharged? In between, when did all these procedures, labs and imaging happen?
With these kinds of questions in mind, we ended up with two levels of detail (two levels of data granularity, if you prefer the technical term): Encounters and Charges.
The Hospital Encounters data set answers questions about the patient’s whole hospital stay. Each record is one time a patient came to our hospital for inpatient or outpatient services. We know when they arrived, what condition they had treated, how long they stayed, and where they went after discharge. Using the encounters data set, we can find out:
- How many heart attacks we treated last year
- What percent of patients enter through the Emergency Room
- What percent of patients we discharge to their home vs. skilled nursing facility (SNF)
The Hospital Charges data set answers questions about specific events that happened within each hospital stay. Each record is one unit of service that showed up on the patient’s bill. We know what services we billed, what departments provided those services, and who ordered those services. Using the charges data set, we can find out:
- How many X-rays, MRIs and CT scans we did last year
- What percent of the time a certain provider orders the basic metabolic panel vs. complete metabolic panel
- How many colonoscopies we did in the medical procedure room vs. the operating room
Just as we built models to understand hospital billing (and the underlying activity the bills represent), we built parallel models to understand what was going on in our physician network.
The Physician Network Encounters data set answers questions about the patient’s whole billable encounter with a physician. Each record is one time a patient was billed for one of our provider’s services (whether it was an office visit or a bill for professional services done in the hospital).
he Physician Network Charges data set answers questions about specific services we provided within each billable encounter with a provider. Each record is one unit of service (one CPT code) that showed up on the patient’s bill.
These four data models helped our team of analysts answer thousands of questions for our healthcare system. The thousands of hours and hundreds of thousands of dollars we invested in building them are already paying off in the form of better decisions every day.
And because we built flexible models that could be expanded later, they became the foundation of an amazing suite of analytic tools, later known as patient-level profitability.
While you’re here…
