Today we’re going to use Google Sheets as a control center of sorts for AdWords Scripts. The Sheets interface allows users to make adjustments via a spreadsheet rather than through the code itself. This method not only makes changes easier to implement but empowers less technical users to harness the power of scripts.
In this article, we’ll cover an example use case then go through a generalized example you can use in your own accounts. Although there won’t be a complete script there will be pointers throughout the article if you are inexperienced with scripts but want to try this yourself.
Background
Imagine a scenario where you have a team making many routine changes based on quickly changing feedback
In an extreme case this could be current events in which you want to make sure ads are showing based on changing external factors. You may increase or decrease bids by different ratio’s depending on marketing needs and recent trends.
We had a client in a similar situation. While building a dashboard to monitor this work, Jacob Brown asked if there was a way we could use that information to make changes to the account. Removing some of the tedious account editing would speed up the team’s workflow and reduce stress.
How To Approach the Setup
The first step is defining the inputs and outputs. What data do you need to feed into the script and what needs to happen?
Then you need to define what you want to do. This could be any action such as enabling or pausing an entity or changing max CPCs.
What to Include
A standardized account structure helps here as you can select only the pieces you need by aspects such as campaign name or keyword text. Labels work too if your structure is a mess or you need fine toothed selections.
In a later section, we’ll use this information we’ll build these selectors into our script.
What to Change
Now that we know what to change we need to define how we’ll change it. For this example, we will cover bidding. Let’s assume that we have a few bidding thresholds for all affected keywords.
We can assign these different levels such as aggressive, balanced, and hyper aggressive.
Balanced bids can be set to $2.00, aggressive can be set to $4,00 and hyper-aggressive can be set to $6.00. This isn’t always the best way. You can use percentages or different bid levels for each keyword selection as well but I’ll skip over that for simplicity’s sake.
Setting Up the Sheet
In the last section, we defined what needs to change and how we’ll change it. Now we need to put that information in a sheet so the script can access it.
We will set up two tables in the sheet. One for input targets and their bid aggression levels and another for bid amounts.
To enforce standard inputs, you can use data validation to control inputs. That way someone putting “Agggressive” does not get passed over because the script is looking for “Aggressive.”
Now we are done with the inputs, let’s handle the script!
Setting Up the Script
You’ll need a little bit of coding knowledge or confidence to tackle this next part. It’s nothing too intense though. All we have to do is get the keywords based on the table and make bid changes based on the data in the other table.
The steps are,
- Load and select the appropriate data from the sheet.
- Select keywords and bid levels based on data in the tables.
- Apply changes to those keywords.
First we’ll load the spreadsheet via openByURL(). If you are using the same sheet for other tasks, such as reporting, you can also select by sheet name.
Then we’ll need to select the data via getRange().getValues(). This will pull the values from this range of cells. Pulling the value of the cells means you can use to grab the output of formulas in a selected cell.
This will return a list of values. Use Logger.Log() if you’d like to see what it contains.
If you have it set up like the example in the article, it’ll return the rows in this format [keyword/theme, bid aggression].
Then you need to select your keywords. Below is an example function for returning a selection of keywords. We’ll use the function later to run through a list of keyword pulls.
We can use a loop to move through that piece by piece. For each item in the list, i, we will select [i][0] for the keyword/theme, and [i][1] for the aggression level. The variable i stands in as a place holder and the two pieces we need will always be at index 0 and 1.
Then we loop through each entry, use our keyword selector function to pull the keywords and apply the appropriate bids.
The last step is to set the script schedule. This can be a weakness as the scheduler runs as frequently as one hour. This means changes are not immediate but will happen within an hour.
Conclusion
We moved through the script portion quickly. Each approach will vary by the clients account set up but we hit the crucial points.
Alternatively if you’d rather not handle the bid changes and selectors in the script you can modify those directly in Google Sheets and use newCsvUpload() to do an automated bulk upload. Check out Mitch Larson’s presentation, Using AdWords Scripts to Create Your Own Ad Tech Landscape, for more details.
