Automating Bidding with Google Sheets and AdWords Scripts

By Jacob Fairclough | @SomeSecretJake | Associate Director of Analytics at Hanapin Marketing |

Bid changes are vital in steering your account towards performance goals. While the process is important, making the changes and implementing them adds little value.

In this article, we’ll cover setting up an automated bidding system using both Google Sheets and AdWords scripts. Specifically, we’ll utilize the bulk upload functionality to format and post the changes directly to AdWords.

You may ask, “why would I need this?”, can’t I use the editor? Bulk Upload allows you to directly upload files to AdWords. This means you can outline your workflow/decision making inside a spreadsheet and use a script to handle the implementation portion. The code is straightforward too. you don’t need to understand much in terms of programming to get it up and running!

In this article, we’ll walk through a use case and show how we set up the sheet, the script, and how we handle scheduling.

Background

We want to make bid changes on a weekly basis. We are currently using a spreadsheet template to take a dataset and make bid suggestions. We then take those changes, format them, and upload them to the editor and post the file to AdWords.

While there is value in the determining the bid rules, there is no value in manually uploading them time after time. Not to mention interrupting your day or someone else’s day to push routine changes live isn’t very considerate. If that doesn’t convince you, we can be more dramatic and say rote, meaningless tasks are making the world a worse place. Be a better person this year!

At a high level our process is,

  1. Get data to the sheet
  2. Manipulate data to create new bids
  3. Format results for upload
  4. Upload

Getting & Manipulating Data

To make the most of this method, you should either use a script or use a tool like SuperMetrics to export the data to Google Sheets. This keeps you from copy and pasting data into your bidding template.

If you need inspiration check out the reporting functionality in AdWords Scripts. It’s easy to use, free, and is a simple query rather than any intense code.

If you are allergic to all code, Fred Valleys has a solution in This Script Adds Any AdWords Data To a Google Spreadsheet. It adds a spreadsheet front end to the reporting script making is more accessible than ever.

Now that you have the data, you need to create any necessary formulas. This will vary based on your needs but we have a few posts on the topic below.

Automate Your Manual Bidding Strategy Using SuperMetrics
Treat Your Bid Management Like Your Daily Fantasy Players

Format Results for Upload

Depending on how you handled the gathering and manipulation of your keyword data you could skip this step and build a script around the data manipulation sheets but I recommend you don’t.

Creating a new sheet for the upload is cleaner and easier to reference. As a result any future troubleshooting is much easier.

By separating your data pull, manipulation, and final output you can isolate each set of processes in the event something goes wrong and you won’t accidentally break the functionality of multiple parts with a single change.

There are a few ways to do this but one clear winner, the query() function. We can write a single query to select certain columns and certain conditions.

Google Sheets query function example

It does not get any easier than that! If you are primarily an Excel user or unfamiliar with query() check out the post and be converted! Since we are talking about scripts, conveniently ignore the name of the site.

Coding is For Losers – The Google Sheets Query Function

Setting up the Bulk Upload

The bulkUpload functionality is straightforward. We initialize the upload file, define the column headers, move through the rows to append the data. Once the file it built we push it to AdWords.

There is an optional preview mode as well. If you include the setting, the changes will stay pending until you approve them in AdWords. This can be helpful for the first few runs and you can turn it off once everything works as intended.

AdWords script preview mode

Once this is in place set up any scheduling you need. If you are pulling the data via a script schedule it before so the data is ready.

As a side note, AdWords scripts are queued up within the scheduled hour. If you schedule the data pull and the upload within the same hour you could run into an issue if the data pull ends up occurring after the upload script runs.

Extending The Tool

Seeing as many PPC practitioners are spreadsheet enthusiasts, this bidding system can go a long way. The formulas can nest to your hearts desire, you could import external data, you could implement some top-level controls and switches to modify the output.

You can import from the conversion types report and make adjustments based on multiple conversion types. Since you already have the data you can create summary sheets as well to monitor changes and highlight portions of the account.

Conversion types report example

Extending the Use Case

You can adapt the bulk upload script for any type of upload too! You could use it for SQRs, changing ad groups statues, adding labels, or controlling budgets.

The only change you’d need to the script is changing the column headers. You can find a list of them in bulkupload entities, or download a template from AdWords.

Give the example script a shot with your own data sets. At the very least you’ll save a little time and make your routine account changes more consistent!

New Research! The State of PPC

This year's State of PPC annual report has arrived! Grab the download for the latest trends and benchmarks in the PPC industry.

x

Enter your email address below.