Siavak: Diversifying client offering and improving marketing reporting with Supermetrics for BigQuery

Siavak is an analytics-focused marketing agency located in Budapest, Hungary, that helps their clients track and collect marketing data in order to drive traffic to their websites and convert that traffic into paying customers.

Specialized in Google Marketing Platform and Google Data Studio, Siavak’s core business is focused on four key areas, which are Audit, Measurement Planning, Tracking Implementation, and Custom Reporting Dashboards. At the heart of their client services is the ability to turn data into insight into action into results. To help do that, Siavak structures its operating processes in the following way:

• Firstly, the data collection needs of each client are determined through a deep measurement audit and conducting interviews to understand their unique business objectives
• Next, Siavak sets up measurement and tracking scripts based on the plan and client reporting needs
• After that, they transform the data and make it usable for reporting
• Lastly, they build custom data visualizations to surface data for their clients to use

Ahmad Kanani, Siavak’s Founder and Director, helps his clients make sense of all their data to measure the impact of their marketing activities. But with such huge amounts of data available, the Siavak team needed a more comprehensive data solution, which included Google BigQuery and the creation of a marketing data warehouse.

The evolution of Siavak’s reporting process

Siavak’s reporting process has gradually been developing from basic to more advanced. Their reporting journey began with simply using Google Analytics and has evolved to include various tools, such as spreadsheets, Google Data Studio, and Google BigQuery.

When reporting was done in Google Analytics, it was pretty limited. The only data which could be seamlessly collected into this platform were pageviews, custom events, and conversion hits sent via Google Tag Manager.

As Siavak’s clients felt the need to bring campaign and cost data from their acquisition activities on Facebook Ads, Bing Ads and other paid platforms outside of Google to their day-to-day reports, Ahmad started looking for another reporting solution where the data from all the different platforms could be extracted and combined in a seamless way.

Google Data Studio was already available at that point, but back then it lacked one key feature Ahmad needed: Data Blending.

“When it came to reporting, we could connect Google Analytics and other data sources to Google Data Studio which did not have the data blending feature yet. As a result, it was not possible to merge different kinds of data sources together,” Ahmad says.

He started using Google Sheets as a platform to store and blend data. This approach was better than the previous one as he could get data from non-Google platforms into the report with Supermetrics. The most popular reports he was creating were a Paid Channel Performance Report and a Post-Click Analysis Report.

Paid channel performance report

Supermetrics for Google Sheets allowed Ahmad to merge campaign and cost data from different marketing platforms together to create a paid channel report. This dashboard would show the performance and ROI of each marketing channel.

Post-click analysis report

The post-click analysis report would combine Google Analytics website conversion metrics with data from other paid marketing channels. This way, Ahmad could analyze how well traffic coming from each channel converted and what was each channel’s ROI. The report would provide answers to questions like What is the user behaviour on a website? and What is the actual business outcome?

Overcoming reporting limitations with Supermetrics for BigQuery

Since then, the reporting scenarios and connectors have not changed significantly, however the sheer volume of data started to get bigger and bigger. Ahmad faced more reporting limitations with a 5-million cell limit in Google Sheets. Even when 100-200k cells were filled with data, the reporting speed significantly slowed down.

Siavak needed the whole reporting process to be improved and sped up. A combination of Google Sheets and Google Data Studio worked, but it was no longer an option as the spreadsheet product had reached its data storage limitations. “One big reason we were looking for a data warehouse solution was the speed of reporting. When Data Studio connects to Google Analytics directly, despite caching, many API calls and metric calculations still need to happen at the time the report is loading. After we switched to a Google Sheets + Google Data Studio process, the speed of reporting has increased. Data Studio was already getting loaded data from the spreadsheet, however, as the dataset size grew reporting was getting slower and slower,” says Ahmad.

Siavak needed a more flexible and scalable platform to store the data. In addition, the data there needed to be cleaned and transformed.

This is where Google BigQuery entered the picture.

For Siavak’s needs, BigQuery was a perfect solution. But one challenge still facing Ahmad was how to pull in data from all the sources he needed? Siavak’s clients at that time were not using the Google 360 Suite, which would natively connect to BigQuery. And even if they were using Google Analytics 360, that would have only solved part of the analytics data puzzle. Ahmad still needed a solution which could get data from non-Google platforms into BigQuery in the required format.

He tried a few different solutions but quickly ran into some inconveniences.

With one solution, raw Google Analytics tables were replicated in Google BigQuery exactly as they would appear in Google Analytics’ database, so their team had to do the data modeling in advance before being able to connect to Data Studio. In addition, setting up relationships between dimensions and metrics in different tables required time, technical expertise and experience.

“One had to write a decent amount of SQL just to be able to connect the needed data to Google Data Studio. Some companies do appreciate the “raw” data format because of the level of flexibility it unlocks, however this requires having in-house development resources to the data modeling,” Ahmad says.

