An Improved AdWords Performance Reporting Script

An Updated AdWords Performance Reporting Script With Phone Impressions and Calls

 

Recently our SEM team wanted to automate some reports that we review weekly for each client's AdWords account. We found a great script at http://www.freeadwordsscripts.com/2013/07/campaign-and-keyword-performance.html, however it was a little dated because there was no data reported for phone impressions or phone calls.

Additionally, the script generated more data than we needed, specifically the tabs for 'Campaign Performance Month to Date' and 'Daily Keyword Performance for the Past 7 Days'.

Our development team is always up for a challenge, so we tasked them with rewriting the script to meet our needs. Since they mostly work in PHP, we figured the Javascript code might help polish up some rusty skills.

The developers did a great job, and we've put this to use now for our client's accounts. We thought it might be nice to share with others who could benefit by saving some time with the automated reporting.

To use the script:

1. Create a new Google Sheet in any desired location your Google drive. Name the sheet whatever you like (example "Weekly Summary Report- ACCOUNT NAME").
2. In AdWords, go to Bulk Operations->Scripts.
3. Click the '+ Script' button.
4. Enter a name for the Script (example "Weekly Summary- Report Account Name".
5. Copy the script code below, and paste in the editor area replacing the default 3 lines of code.
6. Copy the URL of the sheet you created in step 1.
7. Replace the 'var SPREADSHEET URL' value in the code with the URL of your sheet.
8. Save the script.
9. Authorize the script using the 'Authorize now' button.
10. Click 'Run script now'. You can safely run without previewing.
11. After several seconds, check your spreadsheet and see the data that was populated by the script. You will see a tab with Campaign Performance last month, Campaign Performance past 7 days, and Keyword Performance e past 7 days.
12. Use the '+ Create schedule' link to the right of your script to set a weekly run at any desired time. You can also run at other frequencies if you like.
13. Relax knowing that you will have a useful report to review your account performance automatically generated weekly!

Code:

 /************************************
  * Campaign and Keyword Summary Report
  * Version: 1.3
  * Changelog v1.3 - imrpoved reporting to add Phone Impressions and Phone Calls
  * Modified by BCS Website Services
  * BCSWebsiteServices.com
  * Version: 1.2
  * Changelog v1.2 - Fixed INVALID_PREDICATE_ENUM_VALUE
  * ChangeLog v1.1 - Removed apiVersion from reporting call
  * Originally Created By: Russ Savage
  * FreeAdWordsScripts.com    
  ************************************/
  var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/";
   
  function main() {
    //These names are important. change them with caution
    var tabs = ['camp_perf_last_month','keyword_perf_7_days','camp_perf_7_days'];
    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);
    
    var headers = [];
    if(tab.indexOf('camp_perf_7') == 0) {
    headers.push('Date');
    }
    headers.push('Campaign Name');
    headers.push('Campaign Status');
     
    if(tab.indexOf('keyword_') == 0) {
    headers = headers.concat(['Ad Group Name',
                    'Keyword',
                    'Quality Score',
                    'Keyword Match Type']);
    }
      if(tab.indexOf('camp_perf_') == 0) {
    headers = headers.concat(['Phone Impressions',
                    'Phone Calls']);
    }
    headers = headers.concat(['Clicks',
                 'Impressions',
                 'Ctr',
                 'Average Cpc',
                 'Cost',
                 'Average Position',
                 'Conversions',
                 'Cost per Conversion ',
                 'Conversion Rate',
                 'Conversion Value'
             ]);
         
     if(tab.indexOf('camp_perf_last_') == 0) {
     headers.concat(['MonthOfYear',
                 'Year'
             ]);
    }
     
      headers = [headers];
  
    sheet.getRange(1,1,1,headers[0].length).setValues(headers);
    sheet.setFrozenRows(1);
   
  }
   
  function getdateweeks(days,format){
  //var days; // Days you want to subtract
  var date = new Date();
  var last = new Date(date.getTime() - (days * 24 * 60 * 60 * 1000));
  var day =last.getDate();
  var month=last.getMonth()+1;
  var year=last.getFullYear();
  if(month<10){
   month="0"+month; 
  }
      if(day<10){
      day="0"+day;  
      }
    
      if(format==1){
      return year+""+month+""+day;
      }else{
      return month+"/"+day+"/"+year;  
      }
      } 

  //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(' ');
      Logger.log(query);
   var report_iter = AdWordsApp.report(query, API_VERSION).rows();
    var nextnode="";
    var rows = [];
    while(report_iter.hasNext()) { 
     nextnode=report_iter.next();
        nextnode['CampaignId']=getdateweeks(7,2)+" - "+getdateweeks(1,2);
    rows.push(nextnode);
     //Logger.log(nextnode);
    
    }
    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('camp_perf_7') == 0) {
    ret_array.push('CampaignId');
    }
    ret_array.push('CampaignName');
    ret_array.push('CampaignStatus');
     
    if(tab.indexOf('keyword_') == 0) {
    ret_array = ret_array.concat(['AdGroupName',
                    'Criteria',
                    'QualityScore',
                    'KeywordMatchType']);
    }
      if(tab.indexOf('camp_perf_') == 0) {
    ret_array = ret_array.concat(['NumOfflineImpressions',
                    'NumOfflineInteractions']);
    }
    
     ret_array = ret_array.concat(['Clicks',
                 'Impressions',
                 'Ctr',
                 'AverageCpc',
                 'Cost',
                 'AveragePosition',
                 'Conversions',
                 'CostPerAllConversion',
                 'ConversionRate',
                 'ConversionValue']);
    
    if(tab.indexOf('camp_perf_last_') == 0) {
     ret_array=ret_array.concat(['MonthOfYear',
                    'Year']);
    }
    
    return ret_array;
    
    }
     
  //Based on the tab name, this returns the date range for the data.
  function getDateRange(tab) {
    if(tab.indexOf('keyword_perf_7_days') >= 0) {
    return 'LAST_7_DAYS';
    }
     if(tab.indexOf('camp_perf_7_days') >= 0) {
    return getdateweeks(7,1)+","+getdateweeks(1,1);
    }
    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);
  }