An AdWords Script to Track Quality Scores

This script is outdated, but there’s a new one that is much better and much easier to use. Check out the newest Quality Score Tracker.[/su_box]Last month I wrote about AdWords Scripts and came to the conclusion that there is basically just one really exciting thing about them: You can share them! Besides Google, I haven’t found anyone doing that, so I’d like to make the first step.

The script I’m presenting here tracks selected keyword quality scores over time. If a quality score changes, it can notify you via email. The script also uses a Google spreadsheet to track the quality score of each keyword. Over time it logs the complete quality score history of your keywords in a simple, easy to use spreadsheet.

Newer Version Available
This script is outdated, but there’s a new one that is much better and much easier to use. Check out the newest Quality Score Tracker.

You can use this script to track quality scores regularly and see changes over time. You can also use it to diagnose a set of keywords when needed. And, of course, you can use the history for your own analysis later.

Setup

Even though scripts are easy to share, a quick setup is necessary here. There are three steps:

  1. Get the spreadsheet
  2. Get the script
  3. Put the keywords you want to track into the spreadsheet (up to about 100, preferably no more than 80)

The Spreadsheet

Log into your AdWords account and open this mostly empty spreadsheet. You might have to sign in there, too (top right corner). Click File and then Make a copy (choose whatever name you like). The spreadsheet is now in your account and no longer publicly accessible. Keep the browser tab open.

The Script

Go to your AdWords account and click Automation and then Scripts on the left hand side of the AdWords interface. Next, click Create script. Copy and paste the following code:

var spreadsheet_url = "INSERT_SPREADSHEET_URL_HERE";
var email_address = "YOUR_EMAIL_HERE";

function main() {
  var matches = new RegExp('key=([^&#]*)').exec(spreadsheet_url);
  if (!matches || !matches[1]) throw 'Invalid spreadsheet URL: ' + spreadsheetUrl;
  var spreadsheetId = matches[1];
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName('Input Keywords');
  var sheet_values = sheet.getDataRange().getValues();
  var result_range = new Array(); // holds the results to write back
  var alert_text = new Array();
  var history = new Array();
  var currentTime = new Date();
  var today = (currentTime.getMonth() + 1) + "/" + currentTime.getDate() + "/" + currentTime.getFullYear();
  
  for(i = 1; i < sheet_values.length; i++){ // make sure there is actually some data here if(sheet_values[i][0] == "") continue; result_range[i] = [today, 0]; var campaign_name = sheet_values[i][0]; var adgroup_name = sheet_values[i][1]; // remove single quotes at the beginning of the keyword (Excel sometimes adds them in front of modified broad matches, like: '+keyword) var keyword_text = sheet_values[i][2].replace(/^[']+/g, ""); var latest_check = sheet_values[i][3]; var old_quality_score = sheet_values[i][4]; var keywordIterator = AdWordsApp.keywords() .withCondition("CampaignName = '" + campaign_name + "'") .withCondition("AdGroupName = '" + adgroup_name + "'") // this won't let us filter for phrase or exact matches so we have to remove brackets and quotation marks (broad match modifiers are fine) .withCondition("Text = \"" + keyword_text.replace(/[\[\]\"]/g, "") + "\"") .get(); while(keywordIterator.hasNext()){ var keyword = keywordIterator.next(); // since we couldn't filter phrase or exact matches directly, we have to make sure that this is the right keyword if(keyword.getText() == keyword_text){ var current_quality_score = keyword.getQualityScore(); // save quality score for results result_range[i][1] = current_quality_score; // for the history we also note the change or whether this keyword is new if(old_quality_score > 0) var change = current_quality_score - old_quality_score;
        else var change = "NEW";
        var row = [today, campaign_name, adgroup_name, keyword_text, current_quality_score, change];
        history.push(row);
        // if we have a previously tracked quality score and it's different from the current one, we make a note to log it and send it via email later
        if(old_quality_score > 0 && current_quality_score != old_quality_score){
          alert_text.push(current_quality_score + "\t" + old_quality_score + "\t" + change + "\t" + latest_check + "\t" + keyword_text);
        }
        // we've found the keyword we were looking for so we look no further
        break;
      }
    }
  }
  // write results to spreadsheet
  result_range.splice(0,1);
  sheet.getRange(2, 4, result_range.length, 2).setValues(result_range);
  // write history to spreadsheet
  var history_sheet = spreadsheet.getSheetByName('QS history');
  history_sheet.getRange(history_sheet.getLastRow()+1, 1, history.length, 6).setValues(history);
  // if we've made notes for alerts then we send them via email
  if(alert_text.length){
    var message = "The following quality score changes were discovered:\nNew\tOld\tChange\tPreviously checked\tKeyword\n";
    for(i = 0; i < alert_text.length; i++) message += alert_text[i] + "\n";
    // also include a link to the spreadsheet
    message += "\n" + "Settings and complete history are available at " + spreadsheet_url;    
    // if we have an email address we send out a notification
    if(email_address && email_address != "YOUR_EMAIL_HERE"){
      MailApp.sendEmail(email_address, "AdWords quality score changes detected", message);
    }
    // log the message
    Logger.log(message);
  }
}

