Google Sheets Script to Track Google Ads Auction Insights Over Time

Auction Insights is perhaps one of the most insightful tools in Google Ads, allowing you to track competitors and get a glimpse of your competitive landscape. Google Ads has enabled access to this in Looker Studio (formerly Data Studio) for a few years now, which means you can create reports that drill down into different competitors on the handful of metrics available.

However, Google Ads is somewhat reserved with this data. It doesn’t expose almost any auction insights data through it’s API — in fact, the API only gives access to your own data (impr. share, absolute top, etc). This means that if you want to track your competitors over time, or do anything even slightly more sophisticated with your Auction Insights data, you need to be pulling and storing it on a regular basis. That is inconvenient, boring, and therefore tends to be done very rarely. It also means you can’t ever use this data for other scripts, i.e. increase bids on a brand if for example “Competitor A’s impression share increases by X percent”.

This Google Sheets script takes advantage of a bit of a loophole in Google Ads: you can schedule reports for Auction Insights, and you can set them to run on a schedule and export them to Google Sheets. By setting these to run daily and running this script daily, it compiles all the reports that have been exported into a BigQuery table. This allows you to quickly add the reports in Looker Studio and build more advanced automation on top!

Script for Collecting Auction Insights Exports in Google Sheets

Because this script is a bit complicated and divided into several files, it’s easier to simply make a copy of this sheet; this time we are not adding in the code to the blog post, although feel free to copy and paste it directly from the Apps Script section of the sheet if you’d like to.

How to Use the Auction Insights Google Sheets Script

To get this script to work, follow these steps:

Setting up BigQuery

1. Create a GCP Project
2. Create a BigQuery Dataset
3. Create a BigQuery table

Creating Google Ads Reports

1. In your Google Ads account, navigate to the auction insights you’d like to export. You may want to export several different reports – for example, you might want a different report for every campaign, or one for every account.
2. When creating a report, ensure that it’s set to run on a schedule and exports to a Google Sheet.
3. Ensure that the name of the report starts with Auction Insights_ or the script won’t find your report!
4. ⚠️ Anything after the _ will be the “identifier”: make sure you include any information about your segments here (i.e. campaign name, ad group name, etc.) or you won’t be able to segment your data later ⚠️

Copying the Google Sheet

1. Copy the template sheet.
2. Update your settings in the sheet to use your project ID and Dataset ID.
3. From the sheet, click on Extensions > Apps Script.
4. In the left menu, click on Triggers (the alarm icon).
5. Click on “Create a new trigger.”
6. Under “Select Event Source” choose Time-driven.
7. Select the frequency you’d like this script to run on (we recommend “day timer” and “midnight to 1am”) so that it runs every day in the middle of the night.