Store Keyword Performance Report In Amazon S3

It's been a few weeks since my last post about putting data into Amazon S3, and in it, I mentioned that you could use it to store keyword performance reports for multiple AdWords Accounts in a single place. Here is an updated version which does exactly that. It combines my previous post about storing AdWords Account Performance Report in a Google Spreadsheet (with some changes of course).

Also, with this post, I will start versioning all of my scripts so that you can be sure you always have the latest version.

Thanks,
Russ

//-----------------------------------
// Store Keyword Performance Report in Amazon S3
// Created By: Russ Savage
// Version: 1.0
// FreeAdWordsScripts.com
//-----------------------------------
var ACCESS_KEY = 'YOUR_ACCESS_KEY_HERE';
var SECRET_KEY = 'YOUR_SECRET_KEY_HERE';
var S3_BUCKET = 'YOUR_S3_BUCKET_NAME_HERE';

function main() {
  var date_str = Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(),'yyyy-MM-dd');
  var file_name = 'adwords_keyword_perf_'+AdWordsApp.currentAccount().getCustomerId() + '_' + date_str+'.csv';
  putDataToBucket(S3_BUCKET,'/'+file_name,getKeywordPerformanceReport());
}

function putDataToBucket(bucket,file_path,data) {
  var auth_options = {  
    method : 'PUT',
    base_url : "http://" + bucket + ".s3.amazonaws.com",
    s3_bucket : bucket,
    path : file_path,
    headers : { 
       "Date" : getDate(),
       "Content-Type" : "application/x-www-form-urlencoded"
    },
  };
  
  var auth_string = generateAuthString(auth_options);
  auth_options.headers["Authorization"] = auth_string;
  
  var options = {
    method : auth_options.method,
    headers : auth_options.headers,
    payload : data
  };
  
  return (UrlFetchApp.fetch(auth_options.base_url+auth_options.path, options).getResponseCode() == 200);
}

function generateAuthString(url) {
  var string_to_sign =  getStringToSign(url);
  Logger.log(string_to_sign);
  var signature = getSignature(SECRET_KEY,string_to_sign);
  return "AWS" + " " + ACCESS_KEY + ":" + signature;
}

function getSignature(SECRET_KEY,string_to_sign) {
  return  Utilities.base64Encode(
    Utilities.computeHmacSignature(
      Utilities.MacAlgorithm.HMAC_SHA_1,
      string_to_sign,
      SECRET_KEY,
      Utilities.Charset.UTF_8
    )
  );
}

function getStringToSign(url,params) {
  var method = url.method;
  var date = url.headers.Date;
  
  return method + "\n" + "\n" + 
    url.headers['Content-Type'] + "\n" +
    date + "\n" + 
    getCanonicalizedAmzHeaders(url);
}

function getCanonicalizedAmzHeaders(url) {
  var ret_val = "/" + url.s3_bucket;
  ret_val += url.path;
  return ret_val;
}

function getDate() {
  return Utilities.formatDate(new Date(),"GMT", "EEE, dd MMM yyyy HH:mm:ss +0000");
}


function getKeywordPerformanceReport() {
  var date_range = 'LAST_7_DAYS';
   
  var columns = ['Date',
                 'CampaignName',
                 'CampaignStatus',
                 'AdGroupName',
                 'AdGroupStatus',
                 'IsNegative',
                 'Id',
                 'KeywordMatchType',
                 'KeywordText',
                 'DestinationUrl',
                 'FirstPageCpc',
                 'MaxCpc',
                 'MaxCpm',
                 'PercentCpa',
                 'ClickType',
                 'Device',
                 'Slot',
                 'CpcBidSource',
                 'AverageCpc',
                 'AverageCpm',
                 'AveragePosition',
                 'PreferredPosition',
                 'QualityScore',
                 'Clicks',
                 'ConversionRate',
                 'ConversionRateManyPerClick',
                 'Conversions',
                 'ConversionsManyPerClick',
                 'ConversionValue',
                 'Cost',
                 'CostPerConversion',
                 'CostPerConversionManyPerClick',
                 'Ctr',
                 'Impressions',
                 'Cost',
                 'ValuePerConversion',
                 'ValuePerConversionManyPerClick',
                 'ViewThroughConversions'];
  var columns_str = columns.join(',') + " ";
   
  var report_iter = AdWordsApp.report(
    'SELECT ' + columns_str +
    'FROM KEYWORDS_PERFORMANCE_REPORT ' +
    'DURING ' + date_range, {
      includeZeroImpressions: false,
      apiVersion: 'v201302'
    }).rows();
  
  var ret_data = '"' + columns.join('","') + '"\n';
  while(report_iter.hasNext()) {
    var row = report_iter.next();
    var row_array = [];
    for(var i in columns) {
       row_array.push(row[columns[i]]);
    }
    ret_data += '"' + row_array.join('","') + '"\n';
  }
  
  return ret_data;
}

