Select Page
Business Intelligence for Medical Practices

Business Intelligence Projects

Though I teach a lot of Excel classes and record a lot of Excel Videos, I spend most of my time writing code in Microsoft SQL Server to mine PM and EHR data for medical practices.  Pivot Tables and the other Excel tricks I teach are terrific, but you need to have access to your practice data to use those tools effectively.  Exporting your data to Excel, cleaning up all of the extraneous headers and footers, creating your analysis, and then starting over again from scratch the next time you need a report takes way, way too long.  I pull data from a wide variety of PM and EHR systems for practices across America and automatically deliver that data via email, dashboards, web pages, and spreadsheets.  All you need to do is open the spreadsheet, email, or web page to get fresh data, every time.  One well designed Pivot Table can replace dozens and dozens of canned reports.  Getting an email to tell you there’s a problem without even having to open a spreadsheet is even better.  Here’s a sampling of custom projects I’ve worked on.

Use Cubes to See Millions of Rows

These three Excel spreadsheets are connected to cubes I built.  The Billed Charges cube has over 2.5 million charges over 10 years.  The Appointments cube tracks over 1 million appointments, and the Payments cube has detailed payment information for over 4.5 million transactions.  Even with all that data, the spreadsheets are very small and load very fast.  When you need to see millions of rows of data in Excel, cubes are a great solution.

Potential Missing Procedures

This email comes every two weeks to a billing manager.  The logic tests every post-op bill in the past year and makes sure that there is a procedure billed within 90 days of the 99024 code.  The 99024 code won’t pay if the patient is in the global period, but the 99024 indicates that a procedure should have been billed.  The report also catches E&M visits that could have been billed because the patient was out of a global period but were inadvertently coded as 99024.  This is one of several automated tests the practice runs to ensure that every claim that can be billed is billed.


That picture might look like something’s wrong with my web page, but it’s XML code to submit to the AQI registry.  I helped a large anesthesia practice pull data from their PM system and a separate QA database to submit data to the AQI registry in their required XML format.  The process includes error checking and logic tests to make sure the PQRS and ASA measures are submitted accurately and completely for each provider.

Collection and Production Summary Dashboard

I developed this custom dashboard for a midsize group with a CPA as their CEO.  They were very focused on trends in collections, production and RVUs by provider, specialty, location, and more.  We look at trends for the current month, the current 6 months, the prior 6 months, YTD, and prior YTD.  The dashboard automatically highlights variances and concerns for the practice to focus on.  The dashboard is available on an internal website and is also delivered automatically via email as a PDF each month.  We’ve also programmed the dashboard to be sent to physicians with their specific data each month.  There are filters to only show new patient referrals by provider, location, procedure, insurance class and more.

New Patient Trends Dashboard

Are you concerned about where your new patients are coming from?  Which providers and groups are increasing the number of new patients and which providers and groups are sending fewer patients?  This analysis is a page from a comprehensive custom dashboard that tracks those trends with the last month, the last 6 months, and the date of the last new patient referral.  It took some custom coding behind the scenes to group the referring physicians into groups because the PM system didn’t have the data we needed, but now we can send trends by both provider and group.  There are filters to only show new patient referrals by provider, location, procedure, insurance class and more.  This dashboard is available on an internal web site and is also automatically sent via email month.

Unpaid Secondary Claims

Give your accounts receivable staff tools to help them do their job more efficiently and effectively. This automatically refreshing spreadsheet pulls all secondary insurance claims filed more than 30 days ago that don’t have any additional insurance payments after the file date. Staff can quickly sort by the biggest secondary balances to quickly work on the claims that matter most or filter by insurance carrier to work all claims from the same payer at once.

Appeal Deadlines

Here’s another tool to help your billing office improve their numbers and your practice’s net collection percentage. This automatically refreshing spreadsheet captures claims with no insurance payment and shows the ticket/encounter number, patient account number, insurance, procedure code, charge amount, entry date, appeal status, and more. The critical column is the far left column, which reports the number of days until the appeal deadline for that payer. Based on this information, the billing office may appeal a $500 claim which is about to expire before working a $5,000 claim that won’t expire for several months.

Low RVU Visits

This custom report was developed for an orthopedic practice with physical therapy. The practice administrator wanted a daily email that showed PT visits with less than 1.5 work RVUs sent to the PT department manager and practice administration. The email tracks those low RVU visits by therapist and encounter to make sure that the physical therapists are properly documenting visits. We also send a separate email that notifies PT of upcoming visits that will require a progress note/other documentation.

Expiring Authorizations

This detailed table tracks expired authorizations for upcoming appointments with information for pre-auth staff to act on. The spreadsheet can run based on the number of visits authorized or an expiration date. Providing this type of information to your staff allows your clinic to manage pre-authorizations for more providers with fewer clinic employees.

Charge Entry Productivity

Are you concerned about productivity and benchmarking for your billing staff? Here’s an example of an automatically refreshing Pivot Table that shows the number of procedure codes entered by staff member by day, filtered for an entire month. It’s easy to see which staff members are most productive and the times they are least productive.

Which Staff Members are the Most Effective at Collecting Patient Balances?

This automatically refreshing Pivot Table tracks outgoing patient collection calls by the staff making the call. It’s easy to see how many outgoing calls each staff member makes monthly. The Pivot Table also tracks the dollar amount of collections by staff member. The logic in the data warehouse tracks all payments made within 20 days after the call and attributes those payments to the staff member making the call. Managers use this data to decide who to assign to make outgoing calls and who to assign to other tasks in the billing office.

