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.
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.
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.
Click 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.
2. Set Your BigQuery Dataset
Go back to the Google Cloud Platform console and go to the BigQuery page and click “Create Dataset”.
At 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.
Perfect! 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.
Click on “Enrol” to enable the connector.
4. Set Up Data Transfers
When the connector is enabled, you can click on “Configure Transfer”.
It will open a window with the transfer setup.
At “Source”, choose the connector you want to access.
Set “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.
The procedure will be the same for every connector you choose. First you will authenticate with your Supermetrics account.
Enter your ad accounts credentials and click “Submit”.
5. 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.
If you want to dive deeper into the data, you can query any table you want by clicking on “Query table”.
Here 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 .
NOTE: 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.
By clicking “Explore Data” you can use the Data Studio Explore tool to examine the data available and collect statistics or informative summaries.
You can take advantage of this tool to better understand your data or even check its consistency.
You 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.
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.
