Sunday, June 2, 2013

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

15 comments:

  1. Faisalabad is one of the biggest cities in Pakistan and the hub of the textile industry. It is widely acknowledged as the Manchester of Pakistan due to its large industrial role. The quality of the fabrics produced in this city has no parallel. In fact, the fabric is something of a specialty of Faisalabad. Many people from all over the country flock to this city for a spot of cloth shopping. We aim to provide you all of the best of Faisalabad at our store. latest lawn collection , latest lawn collection

    ReplyDelete