Improved analyses through data standardisation and automation

To improve this workflow, we recommended using dbt for data standardisation and automation. This approach would not only enhance data quality but also free up time for more impactful, in-depth analysis. Schiphol recognised the value, and we set the project in motion.

Approach

Action plan: We began by identifying the primary bottlenecks, mainly in data unpacking, cleaning, and standardisation. Our goal was to create usable, ready-to-analyse datasets. We divided the plan into smaller steps and considered how best to transfer knowledge to Schiphol's team. To start small, we decided to build a Proof of Concept (PoC), focusing on standardizing two Google Analytics (GA) properties. If successful, we could expand to other properties.

Technical execution: We developed a generic framework to process GA data using dbt, enabling reusable code for future properties. The process involved unpacking, cleaning, and standardising data. To ensure data quality, we embedded tests to validate our assumptions.

Knowledge sharing and documentation: Beyond the technical setup, we created documentation and slides to transfer knowledge to Schiphol’s team. We held training sessions to get them started with dbt and organised hackathons, which revealed a need for a cheatsheet—summarising the most important code snippets.

Results

Upon completing the PoC, the data was automatically standardised, allowing us to dive into analysis without spending time repeatedly cleaning the data. This enabled us to deliver value through deeper analysis right from the start.

During the process, we also identified data quality issues that hadn’t been previously flagged due to a lack of testing. These issues were resolved, resulting in more accurate data.

The new dashboards now all utilise dbt-standardised data. This new process ultimately provides: time savings, easier-to-analyse data, more accurate analyses, and a centralised process that is easy to transfer.

Future

With Schiphol now equipped with a framework to standardise data from different GA properties using dbt, we’ll extend this to additional properties. The data will be automatically cleaned and prepared. We’ll also migrate other data streams to dbt and update the associated dashboard calculations. Ultimately, all data will be centralised, standardised, and automated, ensuring quality and enabling analysts to focus on deeper insights.

Receive data insights, use cases and behind-the-scenes peeks once a month?

Sign up for our email list and stay 'up to data':