Pages

Tuesday, March 19, 2013

Store Search Query Performance Report in a Google Doc

Well, I promised you that once the AdWords Scripts team had fixed the issue with the reporting API that I would start adding some examples. Thankfully they fixed it today because I wanted to start playing with the Search Query Performance Report.

This report will give you all the user search queries for your broad match keywords. Normally, you have to log in and manually generate the report each day. But with AdWords scripts, you can start storing all of that data into a Google Spreadsheet so that it is easier to work with.

So today I have a simple script that will store the search query report and send you an email reminding you that it's ready. All you need to do is fill in the url from an existing Google Doc that you have access to with the same log in as your AdWords account, and set the email addresses you want to send a notice to, and you should be all set. There is also a flag to determine if you want to ignore exact and exact (close variant) matched keywords from the results, which I recommend.

Thanks,
Russ

/***************************************************
* Store Search Query Perf Report in Google Doc
* Version 1.1
* CHangelog v1.1 - Removed apiVersion, Updated formatting
* Created By: Russ Savage
* FreeAdWordsScripts.com
****************************************************/
var DATE_RANGE = 'LAST_7_DAYS';
var IGNORE_EXACT = true;
var TO = ["email_1@my_company.com","email_2@my_company.com"];
var SPREADSHEET_URL = "your spreadsheet url goes here";  

function main() {
  var columns = ['AccountDescriptiveName',
                 'CampaignName',
                 'AdGroupName',
                 'KeywordTextMatchingQuery',
                 'MatchType',
                 'Query',
                 'Device',
                 'Impressions',
                 'Clicks',
                 'Cost',
                 'Conversions',
                 'AverageCpc',
                 'CostPerConversion',
                 'ConversionRate',
                 'Ctr'];
  var columnsStr = columns.join(',') + " ";
   
  var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
  sheet.clear();
  sheet.appendRow(columns);
   
  var reportIter = AdWordsApp.report(
    'SELECT ' + columnsStr +
    'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
    'DURING ' + DATE_RANGE, {
      includeZeroImpressions: false
    }).rows();
   
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    if(IGNORE_EXACT && row['MatchType'].indexOf('exact') >= 0) { continue; }
    var rowArray = [];
    for(var i in columns) {
      rowArray.push(row[columns[i]]);
    }
    sheet.appendRow(rowArray); 
  }
   
  for(var i in TO) {
    MailApp.sendEmail(TO[i], "Search Query Report Ready", SPREADSHEET_URL);
  }
}