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