***TIP: Subscribe to our newsletter. Don’t miss any new content.***
Have you been dreaming of how smooth it would be to have Excel connected to AdWords? There might be a reliable workaround solution for you. This article is for more advanced users but don’t be scared – why don’t you just become advanced user by trying all of this stuff? You don’t have to be a coder. I am not coder either.
In today’s article, we will be combining AdWords Scripts, Google Big Query, Big Query ODBC drivers for Excel and Power Query in Excel to create an improvised Power Query AdWords Editor linked to AdWords via almost live connection. All the resources in this article are publicly available (including almost ready-to-go script), there is no need for special paid licenses, nothing like that.
This setup has been tested by me and I can confirm it’s been working so far. The initial setup took me about 4 hours, including research, testing and first orientation in Google Cloud Platform & Big Query. If you follow this article step by step, I think you will be done in the less than 2 hours because I have already cracked the hardest steps for you 🙂
Previously, when you wanted to “connect Excel to AdWords”, you could be exporting all the AdWords data to Google Sheets via scripts or via SuperMetrics. Then you’d have this Google Sheet file connected to Excel via Power Query. This approach has several issues.
First of all, Google Sheets can only hold 2,000,000 cells which sounds like a lot but it is not. Imagine you have 500,000 keywords with campaign + ad group name, keyword, match type and max CPC bid. 5 columns x 500,000 rows = 2,500,000 cells and you are done with that file. And you have not even added the ads. Or the extensions. Another issue I have been facing several times is that when exporting decent size account to Google Sheets via AdWords Scripts, the sheets would randomly break – they would stop simply accepting the new data and they would freeze after any change you make. I have reported this to Google here but as usual, nothing has been fixed or attempted to be fixed.
When using SuperMetrics for exporting the data to sheets, I am frequently seeing incomplete queries to be inserted into the Google Sheets but the Google Sheet file would never break unlike with AdWords Scripts. Example: You have 103,304 keywords in you account and but the query only pulls 35,000 keywords (the number is always a rounded one). So in summary, AdWords script tend to work until the Google Sheet file breaks and SuperMetrics don’t work reliably with large accounts.
So do we get out of this vicious circle?
Here is the solution:
- You send your AdWords data to Big Query with AdWords Scripts on hourly basis. Big Query can reliably hold millions of rows.
- You install special ODBC driver for Excel to your PC which will allow you to connect with Power Query to your Big Query tables (which refresh every hour)
- In Power Query, you will use simple SQL statement to pull the data from Big Query
- Once loaded to Power Query, you treat the data like if it came from a CSV file. The only difference is that you are connected to your “almost live” AdWords feed.
So let’s take it step by step in more detail.
Creating Account in Google Cloud Platform
First thing to mention is that Big Query which resides in the Google Cloud Platform is a paid service from certain level of usage. More details here. In short, I think that 99% of users will be fine with Free tier which includes 1 TB for querying plus 10 GB of storage per month. Uploading data to Big Query is for free.
Alright, so how do you start?
Go to https://cloud.google.com/ and sign up with your Google account. You may be asked for your credit card details – there is no way around it. You have to provide the details despite the fact you will not be paying anything at the end.
Once your account is created, you need to create your first Google Cloud Platform project. Just start with “Test” (you can delete the project later). Once the project is created, you will something see like this:
Yes, there is lot of stuff going on but don’t freak out. So what is Google Cloud Platform? Well, it’s an umbrella name for long list of Google products & tools which run in the cloud and which can be relatively easily connected with each based on your needs. Big Query is just ONE of many available products in the Google Cloud Platform. The list is quite long. I don’t even what all these things can be used for but it’s not important for our case. The only thing we care about is Big Query. Here is a not-even-complete list of products in Google Cloud Platform:
Setting Up Big Query Project & Dataset in the Google Cloud Platform
Let’s start with little bit of vocabulary:
- GCP Project = All the inter-connected GCP subproducts running in 1 group. Project has an ID.
- Big Query Dataset = collection of tables (we will have e. g. table of keyword performance or table of search terms). Dataset has an ID.
- Big Query Table = just of table of data. Table has an ID.
Alright, that was not too difficult, was it?
Now, it’s “ID” time. You need to find ID of your GCP project and ID of your Big Query dataset.
Finding ID of your GCP project is relatively easy. Just go to your Google Cloud Platform project “homepage” and find this:
Big Query dataset ID equals to the dataset name. So in my case, it is just “AdWordsData”:
Implementing AdWords Script
There are 2 possible directions you can take. If you use MCC account structure then you need this script:
If you use single account structure, then you need this script (my case):
Copy just one of them and go to AdWords. The default setting of the scripts downloads keyword performance, search terms and account performance for “YESTERDAY”.
Setting up the Script
You can use some fancy name for the script. I am using just “ExportToBigQuery”.
You can also setup your email on line 525 in order to get notifications when the script finishes successfully. If you don’t setup the email, the script will always end with an error (the tables will still be sent to Big Query correctly).
We are now almost ready to run the script but before we do so, you need to go to the “old AdWords” UI in order to turn on Big Query in “Advanced APIs” – this option is not available in the new AdWords API:
After you turn on the API thing, it’s time to run the script. When you run the script for the first time, you need to grant various permissions and again, enable some sort of API. Just do it. I know, it’s lot of “enabling” and I am not quite sure why enabling Big Query API is required again. Just follow the link in the error message, enable it, go back to the AdWords script and “PREVIEW” the script again:
Et voilá, your tables (Account performance, Keyword performance, Search Query performance) are now in Big Query.
Scheduling Your Script
Questions You Might Be Asking
Can you change the default reports in the script? Yes, you can. You need to adjust the script accordingly. It looks difficult at first sight but it is not that bad. Lines 64-100 in the single account script.
Where can I learn more about AdWords scripts? E g. I want to know which time range values I can use. Well, good start is AdWords documentation here. These are all the reports you can store in Big Query relatively quickly. Another good source is https://ppc-scripts.eu/en/.
Can you change the default time frame from “YESTERDAY” to something else? Yes, line 62 in the single account script. You use e.g. “LAST_30_DAYS”, “LAST_MONTH” etc. Again see the Adwords documents above to get your head around it or leave a comment at the end of the article.
Is the data in Big Query overwritten every time the script runs? Depends on your setup, by default, it’s not and new data is only appended to already existing tables. If you set lines 53-54 to “true” the values will get wiped out and inserted as new. This is handy when you e.g. renamed all your campaigns and you want to have correct names in Big Query.
What are the limits? In Big Query, the tables can hold millions of rows. I doubt, you will get even close to hitting the limit. In AdWords, the script can only run for 30 minutes. You will see how timing goes for your accounts and then you will adjust the amount of reports or date range accordingly. You can have multiple scripts running and pulling data into different datasets if you hit the limits.
Setting up Excel ODBC Driver for Big Query Connection
So now you are sending the data to Big Query on regular basis. Imagine you are sending all your keywords, ads, campaigns etc. to a dataset in Big Query every hour and you want to have your Excel connected to the last version of the “snapshots” in order to create your custom “Excel AdWords Editor”.
You need to download ODBC Big Query driver for Excel here: https://cloud.google.com/blog/big-data/2016/11/how-to-connect-bigquery-to-microsoft-excel-and-other-apps-with-our-new-odbc-driver
Once downloaded, run the installer.
During the process, you need to get your access token (then a refresh token – that one should refresh automatically), then choose the project to which you want the connection to be applied. Once done, click OK and close setup windows. Time to move to Excel, finally!
Connecting to Big Query Tables with Power Query in Excel
In this part, little bit of SQL will have to be used. The GIF starts in Big Query demonstrating that there is some account level data (although all the performance metrics are 0), then we move to Excel and connect via Power Query to Big Query with a very simple SQL statement:
What the SQL statement does is that it takes all the columns (the * trick) from ACCOUNT_PERFORMANCE_TABLE in AdWordsData dataset. The SQL statement can get more complicated than that but for our use case, it’s enough. You would proceed similarly with all the tables stored in Big Query, effectively creating your “Power Query AdWords Editor”, so 1 query would always load 1 table (e. g. keywords, ads, sitelinks and so on).
And that’s it. You now have Excel connected to AdWords.
This tutorial demonstrated how to create an improvised Power Query AdWords Editor.
- Download all your important items to Big Query via AdWords Script (keywords, ads, sitelinks etc.)
- Set the AdWords script to always wipe already existing content and replace it with a fresh snapshot
- Set the AdWords script to run every hour
- Connect your Power Query to Big Query tables via the ODBC driver with SQL statement
…then you will basically have Power Query AdWords Editor which pulls (almost) the latest snapshot of your account. Obviously, you will not be able to upload anything back to AdWords from Power Query and you will need to upload the results of your queries somehow manually to AdWords, but still, Power Query has lots of advantages over the conventional AdWords Editor.
***TIP: Subscribe to our newsletter. Don’t miss any new content.***