This is the last in a series of three blogposts about the first year of the new Investigations & Reporting team, a small group of software developers embedded in the Guardian newsroom. You can read about our work on environmental reporting here and election coverage here.
In May 2020 the Guardian turned its attention to a new strand of reporting: the scrutiny of high value public sector contracts awarded without the usual competitive tender, justified by a legislative clause that provides for direct awards in emergency circumstances. As the government responded to the Covid public health crisis, it had to compete in a market distorted by the pandemic and the choices it made under such pressure were beginning to raise questions. Now working from home, we began collaborating with the editorial investigations team to ascertain which companies were profiting most from the situation.
There were two relevant sources of public data. The first, Contracts Finder, is a gov.uk service publishing contracts over £10,000 but limited to England. The second, Tenders Electronic Daily (hereafter TED) is an EU service with a higher minimum threshold (typically €139,000) but covering the entire UK. For the first story on this topic, we made use of both. As with previous projects involving Facebook Ads, we wrote scripts that queried APIs and turned the data into spreadsheets. These spreadsheets allowed Rob Evans and Juliette Garside to sort and group the data to identify a Top 10 ranking of the biggest winners from the state’s largesse under duress.
Both the Contracts Finder and TED APIs had their quirks. Of the two, Contracts Finder was the easier to use and better documented, but TED offered greater flexibility through its “expert search” and was more comprehensive, covering all of the UK. As big contracts continued to be awarded without tender to companies with political connections or dubious prior expertise in the field, we decided to invest further work in our TED scraper. We rewrote the scraper to run daily as an AWS Fargate task and to talk directly to the Google Sheets API, creating a well-formatted spreadsheet of the day’s new Covid-related contracts without any repetitive manual steps like uploading a CSV file.
As the months went by and story after story about Covid contracts was published, it became clear that there were two ways that technology was helping.
The first was generating leads. Getting a daily digest of the latest government contracts allowed us to be quick off the mark reporting on those of particular public interest. One success of this approach was a story about Randox, where Juliette Garside combined an alert from our spreadsheet with her knowledge of the political context and wrote up the story before any other news organisation had reported it.
The second was turning a lead into a story by verifying it and fleshing it out with relevant contextual data. One particularly good example of this was the Uniserve story – our TED scraper, Contracts Finder scraper, and Giant all came together to supply crucial facts for the article.
It’s worth diving a little deeper into how these tools all came into play for that story. First, Guardian journalists David Conn and Simon Goodley wanted to look for contracts where Uniserve was mentioned as the courier, to confirm reports that Uniserve had been given responsibility for a significant proportion of PPE freight. For this, we turned to our little-used Contracts Finder scraper, because unlike TED, Contracts Finder often provides links to the full contract documents. Once we had given the scraper the Puppeteer-powered capability to download contract documents, we uploaded them to Giant, which was built to extract searchable text from a huge variety of document formats (including scanned documents needing OCR). Now we could search for “Uniserve” and confirm that many of the PPE contracts awarded to other companies did indeed mention them as a courier.
The next step was to find out the total value of Uniserve’s contracts and how that compared with other companies. For this, we wanted the UK-wide TED data. We used our script to create a Google Sheet of all Covid-related contracts since the start of the pandemic, added some GOOGLEFINANCE() magic to convert currencies to GBP, and created a Pivot Table to add up awards for each company. Remarkably, Uniserve topped the list.
The story behind this story demonstrates the interplay between journalists’ sources, public data APIs, homegrown and third-party tools, and the complementary skill sets of journalists and software engineers. But it was only one of many such stories in 2020. Taken together, they show even more clearly what can be achieved when the two cultures coalesce to hold power to account.