Search Query Review & Automated Negative Keyword Management

Overview

That was a mouthful! This was originally just a simple tool to make routine Search Query reviews a little quicker, but I added a couple things to soup it up a bit. Instructions below will run through setting up a script in a Google Ads account that will simply filter out potential Keyword Additions and potential Negative Keywords based on some adjustable filters (i.e. Search Query Converted, Search Query Had 10+ Impressions, etc.). The data will then just get dropped into a sheet that you can schedule to be delivered to your inbox every X number of days/weeks/months for your review & updates before you simply copy and paste your new Negatives & Targeted Keywords into Ads Editor to upload! No changes will be automatically applied to the account by the sheet alone. The two scripts at the bottom for "auto-upload" can be added into your Google Ads account if you'd like, but are not in place by default.

I did also, however, add in an option to add "Relevance Scoring" to help identify less relevant queries that have come through on the "Negative Keyword Review" tab of the Sheet. A script using a free(-mium) text similarity algorithm from dandelion.eu provides a relevance score for all queries. You could even set this up with a bonus script at the bottom of the page to fully automate the review & addition of negative keywords...though that's definitely not recommended. Don't do that. Just use the scoring to add suggestions to the review list and then review them yourself when the sheet gets emailed to you.

Setting Up The Spreadsheet

1. Rename the "Client" in your Spreadsheet name and file this away somewhere you can find it if you need to edit it at any point :)

2. Go to your Settings tab (at the end in red)

3. Add your email to cell B1 (YOUR_EMAIL_HERE) and the client name into B2 (CLIENT_NAME).

4. At the bottom of the Settings sheet, you can customize the frequency with which you receive emails. Default is every week on Monday, but you can customize this however you want. Just be sure to update the Google Ads Script (see below) when you insert that into the account to ensure that the data being sent to you is appropriate! See notes in cell A3 for default script time schedules, etc.

5. In Cell B4, you'll see the Relevance Score unchecked. If you'd like to use this feature, simply check that box to get started. Otherwise, just leave this unchecked and you can just use the sheet as a way to quickly organize and copy/paste new keywords and negative keywords.

   • Cell C4 has a link that'll send you over to dandelion.eu to sign up for a free account. Your free account will be limited to 1,000 query comparisons per day, but that should really probably do the trick. If you want, though, feel free to sign up for more!

     1. • I have set up the script so that it'll just pick up where it left off if the "Clear Edits To Sheet" script is not run prior to the RelevanceScore script.  So feel free to edit the frequency of that script to chip away at a longer query list over a few days with a free account! Just update your Apps Script trigger to reflect this – either by updating the RelevanceScore trigger to run hourly (if there are no additional queries to process nothing will happen, so no worries there) or just add another trigger or two in the hours after the first trigger runs. You can shift triggers around to stay on schedule – just make sure that they all still occur in the same order as they do in the template.

   • Once you've signed up for an account and added your API Token into cell D4, just make sure you check & update the "Relevance Threshold" in B5. This will be the limit below which a query will be automatically listed as a potential negative. Note: no changes will be made to the account from this sheet alone. You'll need to copy and paste into Google Ads Editor unless you're using an automated upload script from the bottom of this page. Again – not recommended.

6. When you've updated all your settings, go to the custom menu item at the top of the screen (Schedule Emailing) and hit Update Email Schedule. You'll receive a confirmation pop-up on the screen when that's all set. Hit OK. Your emails are now scheduled!

   • If at any point, you decide to stop receiving these, you can go back to that menu and hit Clear All Scheduled Emails.

7. That's it for the sheet! Time to set up the script.

Setting Up The Google Ads Script To Deliver Account Data To Your Sheet

1. Go to the Google Ads account that you want to review data for.

2. To add a new script, go to Tools > Bulk Actions > Scripts > Create New (Blue plus button).

3. Name your script "Search Query Review" or something identifiable.

4. Copy and paste the full Google Ads script at the bottom of this page into the script editor field.

5. Change YOUR_SPREADSHEET_URL_LEAVE_QUOTES_HERE_IN_PLACE to the URL of your new Google Sheet that you set created in the steps above.

   • Optionally, you can also change DateRange from LAST_7_DAYS to one of the following values, if desired:

     • TODAY, YESTERDAY, LAST_14_DAYS, LAST_WEEK, LAST_BUSINESS_WEEK, THIS_MONTH, LAST_MONTH, LAST_14_DAYS, LAST_30_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, or LAST_WEEK_SUN_SAT 

   • You can also see that in lines 14 and 22 (see line numbers in Google Ads script editor), you can also adjust the criteria for potential new Keywords and Negatives, respectively. Simply add or edit these filters in the same style as you see below. For reference on specific variable names, etc. you can reference the Search Query Performance Report documentation.

