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

2 comments:

  1. Brilliant!

    What if I want to filter out brand keywords ?

    Where do I put the condition ?

    Thanks!

    ReplyDelete
    Replies
    1. Hi Giu, It kind of depends on how you have identified your brand terms. If you put all your brand terms in their own set of campaigns, you could add a filter in the loop that starts on line 41 that flags things as brand or non-brand.

      If you configured things using labels, things get a bit more complicated. Currently, there is no way to get labels from the search query performance report. That means, depending on the size of our account, you would need to have a separate script that stores keyword text and the labels (possibly in the same spreadsheet on a different tab) and then link the two together.

      Thanks,
      Russ

      Delete