One of the most tedious tasks involved in managing a Google Ads account is going through all of the search terms your ads showed up for and filtering out negative keywords.
We used to be able to make the job a little bit easier by being hyper-focused and using exact match keywords, which would show up only when the search term exactly matched your keyword.
However, lately, Google has broadened the definition of exact and taken control away from the advertiser, so now your ads can show on searches that they really shouldn't. Going through those long lists of search terms, which are often in the thousands, takes a lot of time.
Well, today I want to share a way around some of that tedious work with an automated negative keyword script by Marius Blau. The end result will be that any search terms in the last 7 days, that don't exactly match your keywords, will be automatically added as negatives.
How To Automate Negative Keywords For Google Ads
- Create a copy of this spreadsheet.
- In your Google Sheet, toggle "Auto exclude non exact matches" to yes.
- Open Google Ads and log into your account.
- Label any ad groups you want the script to have an effect on with the label "Automate negatives" (*note capitalisation is important here).
- In the left menu select Tools > Bulk actions > Scripts
- Click the blue + icon and select "New script"
- Name your script something descriptive like "Negative Keyword Automation Script"
Copy the script included below into the main code area (make sure you paste over or erase any existing code)
function main() {
// Provide the Google Sheets URL here
var spreadsheetURL = "";
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetURL);
var sheet = spreadsheet.getSheetByName("Search Term Data"); // Negatives are in the first sheet
var settingsSheet = spreadsheet.getSheetByName("Script Settings");
clearSheetExceptHeadline(spreadsheetURL, "Search Term Data")
//Script settings
var automateNegatives = settingsSheet.getRange("C2").getValue();
// Iterate through all ad groups in the account
var adGroupsIterator = AdsApp.adGroups().get();
while (adGroupsIterator.hasNext()) {
var adGroup = adGroupsIterator.next();
var adGroupId = adGroup.getId();
var adGroupName = adGroup.getName();
// Check if the ad group has a label named "Automate negatives"
if (hasLabel(adGroup, "Automate negatives")) {
// Fetch all keywords in the ad group
var keywordsIterator = adGroup.keywords().get();
var adGroupKeywords = [];
while (keywordsIterator.hasNext()) {
var keyword = keywordsIterator.next();
adGroupKeywords.push(keyword.getText());
}
// Get the search terms for the current ad group ordered by clicks in the last 30 days
var searchTermsQuery = "SELECT Query, Cost, Clicks, Conversions, ConversionValue, QueryMatchTypeWithVariant FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
"WHERE AdGroupId = " + adGroupId +
" AND Clicks > 0 " +
"DURING LAST_7_DAYS " +
"ORDER BY Clicks DESC ";
var searchTermsIterator = AdsApp.report(searchTermsQuery).rows();
while (searchTermsIterator.hasNext()) {
var searchTerm = searchTermsIterator.next();
var searchTermText = searchTerm["Query"].trim();
var searchTermCost = searchTerm["Cost"]
var searchTermClicks = searchTerm["Clicks"]
var searchTermConversions = searchTerm["Conversions"]
var searchTermConversionValue = searchTerm["ConversionValue"]
var matchTypeVariant = searchTerm["QueryMatchTypeWithVariant"]
var currentDate = new Date();
// Check if the search term is not in the ad group's keywords
if (adGroupKeywords.indexOf(searchTermText) === -1 && matchTypeVariant == "exact (close variant)") {
if(automateNegatives == "Yes"){
adGroup.createNegativeKeyword("[" + searchTermText + "]");
Logger.log("Excluded search term in ad group '" + adGroupName + "': " + searchTermText);
}
// Log to Google Sheets
var rowData = [currentDate, adGroupName, "[" + searchTermText + "]", searchTermCost, searchTermClicks, searchTermConversions, searchTermConversionValue, matchTypeVariant, "Search Term Excluded"];
sheet.appendRow(rowData);
}
else {var rowData = [currentDate, adGroupName, "[" + searchTermText + "]", searchTermCost, searchTermClicks, searchTermConversions, searchTermConversionValue, matchTypeVariant, "Search Term Not Excluded"];
sheet.appendRow(rowData);}
}
}
}
}
// Helper function to check if an ad group has a specific label
function hasLabel(adGroup, labelName) {
var labels = adGroup.labels().get();
while (labels.hasNext()) {
var label = labels.next();
if (label.getName() === labelName) {
return true;
}
}
return false;
}
function clearSheetExceptHeadline(spreadsheetURL, sheetName) {
// Open the spreadsheet by URL (or you could use SpreadsheetApp.getActiveSpreadsheet() if the script is bound to the spreadsheet)
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetURL);
// Access the specified sheet by name
var sheet = spreadsheet.getSheetByName(sheetName);
// Check if the sheet exists
if (!sheet) {
Logger.log("Sheet not found: " + sheetName);
return; // Exit the function if the sheet does not exist
}
// Get the number of rows and columns to clear
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
// Ensure there are rows to clear beyond the headline
if (lastRow > 1) {
// Clear all rows except the first one (headline)
sheet.getRange(2, 1, lastRow - 1, lastColumn).clearContent();
} else {
Logger.log("No data to clear below the headline row in sheet: " + sheetName);
}
}
- Add your spreadsheet between the quotation marks on line 3.
- Click run.
- Then click authorise.
- Now go out to your scripts and set it to run daily and it will automatically add new negative keywords every day.
There is loads more useful data in the Google Sheet, it will tell you everything about which keywords were removed, what they spent, if they converted and much more.
You can use that data to decide if you'd be better off adding that keyword to your campaign instead of excluding it.
Want to learn more? Follow me on LinkedIn or follow 3B1 on YouTube for regular insights and strategies.
Written by Christian Donovan, Director of Performance Marketing at 3B1.