Supermetrics for BigQuery: Step by Step Guide

In April 2019, Supermetrics team announced their new product called Supermetrics for BigQuery. So let’s have a look on what this thing does and why it could be useful for you – especially when you run massive accounts.

Here some of the questions you might be asking:

  1. Why the hell should I care? I am just fine with Google Data Studio.
  2. What’s BigQuery actually?
  3. Do I need to know SQL to use this product?
  4. How expensive is this Supermetrics for BigQuery thing?
  5. How do I set it up? 
  6. What do I do with data in BigQuery?

In this article, I will try to reply to all of the questions above. I will walk you through the setup process of the connectors and also through the setup process of a simple Power BI report based on BigQuery data.

So let’s get to it.  When you use Supermetrics for Google Data Studio, you can sometimes feel quite limited in what Google Data Studio can do (ehn, cannot do). I personally prefer Microsoft Power BI because of these reasons:

  1. It’s got endless possibilities for creating super-customized metrics in DAX language. Imagine you wan’t to create a metric “avg clicks in the last 7 days” and use it as column. At the same time, you want to have a metric “clicks yesterday” in the same table, next to the “average clicks in the last 7 days”. This is impossible to create in Google Data Studio, but it’s relatively easy to create in Power BI.
  2. The charts are clickable and quickly filterable by simply clicking into various visual elements. That’s something Google Data studio still cannot do.
  3. Power BI is able to cache datasets into its memory so filters and various drill downs are faster than in Google Data Studio.

On the hand, Power BI has certainly some disadvantages:

  1. It’s more difficult to setup
  2. It’s not free (1 user is around 10 USD/per month)
  3. It cannot directly connect to Google Ads, nor to Google Analytics.
  4. Sharing options are limited

Despite the above mentioned reasons, I still prefer Power BI as the main dashboarding solution. When Supermetrics announced Supermetrics for BigQuery, I was super excited…until I saw the pricing (but let’s get to prices later). I was super excited because I have been using BigQuery as data warehouse for the past 2 years and I wanted to see whether there are some upsides to using Supermetrics instead of my custom connectors. 

***Tip: Have a question? Ask in the comment section below or in this Facebook group. You can also sign up for our free newsletter.***

***Do you feel like you need more help? We can help you with PPC Automation and/or with your reporting under our new project MythicalReports.com here.***

Little Bit of My Reporting History

I originally started in Excel based reports where Power Query would do all transformations and normalizations for me (that was 5 years ago) over data sitting in Google Sheets which served as my improvised data warehouse. I’d use Supermetrics for Google Sheets to download the data from the main advertising platforms. This was OK for small accounts. I felt like superhero when pulling data from Google Ads, Bing Ads, and Facebook into 1 nice user friendly pivot table in Excel within seconds. But over time, I realized, it was only semi-automation. I still had to open the damn Excel and press refresh button so the pivot tables got updated. As some of the accounts grew, I had started to hit limits in Google Sheets (2 mil. cells per file) so I’d have to create multiple same files for some of the account and the files would just vary in date periods. This was kind of OK until I had to add a column into my report and update all of my 10 underlying Google Sheet files. But still it was better than pulling manual CSV reports from advertising platforms.

Later on, I discovered that Power Query is fully embedded into Power BI for Desktop and some of you probably know that Power BI reports can be scheduled on Microsoft’s servers so you don’t have to press the damn refresh button in Excel. I felt like super hero again. I “just’ (yeah, it took a while) rebuilt all my Excel reports as Power BI reports trying to make them look similar and then I scheduled everything to run on Microsoft’s servers. In ideal world, this would be good enough setup but…

But the problem is that we don’t live in the ideal world. Sometimes, my Supermetrics queries in Google Sheets would fail so not matter how cool your Power BI dashboard was, it was showing no data. This was especially happening when working with bigger accounts and trying to download KW level report split by date. Sometimes, the query would run but then the results would not get all inserted and for example, out of 63,059 rows, only 30,500 would be inserted into the sheet. Sometimes your refresh token would expire for Bing accounts, so you’d have to go to every single Google Sheets file and login into Supermetrics. Man, this was killing me!

