CUSTOMER STORY
The UK’s National Health Service relies on thousands of different pieces of equipment, consumables and tools to operate effectively — which means it uses a sprawling, complex supply chain. Owned by four NHS trusts, the Collaborative Procurement Partnership (CPP) manages three major contracts within the NHS Supply Chain Operating Model.
The volume and variety of data CPP deals with daily are immense. The team works with 545 suppliers across its five categories. That translates to almost 370,000 different products, from basic consumables like gloves to highly specialized components for prosthetic limbs. Combine that figure with the fact that an individual item could have more than 30 different pricing options, and there are millions of data points to consider.
“We access data from a lot of different systems and sources, including within the NHS Supply Chain and directly from trusts,” says Claire Povey, director of business and performance at the Collaborative Procurement Partnership. “But we weren’t making the best use of all that data.”
In early 2019, CPP undertook a data and analytics review, using the assessment to build a long-term roadmap that charted its transformation plan from manual spreadsheets to full analytics automation.
“We were starting with low-level desktop tech,” says Povey. “It was heavily manual and resource-intensive, and we were spending most of our time and effort on data processing, not analysis.”
We access data from a lot of different systems and sources, including within the NHS Supply Chain and directly from trusts, but we weren’t making the best use of all that data.
We were starting with low-level desktop tech. It was heavily manual and resource-intensive, and we were spending most of our time and effort on data processing, not analysis.
In early 2019, CPP undertook a data and analytics review, using the assessment to build a long-term roadmap that charted its transformation plan from manual spreadsheets to full analytics automation.
Alteryx is central to our ongoing technology development road map, and the transformation of CPP services to NHS Supply Chain.
This challenge led CPP to Alteryx, which the team’s business analysts quickly put to work.
“For each trust, there’s an individual spreadsheet for every category of equipment,” says Jemma Brace, a lead data and insights analyst at the Collaborative Procurement Partnership. “Within that file, there’s an individual sheet for each sub-category. That’s thousands of datasets.”
CPP created a five-stage automated workflow using Alteryx. First, batch macros process all the relevant files, loading and mass-cleansing the data in less than a minute. The workflow then normalizes the data to ensure CPP’s systems can read it properly, changing the word ‘pack’ to the code ‘PK’, for example — before automatically loading it into a consolidated catalogue on CPP’s SQL database.
There’s also a business-as-usual workflow which automatically updates the catalogue as suppliers add, change, or remove products. Rather than simply delete products that are no longer available, the flow adds an ‘obsolete’ tag, which means the data is ignored by benchmarks but retained for auditing.
The team’s benchmarking workflow then uses the catalogue to assess a trust’s spend against the product list, ensuring the appropriate prices and discounts are applied. It outputs the results as a bespoke spreadsheet of quantitative and visual summaries, which include vital metrics that help track general spending, off-contract spend, and potential savings.
Workflows dramatically reduce manual analytics effort
Analysts can produce detailed benchmarks in a fraction of the time
NHS procurement leaders can make more informed decisions
Using spreadsheets, it could take the team up to 10 working days to complete a benchmark analysis and report the results. With its new automation flows in Alteryx, the team completed more than 250 benchmarks in that same window — each taking as little as an hour to complete.
This new, faster process was pushed even further during an ophthalmology benchmarking exercise, where the team completed 130 analyses in just six hours. Previously, that could have taken up to four months of work processing, analyzing and reporting.
“Automation is ensuring consistency and integrity in the way we handle data,” says David Thompson, a lead data and insights analyst at the Collaborative Procurement Partnership. “Before, we were just relying on the VLOOKUP function in Excel, which could be susceptible to errors.
“As well as simplifying data analysis from a technical perspective, working with Alteryx is also helping CPP’s analysts manage their own workloads more effectively. “Its very low maintenance after the initial build,” says Thompson. “And all our team members can process and create reports through Alteryx — it’s not dependent on coding skills.”
Through automation, CPP is helping its trusts make better-informed decisions about procurement, driving down costs without impacting the quality of clinical care — and ensuring medical professionals are equipped with the best-quality tools.
The CPP team is now looking at automating other data feeds beyond the pricing spreadsheets. And with the success of its Alteryx Designer deployment, CPP is also migrating to Alteryx Server. This will help create a complete automated ecosystem, allowing its analysts to focus on saving the NHS even more on its procurement costs.
“Our investment in Alteryx has been rewarded in a very short time,” says Helen Lisle, managing director of the Collaborative Procurement Partnership. “Alteryx is central to our ongoing technology development road map, and the transformation of CPP services to NHS Supply Chain.”