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: