7 min read

As the volume of data from marketing efforts increases, so does the complexity. The more data we have, the greater the challenge of collecting, preparing and managing it.

That’s where Marketing Data Warehouses come in to play as a unified destination for storing and analysing marketing data.

build your marketing data warehouse using supermetrics for bigquery

Still, building a Marketing Data Warehouse has its inherent technical challenges such as heavy design and maintenance. It often requires programming skills or deep expertise in complex ETL tools and processes. Fortunately, technological evolution has enabled us to come up with solutions for those kinds of challenges too. 

In this article, we’re going to show you how to use Supermetrics for BigQuery and take away most of the barriers to a cross-channel view, without requiring IT capabilities. 

Supermetrics to BigQuery
Build Your Marketing Data Warehouse Using Supermetrics for BigQuery

First of all, you may be wondering why you should use these solutions instead of other options available. I’ll tell you why:

Supermetrics

Supermetrics was developed to make Marketing reporting easier. It offers a wide range of data connectors for the main platforms that are easy to set up – requiring no programming skills. 

It also provides access to the most important metrics and KPIs for digital marketers.

Besides that, Supermetrics’ raw data reaches BigQuery in the necessary structure to build cross-channel data visualisation. We can even schedule when and how Supermetrics will move data to the Data Warehouse so that we can have fresh data whenever required.

BigQuery

Google BigQuery has largely taken away the complexity and cost/time-related barriers to build a marketing data warehouse. It has no infrastructure to manage, so you can focus on analysing data and find meaningful insights using familiar SQL rather than operating and sizing computing resources.

5 Steps to Build Your Marketing Data Warehouse

1. Set Your Google Cloud Platform Project 

If you don’t have your first project created on GCP, go to the Manage resources page in the Cloud Console. 

On the “Select organization drop-down” list at the top of the page, select the organisation in which you want to create a project. 

set your google cloud platform projectClick Create Project

In the New Project window that appears, enter a project name. If you want to add the project to a folder, enter the folder name in the Location box. When you’ve finished entering new project details, click Create.

New project in google cloud platform
2. Set Your BigQuery Dataset

Go back to the Google Cloud Platform console and go to the BigQuery page and click “Create Dataset”.

set your bigquery datasetAt the configuration, set your dataset name. For now you can just click “Create Dataset”, but it’s possible to change settings – for example, to define table expiry. 

Set your dataset name in BigQueryPerfect! Now that you have your Dataset created, let’s set up Supermetrics connectors.

3. Configure Supermetrics Connector

Finding Supermetrics connectors is very easy. 

Go to GCP’s Marketplace and search for “Supermetrics” and you will see a list of connectors and choose the ad accounts you want to get data from.

Configure Supermetrics connector Click on “Enrol” to enable the connector.

Facebook Ads by Supermetrics
4. Set Up Data Transfers

When the connector is enabled, you can click on “Configure Transfer”.

Set up data transfers in SupermetricsIt will open a window with the transfer setup.

At “Source”, choose the connector you want to access.

Choose the source connector you want to access in BigQuerySet “Transfer Config Name”. Make sure you’re choosing a self-explanatory name so you can easily identify the channels within your transfers.

On “Schedule options” choose when you want to repeat your transfers.

At “Destination settings” refer to the dataset you created at step 2.

Now you need to authenticate with your ad account by clicking on the “Connect Source” button and accepting the third-party data connection agreement.

Enable a third-party data connection in BigQueryThe procedure will be the same for every connector you choose. First you will authenticate with your Supermetrics account.

Select your Supermetrics teamEnter your ad accounts credentials and click “Submit”.

Transfer settings for Facebook Ads

Transfer settings for LinkedIn Ads5. Explore Your Datasets

If you made it this far, you should already have your dataset ready and your next transfers scheduled. To know the structure of the data you just collected, go back to the BigQuery page and check the dataset in the bottom left menu and click in any of the tables listed to visualise its schema.

Explore your datasetsIf you want to dive deeper into the data, you can query any table you want by clicking on “Query table”. 

Query tableHere you can complete the SQL query by adding a * between SELECT and FROM.

To retrieve more than 1,000 rows, remove “LIMIT 1000” from your query. 

The final query will be  SELECT * FROM dataset.table_name .

Final queryNOTE: the tables are partitioned, so if you want to look at all the dates available, you can replace the date after the last underscore for a “*” to merge all your tables. The query above, for example, would be:  

 SELECT * FROM `supermetrics-277614.supermetrics.FBADS_AD_*` 

When you click Run it will process your query and return the first 100 rows of your table. 

Process your query and return the first 100 rows of your tableBy clicking “Explore Data” you can use the Data Studio Explore tool to examine the data available and collect statistics or informative summaries.

Explore dataYou can take advantage of this tool to better understand your data or even check its consistency.

Explore data and check consistencyYou can also use these views for data management purposes. Build some charts and save it to keep track of your scheduled transfers and to perform recurrent audits. 

As you can see below, you can’t view all your historical data, so the sooner you start collecting marketing platforms data, the sooner you’ll have your data warehouse populated enough to make data-driven decisions.

No historical data Make sure to take some time to get to know your datasets, so that you can plan how to report on it efficiently.

Build a Multi-channel Dashboard Using Supermetrics for BigQuery

Not sure how to build a multi-channel dashboard using Supermetrics for BigQuery? 

Learn how to Build a Data Studio Dashboard in 10 Steps and see how to report the performance of your ads on multiple platforms in an integrated way, using Google Data Studio.

Kirsten Tanner

Kirsten Tanner

Editor in Chief at In Marketing We Trust. Passionate about content marketing and dogs. Loves creating long-form, evergreen and 10x content. Is mentioned in Guy Kawasaki's latest book.

Leave a Reply