Leveraging prescription data to improve our medication search
For a long time now, we’ve had a search tool for medication that is as simple as possible. Care managers use it as part of our medication management software to find and select medications for their care recipients from the NHS’s medicines database. Enter what you’re searching for, and it’ll return matching results. It’s simple, but a bit too simple.
At Birdie, our users are carers and care managers who spend their time taking care of the elderly. The care managers that use our tool spend their time organising the care that their team provide and carrying out any necessary admin to keep things running smoothly. Any time that we can save them from performing admin is time they can redirect towards providing greater care.
We quickly realised our search tool for finding medications was a large time-sink.
As you can see, the list is unordered. Care managers likely have to spend a long time scrolling a list, instead of relying on the search to actually find what they need. As the amount of time a care manager has to dedicate to any given care recipient is limited at best, this becomes a problem.
The power of prescriptions
To resolve the issue, we turned towards finding a solution that could organise the results in a sensible, useful way. We decided that the frequency that any given medication is prescribed is a good way of separating the common medications from the rare.
So we came up with a simple idea...
We could find the number of times a given medication was prescribed in the NHS’s prescription records and make the most common appear at the top of a search; all we need to save is the number of times each medication was prescribed.
There were, of course, some problems along the way.
By downloading this file alongside the prescription data, we could then use that to match up the right values to right medications.
Problem 2) Covering all types of medication
The NHS classifies medications into four main categories.
AMP — Actual Medicinal Product. These can be considered as the actual medication you might buy, perhaps a branded product. E.g. Tenormin 100mg tablets (AstraZeneca UK Ltd).
AMPP — Actual Medicinal Product Pack. These are an expansion of the AMP above, and is a specific pack or size. E.g. Tenormin 100mg tablets (AstraZeneca UK Ltd) 28 tablet 2 x 14 tablets
VMP — Virtual Medicinal Product. These are much more generic, and generally describes the medication, but not the product. E.g. Atenolol 100mg tablets
VMPP — Virtual Medicinal Product Pack. These are a version of the VMP above and is a specific pack or size. E.g. Atenolol 100mg tablets 28 tablet
An example: You might buy a 20 tablet pack of Panadol 500mg painkillers (the AMPP), but you wouldn’t buy raw Paracetamol (the VMP).
Our medication search returns AMP and VMP results in the list, so we needed a way of making sure that the prescription data filters down to them.
Thankfully, it’s possible to make the link between them and their VMPP and AMPP counterparts, so we ended up being able to directly connect prescription numbers to the right medications that were appearing in our search results.
Problem 3) Too much data
At around 7 billion prescriptions, this dataset was an 800MB CSV file.
Although we considered alternatives, we decided to use a raw SQL script to insert the data into our MySQL database, as it would be the fastest to iterate and the easiest for future developers to understand.
The problem was, the first version of the script we wrote took around 15 minutes to complete. This meant that our database could be locked whilst performing this enormous task, meaning that our users would not be able to use the app properly.
We couldn’t process that much data at once without consequences.
Instead, we took a different approach.
We limited the data to be the top 99% of medications prescribed, which was less than 1% of the original data; all of the benefit with a fraction of the data.
We sunk some time into investigating how we could optimise the query, and ended up dumping the data into a temporary database table, where we could process it faster.
With the data cut down, and the process improved, the script ran at lightning speed.
A powerful medication search
With those problems out of the way, we had a powerful new search tool that could save our care managers quite a bit of time and effort.
We even began to notice relevant medications surfacing that none of us had expected. Liquid asparagus, anyone?
Where to go from here?
The great thing about any tool is that they can always be improved. The main idea we’re looking towards in the future, however, is automating the prescription data insertion.
In this first version, it requires someone to download the NHS data and run the script as needed. Automating it would mean it always stays up to date.
We decided not to do this for the first version for the following reasons:
New medications are not added very often.
The data is unlikely to be very different each time.
The data is only updated approximately every 3 months.
Got any interesting insights on a medication-related search? See if you can guess what flavour is the most commonly prescribed milkshake.