The UK Government’s Excel Test and Trace Data Gaffe Explained

We examine the UK government’s high-profile data gaffe to see what the ramifications were and find out how it could have been avoided.

During the global pandemic, the spread of coronavirus has been limited through a range of measures including lockdowns, restricted travel and constraints on socialising. Widespread testing has also played an important role in efforts to contain the virus. Test and Trace is the system the UK government have been using to monitor test results across the country and inform members of the public when they need to self-isolate. It‘s potentially the most significant weapon in the fight against the virus, so the integrity and accuracy of the data is critical.

On the 5th of October it was reported that almost 16,000 positive tests had been left out of the central database. The omission resulted in rates of infection being under-reported and meant tens of thousands of people who may have come into contact with the virus were left totally unaware.

“Ensuring your methodology is fit for purpose is a baseline requirement for data analysis of any kind. Designing a more fitting system at the outset could have avoided this mistake altogether.”

Initially, the reasons given for the missing data varied between software error, a ‘glitch’ and data exceeding the maximum file size. One prominent member of the government claimed the error was due to data being ‘truncated’ (no prizes for guessing who that was). To anyone with even a basic understanding of data, none of these explanations seemed satisfactory. So, what was the real reason, and how could it have been avoided?

As further information came to light, it turned out that the body in charge of Test and Trace, Public Health England, had been using Microsoft Excel to pull together the positive COVID-19 tests. This didn’t explain why the data was missing, but it was an alarming revelation considering the ‘world beating’ £12 billion system that was promised.

Finally, it emerged that the missing data was caused by human error: the way the system had been set up was not adequate for the scale of the information being uploaded.

PHE had been using XLS, an outdated file format, to upload test logs to the central spreadsheet. Because of this, files were limited to about 65,000 rows of data. The way positive tests are recorded requires multiple rows per test, which meant that there was simply not enough space to include them all. Any rows exceeding this limit were ignored.

Ensuring your methodology is fit for purpose is a baseline requirement for data analysis of any kind. Designing a more fitting system at the outset could have avoided this mistake altogether. While Excel is a versatile and reliable tool for storing and organising information up to a point, for something of this scale and level of importance, a bespoke solution would have been more appropriate. A simple database employing SQL would easily accommodate several million rows of data, while enabling efficient retrieval.

If PHE had built quality assurance into their process, the problem would have been identified long before anybody was placed in harm’s way, irrespective of the way the process was designed.

The takeaways are two-fold. Firstly, whether you are forecasting sales, measuring the success of a marketing campaign or reporting infection rates in a pandemic, choosing the right software and data formats, while being aware of their limitations, is absolutely crucial. Secondly, quality assurance should be a non-negotiable feature of recording, analysing or reporting your data, to pick up on errors before they do any damage.

The Author, Matthew Nixon: @mattalytic

Molzana: @MOLZANAuk

Reporting Consultancy


We provide insights and commentary so you can truly understand website and campaign performance and plan killer optimisations.

App & Mobile Analytics

Digital Analytics Consultancy


Let’s turn your Web Analytics data into insight. Our certified web analytics team have vast expertise across Google Analytics, Google Analytics 360, Adobe Analytics and Tag Management Systems.

Data Strategy


Every business needs a data strategy to understand the needs of their company and make smart technology decisions.

Data Strategy

Business Intelligence


Powerful Business Intelligence solutions that allows for seamless integration of your key data sets, automated and in the hands of your business decision makers.

Business Intelligence

Tag Implementation


With up to 150 tags on some website, Molzana’s tag management and implementation services ensure that your data gets collected while your website remains unaffected.

Report Automation & Dashboards


Elegant and automated, Molzana’s reporting team can create amazing dashboard and report solutions to help your business get to insights and decisions quicker.

Report Automation & Dashboards

Visitor Behaviour


Understanding visitor behavior is vital to understanding how visitors interact with website content and marketing channels.

Visitor Behaviour

Consumer Insight


Consumer Trends & Insight hold the key to understanding your audience. Beyond simple reporting, Molzana can help turn your insight into action.

Consumer Insights

App & Mobile Analytics


We assist in providing powerful insight into iOS, Android & Windows Apps as well as Mobile Websites.

App & Mobile Analytics

Marketing Optimisation


We scrutinise user behaviour, audiences, ad copy and landing pages to improve performance.

Data Strategy

Getting Ready For Google Analytics 4 (GA4)


Get UK based expert support in implementing GA4 tracking for your website and avoid losing valuable data before July…

Solutions for Start-Ups

Conversion Rate Optimisation (CRO) Consultancy


Conversion Rate Optimisation is essential for ensuring your site performs in terms of driving sales, leads or sales development.

Report Automation & Dashboards

Data Science


We take your historic data to look into the future with pinpoint accuracy.

Contact Molzana


Molzana

Digital Analytics, SEO & CRO Agency

London & Manchester.


Get in Touch

Telephone: +44 (0) 20 8938 3136
Email: info@molzana.com

London: 32 Cubitt St, London WC1X 0LR
Manchester:
 5 Piccadilly, Aytoun St, M1 3BR
Edinburgh:
 15-16 Queen Street, EH2 1JE


Molzana Logo Blue

  • This field is for validation purposes and should be left unchanged.