4 Ways To Take Your AdWords Scripts To The Next Level

Last week, I presented at HeroConf in Austin and shared some advanced tips for automating AdWords account management with an audience of PPC account managers. They seemed to like what I had to say; so, I’ll share a few of the more advanced scripting tips with you here. When you manage PPC accounts, you likely […]

Chat with SearchBot

Last week, I presented at HeroConf in Austin and shared some advanced tips for automating AdWords account management with an audience of PPC account managers. They seemed to like what I had to say; so, I’ll share a few of the more advanced scripting tips with you here.

When you manage PPC accounts, you likely experience at least one of these two frustrations:

  • You know exactly what you should be doing, but there aren’t enough hours in the day to get it done
  • You’ve bought a tool that automates AdWords account management, but you find yourself locked into a cookie-cutter solution that doesn’t let you tweak things based on the unique aspects of your business

AdWords Scripts are a great solution to address these two problems, though they’re not perfect. They don’t lend themselves well towards working with very large accounts; they require marketers to write code (which could have disastrous outcomes), and they put their outputs in logs that can be hard to read. Luckily, thanks to some recent changes made by Google and a few smart integration moves, all of these issues can now be solved.

Automating Very Large AdWords Accounts

One of the biggest limitations of AdWords Scripts is that they can only interact with 100,000 items every time a script runs. So, if you’re trying to calculate the account quality score for an account with 150,000 keywords, or you need to change bids for more than 100,000 ad groups and keywords, then you’re out of luck. This limitation is still in place, but Google has now added reporting as a beta feature to Scripts, and this can help you get around the 100k item limitation.

For example, when you need to get stats for every keyword because you’re trying to calculate your quality score, instead of requesting stats for every keyword in an account individually (which can quickly bump you into the 100k limit), now you can request a keyword report and use that to do all your calculations, even when there are more than 100,000 keywords in the report.

Reports can also help if you’re making changes to an account. Say you want to change bids, for example: rather than using your quota of 100k items to calculate what the new bids should be, do all those calculations from a report and save your quota to make the required bid changes. Most of the time, bid changes will only be necessary for a portion of the account.

Now, if you still get stuck, consider creating scripts that act on one campaign at a time rather than on the entire account. And, if working on a single campaign is not practical, use an iterator that goes through your account alphabetically. The code snippet below shows how to go through all your ad groups alphabetically:

var alphabet = "0123456789abcdefghijklmnopqrstuvwxyz";
var alphabetStartLetter = "0";
var alphabetStartPosition = alphabet.indexOf(alphabetStartLetter);
Logger.log ("start at ad groups starting with '" + 
alphabetStartLetter + "' (" + alphabetStartPosition + ")");

