How to Connect Excel to Google Ads with Power Query

Have you been dreaming of how smooth it would be to have Excel connected to Google Ads? 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 Google Ads Scripts, Google Big Query, Big Query ODBC drivers  for Excel and Power Query in Excel to create an improvised Power Query Google Ads Editor linked to Google Ads 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.

FOLLOW US DONATE NOW

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 Google Ads”, you could be exporting all the Google Ads 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 Google Ads 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 occasionally seeing incomplete queries to be inserted into the Google Sheets but the Google Sheet file would never break unlike with Google Ads Scripts. Example: You have 103,304 keywords in you account and but the query only pulls 35,000 keywords (the number is always a nice rounded one). So in summary, Google Ads script tend to work until the Google Sheet file breaks and SuperMetrics don’t work reliably with large accounts. However, this is the only slight complaint about SuperMetrics, it’s otherwise great tool for day to day reporting. I think that only a minority of users run such heavy queries like I do and most of you would not face query timeouts.

So do we get out of this vicious circle?

Here is the solution:

  1. You send your Google Ads data to Big Query with Google Ads Scripts on hourly/daily basis. Big Query can reliably hold millions of rows.
  2. 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/day)
  3. In Power Query, you will use simple SQL statement to pull the data from Big Query
  4. 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” Google Ads feed.

So let’s take it step by step in more detail.

GO TO SUPERMETRICS FOLLOW US

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:

  1.  GCP Project = All the inter-connected GCP subproducts running in 1 group. Project has an ID.
  2. Big Query Dataset = collection of tables (we will have e. g. table of keyword performance or table of search terms). Dataset has an ID.
  3. Big Query Table = just of table of data. Table has an ID.

Alright, so after you start setup your first GCP project, you need to somehow get to Big Query and create a dataset which will be the home for your “Google Ads” tables. The steps are:

The second important step is to enable Big Query API within your GCP project. Yes, it sounds a bit wierd but we have to do it (in my case, it’s already activated):

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”:

FOLLOW US DONATE NOW

Implementing Google Ads Script

There are 2 possible directions you can take. If you use MCC account structure then you need this script:
https://developers.google.com/adwords/scripts/docs/solutions/mccapp-bigquery-exporter

If you use single account structure, then you need this script (my case):
https://developers.google.com/adwords/scripts/docs/solutions/bigquery-exporter

Copy just one of them and go to Google Ads. The default setting of the scripts downloads keyword performance, search terms and account performance for “YESTERDAY”.

Setting up the Script

Find the “Scripts” section in Google Ads:

Add a new script via the big blue “+” button and paste the code you copied few minutes ago:

You can use some fancy name for the script. I am using just “ExportToBigQuery”.

Now it’s time to use the IDs which you found earlier. We have to put them “somewhere” into the script:

By scrolling and by using just common sense, you would probably guess that you need to use the IDs in the highlighted section, so in my case, it looks like this:

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 Google Ads 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 Google Ads script and “PREVIEW” the script again:

Et voilá, your tables (Account performance, Keyword performance, Search Query performance) are now in Big Query.

FOLLOW US DONATE NOW

Scheduling Your Script

After you run the script for the first time, you should schedule automated refreshes (also, read the 3rd question below to get an idea what happens to data in Big Query when you run the 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 Google Ads scripts? E g. I want to know which time range values I can use. Well, good start is Google Ads 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 Google Ads 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 Google Ads, 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 Google Ads 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.

Once the installation is finished, setup your ODBC driver (In Windows 10, I just search for “odbc” in Start Menu):

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 Google Ads 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 Google Ads.

Conclusion

This tutorial demonstrated how to create an improvised Power Query Google Ads Editor.

If you…:

  1. Download all your important items to Big Query via Google Ads Script (keywords,  ads, sitelinks etc.)
  2. Set the Google Ads script to always wipe already existing content and replace it with a fresh snapshot
  3. Set the Google Ads script to run every hour
  4. Connect your Power Query to Big Query tables via the ODBC driver with SQL statement

…then you will basically have Power Query Google Ads Editor which pulls (almost) the latest snapshot of your account. Obviously, you will not be able to upload anything back to Google Ads from Power Query and you will need to upload the results of your queries somehow manually to Google Ads, but still, Power Query has lots of advantages over the conventional Googe Ads Editor.

GO TO SUPERMETRICS FOLLOW US DONATE NOW

8 thoughts on “How to Connect Excel to Google Ads with Power Query

  1. Great post. Wondering if there is a script for Search Console. I’d love this exact same solution, but with Search Console data.

  2. Danzrust,
    Thanks for the article. Very helpful, and exactly what we are needing to do. Can you confirm this is the best, or at least a still reliable, way of connecting Excel to Adwords? Also, does the SQL statement for Power Query need to be made once, or every time we want to update our data in Excel? Is this for desktop and cloud Excel? I’m wondering what would happen on the Excel end if we aren’t connected to the Internet.

    1. Hi Ben,
      This article is pretty old by now. I am increasingly moving towards these running all the calculations in BigQuery and only pulling the processed results to Excel via Power Query while doing little-to-no-magic in Power Query. You can read more about it here:
      https://www.excelinppc.com/big-query-automation-powerhouse-for-google-ads/
      https://www.excelinppc.com/generate-keyword-lists-with-sql-in-bigquery/

      However, if the above mentioned articles looks too complicated to you (I can easily understand that) then this article is way to go. Just keep in mind that Google Ads scripts can run only for 30 minutes and if your account is massive, the script may not have enough time to sent everything to Big Query. This time out problem would be solved if you use “Google Ads Transfer Service” for sending the data to Big Query but again, there would be some SQL knowledge involved + $2/month cost for each account you are trying to pull.

      Once you setup your Excels with SQL queries set in Power Query to Big Query, you just keep pressing “Refresh” button whenever you need to pull fresh data into Power Query. Once you refresh you Excel tables from the cached Power Query data, you can go offline. If you try to refresh you Excel tables while being offline that will not obviously work.

      Let me know if you have more questions.

      DZ

  3. Hey there! Excellent write-up! Super helpful, for sure! I have only one question. It seems that excel is pulling in 7 rows for each 1 that it’s supposed to, thus resulting in a TON of duplicate data (approximately 7 times! lol). If I look at the report in my email that I just set up, it shows that there should be 4629 rows of data, however, it’s pulling in 7 of each row, resulting in 32403 rows of data. It does this for each of the three reports (account performance, keywords performance, and search query performance).

    Do you have any advice on how to remedy this?

    Thanks a lot!!

    (sorry to bring up a really old topic)

    1. Hi Bradley,
      aren’t you pulling last 7 days with date column into Big Query so it kinds of get multiplied 7 times?

      Anyway, easy remedy is the deduplication function residing in the Power Query. Just mark all columns, go to Home tab>Remove Rows>Remove duplicate rows.

      DZ

  4. Nevermind! I’m an idiot, I wasn’t truncating the data. bleh. Got it figured out!

    Thanks for this amazing write-up!

Leave a Reply

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