Run Search Query Report Like a Pro Using AdWords Scripts

One of the most important reports of managing AdWords campaigns is the search query report. You need to see the actual search queries that trigger your ads to identify new terms to add to your positive keywords and find undesired search terms and add them as negative keywords. Actually, the process can be time-consuming if you manage multiple accounts as you need to log in and manually generate the report each time.

In order to get the most out of the search term report and make sure it runs consistently across all our accounts. We’ve written a new AdWords script that can automate and schedule the process for us. The script goes through all the campaigns/accounts and returns search queries that meet certain criteria you set and store them in a spreadsheet.  And to ensure that the script doesn’t return the search queries that already added as positive/negative keywords. The script iterates over all the keywords in all accounts and compares them to all the search queries. Then, the script returns 4 values of results in your Google spreadsheet:

# Added: the query is already added to your account as an exact match type.
# Not Added: the query is not added and none of the criteria below applies on it.
# Not added and has conversions: the query is not added and has a number of conversions higher than the conversion threshold.

# Not excluded and low performing: the query is not excluded as the CTR is lower than the threshold.

search query report

We’ve set 3 threshold elements that you can change easily depending on your goals:

# Impressions: this is the minimum number of impressions of search queries to consider enough data.
# CTR: If the CTR is below the threshold, the query will be labeled as “Not excluded and low performing”. 

# Conversions: If the number of conversions is above the threshold and the query is not added anywhere in the account, the query will be labeled as “Not added and has conversions”.

Once the script finishes, it sends an email to the specified email address. So, you can schedule the  Adwords script to run on a specific time and once the report is ready, you will receive an email with your report.

Smart Search Query Report- Manager Account

Here’s the source code for the MCC script. To ensure processing doesn’t exceed limits, we recommend running the script on a maximum of 50 AdWords accounts at a time.

/***************************************************
* Smart Search Query Report Script
* Version 1.0
* Created By: Ahmed Ali
*https://optimizationup.com/
****************************************************/

// Add your spreadsheet here
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/xxxxxxxxxx';

// This is the quereis minmum number of impressions.
var Impressions_Threshold = 50;

// If the CTR is below the threshold, it will be labeled as "Not excluded and low performing"
var CTR_Threshold = 1;

// If the number of converionis is above the threshold and the qury is not added, it will be labeld as "Not added and has conversions"
var Conversions_Threshold = 0;

// Add your email here
var Notify_Me = "add your email here"
var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();

var columns = [
 'CustomerDescriptiveName',
 'ExternalCustomerId',
 'Query',
 'CampaignName',
 'AdGroupName',
 'Impressions',
 'Clicks',
 'Cost',
 'Ctr',
 'Conversions' 
 ];


function main() { 
 sheet.clearContents(); 
 sheet.appendRow(columns);
 sheet.getRange("K1").setValue("Results");
 
 var accountIterator = MccApp.accounts().executeInParallel('SQR');
 send_email(); 
}


function SQR() {

// Get all the keywords in all the acccounts

 var allKeywords = [];
 var report = AdWordsApp.report(
 "SELECT AdGroupId, Criteria " +
 "FROM KEYWORDS_PERFORMANCE_REPORT " +
 "WHERE KeywordMatchType = EXACT " +
 "DURING TODAY");
 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 var KeywordsLower = row['Criteria'].toLowerCase();
 allKeywords.push(KeywordsLower);
 }

// Get all the search queries in all the accounts
 var positiveKeywords = [];
 var columnsStr = columns.join(',') + " ";
 var SQRreport = AdWordsApp.report(
 'SELECT ' + columnsStr +
 'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
 " WHERE Impressions > 50 " +
 "DURING LAST_30_DAYS"
 );
 
 var rows = SQRreport.rows(); 
 while (rows.hasNext()) {
 var row = rows.next(); 
 positiveKeywords.push(row['Query']); 

 for (var i = 0; i < positiveKeywords.length; i++) {

 if ((allKeywords.indexOf(positiveKeywords[i]) === -1) && ( row['Conversions'] > Conversions_Threshold)) {

 row.addedOrNot = 'Not added and has conversions';

 }

 else if ((allKeywords.indexOf(positiveKeywords[i]) === -1) && (parseFloat(row['Ctr']) < CTR_Threshold )) {

 row.addedOrNot = 'Not excluded and low performing';
 }

 else if (allKeywords.indexOf(positiveKeywords[i]) === -1) {

 row.addedOrNot = 'Not added';

 }

 else{

 row.addedOrNot ='Added'

 } 
 }

 sheet.appendRow([ row['CustomerDescriptiveName'], row['ExternalCustomerId'], row['Query'], row['CampaignName'], row['AdGroupName'], row['Impressions'], row['Clicks'], row['Cost'], row['Ctr'], row['Conversions'], row['addedOrNot']]);

 } 
}

function send_email() {
MailApp.sendEmail(Notify_Me,
 'SQR Report',
 'Your SQR report is ready, please visit '+SPREADSHEET_URL+' Thanks'); 

}
Smart Search Query Report-Single Account

Here’s also the single account version:

/***************************************************
* SQR Report Script
* Version 1.0
* Created By: Ahmed Ali
*https://optimizationup.com/
****************************************************/

// Add your spreadsheet here
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/xxxxxxxxxxx';

// This is quereis minmum number of impressions.
var Impressions_Threshold = 50;

// If the CTR is below the threshold, it will be labeled as "Not excluded and low performing"
var CTR_Threshold = 1;

// If the number of conversions is above the threshold and the query is not added, it will be labeled as "Not added and has conversions"
var Conversions_Threshold = 0;

// Add your email here
var Notify_Me = "add your email here"

function main() { 

 var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
 var columns = [
 'CustomerDescriptiveName',
 'ExternalCustomerId',
 'Query',
 'CampaignName',
 'AdGroupName',
 'Impressions',
 'Clicks',
 'Cost',
 'Ctr',
 'Conversions' 
 ];
 
 sheet.clearContents(); 
 sheet.appendRow(columns);
 sheet.getRange("K1").setValue("Added Or Not");

// Get all the keywords in all the acccounts
var allKeywords = [];
 var report = AdWordsApp.report(
 "SELECT AdGroupId, Criteria " +
 "FROM KEYWORDS_PERFORMANCE_REPORT " +
 "WHERE KeywordMatchType = EXACT " +
 "DURING TODAY");
 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 var KeywordsLower = row['Criteria'].toLowerCase();
 allKeywords.push(KeywordsLower);
 }


// Get all the search queries in all the accounts
 var positiveKeywords = [];
 var columnsStr = columns.join(',') + " ";

 var SQRreport = AdWordsApp.report(
 'SELECT ' + columnsStr +
 ' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
 ' WHERE ' +
 ' Impressions > ' + Impressions_Threshold +
 ' DURING LAST_30_DAYS'
 );
 
 var rows = SQRreport.rows(); 
 while (rows.hasNext()) {
 var row = rows.next(); 
 
 positiveKeywords.push(row['Query']); 

 
 for (var i = 0; i < positiveKeywords.length; i++) {

 if ((allKeywords.indexOf(positiveKeywords[i]) === -1) && ( row['Conversions'] > Conversions_Threshold)) {

 row.addedOrNot = 'Not Added and has Conversions';

 }

 else if ((allKeywords.indexOf(positiveKeywords[i]) === -1) && (parseFloat(row['Ctr']) < CTR_Threshold )) {

 row.addedOrNot = 'Not excluded and low performing';

 }

 else if (allKeywords.indexOf(positiveKeywords[i]) === -1) {
 row.addedOrNot = 'Not Added';
 }

 else{

 row.addedOrNot ='Added'

 } 
 }

 sheet.appendRow([ row['CustomerDescriptiveName'], row['ExternalCustomerId'], row['Query'], row['CampaignName'], row['AdGroupName'], row['Impressions'], row['Clicks'], row['Cost'], row['Ctr'], row['Conversions'], row['addedOrNot']]);

 } 

MailApp.sendEmail(Notify_Me,
 'SQR Report',
 'Your SQR report for this month is ready, please visit '+SPREADSHEET_URL+' Thanks'); 

}
mm
Ahmed launched his first digital campaign in 2013 through participating in Google Online Marketing Challenge. He competed with over 12,000 students from 80 countries to win the first place in Africa & MENA region and one of the best five campaigns in the world. Today, he works as an Associate Digital Director at Keyade Middle East.

4 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *