Ad Creative Test Automation Script

This is a script I've been working on for a little while and I think it's really useful. I hope you do too.

Every SEM manager worth anything is constantly running creative tests. And many of you, since you are reading this blog, probably use scripts in some way to make that easier. But how do you figure out exactly when the tests start? They could be different for every AdGroup.

I have seen some people use labels, some people use schedules, but I never really liked having to keep track of those things. So I created this script which will keep track of the start dates for creative tests automatically.

The approach I took stores a copy of your ad ids in a google spreadsheet. Then it checks the current ads in the AdGroup against the ads from the spreadsheet and figures out if something changed by looking at the ad ids. If it has, the script assumes a new test has started and notes the date in the spreadsheet accordingly. Using this method, a marketer can be sure that the script is comparing statistics from the proper time frame to determine a test winner.

But then a reader mentioned that they wanted to be able to choose the metric to measure the ads by and also use a threshold to only start measuring after a certain number of clicks in the AdGroup. So I added that ability to the script as well. You only need to adjust a few parameters at the top of the script and you have a fully functional creative testing script in place.

Whenever it takes action on an ad by pausing it, the results will be sent to you in an email so that you can create a new challenger ad for that AdGroup.

I did a few things differently with this script. I always hate making my readers go into Google Docs and create a blank spreadsheet for my scripts to use as storage. So to get around that, this script will check for the existence of a special label in the account where the spreadsheet id is stored. If it finds one, it will use it. And if it is missing (like it will be on the first run), it will create the spreadsheet and label. This way, no messy spreadsheet urls in the scripts. It is a little bit of a workaround but until Google allows us to store additional config data for scripts, I thought this was an ok way to handle this.

As always, I'm open to your feedback on if this script is useful to you. What other metrics do you judge ad performance by? What else is this script missing?

Thanks,
Russ

/************************************
* Ad Creative Test Automation Script
* Version: 1.3
* Changelog v1.3 - Data is written to the spreadsheet a little faster
* Changelog v1.2 - Added additional threshold options
* Changelog v1.1 - Fixed issue with dates in email
* Created By: Russ Savage
* FreeAdWordsScripts.com
************************************/
// You can use any of the same values here as you can for METRIC below
var THRESHOLD = { metric : 'Clicks', value : 100 };
var TO = ['example@example.com'];
  
//Try any of these values for METRIC:
//AverageCpc, AverageCpm, AveragePageviews, AveragePosition, 
//AverageTimeOnSite, BounceRate, Clicks, ConversionRate, 
//Conversions, Cost, Ctr, Impressions
var METRIC = 'Ctr';
var ASC_OR_DESC = 'ASC'; //ASC - pause ad with lowest value, DESC - pause ad with highest value
  
function main() {
  //Start by finding what has changed in the account
  var ad_map = buildCurrentAdMap();
  var prev_ad_map = readMapFromSpreadsheet();
  prev_ad_map = updatePreviousAdMap(prev_ad_map,ad_map);
  writeMapToSpreadsheet(prev_ad_map);
    
  //Now run through the adgroups to find creative tests
  var ag_iter = AdWordsApp.adGroups().get();
  var paused_ads = [];
  while(ag_iter.hasNext()) {
    var ag = ag_iter.next();
    if(!prev_ad_map[ag.getId()]) { continue; }
      
    //Here is the date range for the test metrics
    var last_touched_str = _getDateString(prev_ad_map[ag.getId()].last_touched,'yyyyMMdd');
    var get_today_str = _getDateString(new Date(),'yyyyMMdd');
      
    var ag_stats = ag.getStatsFor(last_touched_str, get_today_str);
    if(ag_stats['get'+THRESHOLD.metric]() >= THRESHOLD.value) {
      var ad_iter = ag.ads().withCondition('Status = ENABLED')
                            .forDateRange(last_touched_str, get_today_str)
                            .orderBy(METRIC+" "+ASC_OR_DESC).get();
      var ad = ad_iter.next();
      var metric = ad.getStatsFor(last_touched_str, get_today_str)['get'+METRIC]();
      ad.pause();
      paused_ads.push({a : ad, m : metric});
    }
  }
  sendEmailForPausedAds(paused_ads);
}
  
// A function to send an email with an attached report of ads it has paused
function sendEmailForPausedAds(ads) {
  if(ads.length == 0) { return; } //No ads paused, no email
  var email_body = '"' + ['CampaignName','AdGroupName','Headline','Desc1','Desc2','DisplayUrl',METRIC].join('","') + '"\n';
  for(var i in ads) {
    var ad = ads[i].a;
    var metric = ads[i].m;
    email_body += '"' + [ad.getCampaign().getName(),
                         ad.getAdGroup().getName(),
                         ad.getHeadline(),
                         ad.getDescription1(),
                         ad.getDescription2(),
                         ad.getDisplayUrl(),
                         metric
                        ].join('","') +
                  '"\n';
  }
  var date_str = _getDateString(new Date(),'yyyy-MM-dd');
  var options = { attachments: [Utilities.newBlob(email_body, 'text/csv', "FinishedTests_"+date_str+'.csv')] };
  var subject = 'Finished Tests - ' + date_str;
  for(var i in TO) {
    MailApp.sendEmail(TO[i], subject, 'See attached.', options);
  }
}
  
//Given two lists of ads, this checks to make sure they are the same.
function sameAds(ads1,ads2) {
  for(var i in ads1) {
    if(ads1[i] != ads2[i]) { return false; }
  }
  return true;
}
  
//This reads the stored data from the spreadsheet
function readMapFromSpreadsheet() {
  var ad_map = {};
  var sheet = SpreadsheetApp.openById(findSpreadsheetId()).getActiveSheet();
  var data = sheet.getRange('A:C').getValues();
  for(var i in data) {
    if(data[i][0] == '') { break; }
    var [ag_id,last_touched,ad_ids] = data[i];
    ad_map[ag_id] = { ad_ids : (''+ad_ids).split(','), last_touched : new Date(last_touched) };
  }
  return ad_map;
}
  
//This will search for a label containing the spreadsheet id
//If one isn't found, it will create a new one and the label as well
function findSpreadsheetId() {
  var spreadsheet_id = "";
  var label_iter = AdWordsApp.labels().withCondition("Name STARTS_WITH 'history_script:'").get();
  if(label_iter.hasNext()) {
    var label = label_iter.next();
    return label.getName().split(':')[1]; 
  } else {
    var sheet = SpreadsheetApp.create('AdGroups History');
    var sheet_id = sheet.getId();
    AdWordsApp.createLabel('history_script:'+sheet_id, 'stores sheet id for adgroup changes script.');
    return sheet_id;
  }
}
  
//This will store the data from the account into a spreadsheet
function writeMapToSpreadsheet(ad_map) {
  var toWrite = [];
  for(var ag_id in ad_map) {
    var ad_ids = ad_map[ag_id].ad_ids;
    var last_touched = ad_map[ag_id].last_touched;
    toWrite.push([ag_id,last_touched,ad_ids.join(',')]);
  }
  writeToSpreadsheet(toWrite);
}

// Write the keyword data to the spreadsheet
function writeToSpreadsheet(toWrite) {
  var sheet = SpreadsheetApp.openById(findSpreadsheetId()).getActiveSheet();
  sheet.clear();
  
  var numRows = sheet.getMaxRows();
  if(numRows < toWrite.length) {
    sheet.insertRows(1,toWrite.length-numRows); 
  }
  var range = sheet.getRange(1,1,toWrite.length,toWrite[0].length);
  range.setValues(toWrite);
}
  