6. When you're done editing the script, click save at the bottom right corner of the screen. Then hit Run. You'll have to authorize the script to run and to edit your spreadsheet. Once you've completed the Authorization process, you can click "Run" again, then "Run Without Preview," and you'll be brought to a page that will show you the status of the script (provided your date range in the top right corner of the screen includes today).

   • Make sure that the script is completed successfully. If you encounter any errors and are having troubleshooting based on the error message, feel free to conact me!

7. When it's completed, you can return to the Scripts page and customize the Frequency that you'd like this script to run at. For an every-other-week email, I just set it to Weekly, which ensures that the sheet is up to date when it's sent out. But feel free to customize as needed.

8. See instructions on using the spreadsheet below the script.

// Reference: https://developers.google.com/adwords/api/docs/guides/awql

var SheetURL = "YOUR_SPREADSHEET_URL_LEAVE_QUOTES_HERE_IN_PLACE";

var QueryTabName = "Query Import";

var NegativeQueryTabName = "Negative Query Import";

var allTimeSearchQueriesTabName = "All Time Converting Query Data";

var KeywordTabName = "Keyword Import";

var CampaignTabName = "Campaign Import";

var AdGroupTabName = "Ad Group Import";

var QueryTabNameAllTime = "All Time Query Data";

var DateRange = "LAST_7_DAYS"

var QUERIES = [

               {'query' : 'SELECT CampaignName, AdGroupName, KeywordId, KeywordTextMatchingQuery, Query, QueryMatchTypeWithVariant, QueryTargetingStatus, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, CostPerConversion, ConversionValue ' +

                  'FROM   SEARCH_QUERY_PERFORMANCE_REPORT ' +

                  'WHERE  CampaignStatus != "REMOVED" AND Impressions > 1  AND Conversions > 0 AND QueryTargetingStatus = "NONE" ' +

                  'DURING ' + DateRange,

                  'spreadsheetUrl' : SheetURL,

                  'tabName' : QueryTabName,

                  'reportVersion' : 'v201809'

               },

               {'query' : 'SELECT CampaignName, AdGroupName, KeywordId, KeywordTextMatchingQuery, Query, QueryMatchTypeWithVariant, QueryTargetingStatus, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, CostPerConversion, ConversionValue ' +

                  'FROM   SEARCH_QUERY_PERFORMANCE_REPORT ' +

                  'WHERE  CampaignStatus = "ENABLED" AND Impressions > 1  AND Conversions < 0.1 AND QueryTargetingStatus = "NONE" ' +

                  'DURING ' + DateRange,

                  'spreadsheetUrl' : SheetURL,

                  'tabName' : NegativeQueryTabName,

                  'reportVersion' : 'v201809'

               },

               {'query' : 'SELECT Query, Conversions ' +

                  'FROM   SEARCH_QUERY_PERFORMANCE_REPORT ' +

                  'WHERE Conversions > 0 ',

                  'spreadsheetUrl' : SheetURL,

                  'tabName' : allTimeSearchQueriesTabName,

                  'reportVersion' : 'v201809'

               },

               {'query' : 'SELECT CampaignName ' +

                  'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +

                  'WHERE  CampaignStatus != "REMOVED" AND ServingStatus != "ENDED" AND AdvertisingChannelType = "SEARCH" ' +

                  'DURING ' + DateRange,

                  'spreadsheetUrl' : SheetURL,

                  'tabName' : CampaignTabName,

                  'reportVersion' : 'v201809'

               },

               {'query' : 'SELECT CampaignName, AdGroupName ' +

                  'FROM   ADGROUP_PERFORMANCE_REPORT ' +

                  'WHERE  CampaignStatus != "REMOVED" AND AdGroupStatus != "REMOVED" ' +

                  'DURING ' + DateRange,

                  'spreadsheetUrl' : SheetURL,

                  'tabName' : AdGroupTabName,

                  'reportVersion' : 'v201809'

               },

               {'query' : 'SELECT Id, KeywordMatchType ' +

                  'FROM   KEYWORDS_PERFORMANCE_REPORT ' +

                  'WHERE  CampaignStatus != "REMOVED" AND Impressions > 1 ' +

                  'DURING ' + DateRange,

                  'spreadsheetUrl' : SheetURL,

                  'tabName' : KeywordTabName,

                  'reportVersion' : 'v201809'

               },

               {'query' : 'SELECT Query, Impressions, Clicks, Cost, Conversions, ConversionValue ' +

                  'FROM   SEARCH_QUERY_PERFORMANCE_REPORT ' +

                  'WHERE  CampaignStatus != "REMOVED" AND Impressions > 1 AND QueryTargetingStatus = "NONE"',

                  'spreadsheetUrl' : SheetURL,

                  'tabName' : QueryTabNameAllTime,

                  'reportVersion' : 'v201809'

               }

             ];

