Your IT staff possess the data that are the key to your operational success. I have always believed that canned reports can only take you so far, and that you need raw data to have the flexibility to analyze your operations in ways that are meaningful to you.
You don’t always need fancy and expensive software to analyze your data. Pivot tables in Excel and simple queries and reports in Access often suffice.
So how do you obtain raw data in a format that you can use in Excel or Access? Here’s what to ask your IT staff for:
- Ask for the data export to be in Excel or CSV (comma separated value) format.
- Data output should be “pivot table friendly”. Most IT folks know exactly what you would mean by that. Don’t just ask them for an excel export.
- One row per record; each row should be unique, and preferably have a unique identifier (e.g., a visit number or accession number)
- Data should be stacked – every column is a field, with the same type of data repeating. For example, instead of three columns with the headings “First shift”, “Second shift”, and “Third shift”, ask for one column with the heading “Shift”, and the data within can state, First, Second, or Third.
- Dates and times should be combined in one cell, whenever possible. For example, instead of having the date in cell F2 and the time in G2, it is easier to have both date and time in cell F2. You can always separate them afterwards, but it is easier to do math calculations when both are combined.
- Make column headings descriptive. Instead of “TurnAroundTime”, it should state “OrderToComplete” or “OrderToResult” to remove ambiguity.
- Make sure you fully understand what every field represents. For example, what does “Scheduled Time” mean? Is it time that the exam was scheduled ON, or time exam was scheduled FOR. Also, Patient Status is a changing field. Do you want patient status at time of order or time of discharge. Neither is wrong, you just need to specify what you want.
- Beware of averages; they are heavily influenced by outliers. In fact, don’t ask for averages-calculate them yourself.
- Make sure that fields that have numeric values are in a numeric, not text, format. For example, if you are looking at RVUs, make sure they are numeric so that you can sum and average them, etc.
- Make sure the zip codes field is in text format. This way you don’t lose any leading zeros.
By following the tips above, you will save yourself a lot of time and frustration, and your analysis effort will be more productive.