Week 1 – Difficult Data Analysis

A specific example for a difficult data analysis, querying, or scripting problem that you had to solve, and describe the process by which you solved it.

Were you successful in solving the problem? Why or why not? What did you do really well and what could you have done better?

Working in healthcare analytics can be quite challenging. The emergency department has several KPIs that are measured and compared to other organizations. One of the key performance indicators is admission order to ED discharge. For several years, the time stamp that was used was the arrival to the floor (bed) location. As I reviewed the metrics and time stamps, I realized that several hours could be included in the time, especially if the patient went to the operating room before arriving at the inpatient unit.

The challenge was how do we capture the correct date and time the patient left the emergency department as the source of truth to the time stamp. We determined that a custom UDF was needed to capture the specific ED discharge time stamp. Our EMR vendor, Cerner, needed to create a new time stamp. We also needed to develop a process to capture and source the metric in the CCL code to the SQL reports.

It took about six months to develop, program, and source the code for reporting. A new workflow was developed for the patients that were admitted to the floor, and the ED needed to discharge the ED visit but keep the inpatient encounter active. The new checkout process allowed the ED to discharge the patient from the unit as they moved to the floor, regardless if they went to the OR or another procedural area first.

The change has been relatively successful in capturing the correct date and time. However, it still has its flaws that we need to reevaluate. Suppose a patient’s arrival to the floor timestamp is before the ED checkout time. In that case, there is a possibility the checkout time is incorrect unless there was an accidental electronic move to the room occurred. In this situation, a more detailed audit of the timestamps is necessary.

Each week I am responsible for billing and compensation for our logistic drivers in our company. For reasons unknown to me, billing and compensation were processed in two places: in the proprietary TMS, we use and QuickBooks. The CFO used QuickBooks for his audits and finalizing our business for month-end and year-end statistics. The programmers built the mechanism for our software to talk to QuickBooks. We were off a couple of dollars here and there when things started. Then everything came online, and we began showing discrepancies of thousands of dollars. The programmers washed their hands of it because their code “worked.” I was given the task of getting to the bottom of it. I know the database. I know how it behaves. The queries started. The analysis started. Combing the code, I needed to determine the tables the programmers used. I also had to decide how items were coded as GL Codes to talk to QuickBooks. Presto. That was where the bulk of the issues was found. The employee who set up things in the TMS did not code them properly and were being imported into QuickBook incorrectly. Cost of Goods was coded as services; certain accessorial charges were coded as Courier Revenue.

I was successful in solving the problem. My Achilles heel is accounting. It took me a while to figure out what GL Codes were and how they married to the TMS. I am very good at analyzing data and using SQL. That is why I like being a data detective and finding solutions to problems by analyzing data. Fortunately or unfortunately, I have become the one everyone comes to for these types of issues.