Manage Ads Based on Airport Delays

There are tons of APIs out on the internet today. Any of these can be incorporated into your AdWords scripts to automate some of your account management.

The script below uses the Airport Status Service from FAA.gov to enable and pause ads when there is a delay at a major airport. This would be useful for any business that is near an airport that may benefit from delays such as restaurants, bars, or hotels. Your ads could say things like:



You can add or remove airport codes from the list at the top. All you need to do is tag your ads with the label [airport_code]_delay ("ORD_delay","ATL_delay").

What other dynamic data could you use to optimize your ads? Movie times? Sports games? Let me know in the comments below and I'll see what I can come up with.

Thanks,
Russ

/*********************************************
* Pause/Enable Ads Based On Airport Delays
* Version 1.1
* ChangeLog v1.1 
*  - Added ability to completely pause non-delay ads
* Created By: Russ Savage
* FreeAdWordsScripts.com
*********************************************/
// For this to work, you need to add a label to all
// the ads for each airport.  For example, PIT_normal
// or SFO_normal
var PAUSE_NORMAL_ADS_DURING_DELAY = false;
var DELAY_SUFFIX = '_delay'; //the suffix on the label for delayed ads
var NORMAL_SUFFIX = '_normal'; //the suffix on the label for normal ads

var AIR_CODES = ["ATL","ANC","AUS","BWI","BOS","CLT","MDW","ORD","CVG","CLE",
                 "CMH","DFW","DEN","DTW","FLL","RSW","BDL","HNL","IAH","HOU",
                 "IND","MCI","LAS","LAX","MEM","MIA","MSP","BNA","MSY","JFK",
                 "LGA","EWR","OAK","ONT","MCO","PHL","PHX","PIT","PDX","RDU",
                 "SMF","SLC","SAT","SAN","SFO","SJC","SNA","SEA","STL","TPA",
                 "IAD","DCA"];

function main() {
  var faaUrl = "http://services.faa.gov/airport/status/";
  var args = "?format=application/json";
  for(var i in AIR_CODES) {
    try{
      var resp = UrlFetchApp.fetch(faaUrl + AIR_CODES[i] + args);
      if( resp.getResponseCode() == 200 ) {
        var json = Utilities.jsonParse(resp.getContentText());
        if(json.delay == "false") {
          Logger.log("No delays at "+json.IATA+". Pausing delay ads if any are running.");
          turnOffDelayAds(json.IATA);
          if(PAUSE_NORMAL_ADS_DURING_DELAY) {
            turnOnNonDelayAds(json.IATA);
          }
        } else {
          Logger.log("Delays in "+json.IATA+" Reason: "+json.status.reason);
          Logger.log("Turning on delay ads if there are any.");
          turnOnDelayAds(json.IATA);
          if(PAUSE_NORMAL_ADS_DURING_DELAY) {
            turnOffNonDelayAds(json.IATA);
          }
        }
      }
    }
    catch(e) {
      Logger.log("Error: " + e.message);
    }
  }
}

function turnOffDelayAds(airportCode) {
  var labelName = airportCode + DELAY_SUFFIX;
  var adIter = AdWordsApp.ads()
    .withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = ENABLED")
    .withCondition("Status = ENABLED")
    .get();
  while(adIter.hasNext()) {
    adIter.next().pause();
  }
}

function turnOffNonDelayAds(airportCode) {
  var labelName = airportCode + NORMAL_SUFFIX;
  var adIter = AdWordsApp.ads()
    .withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = ENABLED")
    .withCondition("Status = ENABLED")
    .get();
  while(adIter.hasNext()) {
    adIter.next().pause();
  }
}

function turnOnDelayAds(airportCode) {
  var labelName = airportCode + DELAY_SUFFIX;
  var adIter = AdWordsApp.ads()
    .withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = ENABLED")
    .withCondition("Status = PAUSED")
    .get();
  while(adIter.hasNext()) {
    adIter.next().enable();
  }
}

function turnOnNonDelayAds(airportCode) {
  var labelName = airportCode + NORMAL_SUFFIX;
  var adIter = AdWordsApp.ads()
    .withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = ENABLED")
    .withCondition("Status = PAUSED")
    .get();
  while(adIter.hasNext()) {
    adIter.next().enable();
  }
}

Fixing Capitalization Errors in Your Ads

One of the issues that marketers face when they have systems for automatically building creatives from feeds is quality control. I recently ran into an issue where I created around 1,000 new ads where the city name was IN ALL CAPS. Google of course doesn't like that much and I didn't like the idea of having to manually update 1,000 ads, so I put together the following script.

The script runs through your account for all of the disapproved ads and tries to find ones with words in ALL CAPS. I'm not sure what Google's limit on caps is, but in the script below, it looks for anything with 3 or more capital letters in a row. Then it replaces them, creates a new ad, and if it was successful, deletes the old ad.

I ran into some trouble trying to identify the reasons the ads were disapproved. It is not yet available in the API, but it has been requested. The other issue was that sometimes, the new ad you create will fail, which will cause you to delete the old one and not create a new one. I solved that one by counting the ads in the adgroup before and after I created the new ad in order to make sure my ad had been created. Again, I posted the feature request in the adwords scripting forums.

Thanks,
Russ

//-----------------------------------
// Fix Ads with EXCESSIVE CAPITALIZATION
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  var find_caps = /[A-Z]{3,}/g;
  var SEP = '~~@~~'; // this needs to be something you would never put in your ads.
  var ad_iter = AdWordsApp.ads().withCondition("ApprovalStatus = 'DISAPPROVED'").get();
  
  while(ad_iter.hasNext()) {
    var ad = ad_iter.next();
    var old_ad_cnt = get_ad_count(ad.getAdGroup());
    var old_ad_str = [ad.getHeadline(),ad.getDescription1(),ad.getDescription2(),ad.getDisplayUrl()].join(SEP);
    var new_ad_str = old_ad_str;
    Logger.log("Before:"+old_ad_str);
    var m = "";
    while((m = find_caps.exec(new_ad_str)) != null) {
      new_ad_str = replace_all(new_ad_str,m[0],init_cap(m[0]),false);
    }
    Logger.log("After:"+new_ad_str);
    if(old_ad_str != new_ad_str) {
      var [new_headline,new_desc1,new_desc2,new_disp_url] = new_ad_str.split(SEP);
      ad.getAdGroup().createTextAd(new_headline, new_desc1, new_desc2, new_disp_url, ad.getDestinationUrl());
      var new_ad_cnt = get_ad_count(ad.getAdGroup());
      if(new_ad_cnt == (old_ad_cnt+1)) {
        ad.remove();
      }
    } else {
      Logger.log("Skipping because no changes were made."); 
    }
  }
  
  function init_cap(s) {
    return s.charAt(0).toUpperCase() + s.slice(1).toLowerCase();
  }
  
  // This function was adapted from: http://dumpsite.com/forum/index.php?topic=4.msg8#msg8 
  function replace_all(original,str1, str2, ignore) {
    return original.replace(new RegExp(str1.replace(/([\/\,\!\\\^\$\{\}\[\]\(\)\.\*\+\?\|\<\>\-\&])/g,"\\$&"),(ignore?"gi":"g")),(typeof(str2)=="string")?str2.replace(/\$/g,"$$$$"):str2);
  }
  
  function get_ad_count(ad_group) {
    var ad_iter = ad_group.ads().get();
    var new_ad_cnt = 0;
    while(ad_iter.hasNext()) {
      ad_iter.next();
      new_ad_cnt++;
    }
    return new_ad_cnt;
  }
}

Label Countdown for Ignoring New Elements

Robert asked on one of my scripts how to ignore keywords that were recently added to his account. Unfortunately, the API doesn't give you a good way to do this although I figured there was a way to do it using labels.

So I put together the following script that you can use to automatically create a label-based countdown for elements that you want to ignore in your scripts. Each time you add new elements to your account, you can apply a label to it using the format LABEL_PREFIX_. So, if you want your scripts to ignore a new element for 30 days, apply the label "days_left_30" to that element. If you schedule the script to run every day, the number of days left on the label will be reduced by one each day. Once the number of days reaches zero, the label will be removed from the entity.

In the scripts that you want to ignore new elements, add the following function to the bottom of the script (before the last curly brace):

  function _build_label_list() {
    //Build a list of labels to exclude in your .withCondition()
    var LABEL_PREFIX = 'days_left_'; 
    var label_iter = AdWordsApp.labels().withCondition("Name STARTS_WITH '"+LABEL_PREFIX+"'").get();
    var label_array = [];
    while(label_iter.hasNext()) { label_array.push(label_iter.next().getName()); }
    return "'"+label_array.join("','")+"'"
  }

And then add the following

.withCondition("LabelNames CONTAINS_NONE ["+_build_label_list()+"]")