Put AdWords Data to Amazon S3 Using Scripts

I'm not sure about you, but writing all these scripts is putting me on Google Spreadsheet overload. With the release of the ability to access any of the AdWords reports, the data quickly starts to get unwieldy. Once things get over a few thousand rows, I much prefer to use Excel or store and manipulate data in a database.

So I was looking for other ways to get data from an AdWords report out of the scripts world and I thought of Amazon S3. Amazon offers a RESTful API through their Amazon Web Services that allows you to store and retrieve large amounts of data from the cloud. I figured I could use them to store some CSV files of account/campaign/keyword performance for downloading or processing later, maybe by another script or software.

The script below tries to encapsulate all the logic required to build and authorize a proper S3 put request. I followed the documentation found here.

To get started for free, sign up for Amazon S3 here and find your access and secret keys here:


Next, create a new S3 bucket to hold all your data through the AWS console. Fill in the details below and try a test file. Ideally, you would combine this with one of the other AdWords reporting scripts found here to start storing your data in the cloud.

NOTE: This is a very specific set of functions which represent the bare minimum required information to PUT a file to Amazon S3. For more information, please check out the full Amazon S3 Docs.

Thanks,
Russ

//-----------------------------------
// Put Data To Amazon S3
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
var ACCESS_KEY = 'YOUR_ACCESS_KEY_HERE';
var SECRET_KEY = 'YOUR_SECRET_KEY_HERE';
var S3_BUCKET = 'YOUR_S3_BUCKET_NAME_HERE';
  

function main() {
  var date_str = Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(),'yyyy-MM-dd');
  var file_name = 'adwords_keyword_perf_'+date_str+'.csv';
  putDataToBucket(S3_BUCKET,'/'+file_name,'this is where the data from an AdWords report would go.');
}


function putDataToBucket(bucket,file_path,data) {
  var auth_options = {  
    method : 'PUT',
    base_url : "http://" + bucket + ".s3.amazonaws.com",
    s3_bucket : bucket,
    path : file_path,
    headers : { 
       "Date" : getDate(),
       "Content-Type" : "application/x-www-form-urlencoded"
    },
  };
  
  var auth_string = generateAuthString(auth_options);
  auth_options.headers["Authorization"] = auth_string;
  
  var options = {
    method : auth_options.method,
    headers : auth_options.headers,
    payload : data
  };
  
  return (UrlFetchApp.fetch(auth_options.base_url+auth_options.path, options).getResponseCode() == 200);
}

//Generates an AWS Auth String
//For more info, see the AWS docs - http://goo.gl/m5nCe
function generateAuthString(url) {
  var string_to_sign =  getStringToSign(url);
  Logger.log(string_to_sign);
  var signature = getSignature(SECRET_KEY,string_to_sign);
  return "AWS" + " " + ACCESS_KEY + ":" + signature;
}

//Generates an AWS Signature
//For more info, see the AWS docs - http://goo.gl/m5nCe
function getSignature(SECRET_KEY,string_to_sign) {
  return  Utilities.base64Encode(
    Utilities.computeHmacSignature(
      Utilities.MacAlgorithm.HMAC_SHA_1,
      string_to_sign,
      SECRET_KEY,
      Utilities.Charset.UTF_8
    )
  );
}

//Generates an AWS string to sign
//For more info, see the AWS docs - http://goo.gl/m5nCe
function getStringToSign(url,params) {
  var method = url.method;
  var date = url.headers.Date;
  
  return method + "\n" + "\n" + 
    url.headers['Content-Type'] + "\n" +
    date + "\n" + 
    getCanonicalizedAmzHeaders(url);
}

//Generates the Canonicalized Amazon Headers (not really)
//For more info, see the AWS docs - http://goo.gl/m5nCe
function getCanonicalizedAmzHeaders(url) {
  var ret_val = "/" + url.s3_bucket;
  ret_val += url.path;
  return ret_val;
}

function getDate() {
  return Utilities.formatDate(new Date(),"GMT", "EEE, dd MMM yyyy HH:mm:ss +0000");
}

Put Current Crime Statistics in Your Creatives

I live in Chicago and one of the things this city is known for is it's colorful history of Organized Crime. So I thought it might be interesting to see if I could use that history to my advantage.

Chicago runs a website that collects and aggregates the statistics of the city: City of Chicago Data Portal. Here you can find all sorts of data sets, but the one I was interested in was the Crime Stats.

After a little exploration, I found that the data is available in JSON (an easy to consume format for software). So I put together a script to read that data and then automatically update the keyword AdParams with the total count of the different types of crime.

This might come in handy for any companies that sell insurance or security systems, but I'm sure there's other companies that might be able to use this info.

Other major cities in the US keep similar sets of data as well:

• New York, NY Crime Data
• Seattle, WA Crime Data
• A listing of all datasets

What other public records data could your business use to target your customers? Let me know in the comments and I'll take a look.

Thanks,
Russ

//-----------------------------------
// Put Chicago Crime Stats in Your Creatives
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  // You can get this link by going here: http://goo.gl/tfNgM
  // Apply some filters and then click export > api
  // This end point is good for all of 2013
  var DATA_ENDPOINT = "http://data.cityofchicago.org/resource/pga9-zdiw.json";
  var CAMPAIGN_PREFIX = 'Crime_Data_Chicago_'; //All your campaigns start with this
  var AD_PARAM = 1; // 1 or 2
  
  try {
    var json = Utilities.jsonParse(UrlFetchApp.fetch(DATA_ENDPOINT).getContentText());
    var summary = summarizeCrimeStats(json);
    //logCrimeSummary(summary);
    
    for(var i in summary) {
      var total = totalPrimaryDescription(i,summary);
      var kw_iter = AdWordsApp.keywords()
                      .withCondition("CampaignName CONTAINS_IGNORE_CASE '"+CAMPAIGN_PREFIX+i+"'")
                      .get();
      while(kw_iter.hasNext()) {
        var kw = kw_iter.next();
        kw.setAdParam(AD_PARAM, total);
      }
    }
  }catch(e) {
  }
}

// A helper function to aggregate the data by primary description
function totalPrimaryDescription(key,summary) {
  var tot = 0;
  for(var i in summary[key]) {
    tot += summary[key][i];
  }
  return tot;
}

//This function takes in a json formatted object and stores the count of instances
//in a 2 dimentional hash of [Primary Description][Secondary Description]
function summarizeCrimeStats(json) {
  var crime_summary = {};
  for(var i in json) {
    var crime = json[i];
    if(crime_summary[crime._primary_decsription]) {
      if(crime_summary[crime._primary_decsription][crime._secondary_description]) {
        crime_summary[crime._primary_decsription][crime._secondary_description]++;
      }else{
        crime_summary[crime._primary_decsription][crime._secondary_description] = 1;
      }
    }else{
      crime_summary[crime._primary_decsription] = {};
      crime_summary[crime._primary_decsription][crime._secondary_description] = 1;
    }
  }
  return crime_summary;
}

//Just a helper function to print out the summary info so that
//I can find the data I'm interested in.
function logCrimeSummary(crime_summary) {
  for(var i in crime_summary) {
    for(var x in crime_summary[i]) {
      Logger.log([i,x,crime_summary[i][x]].join(', '));
    }
  }
}

Store Account, Campaign, AdGroup, and Keyword Level Quality Score

UPDATE: 2014-02-15 - I updated the script to v2.0 which speeds the script up dramatically, includes the ability to store data into a CSV and use whatever date range you like for the stats.

I've had a lot of good feedback on my previous script: Store Account Level Quality Score. To that end, I've been asked a few times about storing Campaign and/or AdGroup level quality scores as well so I figured it would be a good time for an update.

Below is a script that will attempt to store Account, Campaign, and AdGroup level quality scores for the top 50000 keywords in your account. It will store the results in a google spreadsheet. In order for this to work, you will need to set up a new google spreadsheet. with three sheets named Account, Campaign, and AdGroup. You can simply make a copy of my spreadsheet found here (File > Make a copy...) : Account, Campaign, AdGroup Quality Score Spreadsheet (No longer needed as of v1.1)

Thanks,
Russ

Pause or Enable Campaigns, Keywords or Ads on a Specific Date

UPDATE 2013-12-13: Added the ability to work on Ads as well.

I've seen some questions posted around the forums asking about enabling or pausing keywords on a given day. It is pretty simple to do using labels and scripts. The following script will run through your account and look for keywords labeled with "Pause on " or "Enable on " and perform that action. If you would like to just use dates, you can set the two prefix values to be empty strings (""). The format of the date is YYYY-MM-DD (2013-05-01).

Thanks,
Russ

