AVROTROS: Elevating Data Management through a Scalable Solution

The Challenge

Avrotros had a functional Google Cloud solution in place and sought to elevate their architectural setup to a future-proof level, prompting them to engage Crystalloids for the challenge of designing and building an advanced solution.

The Goal

The Desired Solution: Avrotros had specific requirements for their new solution:

User-friendly for both developers and analysts
Easily monitorable with the ability to detect issues proactively
Simple maintenance, allowing seamless updates and changes to data while ensuring its availability to users
Scalable for effortless addition of new data sources
Redoable steps in case of failure without affecting the overall process
Secure access for predefined user groups

The Process

At Crystalloids we work in a structured way to get maximum results in the shortest time.

Preparation phase:

Information gathering about the existing architecture and high level goals.
Writing backlog together the Product Owner, developers and Commercial Director
Backlog refinement to get all necessary details right such as Definition of Done
Backlog prioritisation by business importance
Estimation of work for the selected user stories

Implementation phase:

Design the architecture
Implement the solution
Demo working software in two sprints of two weeks
Document and handover the code and documentation
Review customer satisfaction

The Solution

Instead of loading data from a source into BigQuery and directly build reports on these BigQuery tables, the best practice we implemented is to divide this process into three steps:

Google Cloud Platform

1. Load

This step only takes data from the source and writes it to BigQuery in its raw form.

No transformations are made on the data, to make sure that you are looking at the same data as was received from the source. It can be useful to not only write it to BigQuery, but also to a file in Google Cloud Storage. For example, when you receive data in JSON format, this can be stored in its purest form.

2. Normalise

This step takes the raw data and makes the transformations that are needed to be able to use the data. Make sure that:

all data is deduplicated: no transformed table contains two identical rows.
all data is actual: only one version of the data is present, the most recent one,
all data is stored at the lowest granularity (no aggregation of raw data, only storing its lowest level)

3. Publish

Once the data is normalised and all entities are stored in tables, you are ready to start gathering the data that you want to use in your reports or for specific analyses. This should happen in a separate publish step. Here you also have the possibility to combine multiple entities and/or sources.

To orchestrate these steps and make sure that the next step will only be executed when the previous is finished, we used Google Workflows. Within this workflow, each step in the solution is handled by a Cloud function. For this case it was required to load the data from the source on a daily basis. Therefore the first step is triggered by Cloud scheduler, since it allows to trigger the process every day at the same time.

The Result

Thanks to the implementation of this setup, Avrotros experienced a transformative impact on their data management process. The new solution facilitated meticulous monitoring at each individual step, allowing operational engineers to promptly respond to any potential issues and efficiently manage the production lifecycle.

One of the standout features of the solution was its pay-per-use pricing model, ensuring cost-effectiveness by eliminating unnecessary expenses. Additionally, the entire process ran serverless, streamlining operations and eliminating the need for complex configurations.

The seamless integration of this new process allowed Avrotros to easily incorporate additional data sources into their Datahub, further enhancing their capabilities. As the Datahub continues to grow, it will become a powerful hub of data-driven decision-making, propelling Avrotros to excel in their domain.

With Crystalloids' expertise and innovative solutions, Avrotros is now equipped with a cutting-edge data management system that empowers their team with real-time insights and the ability to take data-driven actions swiftly. The successful implementation of this future-proof solution has cemented Avrotros as a leader in the data-driven way of working, providing them with a competitive advantage in their industry.

About AVROTROS

AVROTROS is a media organisation that is part of the Nederlandse Publieke Omroep (NPO, the Dutch public broadcasting organisation). With dozens of television shows, radio shows, online shows, 8 sites, 2 apps and over 100 social media accounts AVROTROS is a key player in the Dutch market.

The program budgets are largely determined on the reach per show (on television and online). Therefore the most important KPI on all their platforms is unique/total reach. AVROTROS have a strong ambition to further expand their customer reach based on data and by using data-driven workflows to deliver insights to the publishers for analysis of customer reach of their shows and other formats and also for growth hacking purposes.

Visit their website →

In just four weeks we have been able to learn a lot. The load-normalise-publish process that Crystalloids has built will be an important foundation of our Datahub. We will bring all remaining data sources to the Datahub in the same way which is a major step in the development of our Datahub and the data driven way of working.

TJITZE ZIJLSTRA

Growth Hacker, Avrotros

AVROTROS: Elevating Data Management through a Scalable Solution on Google Cloud Platform

Avrotros is now equipped with a cutting-edge data management system that empowers their team with real-time insights and the ability to take data-driven actions swiftly.