Run Search Query Report Like a Pro Using Google Ads 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 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:
# Not excluded and low performing: the query is not excluded as the CTR is lower than the threshold.
We’ve set 3 threshold elements that you can change easily depending on your goals:
# 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 at 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'); }
Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest product GA4 Auditor and discover all my products here.
Great script. Thank you!
Thanks Tina, glad you like it 🙂
Hi, thank you for your script. Have one question. In AdWords, there is more than 5000 kws, but exported only about 500 kws (changed impres. from 1). Do you have any idea? Thank you
Hi Hana,
The discrepancy is most probably coming from the difference in the date range. Make sure that both match 🙂
HI, thanks for the great script.
is there a way to change the time frame to 7 days?
Hi Ahmed
I hope you’re well! Great script, I’ve been using another but it only worked on the account level – not MCC like yours.
The problem I’m encountering is that it’s not importing all the Search Terms. (I did make sure the date ranges in AdWords & the Script are the same – Last-30-Days 🙂
I did another test and ran the script multiples times after each other, same settings, and it outputted a different amount of Search Terms everytime. The first 1751, the second, 1577, and the third 1722. This was right after each run, with in 10-15 min.
AdWords Script run limits is for 30 min so can’t figure out why it’s giving such different outputs (with many results not showing as well).
Do you have any thoughts?
Thanks 🙂
Hi, thank you for this script. I have a time out unfortunately.
Hi, I have the same problem with Hana as well, the dates match so i m not sure why. Please help to advise. Thanks a lot.
How do you set the date range that the script is evaluating?
Hi Ahmed,
The script give me the following error after running it, could you please assist.
Hi Ahmed,
The script give me the following error after running it, could you please assist.
Document 13SDsf0o5HyMEoY_2H53DLssGCZJm8ouKTUoSzzQYel8 is missing (perhaps it was deleted?) (line 25)
Hi Ahmed. I´m using the Manager Account version. How can I change the script so I can chose to run the script on only one of the External Customer Id´s if I want to?
Best Thomas
Thanks Bro…to shared a successful script…good job…
Hi, I have more than 50 accounts and it give us a problem.
How can i manage that? I really want it to run for all my accounts, but i dont know how to divide that.
what columns do we need to add into the Google Sheet?
I just uploaded and tried this script.
Its worked first time.
Question: Is it possible for the script to SORT BY “Added Or Not” to that “Not Added and has Conversions” appears at the top, followed by Highest Conversions first?
This script works great. However, it keeps timing out. I have around 10,000 keywords in my account. Do you have any suggestions?
Hi Ahmed, would be possibile to get the update version for Google Ads?
Thanks
Diego