/**************************************************
* Pause or Enable Campaigns, Keywords or Ads on a Given Date
* Version 1.2
* Changelog v1.2 - Added ability to pause Campaigns
* Changelog v1.1 - Added ability to run on Ads
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************************/
var ENTITY = 'Keyword'; //or Ad or Campaign
var PAUSE_PREFIX = "Pause on "; //look for labels "Pause on 2013-04-11"
var ENABLE_PREFIX = "Enable on "; //look for labels "Enable on 2013-04-11"

  
function main() {
  var todayStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
  var pauseStr = PAUSE_PREFIX+todayStr;
  var enableStr = ENABLE_PREFIX+todayStr;
  Logger.log("Looking for labels: " + [pauseStr,enableStr].join(' and '));
   
  var labelsArray = buildLabelArray(pauseStr,enableStr);
   
  if(labelsArray.length > 0) { 
    var labelsStr = "['" + labelsArray.join("','") + "']";
    var entityIter;
    if(ENTITY === 'Keyword') {
      entityIter = AdWordsApp.keywords().withCondition("LabelNames CONTAINS_ANY "+labelsStr).get();
    } else if(ENTITY === 'Ad') {
      entityIter = AdWordsApp.ads().withCondition("LabelNames CONTAINS_ANY "+labelsStr).get();
    } else if(ENTITY === 'Campaign') {
      entityIter = AdWordsApp.campaigns().withCondition("LabelNames CONTAINS_ANY "+labelsStr).get();
    } else {
      throw 'Invaid ENTITY type. Should be Campaign, Keyword or Ad. ENTITY:'+ENTITY;
    }
     
    while(entityIter.hasNext()) {
      var entity = entityIter.next();
      pauseEntity(entity, pauseStr);
      enableEntity(entity, enableStr);
    }
  }
}
 
//Helper function to build a list of labels in the account
function buildLabelArray(pauseStr,enableStr) {
  var labelsArray = [];
  try {
    var labelIter = AdWordsApp.labels().withCondition("Name IN ['"+pauseStr+"','"+enableStr+"']").get();
    while(labelIter.hasNext()) {
      labelsArray.push(labelIter.next().getName());
    }
    return labelsArray;
  } catch(e) {
    Logger.log(e);
  }
  return [];
}
 
//Helper function to pause entities
function pauseEntity(entity, pauseStr) {
  var labelIter = entity.labels().withCondition("Name = '"+pauseStr+"'").get();
  if(labelIter.hasNext()) {
    entity.pause();
    entity.removeLabel(pauseStr);
  }
}
 
//Helper function to enable entities
function enableEntity(entity, enableStr) {
  var labelIter = entity.labels().withCondition("Name = '"+enableStr+"'").get();
  if(labelIter.hasNext()) {
    entity.enable();
    entity.removeLabel(enableStr);
  }
}

Merge Labels from Multiple Campaigns

This script is a follow up to one of my previous scripts: Merge Multiple Campaigns Together For Enhanced Campaigns Migration. This one is meant to run after you have merged your campaigns together and it will copy all the labels from the campaigns, adgroups, ads, and keywords from the ORIGIN_CAMAPIGN_NAMES into the DESTINATION_CAMPAIGN_NAME.

Thanks,
Russ

//-----------------------------------
// Merge Labels from Multiple Campaigns
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
var DESTINATION_CAMPAIGN_NAME = "Destination Campaign Name";
var ORIGIN_CAMPAIGN_NAMES = ["Origin Campaign Name 1","Origin Campaign Name 2"];

function main() {
  var label_iter = AdWordsApp.labels().get();
  while(label_iter.hasNext()) {
    var label = label_iter.next();
    //Pre-build all the iterators
    var iters = [
      label.campaigns().withCondition("Name IN ['"+ORIGIN_CAMPAIGN_NAMES.join("','")+"']").get(),
      label.adGroups().withCondition("CampaignName IN ['"+ORIGIN_CAMPAIGN_NAMES.join("','")+"']").get(),
      label.ads().withCondition("CampaignName IN ['"+ORIGIN_CAMPAIGN_NAMES.join("','")+"']").get(),
      label.keywords().withCondition("CampaignName IN ['"+ORIGIN_CAMPAIGN_NAMES.join("','")+"']").get()
    ];
    for(var i in iters) {
      var iter = iters[i];
      while(iter.hasNext()) {
        _copyLabels(iter.next()); 
      }
    }
  }
}

//Copies the labels from entity in Origin campaign 
//to entity with the same name in dest campaign
function _copyLabels(entity) {
  var iter;
  if(_getEntityType(entity) == "Campaign") {
    // it's a campaign
    iter = AdWordsApp.campaigns()
             .withCondition("Name = '"+DESTINATION_CAMPAIGN_NAME+"'")
             .get();
  } else if(_getEntityType(entity) == "AdGroup") {
    // it's an adgroup
    iter = AdWordsApp.adGroups()
             .withCondition("CampaignName = '"+DESTINATION_CAMPAIGN_NAME+"'")
             .withCondition("Name = '"+entity.getName()+"'")
             .get();
  } else if(_getEntityType(entity) == "Ad") {
    // it's an ad
    iter = AdWordsApp.ads()
             .withCondition("CampaignName = '"+DESTINATION_CAMPAIGN_NAME+"'")
             .withCondition("AdGroupName = '"+entity.getAdGroup().getName()+"'")
             .withCondition("Headline = '"+entity.getHeadline()+"'")
             .withCondition("Description1 = '"+entity.getDescription1()+"'")
             .withCondition("Description2 = '"+entity.getDescription2()+"'")
             .withCondition("DisplayUrl = '"+entity.getDisplayUrl()+"'")
             .get();
  } else if(_getEntityType(entity) == "Keyword") {
    // it's a keyword
    iter = AdWordsApp.keywords()
             .withCondition("CampaignName = '"+DESTINATION_CAMPAIGN_NAME+"'")
             .withCondition("AdGroupName = '"+entity.getAdGroup().getName()+"'")
             .withCondition("Text = '"+entity.getText()+"'")
             .withCondition("KeywordMatchType = '"+entity.getMatchType()+"'")
             .get();
  }
  
  while(iter.hasNext()) {
    _copyLabelsHelper(entity,iter.next());
  }
  
}

//Copy the labels form orig entity to dest entity
function _copyLabelsHelper(orig,dest) {
  var label_iter = orig.labels().get();
  while(label_iter.hasNext()) {
    dest.applyLabel(label_iter.next().getName());
  }
}

//Returns a text representation of an entity
//For a better way, check: http://goo.gl/kZL3X
function _getEntityType(obj) {
  if(typeof(obj['getCampaign']) == "undefined") {
    return 'Campaign';
  }
  if(typeof(obj['getAdGroup']) == "undefined") {
    return 'AdGroup';
  }
  if(typeof(obj['getHeadline']) != "undefined") {
    return "Ad";
  }
  if(typeof(obj['getText']) != "undefined") {
    return "Keyword";
  }
  return null;
}

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");
}

Dynamically Adjust Campaign Budgets v2.0

UPDATE 2013-04-06: Made sure daily budgets are multiplied by 30.5 to turn them into monthly budgets (line 150).

UPDATE 2013-04-06: Made sure that when the budgets are reset, they are divided by 30.5 to turn them into daily budgets (line 95).

UPDATE 2013-04-07: A big thank you to FoxSUP for helping me track down an issue with updating the budgets. Fixed line 78 to multiply the current budget by 1+to_change instead of just to_change. Also fixed a bug in calculating the change(line 56).

This is an update to a previous script I put together to dynamically adjust campaign budgets. There have been a few asks from some of the comments and I figured it would be easier to combine them into a new post.

The script will now keep track of the budgets you set at the beginning of the month via a Google Spreadsheet. You can also fill in the LABEL value if you only want this to operate on campaigns with that label name. Leaving it blank operates on all campaigns.

Thanks,
Russ

/********************************
* Dynamically Adjust Campaign Budgets v2.1
* Changelog v2.1 - Fixed opening of spreadsheet
* Created By: Russ Savage
* FreeAdWordsScripts.com
********************************/
// Let's set some constants
var TIMEFRAME = "THIS_MONTH";
//if the campaign is not in the spreadsheet, the budget is reset
//to this value at the beginning of the month.
var DEFAULT_BUDGET = 100; 
var SPREADSHEET_URL = "PLACE EMPYT SPREADSHEET URL HERE";
var LABEL = ""; //Fill in if you only want to operate on campaigns with this label
 
var SIG_FIGS = 1000; //this means round all calculations to 3 decimal places
var MONTHLY_BUDGET = 0; // we will set this later
 
function main() {
  MONTHLY_BUDGET = _pull_budget_data_from_spreadsheet();
  var tot_cost_mtd = _get_total_cost();
  var is_first_of_the_month = ((new Date()).getDate() == 1);
  is_first_of_the_month = (is_first_of_the_month && ((new Date()).getHours() == 0));
  Logger.log("Total cost: " + tot_cost_mtd + ", Monthly budget:" + MONTHLY_BUDGET);
   
  if(is_first_of_the_month) {
    _reset_budgets();
  } else {
    _adjust_campaign_budget(tot_cost_mtd);
  }
   
}
 
