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