for(var i = alphabetStartPosition; i < alphabet.length; i++) {
var adGroupNameStartsWith = alphabet[i];
var adGroupIterator = AdWordsApp.adGroups()
.withCondition("Status = ENABLED")
.withCondition("AdGroupName STARTS_WITH_IGNORE_CASE '" + 
adGroupNameStartsWith + "'")
.get();

 

After a script with this code has run, look at the logs to see the last letter of the alphabet that was used before the script timed out or hit the limit, and then change the variable alphabetStartLetter to that value. When you run the script again, it’ll pick up from roughly the same place where it left off last time.

Make Scripts Accessible For People Who Can’t Code

I wish everybody had at least a basic understanding of programming, but we all know that’s not the case; so, when you want to create automation that relies on some inputs from a user and you want to make it accessible to non-coders, you can move the inputs and outputs into a friendlier UI, like the AdWords frontend or a Google Spreadsheet.

This idea came from https://www.freeadwordsscripts.com/: use labels in your AdWords account to tell a script what to do. For example, if you create a script that manages bids to hit a target CPA goal, rather than coding the CPA in the script, apply it as a label to your campaign.

Now, when your script runs, look at the labels for each campaign, and if the label matches a certain pattern of text like “Target: 50,” it knows that the target CPA is $50. In the code snippet that follows, you can see how I can set different labels to tell the script the type of bid management I want to do, the target, and the min and max bids it’s allowed to set.

var labelSelector = campaign.labels();
var labelIterator = labelSelector.get();
while (labelIterator.hasNext()) {
var label = labelIterator.next();
var labelName = label.getName();
var labelDesc = label.getDescription();
if(labelName.toLowerCase().indexOf("target") !=-1) {
var target = labelDesc;
Logger.log(" Bid Target: " + target);
}
if(labelName.toLowerCase().indexOf("bid style") !=-1) {
var bidStyle = labelDesc;
Logger.log(" Bid style: " + bidStyle);
}
if(labelName.toLowerCase().indexOf("min cpc") !=-1) {
var minCPC = labelDesc;
Logger.log(" Min CPC: " + minCPC);
}
if(labelName.toLowerCase().indexOf("max cpc") !=-1) {
var maxCPC = labelDesc;
Logger.log(" Max CPC: " + maxCPC);
}
}

 

In the AdWords interface, you can now create labels and assign them to campaigns, ad groups or keywords to tell the script what to do.

AdWords labels as script inputs

Use AdWords labels to tell a script what to do

Another way to make scripts less dangerous for everyone is to add a step to review and approve any changes before they are made. When you’re coding complex scripts that make a lot of decisions inside an account, it’s a good idea to take a look at the proposed changes before they are made. You can do this in a limited way by previewing the script, but if you want to see all changes, it makes sense to let a script run to completion rather than cutting it off at 30 seconds, which is what the preview functionality does.

One way I’ve gotten around this is to have 2 separate scripts: one that processes the data and spits out recommendations in a spreadsheet and another that looks for the spreadsheet and applies any changes that are marked as “approved.” Since AdWords Scripts have a direct integration with Google Spreadsheets, outputting the results to a spreadsheet is as easy as a few lines of code like this:

var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var previewChanges = spreadsheet.insertSheet("Changes");
previewChanges.appendRow([“Approved”, "Date", "Campaign", 
"Ad Group", "Keyword", "Imp", "Clicks", "Conversions", "Cost",
"QS", "Avg Pos"]);

 

Then, my other script opens the same spreadsheet and only changes my account if I have put a ‘1’ in the column that indicates my approval.

Reading a spreadsheet can be done with code like this:

var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var previewChanges = spreadsheet.insertSheet("Changes");

var rows = dataSheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();

// Read Header Rows so we know which column has the approval field
var row = values[1];
for(var i = 0; i < 2; i++) {
var value = row[i];
if(value.toLowerCase().indexOf("approved") != -1) {
var approvedIndex = i;
} else if(value.toLowerCase().indexOf("campaign") != -1) {
var campaignIndex = i;
}
}

// Read spreadsheet rows
for(var i = 2; i<numRows; i++) {
var spreadsheetRow = i+1;
var row = values[i];
var approved = row[approvedIndex];
// do something with the data
}

 

Make The Output From Scripts Prettier

I’ve explained before that it is possible to push data from a script into a spreadsheet and use that to create a chart that can be embedded elsewhere. But, if you’re like me and use dashboards from Cyfe.com, it’s much better to just give them the data so they can chart it on their end. That way the date ranges on all my different charts line up nicely, and I can more easily start to look for correlations in my data. Pushing data to Cyfe or one of the many other dashboard tools typically requires calling an API endpoint with the data encoded in JSON.

adwords quality score chart

Historical AdWords account quality score charted in a dashboard widget.

Here’s a simple example of the code you need to push your quality score to a Cyfe endpoint.

Date.prototype.yyyymmdd = function() {
var yyyy = this.getFullYear().toString();
var mm = (this.getMonth()+1).toString(); // getMonth() is 
zero-based
var dd  = this.getDate().toString();
return yyyy + (mm[1]?mm:"0"+mm[0]) + (dd[1]?dd:"0"+dd[0]); // 
padding
};

d = new Date();
var eventDate = d.yyyymmdd();

var payload = { "data":  [
{
"Date":  eventDate,
"Quality Score":  acctQS
}
],
"onduplicate":  {
"Quality Score":  "replace"
},
"average": {
"Quality Score": 1
}
}

var jsonData = JSON.stringify(payload);
Logger.log(jsonData);
var options =
{
"method" : "post",
"payload" : jsonData
};

var response = UrlFetchApp.fetch(dashboardEndPointURL, options);
var responseCode = response.getResponseCode();
var responseText = response.getContentText();
Logger.log(responseCode + ": " + responseText);

 

Dealing With Special Characters In Names, Ad Text Or Keywords

One final tip is about how to deal with special characters in an AdWords script. When I shared my Quality Score script, a lot of people had an issue getting it to work because their campaign names were longer than a single word or it contained special characters. To deal with that, do a little bit of regex to clean up the name as shown for the campaignName below:

var keywordIterator = AdWordsApp.keywords()
.withCondition("CampaignName != \"" + 
campaignName.replace(/[\[\]\"]/g, "") + "\"")
.get();

 

If you like the idea of using Scripts to automate some of the more tedious account management tasks, and you want to learn more, come see me speak at SMX Advanced on June 12th at 3 pm in the session, “Amazing Paid Search Tactics & Tools.”


Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Frederick Vallaeys
Contributor
Frederick (“Fred”) Vallaeys was one of the first 500 employees at Google where he spent 10 years building Google Ads and teaching advertisers how to get the most out of it as the first Google AdWords Evangelist. Today he is the Cofounder and CEO of Optmyzr, a PPC management SaaS company focused on making search, shopping, and display ads easier to manage with rules, scripts, reports, audits, and more. He is a frequent guest speaker at events where he inspires organizations to be more innovative and use AI and Automation Layering to become better marketers. His latest book, Unlevel the Playing Field, follows his best-seller, Digital Marketing in an AI World.

Get the must-read newsletter for search marketers.