// Returns the total cost for the set TIMEFRAME
function _get_total_cost() {
  var camp_iter = (LABEL == "") ? AdWordsApp.campaigns().get() :
                                  AdWordsApp.campaigns()
                                    .withCondition("LabelNames CONTAINS_ANY ['"+LABEL+"']")
                                    .get();
   
  var tot_cost = 0;
  while(camp_iter.hasNext()) {
    tot_cost += camp_iter.next().getStatsFor(TIMEFRAME).getCost();
  }
  return tot_cost;
}
 
// Calculates run rate and adjusts campaign bids as needed.
function _adjust_campaign_budget(my_tot_cost) {
  var today = new Date();
  // Accounting for December
  var eom = (today.getMonth() == 11) ? new Date(today.getFullYear()+1,0,1) : 
                                       new Date(today.getFullYear(),today.getMonth()+1,1);
  var days_left = Math.round((eom-today)/1000/60/60/24);
  var days_spent = today.getDate();
  var run_rate = Math.round(my_tot_cost/days_spent*SIG_FIGS)/SIG_FIGS;
  var projected_total = my_tot_cost + (run_rate * days_left);
  var perc_over = Math.round(((MONTHLY_BUDGET-projected_total)/projected_total)*SIG_FIGS)/SIG_FIGS; 
  _change_spend(perc_over,my_tot_cost);
}
 
//Adjusts the budget for a given campaign based on percentage of total spend
//Note: if the cost of a campaign is $0 mtd, the budget is not changed.
function _change_spend(perc_to_change,my_tot_cost) {
  var camp_iter = (LABEL == '') ? AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .get() :
                                  AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .withCondition("LabelNames CONTAINS_ANY ['"+LABEL+"']")
                                    .get();
   
  while(camp_iter.hasNext()) {
    var camp = camp_iter.next();
    var camp_cost = camp.getStatsFor(TIMEFRAME).getCost();
    var perc_of_total = Math.round(camp_cost/my_tot_cost*SIG_FIGS)/SIG_FIGS;
    //If there is no cost for the campaign, let's not change it.
    var to_change = (perc_of_total) ? (perc_of_total*perc_to_change) : 0;
    camp.setBudget(camp.getBudget()*(1+to_change));
  }
}
 
// Resets the budget unevenly
function _reset_budgets() {
  var camp_budget_map = _pull_campaign_data_from_spreadsheet();
  var camp_iter = (LABEL == '') ? AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .get() :
                                  AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .withCondition("LabelNames CONTAINS_ANY ['"+LABEL+"']")
                                    .get();
  while(camp_iter.hasNext()) {
    var camp = camp_iter.next();
    if(camp_budget_map[camp.getName()]) {
      camp.setBudget(camp_budget_map[camp.getName()]/30.5);
    } else {
      camp.setBudget(DEFAULT_BUDGET);
    }
  }
}
 
function _pull_campaign_data_from_spreadsheet() {
  var spreadsheet = getSpreadsheet(SPREADSHEET_URL);
  var sheet = spreadsheet.getActiveSheet();
  var data = sheet.getRange("A:B").getValues();
  if(data[0][0] == "") {
    //This means this is the first run and we should populate the data.
    _populate_spreadsheet(sheet);
    data = sheet.getRange("A:B").getValues();
  }
  var campaign_budget_map = {};
  for(var i in data) {
    if(i == 0) { continue; } //ignore the header
    if(data[i][0] == "") { break; } //stop when there is no more data
    campaign_budget_map[data[i][0]] = parseFloat(data[i][1]);
  }
  return campaign_budget_map;
}
 
function _pull_budget_data_from_spreadsheet() {
  var spreadsheet = getSpreadsheet(SPREADSHEET_URL);
  var sheet = spreadsheet.getActiveSheet();
  var data = sheet.getRange("A:B").getValues();
  if(data[0][0] == "") {
    //This means this is the first run and we should populate the data.
    _populate_spreadsheet(sheet);
    data = sheet.getRange("A:B").getValues();
  }
  var tot_budget = 0;
  for(var i in data) {
    if(i == 0) { continue; } //ignore the header
    if(data[i][1] == "") { break; } //stop when there is no more data
    tot_budget += parseFloat(data[i][1]);
  }
  return tot_budget;
}
 
function _populate_spreadsheet(sheet) {
  sheet.clear();
  sheet.appendRow(['Campaign Name','Monthly Budget']);
  var camp_iter = (LABEL == '') ? AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .get() :
                                  AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .withCondition("LabelNames CONTAINS_ANY ['"+LABEL+"']")
                                    .get();
  while(camp_iter.hasNext()) {
    var camp = camp_iter.next();
    sheet.appendRow([camp.getName(),(camp.getBudget()*30.5)]);
  }
}
 