Around the same time Ahmad was trying different data connectors for BigQuery he received an email from Supermetrics when Supermetrics for BigQuery was announced. “That was perfect timing. I was speaking with a client about the reporting limitation that we had with Google Sheets. I told them that BigQuery is a powerful platform, but we have to find a way to transfer all the needed data. I was already using Supermetrics and started talking to their support team about the new product,” Ahmad says.

First Impressions of Supermetrics for BigQuery

When Ahmad started the trial of Supermetrics for BigQuery, he immediately noticed that it helped improve Siavak’s reporting process. He says that, “Supermetrics offered a real bonus for us and our clients. Supermetrics for BigQuery does all the heavy lifting of automatically pulling data from different sources, doing the data modeling in BigQuery, and creating the data source in the correct format to be connected to and visualized in Data Studio.”

Overall, Ahmad and Siavak’s impression of the product was very positive. “First of all, I didn’t expect the connection to be so seamless. I remember initially there was a couple of days of going back and forth with Supermetrics’ support that helped me figure out how to backfill data. And from that moment onwards, I haven’t used the product actually! It works seamlessly, transfers all data automatically, and the client has the peace of mind that we have complete data ownership with Facebook, Bing, and Google Ads data being fully accessible,” he adds.

Apart from marketing channels and Google Analytics, Ahmad could bring data from their clients’ own data systems into BigQuery. After the data was in the data warehouse, Ahmad could easily join all the datasets. He says, “We wanted to give the client a real time report which would cover the ad cost, campaign performance, user behaviour, and conversions data together in a reliable data destination. And with Supermetrics and BigQuery we were not worried about the amount of data we were transferring or our ability to access the data for reporting and analysis purposes in the future.”

Getting the data from the client’s system also opened up the possibilities of using the User ID. Previously by using Google Sheets only, Ahmad couldn’t easily blend and manage all online and offline data together. Having User ID as a primary key allowed him to understand the information on a much deeper level and get a 360° view of the user journey. As he understood how the user experienced different touchpoints, Ahmad could have a better idea of how campaigns for each particular target audience could be optimized.

“The reports are still built in Google Data Studio, and their look and feel is exactly the same – however the process of getting the data to Data Studio has changed significantly,” Ahmad says.

He did not have to decide which metrics or dimensions he wanted to use for projects as he could pull all the available data from any source he needed. “We could have everything set and let the future bring the questions. In case we need anything we can be assured that the data is already there,” Ahmad adds.

How Supermetrics for BigQuery uncovered opportunities for Siavak

Supermetrics made the reporting process not only faster but also very convenient. Ahmad says that, “Supermetrics for BigQuery is creating the BigQuery views for the ease of access to data, especially for Google Analytics, which I appreciate. As far as I know, that’s not the case with other providers. So if you want to just load the GA data into BigQuery without Supermetrics you would need to write SQL and join multiple tables just to get a single report.”

Ahmad says that his clients reacted positively to the improvement. “It is a lot faster to connect to BigQuery rather than to Google Sheets, especially with the introduction of the BigQuery BI Engine. For some specific reports we have to pull data for multiple years, which we couldn’t do when we were using spreadsheets, as the data set was too big in size. With BigQuery, the client can have all the time frame that they expect to see in a single report and without data sampling,” he adds.

With Supermetrics for BigQuery, their clients also gained complete ownership of their data. “Usually these companies spend a lot of time and resources on planning, data collection, cleaning and processing, so for them getting full ownership of this data is a huge advantage. The client is paying a small fee to Google Cloud Platform to store and process data and to run every query, however this cost is quite cheap compared to the value they are getting. Supermetrics not only gives data ownership but also makes the reporting more flexible. With data available in BigQuery, clients can analyze and report it the way they want.”

Siavak’s future vision of reporting with Supermetrics

Ahmad shared that he does not think he is planning to use Supermetrics very differently compared to what he is doing already. He says that, “I have confidence that Supermetrics can provide us with the needed data with the five connectors I am using. But also if our clients decide to try another marketing platform, I know I will find a needed integration from Supermetrics; there are many connectors currently available and more of them are being added at a fast pace.”

Ahmad says that Supermetrics has also helped make Siavak’s service offering more valuable and it will continue to do so in the future. “As an analytics agency we can provide better options to our clients as we can now use Google BigQuery to its full potential. As Supermetrics takes care of loading all the data into BigQuery, we can spend our time analyzing the data. Data transfers have become less expensive, therefore we can offer our services not only to big enterprises, but also to SMBs with small teams as well.”

Ahmad was also excited about the opportunities Supermetrics, BigQuery, and the Google Cloud Platform will offer Siavak in future, concluding that, “After the data is loaded into the data warehouse, marketers and analysts can use Machine Learning and all the other tools that are accessible through Google Cloud Platform. It is good for me as an analyst, and as someone who mainly works with data collection, data processing and reporting. This opens up the possibilities for me to help clients further.”

Want to improve your agency’s client reporting just like Ahmad and Siavak?