6 steps to building a medical practice dashboard

Practice managers and division administrators are often tasked with reporting on how a medical practice is doing. This can be a daunting task, especially for those who are not yet analytics gurus. In this post, I describe the 6 steps to building a practice dashboard.

1. Understand the aim

Why you’re doing the report dictates what data you need and how you build the dashboard.

• Let’s say you’re tracking doctors’ productivity against their targets (e.g. Relative Value Units (RVUs), patient volume), you will need visit, patient counts, total RVUs etc per doctor over say monthly.
• If you’re tracking quality metrics say % of seniors who received flu vaccine (a HEDIS measure), you will need to know how many 65+ patients each doctor saw and how many of those had their flu vaccines, within the last year.

To derive actionable insights, you should understand what the tools each doctor has to make the changes necessary. Reporting on things no one can do anything about just frustrates people…

If you don’t have a clear sense of what the goal is, stop now and figure that out. Next steps will be inefficient if you don’t understand your objective for analysis.

Keep in mind that whoever asked you for the analysis might not have a clear question in mind or may be wanting something else unknowingly. Therefore, it’s always good practice to imagine what issues might trouble him/her. You will add more value this way.

2. Get the data

You will need some way of extracting the data you need, from the data management platform, such as through EHR user interface, Business Intelligence software, pulling directly from databases using SQL.

• Obtain the necessary data elements, including practice name, dates/week/month, doctor name, visit count, patient count, RVU total (last 3 are measures summed over prior 3 columns).
• Specify necessary filters to limit data extract to only what you’re interested in. e.g. time frame, practice name, Medicare only, patient age range etc. Filters usually also make your queries run faster.

Beware of naturally occurring data features such as seasonality or time lags for information to pass through various steps to get to you.

It’s also a good idea to develop a knowledge of where the data comes from, how it was generated. You will know what to look out for and can anticipate issues, e.g. a doctor who always does poor coding, some data feature that is auto-generated and meaningless.

3. Clean, transform the data

Once you receive the data, don’t assume it’s right… Check that the numbers look reasonable. e.g. check month on month patient counts are consistent with previous reports; check there are no unrelated practice/doctors; look for blanks and wild characters such as “#,%” which could indicates underlying data was wrong before you pulled the extract.

Once you’re reasonably satisfied that the data is correct, you will likely need to transform the data before doing analyses. Data transforms include: appending to previous datasets, converting dates to year/month, converting ICD9 to ICD10s, adding age ranges for patients. Doing these transforms allows you to more easily identify actionable insights.

I’ll describe data cleaning, and data transforms in future posts. Subscribe so you don’t miss out.

4. Analyze the data

Analysis of the cleaned and transformed data involves further aggregation and exploration. For medical practice reports, MS Excel should suffice. You will initially explore the data, probably using pivot tables. Pivot tables are fast and relatively easy to use, but you need to be aware of a number of issues with using pivot tables. My personal preference is to use formulae such as sumifs/countifs, as these tend to be more automated and less error prone.

Some issues to beware of when using pivot tables:

• include all/latest data
• getting unique entries where necessary
• filtering correctly, based on updated data elements
• formatting is easily messed up
• appropriately structured data is needed to use built-in pivot calculations, % of column/row, min/max/avg etc

Let me know if you’re interested to learn how to use pivot tables to create practice dashboards.

When you’re analyzing the data, you may be

• checking individual doctor performance against benchmarks,
• compare performance of doctors to their peers,
• tracking doctors performance over time,
• identifying bottlenecks, such as lags in lab result feedback, or long wait lists for specific doctors.

A clear focus on why you’re doing the analysis and your curiosity will guide you to useful and interesting findings.

5. Present the data

Whatever way you deliver the report (verbal, PDF, email, PPT, HTML), it’s a good idea to:

• check before you send – for accuracy, and obvious typos
• be concise – say what you need to say, no more, no less
• explain enough of what you did so they are confident of your findings, but don’t bore them with detail
• deliver actionable findings in 2-3 sentences – don’t expect people to sift through the numbers themselves to get the answers…
• use color/font to highlight

6. Iterate

Whatever you’re doing can always be improved upon. Chances are your initial attempts will be very manual, requiring lots of copy paste, hard coded numbers (instead of formulae) and contains errors. I always try to remove the weakest link (me) from the analytic steps.

Keep tinkering on your analysis, taking feedback into consideration. Write enough documentation so you can be reminded of what you did next time to pick up this analysis.

Check out my course on analytic best practices here.