Optimization Up

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 Google Ads 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:

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

}

Ahmed Ali

Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest products Marketing Auditor & GA4 Auditor – discover all my products here.