function getSpreadsheet(spreadsheetUrl) {
  return SpreadsheetApp.openByUrl(spreadsheetUrl);
}

Automate Your Ads Based on Your Baseball Team's Schedule

I know everyone is focused on March Madness right now, but opening day for Baseball season is right around the corner. I wondered if it were possible to enable and disable specific ads based on the schedules of your local MLB team.

It turns out, the MLB has every team's full schedule in an easy to parse CSV format. All you need to do is search Google for " downloadable schedule" and look for the page that ends in "downloadable.jsp". It will look something like this:

The link you need is circled in the image above. You can see from the url what the team_id value should be in the code below. And if you right-click and download that .csv file, you will be able to see what value to use for the home_field as well. In the example below, I used my hometown teams of the Cubs and the White Sox, but you can use any teams you want.

The script itself will run through and look for a home game for your team. If the team is playing today and it is a home game, it will enable any ads that are tagged with the label you configured in the TEAM_INFO. If the team isn't playing a home game today, it will pause those same ads.

In the example below, I only used a portion of the data in the csv files. It would also be possible to enable ads only during the actual game, or enable ads for all game days, not just home games.

I encourage you to play around with the data and see what you can come up with. If you find anything useful, please leave a comment.

Thanks,
Russ

//-----------------------------------
// Enable/Disable Ads Based on the MLB Schedule
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  var TEAM_INFO = [
    { team_id : 112, home_field : 'Wrigley Field', label : 'cubs' }, // Cubs
    { team_id : 145, home_field : 'U.S. Cellular Field', label : 'whitesox' } // White Sox
  ];
  //hopefully you've already created and tagged some ads with these labels
  //but just in case...
  createLabelsIfNeeded(TEAM_INFO); 
  
  var SEASON = (new Date()).getFullYear();
  var is_home = false, is_game_day = false;
  for(var t in TEAM_INFO) {
    var team = TEAM_INFO[t];
    var url = "http://mlb.mlb.com/soa/ical/schedule.csv?team_id="+team.team_id+"&season="+SEASON;
    var html = UrlFetchApp.fetch(url).getContentText();
    var date_list = html.split("\r\n");
    for(var i in date_list) {
      if(i == 0) {continue;}
      var [start_date,start_time,start_time_et,
           subject,location,description,
           end_date,end_date_et,end_time,end_time_et] = date_list[i].split(",");
      
      var today = new Date();
      var game_day = new Date();
      game_day.setFullYear(SEASON,parseInt(start_date.split("/")[0])-1,parseInt(start_date.split("/")[1]));
      
      is_home = (location == team.home_field);
      is_game_day = (diffDays(game_day,today) == 0);
      
      if(is_home && is_game_day) {
        enableBaseballAds(team.label);
        break;
      }
    }
    if(!(is_home && is_game_day)) {
      disableBaseballAds(team.label); 
    }
  }
  
}

function enableBaseballAds(label) {
  Logger.log("Enabling all ads with the "+label+" label.");
  var ads = AdWordsApp.ads().withCondition("LabelNames CONTAINS_ALL ['"+label+"']").get();
  while(ads.hasNext()) {
    ads.next().enable(); 
  }
}

function disableBaseballAds(label) {
  Logger.log("Disabling all ads with the "+label+" label.");
  var ads = AdWordsApp.ads().withCondition("LabelNames CONTAINS_ALL ['"+label+"']").get();
  while(ads.hasNext()) {
    ads.next().pause(); 
  }
}

function createLabelsIfNeeded(team_info) {
  var label_iter = AdWordsApp.labels().get();
  var label_list = [];
  while(label_iter.hasNext()) {
    label_list.push(label_iter.next().getName());
  }
  for(var i in team_info) {
    if(label_list.indexOf(team_info[i].label) == -1) {
      AdWordsApp.createLabel(team_info[i].label);
      label_list.push(team_info[i].label);
    }
  }
}

//A helper function to compare dates.
//Copied from: http://goo.gl/uW48a
function diffDays(firstDate,secondDate) {
  var oneDay = 24*60*60*1000; // hours*minutes*seconds*milliseconds
  return Math.round(Math.abs((firstDate.getTime() - secondDate.getTime())/(oneDay))); 
}

Store Account Performance Report in a Google Doc

Day 2 and I'm still playing around with the new reporting API. I figured I'd post something that people started asking a little about from my previous post about Storing Account Level Quality Score which is storing all account level performance in a spreadsheet.

All you need to do is create a brand new Google Doc to store your data and paste that url into the script. The first time the script runs, it will create column headings. You can add, remove, or rearrange columns as you like by moving the column names around. You should reset your spreadsheet after doing this though.

