Thursday, February 28, 2013

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(; }
    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.


// Label Countdown
// Created By: Russ Savage
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()) {
  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 =;
      var l_iter = kw.labels().withCondition("Name STARTS_WITH '"+LABEL_PREFIX+"'").get();
      var label =; // lazy here because we know this keyword has a label
      var days_left = parseInt(label.getName().substr(LABEL_PREFIX.length)) - 1;
      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) {

Tuesday, February 26, 2013

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 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:

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.


* 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:
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")
    .withCondition("Impressions > 0")
    .orderBy("Impressions DESC")

  var tot_imps_weighted_qs = 0;
  var tot_imps = 0;
  while(kw_iter.hasNext()) {
    var kw =;
    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;

Update Your Bids from a Google Spreadsheet

Today we have a little script to help adjust bids at a large scale.  This is an example of how you can use the integration with Google Spreadsheets to update some of your bids for keywords.  I have provided a sample spreadsheet which you can make a copy of.

As long as the Adwords Account and the Google Docs account use the same login, it will be able to access any spreadsheet you have.  I suggest opening my sample spreadsheet and then making a copy if it in your account.  You can then replace the URL from the script below with the url of your copy of the spreadsheet.

Link to my sample spreadsheet:

 * Update Bids Using a Google Spreadsheet
 * Version 1.1
 * Created By: Russ Savage
function main() {
  var SPREADSHEET_URL = "Insert Url Here";
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName('KeywordBids');
  var data = sheet.getRange("A:E").getValues();
  var kwBidHash = {};
  for(var i in data) {
    if(i == 0) { continue; }
    if(data[i][0] === '') { break; }
    var kwKey = Utilities.base64Encode([data[i][0],data[i][1],data[i][2]].join('~~!~~'));
    kwBidHash[kwKey] = data[i];
  var kwIter = AdWordsApp.keywords()
    .withCondition("Status = ENABLED")
  while(kwIter.hasNext()) {
    var kw =;
    var campName = kw.getCampaign().getName();
    var kwKey = Utilities.base64Encode([campName,kw.getText(),kw.getMatchType()].join('~~!~~'));
    if(kwBidHash[kwKey]) {
      if(kwBidHash[kwKey][3] === "FIXED") {
        kw.setMaxCpc(kw.getMaxCpc() * (1+kwBidHash[kwKey][4]));

Authenticating to OAuth Services Using AdWords Scripts

Many APIs today use OAuth in order for users to authenticate and call their service.  One very popular example is the Twitter REST API.  Digging through the AdWords Scripting reference, you'll notice that one of the cool things you can do with AdWords Scripts is to pull data from a URL using the UrlFetchApp.

So the question I asked was "Could I write a script to authenticate using OAuth and retrieve data from the Twitter REST API?"

Obviously, the first place I looked was in the OAuthConfig class of the UrlFetchApp.  I set up the configuration and tried to make a call but I kept getting an authentication error.  A little research showed that I wasn't the only one having this problem.  The issue stems from the fact that normally, users need to authenticate through some sort of dialog box before they can access data. See this example about connecting to Picasa Web Albums for more details.

But then I found a post about someone managing to get around this by re-implementing the OAuth authentication system in their script and using a standard UrlFetchApp.fetch() request.

I was up for a challenge so I recreated it myself.  It turned out to be quite a bit of code, but in the end, the only thing you really need to worry about is getting the correct keys from Twitter and calling _build_authorization_string().


// Authenticate and Connect to OAuth Service
// Created By: Russ Savage
function main() {
  //Define the Twitter Keys and Secrets
  //More info on obtaining these can be found at
  var oauth_key_stuff = {
    "consumer_key" : "your consumer key",
    "consumer_secret" : "your consumer secret",
    "access_token" : "your access token",
    "access_token_secret" : "your access token secret"

  // Update this with the REST url you want to call.  I only tested it with GET
  // but i don't think there is anything stopping a POST request from working.
  var url_stuff = {  
    "http_method" : 'GET',
    "base_url" : ""

  //Add the parameters for the REST url you want to call.
  var url_param_stuff = {
    "screen_name" : "russellsavage" //hey that's me!
  // Don't touch this stuff
  var other_oauth_data = {
    "oauth_nonce" : Utilities.base64Encode(Math.random() +
          "secret_sauce" +
          (new Date()).getTime()).replace(/(?!\w)/g, ''),
    "oauth_signature_method" : "HMAC-SHA1",
    "oauth_timestamp" : Math.round((new Date()).getTime() / 1000.0),
    "oauth_version" : "1.0"
  // Here is where the magic happens
  var auth_string = _build_authorization_string(oauth_key_stuff,url_stuff,url_param_stuff,other_oauth_data);

  var options = {
    "headers" : { "Authorization" :  auth_string }
  var url = _build_url(url_stuff,url_param_stuff);
  var response = UrlFetchApp.fetch(url, options);
  var tweets = JSON.parse(response.getContentText());
  //now let's log my amazing tweets!
  for(var tweet in tweets) {
    var t = tweets[tweet];
  function _build_url(base_url,param_stuff){
    var url = base_url.base_url;
    if(param_stuff != {}) {
      url += '?';
    for(var key in param_stuff) {
      url += key + "=";
      url += encodeURIComponent(param_stuff[key]);
      url += '&';
    return url.slice(0,-1);
  function _build_param_string(auth_keys,url_data,oauth_data) {
    var data_for_param_string = {
      "oauth_consumer_key" : auth_keys.consumer_key,
      "oauth_nonce" : oauth_data.oauth_nonce,
      "oauth_signature_method" : oauth_data.oauth_signature_method,
      "oauth_timestamp" : oauth_data.oauth_timestamp,
      "oauth_token" : auth_keys.access_token,
      "oauth_version" : oauth_data.oauth_version
    // add additional url values
    for(var my_key in url_data) { 
      data_for_param_string[my_key] = url_data[my_key]; 
    // find and sort the keys for later
    var keys = [];
    for(var key in data_for_param_string) {
    //finally build and return the param string
    var param_string = "";
    for(var i in keys) {
      param_string += keys[i] + "=" + encodeURIComponent(data_for_param_string[keys[i]]);
      if(i < keys.length - 1) {
        param_string += "&";
    return param_string;
  function _build_sig_base_string(my_url_stuff,my_param_string) {
    return my_url_stuff.http_method +
      "&" + encodeURIComponent(my_url_stuff.base_url) +
      "&" + encodeURIComponent(my_param_string);
  function _build_sigining_key(my_key_stuff) {
    return encodeURIComponent(my_key_stuff.consumer_secret) + 
      "&" + encodeURIComponent(my_key_stuff.access_token_secret);
  function _build_oauth_signature(base_string,sign) {
    return Utilities.base64Encode(
  function _build_authorization_string(my_key_stuff,my_url_stuff,my_url_param_stuff,my_oauth_stuff) {
    var param_string = _build_param_string(my_key_stuff,my_url_param_stuff,my_oauth_stuff);
    var sig_base_string = _build_sig_base_string(my_url_stuff,param_string);
    var signing_key = _build_sigining_key(my_key_stuff);
    var oauth_signature = _build_oauth_signature(sig_base_string,signing_key);
    return "OAuth " +
           encodeURIComponent("oauth_consumer_key") + '="' + 
             encodeURIComponent(my_key_stuff.consumer_key) + '", ' +
           encodeURIComponent("oauth_nonce") + '="' + 
             encodeURIComponent(my_oauth_stuff.oauth_nonce) + '", ' +
           encodeURIComponent("oauth_signature") + '="' + 
             encodeURIComponent(oauth_signature) + '", ' +
           encodeURIComponent("oauth_signature_method") + '="' + 
             encodeURIComponent(my_oauth_stuff.oauth_signature_method) + '", ' +
           encodeURIComponent("oauth_timestamp") + '="' + 
             encodeURIComponent(my_oauth_stuff.oauth_timestamp) + '", ' +
           encodeURIComponent("oauth_token") + '="' + 
             encodeURIComponent(my_key_stuff.access_token) + '", ' +
           encodeURIComponent("oauth_version") + '="' + 
             encodeURIComponent(my_oauth_stuff.oauth_version) + '"';