Then, one day in 2017, I accidentally discovered BigQuery (I actually accidentally discover many things) and I could feel some potential there! And the best part was that it was nearly free! However, there were 2 issues:

  1. I could not write even basic SELECT * FROM xyz in SQL.
  2. and bigger problem was how to get the data from various advertising platforms into BigQuery.

I have quickly figured Google Ads – as usual – there is a script for that. or you can use natively supported BigQuery transfers for Google Ads. What about Bing and Facebook for example? I had to hire a developer – have him write the codes, setup a server, schedule import jobs etc. But it was worth it. Why? Because I finally had a solution in place:

  1. Solution which does not randomly fail
  2. Solution where I pay nearly nothing for storage
  3. Solution where I can train myself on SQL
  4. Solution which is blazing fast to query millions of rows.
  5. Solution which has truly unlimited storage

Now it’s 2019 and guess what – I still dump all the advertising data to BigQuery, create SQL  transformation/normalization views on the top of all the raw data and use the views as source “master table” for all my Power BI dashboards. And now… Supermetrics guys show up with their connectors for BigQuery. As you probably understand, I have no choice but to try it.

Step 1: Creating Google Cloud Project

Before you can import anything to BigQuery, you’ll need to create your very first Google Cloud Platform project. Generally speaking, Google Cloud Platform is a paid product, however, if you don’t go too crazy, many modules are free or nearly free. More info about creating your first Google Cloud Platform project can be found here.

The good thing is that Google will give you $300 credit for all the possible infrastructure costs of your new project. This free credit is valid for 12 months. In return for the credit, you will need to enter your credit card despite the fact you will likely pay nothing for several months or even for the whole year. Google just wants your card!

You might be now asking what will be your total cost. It will consist of:

  1. “infrastructure cost” => you pay that to Google.
  2. “cost of connectors” => you pay that to Supermetrics. I will go into details towards the end of this article. 

Once you create your project, you just navigate to BigQuery which easily is accessible via UI:


And you will see something like:

In your case, there will likely be just one sample project form Google.

Step 2: Creating a Dataset in Your Shiny New Google Cloud Project

After you created the project and finally entered BigQuery, you need to create a dataset where you will be storing all the data pulled via Supermetrics connectors. How to do it? It’s simple. Navigate to “project level” (=the arrow in the image) and select the project. On the right side and there will be a button “CREATE DATASET”, so click it:

You will be then asked for some info:

Dataset ID is basically dataset name, it can be letters and numbers. You cannot change this after you create the dataset so think twice.

Data location is the physical location of where the data actually sits. You cannot change this after you create the dataset so think twice again. On the top of all that, you cannot query datasets sitting in different locations in 1 query (e. g. doing left join from table sitting in EU to table sitting in US). So think three times!!! You can probably imagine how painful it is to realize that you are using incorrect location of 1 out of 10 datasets in your project while the data is already loaded to BigQuery 🙂

Step 3: Connecting to Advertising Platforms via Supermetrics for BigQuery

So say, you figured how to get Google Ads data into BigQuery and now you need to start storing Bing Ads data. How to do it? You start on this page. Select “Start free trial”:

 

Since you are starting free trial, you obviously don’t have to pay anything upfront. Supermetrics offer trial for all the BigQuery connectors so you can decide if it’s useful for you before paying a dime. What’s also quite user friendly is that you won’t be auto-billed after your trial expires.

After starting your trial, you’ll end up here:

As mentioned, I will be going with Bing Ads (ehm…Microsoft Advertising). So I click “Bing Ads by Supermetrics” and land here:

 

 I click ENROLL and land here:

Okay, now it’s time to select your project, where you’ll be storing your data. I will just select my test project and press continue:

Now, don’t get confused – you will land on the page which you have already seen before:

It will be working for few seconds and eventually, it will look like this. Go ahead and select “CONFIGURE TRANSFER”:

Now it’s time to fill in few fields:

 

  1. Transfer config name: write some nice understandable name
  2. Schedule options => Start now: I don’t see any reason to select “Start at set time”
  3. Repeats: Daily
  4. Destination dataset – you need to select the dataset where to store the raw data. It’s the dataset name we created in one of the previous steps. 

So my setup is:

Once you figure out your name, and destination dataset, it’s time to “CONNECT SOURCE”. Go ahead and click it. Immediately, a nag screen will pop out. Accept the agreement, we don’t have any other option anyway.