//This builds a map of the ads in the account so that it is easy to compare
function buildCurrentAdMap() {
  var ad_map = {}; // { ag_id : { ad_ids : [ ad_id, ... ], last_touched : date } }
  var ad_iter = AdWordsApp.ads().withCondition('Status = ENABLED').get();
  while(ad_iter.hasNext()) {
    var ad = ad_iter.next();
    var ag_id = ad.getAdGroup().getId();
    if(ad_map[ag_id]) {
      ad_map[ag_id].ad_ids.push(ad.getId());
      ad_map[ag_id].ad_ids.sort();
    } else {
      ad_map[ag_id] = { ad_ids : [ad.getId()], last_touched : new Date() };
    }
  }
  return ad_map;
}
  
//This takes the old ad map and the current ad map and returns an
//updated map with all changes.
function updatePreviousAdMap(prev_ad_map,ad_map) {
  for(var ag_id in ad_map) {
    var current_ads = ad_map[ag_id].ad_ids;
    var previous_ads = (prev_ad_map[ag_id]) ? prev_ad_map[ag_id].ad_ids : [];
    if(!sameAds(current_ads,previous_ads)) {
      prev_ad_map[ag_id] = ad_map[ag_id];
    }
  }
  return prev_ad_map;
}
  
//Helper function to format the date
function _getDateString(date,format) {
  return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format); 
}

Finding Anomalies In Your Keywords, Adgroups, and Ads

UPDATE 2013-07-23: Added the ability to add labels to your ads as well. This might be helpful for creative testing.

In a recent post from SearchEngineLand, Larry Kim wants PPC Managers to stop being lazy. Link baiting aside, he has some good points. I think much of the time spent pouring over client spreadsheets is really trying to answer the simple question of "Where Are The Anomalies?"

Anomalies are keywords or AdGroups that seem to be performing very differently than their brothers and sisters in the same AdGroup or Campaign. Maybe you have an AdGroup with 15 keywords and one or two keywords seem to be getting nearly all of the clicks. Or maybe you just have an AdGroup that seems to be eating up a good chunk of your budget and you want to have more control over it. In either of these cases, you need some way to quickly identify which Keywords or AdGroups you want to take action on.

So I created the following script to help me do just that. It has a little bit of statistics embedded in it to calculate the mean and standard deviation of a set of entities (AdGroups or Keywords). Then I apply a label to any entity that seems to be more than two standard deviations away from the mean, which would indicate it is performing much better or much worse than its siblings. That way, I can take action on those entities pretty easily in my account.

This script will also send you an email each day with a summary of the entities that it deems to be anomalies. Your goal is to fix these issues by moving them into their own Campaigns and AdGroups or maybe even getting rid of them altogether (possibly negatives).

Right now, the script will check every metric that is available in the AdWordsApp.stats object. It could be easily modified to check for things like cost per conversion or profit per impression. You will see in the code below where you can remove stats that don't interest you as well.

One note of warning is that I do not claim to be a statistics guy, so this may or may not be a valid way to look at things. Hopefully, it will help you locate the issues in an account quickly and fix them so that you can spend more time being lazy :)

Also, I am open to your comments and suggestions for this script. Is it useful to anyone else?

Thanks,
Russ

/**************************************
* Find the Anomalies
* Created By: Russ Savage
* Version: 1.2
* Changelog v1.2
*  - Fixed divide by 0 errors
*  - Changed SIG_FIGS to DECIMAL_PLACES
* Changelog v1.1
*  - Added ability to tag ad anomalies as well
* FreeAdWordsScripts.com
**************************************/
var DATE_RANGE = 'LAST_30_DAYS';
var DECIMAL_PLACES = 3;
var STANDARD_DEVIATIONS = 2;
var TO = ['you@your_domain.com'];
 
