Streamlining Home Office Data Collection and Improving Dashboard Usability

Challenge

Two reporting teams within the Home Office faced separate but related data and reporting issues, each hindering efficiency and clarity of output.

1. Quarterly Progress Reporting (QPR) inefficiencies
One team was responsible for collecting Quarterly Progress Reports (QPRs) from around 100 projects, which were then manually collated into a Quarterly Monitoring Report (QMR). The process relied on emailed Word documents, which were then manually input into Excel spreadsheets. This method was time-consuming, error-prone, and lacked automation. The team recognised these inefficiencies and asked Butterfly Data to assess and suggest improvements.

2. Ineffective strategic dashboard
A second team was developing a dashboard that visualised global threats for strategic leaders and ministers. However, the dashboard suffered from performance issues, suboptimal design, and unclear future direction. They sought both technical improvements to the code and design, as well as guidance on tools, hosting, and team capability development.

Solution

Butterfly Data undertook an initial discovery phase to gather requirements and understand existing constraints, before providing tailored solutions for both focus areas.

For QPR process optimisation, two automation options were presented:

• A short-term R script to extract content from Word files to Excel, but this was flagged as fragile and not scalable
• A long-term Microsoft Forms solution, allowing project teams to submit reports directly into a connected Excel spreadsheet

The latter was recommended and accepted by the client due to its scalability, reliability, and improved automation.

Then, for the dashboard redesign and performance improvement, we carried out an in-depth review of the existing dashboard. Improvements were made across two fronts:

• Backend optimisation: Code was simplified and modularised, with HTML replaced by lightweight R functions, significantly reducing file size and increasing maintainability.
• Frontend enhancements: The layout was redesigned for clarity, including a new Home Office-aligned colour scheme and moving the map display to a standalone tab.

Regular review meetings ensured that changes aligned with user needs. Additionally, we provided forward-looking recommendations, covering hosting strategies, database integration, and ways to upskill the internal team.

Impact

We were able to streamline the QPR reporting process by developing a prototype Microsoft Form in close collaboration with the client, accompanied by a process flow diagram and a user guidance document.

This new process reduced manual effort, improved data accuracy, and created a more efficient, scalable workflow. Programme Leads could now approve submissions digitally, and the entire data collection process was significantly more robust.

We also enhanced dashboard usability and performance, reducing file size by over 50%, developing a cleaner, more intuative user interface with Home Office branding, and making code improvements to ensure the dashboard was easier to maintain and extend.

The dashboard could now be confidently shared with senior leadership. The additional strategic recommendations from Butterfly Data gave the client clear next steps to continue improving their reporting capability and team maturity.

The invaluable work that Butterfly Data has undertaken with a key collaborator of mine will feed directly into my work, making it both simpler and faster and enabling me to better identify data gaps. Incredibly useful. Thank you.

Key Project Stakeholder, Home Office

‍