Confessions of health data – 1

This is the first in a series of posts to illustrate analytic methods of getting insights out of healthcare data: Please download the companion workbook here.

Context: Medical practice managers are often requested to track physician productivity, whether against targets or identify areas of improvements, for example through practice dashboards. Below is an example of how this could be done, going from raw data, to valuable insight.

Step 1: raw data

When analyzing physician claims, practice managers can be provided data of each physician’s daily activities such as this: you are provided the physician’s name (or ID), service dates, procedure codes and number of visits for each of those procedure codes. So on January 8th 2018, doctor Billy Bob performed 99 times CPT 99201.

Let’s see how to dig into this data.

Step 2: add to/edit the raw data

From the post on procedure codes, you realize that 99201 means new patient office visit (simple) and that 99205 are new patient (complex) and is reimbursed at a higher RVU (relative value unit). To gauge productivity, you would need to know the number of RVU for each office visit CPT. If not already provided, you can search online to find the corresponding RVUs for each office visit CPT. More complex cases 99205 has 5.82 RVU while 99201 the simple case has 1.24 RVU. Higher RVU CPTs are reimbursed more than lower RVUs.

Health sector dynamics: Physicians and medical practices often have RVU targets.

• RVUs allow you to analyze physician performance taking into account the complexity of cases he/she saw, which is much more nuanced and better at reflecting reality than checking visits alone.
• RVUs are often used as targets because they are comparable across different payors, e.g. 99201 might be paid higher by Medicare than would Medicaid, but the RVU 1.24 per code is consistent across payors, thus allowing productivity tracking without the complexities of payor mix.
• Different specializations also get reimbursed differently, e.g. internists get paid different amounts per RVU than would a cardiologist. Here, let’s assume these are all internists/Primary Care Physicians.

Next step, you use a vlookup formula (see [data] tab in the workbook) to look up for each CPT code what the corresponding RVU was. Next to that column, you calculate the RVU total per day for each line of data.

With the total RVUs per line, you can proceed to analyze the performance of the physicians present in the data.

Remember our goal is to analyze the performance of each doctor by looking at RVUs and visits.

Please subscribe to receive future posts.

Step 3: quick summaries

Using a simple pivot table, you summarize the RVUs and total visits by doctor. You see that John Smith performed the most amount of RVUs AND total visits. You then add a formula next to the pivot table RVU/visits (see [analysis] tab). This shows another interesting story, that even though Smith produced the most RVUs, Bob in fact saw the most complex patient cases and had the highest RVU/visit of the three doctors.

Valuable insight:

Billy Bob bills more per visit than does the other two doctors. Could John Smith and Peggy Sue be missing revenue by under coding? Without adding the RVU to the raw data and aggregating and then obtaining the RVU/visit, this insight would not have been obvious.

A neat trick with pivot tables is you can add formulas embedded in a pivot table. This has the benefit of having the formula dynamically calculate as you drag and drop content of the pivot table.

You can add formulas in pivot tables in the calculated fields tab as below.

This example will be developed further in future posts. If you have any questions thus far, feel free to ask me.