to any iterators you have in your other scripts. Good luck, and if you have any questions, feel free to ask.

Thanks,
Russ

//-----------------------------------
// Label Countdown
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  var LABEL_PREFIX = "days_left_"; // you can change this if you want
  
  // First lets build a list of labels to work with
  var label_iter = AdWordsApp.labels().withCondition("Name STARTS_WITH '"+LABEL_PREFIX+"'").get();
  var labels_array = [];
  while(label_iter.hasNext()) {
    labels_array.push(label_iter.next().getName());
  }
  if(labels_array.length > 0) { 
    var labels_str = "['" + labels_array.join("','") + "']";
    // grab all the keywords with the labels we want to countdown
    var kw_iter = AdWordsApp.keywords().withCondition("LabelNames CONTAINS_ANY "+labels_str).get();
    
    while(kw_iter.hasNext()) {
      var kw = kw_iter.next();
      var l_iter = kw.labels().withCondition("Name STARTS_WITH '"+LABEL_PREFIX+"'").get();
      var label = l_iter.next(); // lazy here because we know this keyword has a label
      var days_left = parseInt(label.getName().substr(LABEL_PREFIX.length)) - 1;
      kw.removeLabel(label.getName());
      if(days_left != 0) {
        var new_label_name = LABEL_PREFIX+days_left;
        // Create a new label if it doesn't exist
        if(labels_array.indexOf(new_label_name) == -1) {
          AdWordsApp.createLabel(new_label_name);
          labels_array.push(new_label_name);
        }
        kw.applyLabel(new_label_name);
      }
    }
  }
}

Store Account Level Quality Score in a Google Spreadsheet

EDIT 2013-04-22: Check out an updated version of this script that stores Account, Campaign, and AdGroup level quality score.

EDIT 2013-03-18: I added DESC to the .orderBy() clause so that it orders the impressions from highest to least.

I was reading a post on SearchEngineLand.com the other day about storing and analyzing your account level quality score.

The Author of the article provided a script, but I figured I could come up with my own version pretty easily.  In the following script, I am using the same logic to calculate the account level quality score but I am looking at all campaigns and all keywords with an impression in the last 30 days.

Depending on your account size, this might be too much data to analyze in the 30 minute limit imposed by AdWords, but ordering by impressions should get you the top 50000 (again, another AdWords limit) keywords with impressions.

You can find the spreadsheet I am storing data in here: https://docs.google.com/spreadsheet/ccc?key=0Aotb6eheEOpodGNKMW1UWkZKekc5NWxkR3Zra3lzWVE

Your best bet is to copy that sheet, erase my data (except the headers) and copy the new spreadsheet url into the script. If you have multiple accounts that this will run in, you can store the data in the same spreadsheet but add additional tabs for the account name. Then update the ACCOUNT_NAME variable and you should be all set.

Thanks,
Russ

/***************************************
* Store Account Level Quality Score in Google Spreadsheet.
* Version 1.1
* ChangeLog v1.1
*  - Changed ACCOUNT_NAME to SHEET_NAME and updated the default value.
*  - Removed getSpreadsheet function
*
* Created By: Russ Savage
* Based on script originally found at: http://goo.gl/rTHbF
* FreeAdWordsScripts.com
*********************************/
function main() {
  var SPREADSHEET_URL = "Your Spreadsheet Url Goes Here";
  var SHEET_NAME = 'Sheet1';
  var today = new Date();
  var date_str = [today.getFullYear(),(today.getMonth() + 1),today.getDate()].join("-");
  
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var qs_sheet = spreadsheet.getSheetByName(SHEET_NAME);
  
  var kw_iter = AdWordsApp.keywords()
    .withCondition("Status = ENABLED")
    .forDateRange("LAST_30_DAYS")
    .withCondition("Impressions > 0")
    .orderBy("Impressions DESC")
    .withLimit(50000)
    .get();

  var tot_imps_weighted_qs = 0;
  var tot_imps = 0;
  
  while(kw_iter.hasNext()) {
    var kw = kw_iter.next();
    var kw_stats = kw.getStatsFor("LAST_30_DAYS");
    var imps = kw_stats.getImpressions();
    var qs = kw.getQualityScore();
    tot_imps_weighted_qs += (qs * imps);
    tot_imps += imps;
  }
    
  var acct_qs = tot_imps_weighted_qs / tot_imps;
  
  qs_sheet.appendRow([date_str,acct_qs]);
}