Loading the data into Gooroo Planner

Loading the data into Gooroo Planner

12/10/2017
by Rob Findlay

The first thing we are asked by any information professional worth their salt is: how do we get the data into Gooroo Planner? And how difficult is it?

The short answer is: you upload the data to our website in anonymised patient-level CSV files. You can usually create queries that generate the full set of data files in less than a day (building them properly, for eventual automation).

But that might sound implausibly easy to you, so here is a somewhat longer answer with a bit more detail in it.

The process

What happens is that we will come on-site and spend a day with your best information analyst, writing queries that extract the necessary first-cut data. This support is inclusive with your Gooroo Planner licences, and we get you off to a flying start using the starter SQL code and documentation on our Publications page.

It doesn’t matter which PAS or databases you use, because the data requirements are pretty standard stuff. At the time of writing (and touching wood) we have never yet failed to extract the necessary data, and our experience includes both common and unusual PAS systems and a good spread of databases including SQL Server (the most common), MS Access, and SAP Business Objects and Crystal Reports. (We do not recommend MS Excel for this purpose because it does not lend itself to eventual automation.)

If your data is quite well organised in a data warehouse then it would typically take just a few hours to create good data queries producing first-cut data. If you have an unusual PAS and/or you are working directly from its raw data tables then it might take longer than a day, in which case rest assured that we will continue supporting you until it is ready.

Activity and waiting list data

You can find an overview of the inputs and outputs for Gooroo Planner here.

Gooroo Planner accepts both anonymised patient-level data and statistical data. People usually go for mainly patient-level data because it is much easier to prepare, although you get faster performance with statistical data. There are three types of patient-level data files, all in comma separated value (CSV) format:

activity (for elective and non-elective patients; usually covering the last 13 months, or longer if you prefer)
additions to the waiting list (again usually 13 months)
snapshots of the waiting list (usually one recent, and one 12 months older)

You can adapt your files to suit the way your data is organised. So if your outpatient data comes from a different source from your inpatient/daycase data (which is usual) then you can prepare separate queries for each and then there will be 6 patient-level data files in all. And if it’s easier to put each waiting list snapshot in its own data file then that is fine too.

Different PAS systems hold different waiting list movement data in different places, so the model lets you use either the activity or the additions files to capture information about removals, cancellations, and the clinical priority of waiting list patients.

After uploading all the patient-level data, you can use small statistical files (which we’ll help you create in MS Excel) to set up high-level assumptions such as clinical pathways and the waiting time targets you want to achieve. And if you have particular assumptions that you want to try, then you can chuck them in by the file-load for bulk scenario testing.

What we have outlined so far is enough for detailed forecasting and trajectories of demand, activity and waiting lists/times. But we haven’t covered capacity yet, so let’s look at that now.

Capacity data

When it comes to capacity performance data, usually beds are straightforward because each patient’s admission and discharge dates, and lengths of stay, can simply be included in the activity file for the model to measure. (You can provide bed occupancy assumptions in the separate high-level statistical file, and the best way to determine the right bed occupancy is using Gooroo Beds.)

Your theatre data will typically come from a separate IT system, and it is usually easiest to extract it as a separate file. (Again it doesn’t matter which theatre system you have, and we have successfully worked with several including Opera, Ormis, TheatreMan, SurgiNet and Bluespier). Gooroo Planner will automatically measure your average times per patient, theatre utilisation, and other theatre metrics at the chosen level of detail (even across mixed theatre lists).

What you may not have is meaningful patient-level clinic data. If you are lucky enough to have real-life timestamps of patients entering and leaving clinic then you are in a very exclusive club. Even data about the intended appointment start times and durations is not widely available in clean form. So usually you will provide statistical assumptions about average times per patient and the expected clinic utilisation for each service, or simply content yourself with activity as a measure of clinic capacity.

At this point you might be wondering: what about the actual capacity of the hospital? – the physical numbers of beds in each clinical area, and the theatre and clinic session time available? You can certainly do it, but my advice is not to focus on that on day one – it can all be fed in later, as operational managers get more involved in the modelling.

The tricky bits

There are usually some clinical areas that don’t conform to the normal rules, and here a bit of extra care needs to be taken with the data. (This would apply to any planning approach, by the way, and not just Gooroo Planner.) Typically we wouldn’t try to solve all these issues on day one, but they are worth keeping an eye on and the starter SQL code gives you a helping hand.

Endoscopy waiting lists and activity are often mixed up between gastroenterology and general surgery daycases, and at some point these will need separating out cleanly;
Eye casualty and fracture clinic are usually best separated out too;
If theatre data is provided separately from PAS activity then you may wish to create statistical data about the ratio of activity to theatre episodes;
…and other refinements which are explained in more detail here.

Nothing to prepare

Diligent information professionals like to be prepared, and we are often asked what needs doing in advance of the supported on-site data preparation day.

A realistic option is: nothing. And that is absolutely OK.

But if you want to have a go then we aren’t going to stop you – the starter SQL code and detailed documentation are all freely available in the Publications section of our website.

Here to help

If you have any questions about preparing data for Gooroo Planner, or would like to ask about anything else, then please get in touch and we will be happy to help.

And if you would like a free on-site demo of Gooroo Planner then it would be a pleasure to meet you and your colleagues – please get in touch using this online form.

Return to Post Index