function main() {
  // This will add labels to and send emails about adgroups, keywords and ads. Remove any if you like.
  var levels_to_tag = ['adgroup','keyword','ad'];
  for(var x in levels_to_tag) {
    var report = getContentRows(levels_to_tag[x]);
    var entity_map = buildEntityMap(levels_to_tag[x]);
    for(var parent_id in entity_map) {
      var child_list = entity_map[parent_id];
      var stats_list = Object.keys(child_list[0].stats);
      for(var i in stats_list) {
        var mean = getMean(child_list,stats_list[i]);
        var stand_dev = getStandardDev(child_list,mean,stats_list[i]);
        var label_name = stats_list[i]+"_anomaly";
        report += addLabelToAnomalies(child_list,mean,stand_dev,stats_list[i],label_name,levels_to_tag[x]);
      }
    }
    sendResultsViaEmail(report,levels_to_tag[x]);
  }
}
  
//Takes a report and the level of reporting and sends and email
//with the report as an attachment.
function sendResultsViaEmail(report,level) {
  var rows = report.match(/\n/g).length - 1;
  if(rows == 0) { return; }
  var options = { attachments: [Utilities.newBlob(report, 'text/csv', level+"_anomalies_"+_getDateString()+'.csv')] };
  var email_body = "There are " + rows + " " + level + "s that have abnormal performance. See attachment for details.";
  var subject = 'Abnormal ' + _initCap(level) + ' Entities Report - ' + _getDateString();
  for(var i in TO) {
    MailApp.sendEmail(TO[i], subject, email_body, options);
  }
}
  
//Helper function to return a single row of the report formatted correctly
function toReportRow(entity,level,label_name) {
  var ret_val = [AdWordsApp.currentAccount().getCustomerId(),
                 entity.getCampaign().getName()];
  ret_val.push( (level == 'adgroup') ? entity.getName() : entity.getAdGroup().getName() );
  if(level == 'keyword') {
    ret_val = ret_val.concat([entity.getText(),entity.getMatchType()]); 
  } else if(level == 'ad') {
    ret_val = ret_val.concat([entity.getHeadline(),entity.getDescription1(),entity.getDescription2(),entity.getDisplayUrl()]); 
  }
  ret_val.push(label_name);
  return '"' + ret_val.join('","') + '"\n';
}
  
//Helper function to return the column headings for the report
function getContentRows(level) {
  var ret_val = ['AccountId','CampaignName','AdGroupName'];
  if(level == 'keyword') {
    ret_val = ret_val.concat(['KeywordText','MatchType']); 
  } else if(level == 'ad') {
    ret_val = ret_val.concat(['Headline','Description1','Description2','DisplayUrl']);
  }
  ret_val.push('LabelName');
  return '"' + ret_val.join('","') + '"\n';
}
  
//Function to add the labels to the entities based on the standard deviation and mean.
//It returns a csv formatted string for reporting
function addLabelToAnomalies(entites,mean,sd,stat_key,label_name,level) {
  createLabelIfNeeded(label_name);
  var report = '';
  for(var i in entites) {
    var entity = entites[i]['entity'];
    var deviation = Math.abs(entites[i]['stats'][stat_key] - mean);
    if(sd != 0 && deviation/sd >= STANDARD_DEVIATIONS) {
      entity.applyLabel(label_name);
      report += toReportRow(entity,level,label_name);
    } else {
      entity.removeLabel(label_name);
    }
  }
  return report;
}
  
//This is a helper function to create the label if it does not already exist
function createLabelIfNeeded(name) {
  if(!AdWordsApp.labels().withCondition("Name = '"+name+"'").get().hasNext()) {
    AdWordsApp.createLabel(name);
  }
}
  
//This function returns the standard deviation for a set of entities
//The stat key determines which stat to calculate it for
function getStandardDev(entites,mean,stat_key) {
  var total = 0;
  for(var i in entites) {
    total += Math.pow(entites[i]['stats'][stat_key] - mean,2);
  }
  if(Math.sqrt(entites.length-1) == 0) {
    return 0;
  }
  return round(Math.sqrt(total)/Math.sqrt(entites.length-1));
}
  