The next screen will be:

Login with your Google account:

Then you’ll need to authorize with the engine of your choice – in my example, I am using Bing Ads aka Microsoft Advertising.

Then sign in into your Microsoft account:

One more prompt screen, click “Continue”:

Select the account(s) (you can select more than one) whose data you want to import to BigQuery:

And that’s all for the authorizations/logins/verifications. That was quite a few screenshots to take!

Go back to your browser where you setup the Bing Ads (Microsoft Advertising) connector and you should see “Source connected”. Click “SAVE”:

After clicking the “SAVE” button, you will finally be taken to Big Query to the transfer section where you will see something like this:

Step 4: Exploring Configuration Options

After a while, click the transfer and the right pane in your browser will open with the recent statuses. In my case, I am getting “no data found” because I connected a paused account.

Your next question would be: What about the historical data? Is it getting downloaded automatically? No, it’s not but you can schedule a backfill. Click “MORE” and then “Schedule backfill”:

You’ll be taken to:

Just set backfill dates (the max is 180 days) and off you go!

Next question: Can I change the time when the sync runs? Yes but it’s little tricky. On this screen, click “EDIT”:

You will be taken to the know config page, select “Start at set time”:

And you will be able to edit that time which will be then used for daily load as well. Once you are done just click the SAVE button.

Next question: Can I change how many days are being reloaded every day? Yes, you can but it’s little bit clunky. You need to navigate to the old BigQuery UI by going to: https://bigquery.cloud.google.com/transfers/test-project-excelinppccom (sample for my test project, change the value after the last / to the name of your project). Then once in the old BigQuery UI, navigate to transfers and expand click the transfer which is run by Supermetrics:

 

Next, set “Refresh window” to 30 days and press the “Save” button.:

Why would you want to reload last 30 days every day? Well, even Bing now assigns conversions to the date of the last click (Google has been doing this for ages) and NOT to the date of happening. So historical numbers can change retroactively. Facebook behaves in the same way.

Step 5: Finally Making Use of Data with Little Bit of SQL

Okay, with the questions cleared, it\s finally time to look at what actually happened in your dataset in BigQuery. We can see that new tables got created. From now on, I won’t be looking into my BigQuery since no data was downloaded from my paused account and I will be using sample dataset provided by Supermetrics available here: https://bigquery.cloud.google.com/table/supermetrics-sample-data:Supermetrics_public_Bing_Ads_example.BINGADS_KEYWORD_20190307?pli=1&tab=preview.


For some reason, I was still taken to the old BigQuery UI – by the time you read this article, you might be already taken to the new UI. These are the tables which were downloaded by Supermetrics:

So you are getting the most important tables but you don’t have option to change which tables are being downloaded and which are not. 99% of the people will be just fine but more advanced users could be disappointed by the lack of options. I am pretty sure that the list of downloaded tables will eventually grow in future for each connector.

So now assume, you want to combine campaign level Google Ads and Microsoft Advertising data into one view and import then use this view as data source in Power BI. So let’s see what tables we get if you enable even the Google Ads connector:

Hmm, no keyword level data for Google Ads connector for now – that’s a pity.

Anyway, let’s have a look on our task – putting together campaign level data from Google and Bing into 1 table. The query would be:

If you want to copy and try yourself:

SELECT date, ‘Google’ AS engine, campaign_name, impressions, clicks, cost, ‘google data’ AS source
FROM `supermetrics-sample-data.Supermetrics_public_Google_Ads_example.GOOGLEADS_CAMPAIGN_*`

UNION ALL

SELECT date, ‘Bing’ AS engine, campaign_name, impressions, clicks, spend, ‘bing data’ AS source
FROM `supermetrics-sample-data.Supermetrics_public_Bing_Ads_example.BINGADS_CAMPAIGN_*`

Let’s deconstruct the problems solved by this basic SQL:

  1. I am appending Google Ads and Bing Ads data with the UNION ALL command into 1 table
  2. I am setting proper Engine names as second column
  3. The costs are called differently across Google (cost) and Bing (spend) – by appending these two tables with these cost columns on the same position, I am effectively making Bing spend column called “cost” since the original spend column is on the 6th position same as in the Google Ads table.
  4. There are many “daily” tables in the datasets but I am querying all of them at once. Notice the * wildcard in the FROM clause – that’s doing the trick. In stead of querying the “daily” table such as `supermetrics-sample-data.Supermetrics_public_Bing_Ads_example.BINGADS_CAMPAIGN_20190307`, using * instead of the date suffix will make the query run through all the daily tables.

