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.
Collaborative Procurement Partnership