Is your practice ready for Business Intelligence? I use SQL Server to help practices mine their existing data in PM, EHR, and other systems to get better information to run their practice. I also help organizations use Excel as a reporting tool for the data mining. Once you've seen your data in Pivot Tables, your practice will never be the same. When you're ready to start analyzing your data, here's how to contact me.
Here are some recent projects I've worked on.
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.
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.
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.
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.
Wrote custom code to analyze how many days until the third next available appointment for each provider. We chose the third next available appointment to smooth out unusual fluctuations due to appointment cancellations and other factors. The data is calculated every morning and stored in a table which is linked to a Pivot Table for analysis of trends by provider and specialty.
Created a custom spreadsheet that 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.
Designed an Excel table that automatically loads patient balances by bucket (0-30 days, 31-60 days, etc.) into an Excel table. The table also tracks upcoming patient appointments and locations so financial counselors can contact patients before their next appointment to confirm payment arrangements. The table can filter by patient balances, which member of the A/R team is responsible for the claim, aging buckets, upcoming appointments, and more.
Created a Payer Performance Matrix that compared a practice's top payers on a variety of factors, including receivables over 120 days, payments not a contracted amount, reimbursement per RVU, appeal rates, and more.
Linked specific appointment information from a practice management system to Excel and created a daily appointment report to send to a transcription group. The report pulls just the information the transcription company needs in the format they requested to ensure dictation isn't missed.
Produced a custom report for a medical billing company that showed billed charges by the month the claim was billed and collections by the month(s) when the payments on the claim were received. Their clinics can now look at each months' collections and analyze when the services were rendered that generated those collections. The billing company uses the report to show practices how efficiently their claims are being collected. Practices use the report to forecast cash flow.
Used a practice's appointment cancellation reasons to find and analyze patient no-shows. Created a Pivot Table to track no-shows by appointment date, appointment time, primary insurance, referral source, referring physician, patient's city and Zip Code, location, scheduled physician, and more. Discovered that morning appointments cancelled more often than afternoon appointments, confirmed that Medicaid patients were more likely to no-show than commercial patients and quantified the difference, looked at trends in no-shows by location, and much more. That practice now has an ongoing tool, not just a one-time report, to track and manage no-shows.
Took the same analysis described above and added a calculation of the time between the date the appointment was scheduled and the actual date of the appointment. Discovered that appointments scheduled 1 week out, 2 weeks out, etc. were much more likely to no show than other appointments. We charted the data and you could learn your 7 times tables from the spikes in no shows every 7 days. It turns out doctors were telling patients, "Come back in x weeks if it still hurts." For some doctors, x was 1 week. For other doctors, x was 3 or 4 weeks. The appointments were made, the patients didn't hurt, so the patients didn't show up. The practice is now changing their approach to reduce the number of no shows. I did a similar analysis for another group. You'll still see spikes, especially for weeks 3-8. Also notice that if an appointment is scheduled within 2-3 days, the no show rate is less. As soon as patients have to wait longer for an appointment, the no show rate jumps. Those first few days are a sweet spot for reducing no shows.
Designed a custom analysis for a group of anesthesiologists. They were negotiating overtime rates with a hospital and needed to know how many cases ended after 3:30 and how many cases ended after 5:00 but before the group of anesthesiologists' call obligations started. We started with a huge list of cases and then divided the overtime rates into two buckets. I created some pretty complicated Excel formulas to determine which cases hit overtime and how much overtime fell into each rate bucket. It took some fairly involved Excel functions, but at the bottom of the spreadsheet was some very valuable data about how much time was spent in each bucket. With that data, it was easy to apply a value to the overtime and prepare something to present to the hospital.
Reviewed a practice's dashboard and suggested ways to better automate and communicate the practice's information. I added a variety of Excel tips and tricks including conditional formatting, custom formats, and advanced formulas to provide more information more efficiently to the doctors.
Helped a group of anesthesiologists perform an in-depth analysis of hospital data to determine the first start time and the last end time of each provider each day. We used these times to calculate the total hours each provider spent at each location. We put this information into a Pivot Table to analyze total hours by location, by provider, by day of the week, by week of the month to track vacation, and more.
Produced a spreadsheet for an orthopaedic practice that had three automatically updating Excel tables. The first table tracked upcoming appointments that needed pre-certification. The second table tracked expired pre-authorizations based on either an expiration date or on a certain number of visits. Finally, we analyzed pending pre-certifications to make sure insurance companies responded timely and before the patient's next scheduled appointment.