AdWords is a Google product, so most AdWords users have probably come across the PowerBI equivalent – Google Data Studio (beta) (well kind of doesn’t compete yet, but will discuss in another post!)…
In Google Data Studio, there are report templates, that can just be pointed to your data, for common Google products like Adwords (now Google Ads). Below is the Google Ads Overview Report Template, which is what our Campaign Strategist was using to manage our AdWords Campaigns. Inside the Google Ads API it is referred to as the “Campaign Performance Report”
So in order to make sure our Campaign Strategist trusted our data and reporting when we moved it to Power BI, I set about rebuilding this report in Power BI. To do this I needed to create a simplified view using the AdWords data-set in Google Big Query (GBQ) which I could consume in Power BI. And to do that, I needed to understand the Google AdWords (now Google Ads) tables, measures and data model, so I started here
After lots of querying inside the Google Big Query editor interface, I was able to collate most of the data presented in the Google Campaign Report Template together.
The Search Impression Share was stored in a separate set of underlying tables, so I compiled it into a view of its own to combine via a model once in Power BI.
Now I have the Adwords data I need, in the format I need, to consume it in Power BI and rebuild this report there.
I can also customize the look and filtering and show and drill to details as needed.
I can also now combine it with my other data sources for Revenue and Search Rank. But how can we combine these? This is covered in my next post… Adwords + Power BI Part 4 – Combine the data (AdWords, Revenue & Search Ranking) and handling Exceptions
This is a continuation of the series “Adwords Analytics in PowerBI”
At time of writing, having the option to connect directly to Adwords data via Power BI is not being considered by the PowerBI development team, but there are options, if you are willing to write your own custom connector as suggested here. Looked a bit scary for me 😉
We do currently have the option use data from Google Big Query in Power BI thought, so what if we were able to move data from Google Adwords to Big Query?
Everyone say hi to Google Adwords Transfers
In a nutshell this requires enabling the transfer to happen on both sides (Adwords & Big Query) and giving a specific user access to do it.
You need to add a payment method to cover storage costs with varies depending on the amount of campaigns you have. We had around 500 campaigns and lost month the storage costs was close to $2 (yes $2!).
So now you have a whole set of views and tables related to AdWords in Big Query. How do I turn this into something I can use?
This is covered in my next post… Adwords + Power BI Part 3 – How do we make sure Adwords users trust our reporting in Power BI?
Anyone who works in search cannot avoid Google Adwords & Google Analytics. Power BI has built some templates for reporting for Google Analytics, but nothing exists at time of writing for Google Adwords.
We had a need to measure the success of our Adwords efforts, while also determining where it made most sense to spend on campaigns. We had some existing data we could leverage to order revenue by advertising spend and also keyword ranking.
First we had a couple of issue to solve…
- How do we get our campaign data from Google Adwords into Power BI?
- How do we combine the data (AdWords, Revenue & Search Ranking)
Then we had some other questions to answer…
- How do we help fix exceptions? (when data doesn’t match)
- How do we make sure Adwords users trust our reporting in Power BI?
- What are we going to show to help manage and suggest AdWords Campaign spend?
the questions will be answered in my next few posts…
Starting with How do we get our campaign data from Google AdWords into Power BI?