Go back to your spreadsheet and copy the URL from your browser’s address bar. At the beginning of the script, replace INSERT_SPREADSHEET_URL_HERE with this URL.

If you want to receive notifications, replace YOUR_EMAIL_HERE with your email address. If you don’t want any notifications, just ignore this.

Name the script “Quality Score Tracker” and click Save.

The Keywords

Go to the spreadsheet again and make sure to select the sheet Input Keywords. Now list the keywords you want to track, along with their campaigns and adgroups. Leave the columns for Latest Check and Latest Quality Score empty.

You can always come back to this and add or remove keywords later.

Using the Script

To run the script, click the button Run script now. Before the first run, AdWords asks you to authorize the script.

Since AdWords Scripts aren’t exactly fast, execution takes a while, but you don’t have to wait around for the script to finish. If the script finds any changes, it sends an email notification at the end. Notifications are also logged and can be found under the View details link in the script logs. In its first run the script naturally won’t find any changed quality scores.

Whenever there’s a new keyword you want to track, or an old keyword you no longer want tracked, you can always change the set of keywords in the spreadsheet. Simply add keywords to the first sheet (Input Keywords).

If you ever want to analyze the quality score history of your keywords, check the second sheet (QS history). You can’t break anything there, but it’s probably best if you copy the history so far into a new spreadsheet and then play with the data there. Filter out the zeros from the Change column to only look at changed quality scores over time.

Limitations

Like all AdWords Scripts at the time of this writing, this one has a big, frustrating limitation: execution time. Since AdWords Scripts are slow and not supposed to run more than five minutes, the number of quality scores you can track is also limited.

To make matters worse, performance fluctuates, making it impossible to determine this number. With the same set of keywords, on some days the script was able to check only 50, on others 150 quality scores. The system seems to be getting faster though. Based on the last three weeks it seems that with 80 keywords or less you’re on the safe side.

Words of Warning

With this script you can track quality scores of a limited, but still big number of keywords. And you can run this script as often as you like. However, overdoing this might drive you crazy.

From my experience with this script and similar ones, I can tell that there are more quality score fluctuations than you might think. In most instances you won’t be able to find a reason for a change. Sometimes a quality score changes over night and then returns to the original value the day after. Noticing the first change, especially if it’s a drop, can make you worry and take unnecessary steps. For some people it even causes distress.

If you don’t want to go crazy over this, my advice would be to track only the ten or twenty most important keywords and not to react too quickly to changes, unless there is a really good reason.

Is this useful?

The purpose of writing this script was to try out AdWords Scripts and to see whether they are in fact something that’s easily shared. Due to the necessary setup I’m not so sure about the easy sharing with this one.

So what do you think? Is this script helpful? Do you have another idea for a useful script? I’d love to hear your feedback.

Updates

  • To accommodate keywords with apostrophes, line 32 of the script has be updated.
  • In September 2012 Google announced that AdWords Scripts can now run up to 30 minutes, so you could track six times as many keywords. Now you can also schedule scripts to run daily, weekly, or monthly. My advice is to track the most important keywords weekly or monthly.
  • As of Jan 26, a new version of this script is available here: AdWords Quality Score Tracker Version 2.0