Thanks,
Russ

/**************************************
* Store Account Performance Report in a Google Doc
* Version 1.1
* Changelog v1.1 - Removed apiVersion, Removed get spreadsheet
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************/
function main() {
  var spreadsheet_url = "Your Spreadsheet Url Goes Here";
  var date_range = 'YESTERDAY';
  var columns = ['Date',
                 'AccountCurrencyCode',
                 'AccountDescriptiveName',
                 'AccountId',
                 'AccountTimeZoneId',
                 'CustomerDescriptiveName',
                 'ExternalCustomerId',
                 'PrimaryCompanyName',
                 'PrimaryUserLogin',
                 'Device',
                 'AverageCpc',
                 'AverageCpm',
                 'AveragePosition',
                 'Clicks',
                 'ConversionRate',
                 'ConversionRateManyPerClick',
                 'Conversions',
                 'ConversionsManyPerClick',
                 'ConversionValue',
                 'Cost',
                 'CostPerConversion',
                 'CostPerConversionManyPerClick',
                 'Ctr',
                 'Impressions',
                 'SearchBudgetLostImpressionShare',
                 'SearchExactMatchImpressionShare',
                 'SearchImpressionShare',
                 'SearchRankLostImpressionShare',
                 'ValuePerConversion',
                 'ValuePerConversionManyPerClick',
                 'ViewThroughConversions'];
  var columns_str = columns.join(',') + " ";
  
  var sheet = SpreadsheetApp.openByUrl(spreadsheet_url).getActiveSheet();
  if(sheet.getRange('A1:A1').getValues()[0][0] == "") {
    sheet.clear();
    sheet.appendRow(columns);
  }
  
  var report_iter = AdWordsApp.report(
    'SELECT ' + columns_str +
    'FROM ACCOUNT_PERFORMANCE_REPORT ' +
    'DURING ' +date_range).rows();
  
  while(report_iter.hasNext()) {
    var row = report_iter.next();
    var row_array = [];
    for(var i in columns) {
       row_array.push(row[columns[i]]);
    }
    sheet.appendRow(row_array); 
  }
}

Store Search Query Performance Report in a Google Doc

Well, I promised you that once the AdWords Scripts team had fixed the issue with the reporting API that I would start adding some examples. Thankfully they fixed it today because I wanted to start playing with the Search Query Performance Report.

This report will give you all the user search queries for your broad match keywords. Normally, you have to log in and manually generate the report each day. But with AdWords scripts, you can start storing all of that data into a Google Spreadsheet so that it is easier to work with.

So today I have a simple script that will store the search query report and send you an email reminding you that it's ready. All you need to do is fill in the url from an existing Google Doc that you have access to with the same log in as your AdWords account, and set the email addresses you want to send a notice to, and you should be all set. There is also a flag to determine if you want to ignore exact and exact (close variant) matched keywords from the results, which I recommend.

Thanks,
Russ

/***************************************************
* Store Search Query Perf Report in Google Doc
* Version 1.1
* CHangelog v1.1 - Removed apiVersion, Updated formatting
* Created By: Russ Savage
* FreeAdWordsScripts.com
****************************************************/
var DATE_RANGE = 'LAST_7_DAYS';
var IGNORE_EXACT = true;
var TO = ["email_1@my_company.com","email_2@my_company.com"];
var SPREADSHEET_URL = "your spreadsheet url goes here";  

function main() {
  var columns = ['AccountDescriptiveName',
                 'CampaignName',
                 'AdGroupName',
                 'KeywordTextMatchingQuery',
                 'MatchType',
                 'Query',
                 'Device',
                 'Impressions',
                 'Clicks',
                 'Cost',
                 'Conversions',
                 'AverageCpc',
                 'CostPerConversion',
                 'ConversionRate',
                 'Ctr'];
  var columnsStr = columns.join(',') + " ";
   
  var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
  sheet.clear();
  sheet.appendRow(columns);
   
  var reportIter = AdWordsApp.report(
    'SELECT ' + columnsStr +
    'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
    'DURING ' + DATE_RANGE, {
      includeZeroImpressions: false
    }).rows();
   
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    if(IGNORE_EXACT && row['MatchType'].indexOf('exact') >= 0) { continue; }
    var rowArray = [];
    for(var i in columns) {
      rowArray.push(row[columns[i]]);
    }
    sheet.appendRow(rowArray); 
  }
   
  for(var i in TO) {
    MailApp.sendEmail(TO[i], "Search Query Report Ready", SPREADSHEET_URL);
  }
}