From Audit to Insights – Seris

As a ‘Total Security Concept Provider’, our client Seris offers solid security solutions for companies, public authorities and private individuals.

For one of its customers we analysed their auditing process and rolled-out automated reporting.

The briefing

Seris conducts security audits every semester for a Belgian supermarket chain . Every store is audited regarding security compliance in terms of camera security, alarm systems, fire safety, COVID-measures, … to make sure each store is compliant with Belgian law and internal security standards.

In the past, auditors had to assess the stores via a questionnaire in Excel. All completed Excel files were delivered to the customer. They had to manually calculate the results of all audits from multiple Excel files to create an overview and a consolidated report of all scores across Belgium. As you can imagine, this was a very tedious and time-consuming process. Our client was convinced there was a more efficient way to do this. Since their customer uses the Google Suite, they proposed Google Data Studio to create automated reports.

• Move away from Excel setup
• Automating data processing to become more efficient.
• Automated reporting

Our solution

1. We converted the audit questionnaire from an Excel file to a Google Form. The Google Form is easier to fill out on the go than the massive Excel sheet. All data from the completed Google Forms are collected in one global Sheet via an automated connection. This way, we created one overview of all audit results.
2. A macro was created to execute automated transformations on the data and to generate a result sheet for each store individually.
3. A report was built in Looker Data Studio. Via the automated connection, data in a Google Sheet can easily be uploaded into Looker Data Studio. We designed one report with three tabs.

• Tab 1: An overview of all audit results.
• Tab 2: A visualisation of all audit results by region.
• Tab 3: An overview of the data by store. This makes it possible to benchmark recent results against previous results for one store.

Each time someone completes the Google Form, all data will automatically be uploaded into the global Google Sheet and the report in Google Data Studio will be updated accordingly.

The outcome

This integrated set-up using Google Forms, Google Sheets and Looker Data Studio makes it possible to automate the data flow from audit to reporting. This way of working saves both the client and the auditor a lot of time.

By using Google Forms to complete the audits, all data can be automatically collected and visualised in a Google Data Studio report. It eliminates all manual calculations and makes it easier to compare results over time, region and store.

Even more important, it frees up a lot of time for the retailer which allows them to focus on improving its service, quality and safety in store.