function main() {

 for(var i in QUERIES) {

   var queryObject = QUERIES[i];

   var query = queryObject.query;

   var spreadsheetUrl = queryObject.spreadsheetUrl;

   var tabName = queryObject.tabName;

   var reportVersion = queryObject.reportVersion;

   //Logger.log(spreadsheetUrl + " " + query);

   var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

   var sheet = spreadsheet.getSheetByName(tabName);

   var report = AdWordsApp.report(query, {apiVersion: reportVersion});

   report.exportToSheet(sheet);

 }

  //update sheet name to confirm data recency

  var todaysDate = new Date();

  var todaysDateTitle = Utilities.formatDate(todaysDate, AdsApp.currentAccount().getTimeZone(), 'yyyy.MM.dd');

  var currentName = spreadsheet.getName();

  spreadsheet.rename(currentName.substring(0, currentName.indexOf(" - ")) + ' - ' + todaysDateTitle);

}

Using the Sheet

Alright! Did all that and now what?

1. So just double check that there are now search queries listed out on the New Keyword Review and Negative Keyword Review tabs.

2. For right now, you probably don't want to do anything. You'll receive an email with this sheet attached next Monday and you can run through it all at that time. But essentially, you're going to do all of your "review" work in the two yellow tabs. The green tabs will simply just be where everything you've done is formatted for upload via Ads Editor.

Adding New Keywords

1. In the New Keyword Review tab, you'll see a list of Search Queries from the date range that you've selected that meet your criteria. If you feel that there are too many/few, simply make adjustments to your filters in the Google Ads script to update.

2. Within this tab, you're only going to edit the green cells. Don't touch the yellow ones, as they're linked to the data you exported from the account. So you'll essentially edit around these.

3. When you receive this Spreadsheet in your email every week, by default every search query will have the Add Keyword? column checked – meaning that it will be included on the tab for export. Simply uncheck any terms you want to leave out of your upload to the account using Column A (tip: use the space bar to quickly check/uncheck).

4. You can also tweak the keyword text using Column G, select a different "Campaign \\\ Ad Group" to add the keyword to in Column H, and choose a different Match Type for the Keyword in Column I.

   • Note that the default Match Type is set to just scale to the next most specific Match Type (i.e. converting search from a Broad Keyword will recommend Phrase, Phrase will recommend Exact, and Exact will recommend Exact).

   • Note, though, that if you want to add a Keyword as Broad match, you don't need to edit the Keyword text by copying/pasting/adding plus marks in Column G. Broad Match keywords will automatically apply plus marks in the New Keyword Export tab.

5. When you've reviewed all terms, checked any that you want to include, and made any edits you need to make, you can go to the New Keyword Export tab and copy and paste the contents of that tab into the Ads Editor for a quick upload!

Adding New Negative Keywords

1. Go to the Negative Keyword Review tab (also in yellow!). That's where you'll be adding negatives from, and then copying & pasting the Negative Keyword Export (which will reformat all of your edits) into Ads Editor.

   • Same as with the New Keyword Review tab, you'll only be editing the green cells on this tab. Blue cells are linked to the data export and can't be edited.

2. This one is a bit more manual, since you'll want to be selecting the smallest common part of a Search Query to exclude large swaths of future Searches. So you'll be using Column C to add new Negative Keywords.

   • You can run a formula down the column and edit as needed, whatever works best. Totally open-ended what you choose to do in Column C.

   • When you add the Negative Keyword, it'll automatically be set as a Phrase Match Negative Keyword, but if you want Exact or Broad, simply add brackets or plus marks to update the Match Type.

3. When you add keywords, an option will appear in Column B to add the Negative Keyword as a campaign-level negative. Simply check that box to apply the Keyword to the entire campaign that the Search Query originated from.

4. If you check Column B, you'll see another checkbox appear in Column A to apply the Negative Keyword to all campaigns in the account. This will just iterate your Negative Keyword and when you paste the output into Ads Editor, it will add the Keyword into All But Removed campaigns in the account.

5. Once you're done scanning through and adding new negatives, same deal! Just go to the Negative Keyword Export tab and simply copy and paste the contents of that tab into the Ads Editor.

And you're done! Next time the sheet gets sent to you, it will clear all of the edits you made this past time you reviewed, so it's a clean slate for when you get it in your inbox next week!

• If you want to review before the email gets to your inbox, though, you can simply go to the "Schedule Emailing" menu and hit "Clear All Edits To Sheet" to clear off the sheet. Then just run the script manually from the account and update away!