//Returns the mean (average) for the set of entities
//Again, stat key determines which stat to calculate this for
function getMean(entites,stat_key) {
  var total = 0;
  for(var i in entites) {
    total += entites[i]['stats'][stat_key];
  }
  if(entites.length == 0) {
    return 0;
  }
  return round(total/entites.length);
}
  
//This function returns a map of the entities that I am processing.
//The format for the map can be found on the first line.
//It is meant to work on AdGroups and Keywords
function buildEntityMap(entity_type) {
  var map = {}; // { parent_id : [ { entity : entity, stats : entity_stats } ], ... }
  var iter = getIterator(entity_type);
  while(iter.hasNext()) {
    var entity = iter.next();
    var stats = entity.getStatsFor(DATE_RANGE);
    var stats_map = getStatsMap(stats);
    var parent_id = getParentId(entity_type,entity);
    if(map[parent_id]) { 
      map[parent_id].push({entity : entity, stats : stats_map});
    } else {
      map[parent_id] = [{entity : entity, stats : stats_map}];
    }
  }
  return map;
}
  
//Given an entity type (adgroup or keyword) this will return the parent id
function getParentId(entity_type,entity) {
  switch(entity_type) {
    case 'adgroup' :
      return entity.getCampaign().getId();
    case 'keyword':
      return entity.getAdGroup().getId();
    case 'ad':
      return entity.getAdGroup().getId();
  }
}
  
//Given an entity type this will return the iterator for that.
function getIterator(entity_type) {
  switch(entity_type) {
    case 'adgroup' :
      return AdWordsApp.adGroups().forDateRange(DATE_RANGE).withCondition("Impressions > 0").get();
    case 'keyword' :
      return AdWordsApp.keywords().forDateRange(DATE_RANGE).withCondition("Impressions > 0").get();
    case 'ad' :
      return AdWordsApp.ads().forDateRange(DATE_RANGE).withCondition("Impressions > 0").get();
  }
}
  
//This returns a map of all the stats for a given entity.
//You can comment out the things you don't really care about.
function getStatsMap(stats) {
  return { // You can comment these out as needed.
          avg_cpc : stats.getAverageCpc(),
          avg_cpm : stats.getAverageCpm(),
          avg_pv : stats.getAveragePageviews(),
          avg_pos : stats.getAveragePosition(),
          avg_tos : stats.getAverageTimeOnSite(),
          bounce : stats.getBounceRate(),
          clicks : stats.getClicks(),
          cv : stats.getConversionRate(),
          conv : stats.getConversions(),
          cost : stats.getCost(),
          ctr : stats.getCtr(),
          imps : stats.getImpressions()
         };
}
  
//Helper function to format todays date
function _getDateString() {
  return Utilities.formatDate((new Date()), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
}
  
//Helper function to capitalize the first letter of a string.
function _initCap(str) {
  return str.replace(/(?:^|\s)\S/g, function(a) { return a.toUpperCase(); });
}

// A helper function to make rounding a little easier
function round(value) {
  var decimals = Math.pow(10,DECIMAL_PLACES);
  return Math.round(value*decimals)/decimals;
}

Report on Broken Urls In Your Account

Note: If you are looking for a version of this script to run at the MCC level, check out Monitor Broken Links Using MCC Level Scripts.

UPDATED: 2013-05-20: Based on a comment from a reader, the script now only checks active campaigns and adgroups and it also only checks each url once.

UPDATED: 2013-04-28: Based on a comment from a reader, I made some updates to this script which include adding the response code to the email and formatting the results as an attachment.

It happens to the best of us. Sometimes, we remove pages on our site or update links and forget to make the corresponding change our SEM accounts. So tonight I put together a quick script to run through all your ads and keywords and create an email report with any of them that return a 404 Not Found or a 500 Server Error response code. You can easily add more error codes to check for by adding them to the BAD_CODES array at the beginning of the script.

Thanks,
Russ

/****************************
* Find Broken Urls In Your Account
* Version 1.1
* ChangeLog v1.1
*  - Updated to only see Text Ads
* Created By: Russ Savage
* FreeAdWordsScripts.com
****************************/
function main() {
  // You can add more if you want: http://goo.gl/VhIX
  var BAD_CODES = [404,500];
  var TO = ['email@example.com'/*,'email_address_2@example.com'*/];
  var SUBJECT = 'Broken Url Report - ' + _getDateString();
  var HTTP_OPTIONS = {
    muteHttpExceptions:true
  };
   
  //Let's look at ads and keywords for urls
  var iters = [
    //For Ad Level Urls
    AdWordsApp.ads()
      .withCondition("Status = 'ENABLED'")
      .withCondition("AdGroupStatus = 'ENABLED'")
      .withCondition("CampaignStatus = 'ENABLED'")
      .withCondition("Type = 'TEXT_AD'")
      .get(),
    //For Keyword Level Urls
    AdWordsApp.keywords()
      .withCondition("Status = 'ENABLED'")
      .withCondition("DestinationUrl != ''")
      .withCondition("AdGroupStatus = 'ENABLED'")
      .withCondition("CampaignStatus = 'ENABLED'")
      .get()
    ];
  
  var already_checked = {}; 
  var bad_entities = [];
  for(var x in iters) {
    var iter = iters[x];
    while(iter.hasNext()) {
      var entity = iter.next();
      if(entity.getDestinationUrl() == null) { continue; }
      var url = entity.getDestinationUrl();
      if(url.indexOf('{') >= 0) {
        //Let's remove the value track parameters
        url = url.replace(/\{[0-9a-zA-Z]+\}/g,'');
      }
      if(already_checked[url]) { continue; }
      var response_code;
      try {
        Logger.log("Testing url: "+url);
        response_code = UrlFetchApp.fetch(url, HTTP_OPTIONS).getResponseCode();
      } catch(e) {
        //Something is wrong here, we should know about it.
        bad_entities.push({e : entity, code : -1});
      }
      if(BAD_CODES.indexOf(response_code) >= 0) {
        //This entity has an issue.  Save it for later. 
        bad_entities.push({e : entity, code : response_code});
      }
      already_checked[url] = true;
    }
  }
  var column_names = ['Type','CampaignName','AdGroupName','Id','Headline/KeywordText','ResponseCode','DestUrl'];
  var attachment = column_names.join(",")+"\n";
  for(var i in bad_entities) {
    attachment += _formatResults(bad_entities[i],",");
  }
  if(bad_entities.length > 0) {
    var options = { attachments: [Utilities.newBlob(attachment, 'text/csv', 'bad_urls_'+_getDateString()+'.csv')] };
    var email_body = "There are " + bad_entities.length + " urls that are broken. See attachment for details.";
     
    for(var i in TO) {
      MailApp.sendEmail(TO[i], SUBJECT, email_body, options);
    }
  }  
}
 
//Formats a row of results separated by SEP
function _formatResults(entity,SEP) {
  var e = entity.e;
  if(typeof(e['getHeadline']) != "undefined") {
    //this is an ad entity
    return ["Ad",
            e.getCampaign().getName(),
            e.getAdGroup().getName(),
            e.getId(),
            e.getHeadline(),
            entity.code,
            e.getDestinationUrl()
           ].join(SEP)+"\n";
  } else {
    // and this is a keyword
    return ["Keyword",
            e.getCampaign().getName(),
            e.getAdGroup().getName(),
            e.getId(),
            e.getText(),
            entity.code,
            e.getDestinationUrl()
           ].join(SEP)+"\n";
  }
}
 
//Helper function to format todays date
function _getDateString() {
  return Utilities.formatDate((new Date()), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
}