The Patient and Medicine Journey

-by Veerle van Leemput and Greg Mills, Managing Directors at Analytic Health

It all starts with the patient

In this blog we’ll guide you through the Patient and Medicine Journey. The aim is to help you understand how patients in the United Kingdom get their medicines and where these medicines originate from. Pharmly Cloud Data offers a range of datasets that cover (parts of) this journey.

The full journey

Primary care

The patient’s journey starts when they need medical support and generally begins in the Primary Care system at a General Practice (GP).

The GP can either be a practice that also dispenses medicines (known as Dispensing Doctors) or a practice that only writes prescriptions. If there is no specialist referral needed, the patient’s journey ends by collecting their medicines at a community pharmacy (or directly from the GP in the case of Dispensing Doctors).

All this data is available in Pharmly Cloud Data’s Prescribing dataset, either grouped by Primary Care Organisation (known as Clinical Commissioning Group (CCG) in England) or individual GP practice. Information about the volume and value going through a Dispensing Doctor is available in our Prescriptions Cost Analysis (PCA) data*.

Secondary care

If the patient gets referred from Primary Care to a specialist, they arrive in a hospital (or other similar NHS Trust) for Secondary Care medical support. Depending on the urgency, the patient may have begun their journey in the Secondary Care system (e.g. accident and emergency (A&E)). Hospitals can both prescribe (for later use) and dispense (for immediate use) medicines to a patient.

Information about the number of patients admitted to hospitals is available in our Hospital Episodes Statistics (HES) data. The quantities and values of medicines used in hospitals are available in our Secondary Care Medicines Data (SCMD). Data on the prescriptions written in hospitals but dispensed at community pharmacies is available in our Hospital Prescriptions Dispensed in Community data*. This is particularly important in understanding the prescribing rates for each brand in each hospital.

Community Pharmacies and Pricing

Community pharmacies get their medicines supply from wholesalers (such as AAH Pharmaceuticals). They buy medicines at a certain price from the wholesaler and after dispensing to the patient they get financially reimbursed by the Department of Health and Social Care (DHSC) at the Drug Tariff price or, in case of a supply shortage, the Concessionary Price. Knowing these prices is important to understand buying decisions of community pharmacies and identify patterns of medicines shortages.

Pharmly Cloud Data provides access to Drug Tariff prices and Concessionary prices.

The Manufacturer and the Supply Chain

The medicines that the patient eventually receives are produced by a pharmaceutical manufacturer. The manufacturer sells their product to wholesalers, hospitals and pharmacy chains. When sold to hospitals, the manufacturer generally needs to be granted a tender for the supply.

The List Price is the basic price for a medicine, as defined by the manufacturer. Manufacturers sell their product using the List Price as a baseline but generally offer contractual discounts, depending on the level of competition or National Health Service (NHS) negotiations. This makes List Prices an important piece of information in the healthcare system.

All List Prices are available in our List Price dataset.

Dictionary of Medicines and Devices

The Dictionary of Medicines and Devices (DM+D) is a large repository of all the medicines and devices used across the NHS. When any company would like to supply a medication or medical device they must first register their product, including detailed characteristics, with the DM+D; making the dataset a vital piece in understanding the market.

The full DM+D database is available via Pharmly Cloud Data.

The Pharmly Code

All the vital elements of this patient and medicine journey are covered in Pharmly Cloud Data and our Pharmly Code ties all the pieces together.

We developed our pharmaceutical coding system (the Pharmly Code), as an enhancement to the DM+D, out of a necessity to combine, manage and make available our wide range of healthcare data via Pharmly Cloud Data. Our Pharmly Code allows you to combine datasets and to identify Active Ingredients, Brands, Product Packs, Companies and more. It’s the underlying infrastructure of all the data in the patient and medicine journey.

Pharmly Cloud Data allows you to simply browse through all Pharmly Codes so you can easily identify the products and companies you need.

Want to keep this for reference? Download our Patient and Medicine Journey here!

*this is currently under development and expected in early 2022.

Using the Pharmly Cloud Data API in Excel

– By Greg Mills, Founder of Analytic Health

Accessing healthcare data is a simple and enjoyable process...

… when you have the right tools for the job🛠

Our programmatic REST API

Data-driven healthcare decisions are more important than ever but accessing the data is not always as simple as we would like. So let’s take a look at how simple this can be with a REST API, which in simple terms means a process to share data, or not-so-simple terms… an Application Programming Interface for Representational State Transfer (let’s stick to the former).

Along with my business partner (and data-science star ⭐) Veerle van Leemput, we created a REST API to make an enriched set of England prescribing data freely available. Here we’ll demonstrate how to access that data, and as Microsoft Excel is still one of the most commonly used tools we’ll base the demo in Excel.

So, let’s see how we can impress all our colleagues by using a REST API to get some live data! You can follow these 7 simple steps to create the Excel workbook yourself or even download a fully working demo version here*. 

* You will need to adjust privacy settings for this example to work (step 2 below). Excel displays the warning: ‘this setting could expose sensitive or confidential data’. So I recommend you only adjust these if you have permission and know what you’re doing. We can’t access your data, it is merely a requirement from Excel, to enable the API functionality. Chis Webb gives some nice background in his article here. We don’t record any personal information relating to the requests to this data source and we don’t ask for any of your details to use the service.

7 Simple Steps

  1. Open Excel.
  2. Click Data > Get Data > Query Options > Privacy > Ignore the Privacy Levels and potentially improve performance > OK
Set privacy levels in Excel

3. Click Data > From Web > copy and paste this URL: > click OK

Get Data From Web

4. If you see this pop-up, just click Connect

If you see a pop-up, just click on Connect

5. A new window will open. Click To Table > OK (leave the default settings)

A new window will open where you have to click To Table and OK, leaving the default settings.

6. Click the button to expand the columns > Untick the column prefix option > click OK.

Click the expand button to expand columns, untick the column prefix option, then OK

7. Click Close & Load, which will show you the full table in an Excel sheet 😎.

Click Close & Load, which will show you the full table in an Excel sheet

Great work! You should now have a fully automated workbook of England prescribing data. Each month just hit the Refresh button to keep the data live!

You need to click refresh if you want to see the live data

Available data

As part of this demo, we made a selection of active ingredients available, including Aciclovir, Baclofen, Paracetamol and Risperidone. Why not see if you can build a more dynamic way to retrieve the data for each of these! (This article might help you get started).

(This list of active ingredients may change- you can always contact us to enquire what is currently available)

I hope this helps you to better understand what a REST API is and how it can be used to access data. And if you’ve managed to follow the process yourself, then well done you for learning some great techie skills! ⭐️

For more details about Pharmly Cloud Data – the application that inspired this demo – take a look at our website here. We also wrote a white paper about the challenges of accessing high-quality data and some of our solutions, which you might find interesting. You can download it here.

We’ll be following up on this example with a Tableau dashboard using the same dataset to see how we can analyse prescribing trends across the UK.

See you next time!


A good time for a major restructure of the NHS?

– By Greg Mills, Founder of Analytic Health

In this article we take a brief look into the ever-changing landscape of NHS England and the Clinical Commissioning Groups (CCGs), which are groups of General Practices (GPs) that work together to define the best services for their patients and population1.

What exactly is happening?

In November 2020, NHS England released plans for major structural changes to its organisation. In April 2021 this change took effect when 38 of the CCGs were consolidated to become 9, taking the total number of CCGs down to 106.2

A one-off adjustment or part of a bigger picture?

In short, it’s part of a much bigger picture. Since the introduction of the CCGs with the Health and Social Care act of 2012, which aimed to strengthen competition within healthcare by creating a complex, localised structure3, there has been a notable reversal in policy4. From over 200 CCGs, initially, there are now likely to be just 42 by April 2022, as NHS England moves towards its vision of a fully Integrated Care System (ICS).

Why make these changes?

There are suggested to be many benefits to an ICS, including that the action will support the pandemic recovery by removing unnecessary bureaucracy, empowering local bodies and tackling health inequalities5.  However, others question the timing of such a shakeup, as the NHS continues to struggle with Covid-19- one of the biggest challenges in its history. There are also concerns over the loss of local knowledge and community partnerships, even the threat of privatisation that critics say the consolidation may lead to6.

What does it mean for Pharmly Analytics?

Pharmly Analytics is an application that provides analysis of prescribing habits and healthcare trends around the UK, with maps, graphs and insights aiming to support decision making for a range of organisations. We wrote another article about how the prescribing data can be useful and also some of its challenges here

As the prescribing data is released 2 months in arrears, we are now gearing up for the new data structure. The April data for England will be released in the middle of June, so as soon as that happens we will switch our setup to include the new CCG structure: 106 CCGs instead of the current 135. The historical data for the 38 CCGs which technically no longer exist will be integrated into the 9 newly introduced CCGs, so no data will be lost. 

How can I learn more?

Please contact us if you have any questions regarding the restructure, how it affects the Pharmly Analytics application, or to collaborate on projects which help to accelerate innovation in healthcare.

Details of all the mergers:

CCG change summary
Figure 1. CCG change summary (source: NHS Digital)


  3. BMJ 2021; 372 doi:

Quantifying the Financial Impact of UK Generic Pharmaceutical Shortages.

– By Greg Mills, Founder of Analytic Health

Generic pharmaceutical shortages have a large impact on the National Health Service (NHS) finances. During a shortage event the Department of Health and Social Care (DHSC) issue an amendment to the original reimbursement price (or drug tariff price), in what is known as the concessionary price.

It is clear to see what the additional spend is during a shortage event: if the price increases from £2 to £10 per pack, for 10 packs, for one month of shortage then there has been an £80 additional spend. However, the more interesting question is what happens after the shortage event; does the price immediately return to its pre-shortage rate? The answer is no, it takes many months, and 73% of the products included in our study are yet to return to the original price. The effect of a pharmaceutical shortage is long-lasting and has a longer-term financial impact.

We built a model to help quantify the impact of these price increases over time, which we made freely available here. The model includes all shortages since January 2015 and will be updated monthly as new prescribing and shortage data are made available.

Quantifying the Financial Impact of UK Generic Pharmaceutical Shortages
Figure 1. Showing the additional NHS spending on Chlorpromazine 25mg tablets due to a shortage event beginning in 2017 and the on-going financial effect.

In the example above you can see a snapshot of the report, focussing on a single product- Chlorpromazine 25mg tablets. The vertical dashed lines represent the start and end of the supply- shortage months. It is clear from this image that the Actual NHS costs soared from £30k per month to a peak of £760k during the second month of the shortage. To date, the financial impact of this shortage has been additional NHS spending of over £20 million, and three years since the end of the shortage, remains 22 times higher per month than the estimated value if the shortage had not occurred. 

These additional costs quickly rise for all 214 active ingredients included in the report to a cumulative additional spending total since 2015 of £2.5 billion or around £55 million per month (and continuing to grow).

Quantifying the Financial Impact of UK Generic Pharmaceutical Shortages
Figure 2. Showing the cumulative financial effect of generic pharmaceutical shortages since 2015, totalling almost £2.5 billion.

The financial impact of UK Generic Pharmaceutical Shortages is significant, it is a hugely important area for healthcare, and we welcome further research. If you have any questions or suggestions, please reach out and we will be happy to discuss further. 

At Analytic Health we work with a range of organisations to help them understand the pharmaceutical supply situation and provide access to Pharmly- the pharmaceutical market intelligence web application. We are also in the process of making the data used to create this report readily available, both via a web interface and a REST API.

There are certain points to be aware of when viewing this report: 


Company affiliations in developing this report: None

Empowering people with data.

People need information from data

The richest source of information in UK healthcare is the prescribing dataset. Understanding the medications prescribed, down to individual GP surgeries, can offer invaluable (anonymised) information which people use to shape regional health policies, monitor disease trends, and guide company portfolio strategies.

All this information is available, however, there is a catch; with over 20million rows of data released by the NHS every month – it is ‘somewhat’ inaccessible without a high-performance computing environment, a team of data scientists and a lot of time on your hands.

So, how can I get the data?

Previously, accessing prescribing data could go something like this…

“Downloading the file… hmm that’s taking some time. While I wait, I’ll try to install the data science software I need to view the data… ok let’s give it a go.”

*Some hours later*

“I can view some data! Now to check the trends- I just need to download 11 more files and I’ll be able to see trends over a year.”

*After manually downloading the 11 files*

“Ah, but that’s at the molecule level for England only, how can I check brand performance, just for tablets, across the whole of the UK?” ?

What’s the problem here?

Clearly, there is a problem here, and the problem is not the analyst, the account manager, or the data scientist. Every person has a unique skill set, and we add value to our team or project, in the best way we can. But we cannot be expected to do everything- I certainly do not manufacture my running shoes before I go out for a jog! So why should you spend your time gathering huge datasets from various places to get that piece of information you need?

How can we help?

At Analytic Health we have gone through the process above so that you and your teams don’t have to. After many years in roles across healthcare, our team has experienced it all, and that is exactly the reason we now so passionately make the information available to our clients.

With enriched datasets* and interactive visualisation tools, we let people do what they do best (which not even the most advanced technology can do) – feel empowered to collaborate with their colleagues while acting upon the information gained from data!

Above you can see part of the prescribing analysis view of our Pharmly web application.

Let’s discuss

If you would like a demonstration of how we can help you, please contact us and we will happily arrange a call to discuss this with you in more depth.

Stay safe and stay empowered by data!

*the term enriched datasets here refers to cleaned data, merged with other sources, thereby adding value.