Showing posts with label keyword performance report. Show all posts
Showing posts with label keyword performance report. Show all posts

Monday, July 1, 2013

Campaign and Keyword Performance Reporting

Many of the daily or weekly reporting tasks that your boss asks you to put together can be automated using AdWords Scripts. One of my readers passed along the following request for a script that would save him time each day putting together some of his performance reports. Maybe it can save you some time as well.

The following script will build out a Google Spreadsheet with five tabs:
  1. Campaign Performance Summary for the Past 7 Days
  2. Campaign Performance Month to Date
  3. Campaign Performance for Last Month
  4. Keyword Performance Summary for the Past 7 Days
  5. Daily Keyword Performance for the Past 7 Days

All you need to do to get this up and running is to create a New Google Spreadsheet and paste the url into the script. Then you can schedule this script to run daily and when you get to work in the morning, all of your data will be refreshed.

Thanks,
Russ

/************************************
* Campaign and Keyword Summary Report
* Version: 1.2
* Changelog v1.2 - Fixed INVALID_PREDICATE_ENUM_VALUE
* ChangeLog v1.1 - Removed apiVersion from reporting call
* Created By: Russ Savage
* FreeAdWordsScripts.com
************************************/
var SPREADSHEET_URL = "PASTE GOOGLE SPREADSHEET URL HERE";

function main() {
  //These names are important. change them with caution
  var tabs = ['camp_perf_7_days','camp_perf_mtd','camp_perf_last_month','keyword_perf_7_days','keyword_perf_7_days_daily'];
  for(var i in tabs) {
    var results = runQuery(tabs[i]);
    writeToSpreadsheet(tabs[i],results);
  }
}

//Helper function to get or create the spreadsheet
function getSheet(tab) {
  var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet;
  try {
    sheet = s_sheet.getSheetByName(tab);
    if(!sheet) {
      sheet = s_sheet.insertSheet(tab, 0);
    }
  } catch(e) {
    sheet = s_sheet.insertSheet(tab, 0);
  }
  return sheet
}

//Function to write the rows of the report to the sheet
function writeToSpreadsheet(tab,rows) {
  var to_write = convertRowsToSpreadsheetRows(tab,rows);
  var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = getSheet(tab);
  sheet.clear();
  
  var numRows = sheet.getMaxRows();
  if(numRows < to_write.length) {
    sheet.insertRows(1,to_write.length-numRows); 
  }
  var range = sheet.getRange(1,1,to_write.length,to_write[0].length);
  range.setValues(to_write);
}

//A generic function used to build and run the report query
function runQuery(tab) {
  var API_VERSION = { includeZeroImpressions : false };
  var cols = getColumns(tab);
  var report = getReport(tab);
  var date_range = getDateRange(tab);
  var where = getWhereClause(tab);
  var query = ['select',cols.join(','),'from',report,where,'during',date_range].join(' ');
  var report_iter = AdWordsApp.report(query, API_VERSION).rows();
  var rows = [];
  while(report_iter.hasNext()) { 
    rows.push(report_iter.next());
  }
  return rows;
}
 
//This function will convert row data into a format easily pushed into a spreadsheet
function convertRowsToSpreadsheetRows(tab,rows) {
  var cols = getColumns(tab);
  var ret_val = [cols];
  for(var i in rows) {
    var r = rows[i];
    var ss_row = [];
    for(var x in cols) {
      ss_row.push(r[cols[x]]);
    }
    ret_val.push(ss_row);
  }
  return ret_val;
}

//Based on the tab name, this returns the report type to use for the query
function getReport(tab) {
  if(tab.indexOf('camp_') == 0) {
    return 'CAMPAIGN_PERFORMANCE_REPORT';
  }
  if(tab.indexOf('keyword_') == 0) {
    return 'KEYWORDS_PERFORMANCE_REPORT';
  }
  throw new Exception('tab name not recognized: '+tab);
}

//Based on the tab name, this returns the where clause for the query
function getWhereClause(tab) {
  if(tab.indexOf('camp_') == 0) {
    return 'where CampaignStatus = ENABLED';
  }
  if(tab.indexOf('keyword_') == 0) {
    return 'where CampaignStatus = ENABLED and AdGroupStatus = ENABLED and Status = ENABLED';
  }
  throw new Exception('tab name not recognized: '+tab);
}

//Based on the tab name, this returns the columns to add into the report
function getColumns(tab) {
  var ret_array = [];
  if(tab.indexOf('daily') >= 0) {
    ret_array.push('Date');
  }
  ret_array.push('CampaignName');
  ret_array.push('CampaignStatus');
  
  if(tab.indexOf('keyword_') == 0) {
    ret_array = ret_array.concat(['AdGroupName',
                                  'AdGroupStatus',
                                  'Id',
                                  'KeywordText',
                                  'KeywordMatchType']);
  }
  return ret_array.concat(['Clicks',
                           'Impressions',
                           'Ctr',
                           'AverageCpc',
                           'Cost',
                           'AveragePosition',
                           'Conversions',
                           'ConversionRate',
                           'ConversionValue']);
}

//Based on the tab name, this returns the date range for the data.
function getDateRange(tab) {
  if(tab.indexOf('7_days') >= 0) {
    return 'LAST_7_DAYS';
  }
  if(tab.indexOf('mtd') >= 0) {
    return 'THIS_MONTH';
  }
  if(tab.indexOf('last_month') >= 0) {
    return 'LAST_MONTH';
  }
  throw new Exception('tab name not recognized: '+tab);
}

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