That was not too difficult, was it?

Now imagine, you need to create a custom column “Brand vs Generic”. You want to classify all campaigns containing “Brond” as “Brand” and everything else as “Generic”. You have 2 options:

  1. You write a CASE statement in each of the queries => so 2 CASE statements would be needed
  2. Wrap the existing SQL into another SELECT … FROM … with 1 CASE statement => this is more efficient as well as more difficult at the same time but I am going for this option. The query would be:

In case you want to copy:

SELECT
date, engine, campaign_name,

CASE
WHEN campaign_name LIKE ‘%Brond%’ THEN ‘Brand’
ELSE ‘Generic’
END AS brand_vs_generic,

impressions, clicks, cost, source


FROM
(
SELECT date, ‘Google’ AS engine, campaign_name, impressions, clicks, cost, ‘google data’ AS source
FROM `supermetrics-sample-data.Supermetrics_public_Google_Ads_example.GOOGLEADS_CAMPAIGN_*`

UNION ALL

SELECT date, ‘Bing’ AS engine, campaign_name, impressions, clicks, spend, ‘bing data’ AS source
FROM `supermetrics-sample-data.Supermetrics_public_Bing_Ads_example.BINGADS_CAMPAIGN_*` 
)

And that’s how you create custom columns – there is variety of functions available to help you manipulate your data. To name a few: LOWER, UPPER, REPLACE, SPLIT()[ORDINAL()], SUBSTR but that’s probably a topic for the next article.

A Few Words about Pricing of BigQuery Connectors from Supermetrics

You can visit the pricing page here.

One data source connector for 1 account costs $199/per month. 1 data source for 5 accounts costs $499/month. Yes, it’s not cheap. If you run basic combination of 1 Google Ads account, 1 Bing Account and 1 FB account, Supermetrics for BigQuery will cost you $600 per month just for collecting the data.  If your advertising budget is a few thousand dollars per month, this product is no-go for you.

What if you spend $100k or more per month? Then it’s little different story. You probably don’t care if you spend extra $600 on:

  1. Having solid data warehouse with unlimited storage
  2. Having possibility to run blazing fast SQL over millions rows of data
  3. Being able to run super custom queries in SQL
  4. Being able to create different SQL views for various people in the company
  5. Being able to export raw data anytime you want 

Yep, so there you have it – good products are never cheap and this is one of them. Who know, maybe they’ll cut the prices in future. 🙂

Step 6: Importing Data to Power BI and Making Your First Dashboard

Assume you are happy with your query and you want to start visualizing your data in Power BI. You will need to save your query is a view. So go ahead and click “Save View”:

This window will pop out. Choose the correct project and dataset, set a table ID (can be letters and numbers without spaces though) and press “OK”:

I will now switch back to the new UI and check if my view got saved. Yes it did save!


It’s time to open Power BI for Desktop. If you are looking for a Mac version then too bad! Microsoft does not have one at this time. They only support Windows. If you don’t have Power BI for Desktop yet, you can go ahead and download the exe from here: https://www.microsoft.com/en-us/download/details.aspx?id=45331

Download it, follow the install instructions and shortly, you will be creating your very first file. Press “Get data”:

Then look for “bigquery”, select it and press “Connect”:

Find your project>dataset>view and load:

Then go for “Import” option:

After while, you will see that your report has loaded – you should see all the columns which you have in your BigQuery view:

Then I will just create a simple chart and table:

Step 7: Scheduling Your Report + Power BI basics (DAX) for Digital Marketers

Coming next my next article!

In order not to miss the next cool content, you may want to join my Facebook group here or alternatively, you can subscribe to my free newsletter.

Also, don’t forget the try Supermetrics for BigQuery in the meantime!

 

***********************************************************************
***********************************************************************

***Do you feel like you need more help with the PPC world? We can help you with PPC Automation and/or with your reporting under our new project MythicalReports.com here.***

Leave a Reply

Your email address will not be published. Required fields are marked *