Who’s Where

This custom spreadsheet reads Centricity appointment schedules and allows users to track which providers are in which location. Created a second spreadsheet that uses the providers’ schedule to track and schedule support staff based on the providers’ locations. I also added a custom routine that summarizes each supporting staff member’s schedule for distribution, saving the scheduling staff hours each week.

Visualize Upcoming Appointment Availability

This report was designed by an orthopedic practice. About a third of their patient appointments don’t involve a request to see a specific physician, so the practice needed a report by body part to show appointment availability in the next couple of days for physicians who specialize in that body part. The practice can then build logic behind the report to support or emphasize specific physicians. For example, the logic could help a new physician fill their schedule or push patients to a specific specialist.

Save Money by Combining PM and EHR Data

This report was designed for an orthopedic practice who was remodeling and expanding a major clinic location. The issue was whether to build 3 or 4 x-ray rooms. The process started by changing the EHR form to track patients who would need an x-ray at their next visit, including body part, the type of views, and whether a cast should be on or off during the view. This report combines that EHR data with upcoming appointment data from the practice management system so that the x-ray technicians can look at the day’s schedule and know which patients will need an x-ray and the details of that x-ray. The x-ray staff has been able to use that data to do 50% more x-rays in a three room configuration, saving the practice the cost of a fourth x-ray machine, shielding a fourth room, and the related staff costs. Giving staff better tools to be more efficient has saved this practice hundreds of thousands of dollars.

Missing EMR Billing

Are you worried about the consistency of your billing interfaces? Do you stay up at night wondering if everything your providers wanted to charge actually was charged? This report looks for specific codes charged in the EHR that were not charged in the practice management system. Teach the data warehouse the criteria to look for and get an easy to use spreadsheet that tells you exactly which claims to focus on.

Compliance Reports

Are you and your providers frustrated by the never-ending list of compliance reports? Are you especially frustrated when your system already has all the information but can’t generate the report in the format you need? Rather than create the report manually, this example uses SSRS to automatically generate the report based on the date parameters entered at the top of the page. The report is much more reliable and much faster than manually generating the report. What compliance reports could save your practice time?

Give Your Nurses the Clinical Data they Need

Are your nurses overworked? Do they find it hard to keep up with the increasing documentation requirements in your practice? Use the data you have to make their lives easier and more productive. This dashboard is designed for nurses who support pain doctors. They use the dropdown menus at the top to choose specific providers and a date range for upcoming appointments. The SSRS dashboard automatically pulls all future appointments in the date range and checks each patient chart for documented compliance with narcotics rules. Patients who are missing required components show a blank in the column and compliance areas about to expire light up in bold to notify the nurse that something (a narcotic contract, urine drug screen, etc.) needs to be addressed before the patient’s appointment. The dashboard saves the nurses from checking each upcoming patient chart. Having all of the documentation in place makes the provider’s visit more productive.

Daily Email Catching Potential Problems with Upcoming Appointments

I analzye all future appointments and send an email every weekday morning to a practice showing potential appointment problems so the clinic can contact patients and fix things proactively. The email shows patients potentially seeing the wrong provider, patients who may have out-of-network insurance issues, inactive patients with an appointment, patients who haven’t paid a deposit, and much more. The appointment exception rules are customized for the practice and can be modified any time the practice finds an appointment they need to watch for. The email can be routed based on the appointment exception, provider, or location to make sure the staff responds promptly.

Daily Email Charting Tomorrow’s Front Desk Activity

Would you like to see the future? This clinic has their front desk staff categorize each patient appointment (new patients, patients with insurance changes, benefits verified, etc.) as an appointment type. I chart those appointment types in an email sent each weekday afternoon by location. Supervisors can then move staff to locations to fill surges in front desk demand. Staff can also call patients the day before to come in early to make sure the clinic runs smoothly. Similar reports can be done to schedule departments like imaging, lab, or casting. If the clinic staff can see that imaging will be busy later this morning, they can take patients to imaging sooner and/or schedule extra staff to provide quality patient care without delaying the rest of the clinic.

Daily Email With Next 5 Open Surgery Appointments by Location

This report is an example of a surgery schedule that is emailed to a specific sub-specialty scheduling department every day. The email tracks the five next surgical appointments by location and is customized to only select specific surgical appointment types. Even though surgery schedules change throughout the day, this report gives a quick snapshot of where there is availability. Equally important, staff can quickly see where the is not availability and work to provide more surgical appointments as needed. Since the scheduling staff see this summary every day, they develop a good sense of what is normal and when the schedule needs to be adjusted. If a specific sub-specialty is trending too far out in the future, managers can see those changes and plan to add capacity.

My Data’s in the Cloud. Can you Help Me?

If your data’s in the cloud, it can be much harder to get Business Intelligence and do data mining. You’re often limited to canned reports if you’re stuck in the cloud. If you can get a daily or weekly download of data as an Excel, csv, or txt file, I can upload the data into SQL Server and give you email, dashboards, Pivot Tables, and more. This flowchart is from a project I did to help a Washington state group import and then analyze their data. I can do similar projects to import data from other, outside sources so that you can combine data in one place for analysis. I helped a group in Texas import pathology data nightly and link the data with the appointments in the practice management system so they can make sure pathology specimens are analyzed before the patient’s next appointment.