Are you tired of endlessly sifting through your search term reports?
With Google pushing broad match like a fitness coach demanding “just one more rep”,
and match types becoming more fuzzy than my memory of high school Greek,
we are all forced to monitor our search terms like a hawk, 24/7,
and negate all those completely irrelevant keyword matches that Google throws at us.
Not doing so inevitably brings unnecessary costs and low-quality clicks.
BUT, the amount of time needed to spend on Negative Keywords these days, just to block irrelevant searches is simply ridiculous.
It can easily take HOURS per week!
Before you know it, you’re turning into a full-time bouncer for irrelevant search traffic.
Not my ideal job (especially since I hardly reach 5’8″ / 1.76m);
I needed something that could punch above my own weight to fight all that off-target traffic.
So, how about we automate this monotony marathon of discovering search terms that waste our budget?
That’s exactly what I thought when I created this script:
Script: Negative Keyword Suggestions
What it does:
This script suggests negative keywords for search terms that had a significant number of clicks, but little to none conversions.
If new negatives are suggested the negatives are reported via email.
The email contains a link to a Google Doc spreadsheet documenting all the negative keyword suggestions.
Why you care:
Google’s keyword matching algorithms are far from perfect, change all the time, and continuously send irrelevant clicks from search terms that don’t convert.
Spending money on these clicks is a waste of budget. Adding poor-performing search terms as negatives prevents Google from wasting more money on these clicks.
Now go ahead, and install the script (it only takes 5 mins!).
Run the script weekly (I prefer Monday mornings)
And sleep comfortably knowing that all money-wasting-search-terms will pop up in your inbox when they don’t convert.
Happy scripting!
NB: If you like this script be sure to also check out my Google Ads Script for Non-Converting Performance Max Search Terms Alerts
All the scripts you publish save my life
"All the scripts you publish save my life, SO MANY THANKS , great job!!!!!!"
ᴎils rooijmans
2023-05-04T07:40:54+00:00
Marta Sabaté Méndez, PPC/SEM Manager & Digital Analyst - Barcelona, Spain
https://nilsrooijmans.com/testimonials/all-the-scripts-you-publish-save-my-life/
0
ᴎils rooijmans
INSTRUCTIONS:
- See the script code below. Install the script in your account.
Don’t worry if you have never done this before. You do not need any coding skills. It is as simple as copy-paste. Simply follow these instructions on how to set up and schedule google ads scripts. - Create a new Google Sheet
(tip for chrome users: simply type ‘sheets.new’ in the address bar) - Add the complete URL of the spreadsheet to the SPREADSHEET_URL below (line 35)
- Decide on the date range / PERIOD (line 37)
- Set thresholds for MIN_CONVERSIONS, MIN_CLICKS, MIN_COST (line 42-44)
- Add the name of your Google Ads account (line 62)
- Add your email address to the script (line 65)
- Authorize and Preview
- Schedule to run weekly (I prefer Mondays at 6AM)
Subscribe to my mailing list to receive more scripts and updates on how to start learning to write your own Google Ads Scripts.
/**
*
* Google Ads Script - Negative Keyword Suggestions (FREE version)
*
* (C) Nils Rooijmans , https://nilsrooijmans.com
*
* This script suggests negative keywords for search terms that had little to none conversions.
* If new negatives are suggested the negatives are reported via email.
* The email containts a link to Google Doc spreadsheet documenting all the negative keyword suggestions.
*
* INSTRUCTIONS:
*
* If you are new to scripts -> follow these instructions on how to copy-paste and install Google Ads Scripts:
* https://nilsrooijmans.com/how-to-set-up-and-run-google-ads-scripts/
*
* Next:
* 1. Create a new Google Sheet (tip for chrome users: simply type 'sheets.new' in the address bar)
* 2. Add the complete URL of the spreadsheet to the SPREADSHEET_URL below (line 35)
* 3. Decide on the date range / PERIOD (line 37)
* 4. Set thresholds for MIN_CONVERSIONS, MIN_CLICKS, MIN_COST (line 42-44)
* 5. Add the name of your Google Ads account (line 62)
* 6. Add your email address to the script (line 65)
* 7. Authorize and Preview
* 8. Schedule to run weekly (I prefer Mondays at 6AM)
*
*
* Version: 1.2 - Added support for Shopping campaigns
* Version: 1.1 - Fixed bugs with PERIOD and fixed check to see if neg KW has successfully been added
* Version: 1.0
*
* ---------------------------------------------------
**/
/// START OF SCRIPT CONFIGURATION ///
var SPREADSHEET_URL = ""; //insert url of new Google spreadsheet
var PERIOD = "LAST_90_DAYS"; // the date range for your search term data, can be either "LAST_7_DAYS", "LAST_30_DAYS", "LAST_60_DAYS", "LAST_90_DAYS", "LAST_12_MONTHS" or "THIS_YEAR"
/*** THRESHOLDS ***/
var MIN_CONVERSIONS = 0.5; // suggest negative keywords for search terms that had less than this amount of conversions during the PERIOD you defined above
var MIN_CLICKS = 60; // Only look at search terms that have had over this amount of clicks during the PERIOD you defined above
var MIN_COST = 5; // Only look at search terms that accrued over this amount of cost during the PERIOD you defined above
/*** CAMPAIGN FILTERS ***/
var CAMPAIGN_NAME_CONTAINS = "";
// Use this if you only want to process some campaigns
// such as campaigns with names containing 'Brand' or 'Shopping'.
// Leave as "" if you want to process all campaigns.
var CAMPAIGN_NAME_DOES_NOT_CONTAIN = "";
// Use this if you want to exclude some campaigns
// such as campaigns with names containing 'Brand' or 'Shopping'.
// Leave as "" if not wanted.
/*** EMAIL CONFIGURATION SETTINGS ***/
var ACCOUNT_NAME = ""; // insert short name to recoginze the account
var CURRENCY = "USD"; // USD, EUR etc
var SEND_EMAIL = true;
var EMAIL_ADDRESS = ""; // insert email addresses of people that want to get the alerts between the quotes, seperate multipe email addresses by a comma
/// END OF SCRIPT CONFIGURATION ///
/*** DON'T CHANGE ANYTHING BELOW THIS LINE ***/
var DEBUG = true;
var EMAIL_SUBJECT = ACCOUNT_NAME+" - New Negative Keyword suggestions based on Low Performing Search Terms ";
var EMAIL_BODY = "This script looks at all enabled ad groups in all enabled campaigns, and"+
" suggests negative keywords at the ad group level for search terms that had over "+MIN_CLICKS+" clicks"+
" AND spend over "+MIN_COST+" "+CURRENCY+" AND less than "+MIN_CONVERSIONS+" conversions during "+PERIOD+".\n";
function main() {
var result = getResults();
reportResults(result);
}
function reportResults(result) {
var outputSheet = prepareOutputSheets();
var nrOfResultsReported = 0;
var output = result.output;
if (output.length > 0) {
addOutputToSheet(output, outputSheet);
nrOfResultsReported += output.length;
}
if(SEND_EMAIL && nrOfResultsReported > 0) {
var emailSubject = "[Gads Script] "+EMAIL_SUBJECT;
var emailFooter = getEmailFooter();
var emailBody = EMAIL_BODY+"\nNumber of negative keyword suggestions added in total: "+nrOfResultsReported+"\n\n"+
"You can find all negative keyword suggestions here: "+SPREADSHEET_URL+"\n\n"+
"---\n"+emailFooter;
var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
Logger.log("Remaining email quota: " + emailQuotaRemaining);
MailApp.sendEmail(EMAIL_ADDRESS, emailSubject, emailBody) ;
}
}
function getResults() {
var accountName = AdsApp.currentAccount().getName();
var results = [];
var date = getTodaysDate();
Logger.log("date: "+date);
var startDate = getStartDate(PERIOD);
var endDate = getEndDate(PERIOD);
var whereClause =
" WHERE metrics.clicks > " + MIN_CLICKS +
" AND metrics.cost_micros > " + toMoney(MIN_COST) +
" AND metrics.conversions < " + MIN_CONVERSIONS;
if (CAMPAIGN_NAME_CONTAINS != "" ) {
whereClause = whereClause +
" AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS + "%'";
}
if (CAMPAIGN_NAME_DOES_NOT_CONTAIN != "" ) {
whereClause = whereClause +
" AND campaign.name NOT LIKE '%" + CAMPAIGN_NAME_DOES_NOT_CONTAIN + "%'";
}
whereClause = whereClause +
" AND campaign.status = ENABLED AND ad_group.status = ENABLED" +
" AND segments.date BETWEEN '" + startDate + "' AND '" + endDate +"'";
" ORDER BY metrics.clicks DESC" ;
// + " LIMIT 10";
var query =
"SELECT search_term_view.search_term, campaign.name, campaign.advertising_channel_type, ad_group.name, ad_group.id, metrics.impressions, metrics.clicks, metrics.average_cpc, metrics.cost_micros, metrics.conversions" +
" FROM search_term_view" + whereClause;
//debug(query);
try {
var result = AdsApp.search(query);
while (result.hasNext()) {
var row = result.next();
//debug(row);
var searchTerm = row.searchTermView.searchTerm;
var campaignName = row.campaign.name;
var campaignType = row.campaign.advertisingChannelType;
var adGroupName = row.adGroup.name;
var adGroupId = row.adGroup.id;
var impressions = row.metrics.impressions;
var clicks = row.metrics.clicks;
var ctr = parseFloat((clicks/impressions).toFixed(2));
var avgCPC = convertMoney(row.metrics.averageCpc).toFixed(2);
var cost = convertMoney(row.metrics.costMicros).toFixed(2);
var conversions = parseFloat((row.metrics.conversions).toFixed(2));
var conversionRate = parseFloat((conversions/clicks).toFixed(2));
var costPerConversion = parseFloat((cost/conversions).toFixed(2));
Logger.log("\nPotential Negative keyword candidate: "+searchTerm+" for ad group: "+adGroupName+" (adGroupId:"+adGroupId+") in campaign: '"+campaignName+"' of campaign type: "+campaignType);
var isShoppingCampaign = (campaignType == 'SHOPPING');
//debug ("isShoppingCampaign: "+isShoppingCampaign);
if(!isNegated(searchTerm, adGroupId, isShoppingCampaign)) {
console.log(`Negative keyword suggestion: campaign '${campaignName}' | adgroup '${adGroupName}' | searchTerm '${searchTerm}' | clicks ${clicks} | conversions ${conversions}`);
results.push([date, accountName, campaignName, adGroupName, impressions, clicks, ctr, avgCPC, cost, conversions, conversionRate, costPerConversion, searchTerm, "Search term not yet negated"]);
}
}
} catch(e) {
alert("ERROR: "+e);
MailApp.sendEmail(EMAIL_ADDRESSES, "### ERROR: "+EMAIL_SUBJECT, "Could not suggest negative keywords: "+e);
}
Logger.log("\nFinished analyses\nNumber of new negative keyword suggestions: "+results.length);
return {
output: results
};
}
function isNegated(keyword, adGroupId, isShoppingCampaign) {
var negativeKWs = getNegativeKeywords(adGroupId, isShoppingCampaign);
for(var i=0;i<negativeKWs.length;i++) {
if(negativeBlocksPositive(negativeKWs[i], keyword)) {
Logger.log("Keyword already negated: "+keyword);
return true;
}
}
return false;
}
function getNegativeKeywords(adGroupId, isShoppingCampaign) {
var ids = [adGroupId];
var negKWs = [];
var campaignId;
// first: let's get all the negative keywords for the ad group
if (!isShoppingCampaign) {
var adGroupIterator = adGroupIterator = AdsApp.adGroups().withIds(ids).get();
} else {
var adGroupIterator = adGroupIterator = AdsApp.shoppingAdGroups().withIds(ids).get();
}
while (adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
var negKWIterator = adGroup.negativeKeywords().get();
while (negKWIterator.hasNext()) {
var negativeKW = negKWIterator.next();
negKWs.push(negativeKW);
}
campaignId = adGroup.getCampaign().getId();
}
// second: let's get all the negative keywords for the campaign
var campaignIds = [campaignId];
if (!isShoppingCampaign) {
var campaignIterator = AdsApp.campaigns().withIds(campaignIds).get();
} else {
var campaignIterator = AdsApp.shoppingCampaigns().withIds(campaignIds).get();
}
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
// let's get all the negative keywords at the campaign level
var negKWIterator = campaign.negativeKeywords().get();
while (negKWIterator.hasNext()) {
var negativeKW = negKWIterator.next();
negKWs.push(negativeKW);
}
// let's get all the negative keywords from negative keyword lists associated to this campaign
var negativeKeywordListIterator = campaign.negativeKeywordLists().withCondition('Status = ACTIVE').get();
while (negativeKeywordListIterator.hasNext()) {
var negativeKeywordList = negativeKeywordListIterator.next();
var negativeKeywordIterator = negativeKeywordList.negativeKeywords().get();
while (negativeKeywordIterator.hasNext()) {
var negativeKW = negativeKeywordIterator.next();
negKWs.push(negativeKW);
}
}
}
return negKWs;
}
function negativeBlocksPositive(negativeKeyword, positiveKeywordText) {
switch (negativeKeyword.getMatchType()) {
case 'BROAD':
return hasAllTokens(negativeKeyword.getText(), positiveKeywordText);
break;
case 'PHRASE':
return isSubsequence(negativeKeyword.getText(), positiveKeywordText);
break;
case 'EXACT':
return positiveKeywordText === negativeKeyword.getText().replace('[', '').replace(']', '');
break;
}
}
/**
* Tests whether all of the tokens in one keyword's raw text appear in
* the tokens of a second keyword's text.
*
* @param {string} keywordText1 the raw keyword text whose tokens may
* appear in the other keyword text.
* @param {string} keywordText2 the raw keyword text which may contain
* the tokens of the other keyword.
* @return {boolean} Whether all tokens in keywordText1 appear among
* the tokens of keywordText2.
*/
function hasAllTokens(keywordText1, keywordText2) {
var keywordTokens1 = keywordText1.split(' ');
var keywordTokens2 = keywordText2.split(' ');
for (var i = 0; i < keywordTokens1.length; i++) {
if (keywordTokens2.indexOf(keywordTokens1[i]) == -1) {
return false;
}
}
return true;
}
/**
* Tests whether all of the tokens in one keyword's raw text appear in
* order in the tokens of a second keyword's text.
*
* @param {string} keywordText1 the raw keyword text whose tokens may
* appear in the other keyword text.
* @param {string} keywordText2 the raw keyword text which may contain
* the tokens of the other keyword in order.
* @return {boolean} Whether all tokens in keywordText1 appear in order
* among the tokens of keywordText2.
*/
function isSubsequence(keywordText1, keywordText2) {
return (' ' + keywordText2 + ' ').indexOf(' ' + keywordText1 + ' ') >= 0;
}
/*** REPORT FUNCTIONS ***/
function prepareOutputSheets() {
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
if (!spreadsheet) {
Logger.log("Cannot open new reporting spreadsheet") ;
return ;
}
var sheet = spreadsheet.getActiveSheet();
if (!sheet) {
Logger.log("Cannot open new reporting sheet") ;
return ;
}
var numberOfRows=sheet.getLastRow() ;
debug("NR of rows in output sheet: "+numberOfRows);
// set width of columns
sheet.setColumnWidth(1, 80);
sheet.setColumnWidth(2, 200);
sheet.setColumnWidth(3, 300);
sheet.setColumnWidth(4, 200);
sheet.setColumnWidth(5, 100);
sheet.setColumnWidth(6, 60);
sheet.setColumnWidth(7, 60);
sheet.setColumnWidth(8, 60);
sheet.setColumnWidth(9, 60);
sheet.setColumnWidth(10, 80);
sheet.setColumnWidth(11, 80);
sheet.setColumnWidth(12, 80);
sheet.setColumnWidth(13, 200);
sheet.setColumnWidth(14, 100);
addHeaderToOutputSheet(sheet);
return sheet;
}
function addHeaderToOutputSheet(sheet) {
try {
var headerSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/11BzTJ4MhjgVrJ7gN9TpZiVFj89aRRBEklABT4onQiOo/").getSheetByName('header_sheet');
} catch(e) {
console.log(`### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com\n${e}`);
throw `### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com\n${e}`;
}
var headerRange = headerSheet.getRange(1, 1, 2, headerSheet.getLastColumn());
var headerData = headerRange.getValues();
console.log("Adding header to the output sheet");
var range=sheet.getRange(1,1,2,headerData[1].length);
range.clear();
range.clearFormat();
range.setValues(headerData)
range.setFontWeight("bold");
range = sheet.getRange(1,1,1,headerData[1].length);
range.setFontColor('#007BFF')
sheet.setFrozenRows(2);
}
function addOutputToSheet(output, sheet) {
var numberOfRows=sheet.getLastRow() ;
sheet.insertRowsBefore(3, output.length);
var startRow = 3;
var range=sheet.getRange(startRow, 1, output.length, output[0].length) ;
range.setValues(output) ;
range.sort([{column: 1, ascending: false}, {column: 5, ascending: false}]);
}
function getEmailFooter() {
try {
var footerSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/11BzTJ4MhjgVrJ7gN9TpZiVFj89aRRBEklABT4onQiOo/").getSheetByName('email_footer');
} catch(e) {
console.log(`### There was an issue opening the email_footer sheet. Please download the latest version of this script at https://nilsrooijmans.com\n${e}`);
throw `### There was an issue opening the email_footer sheet. Please download the latest version of this script at https://nilsrooijmans.com\n${e}`;
}
var footerRange = footerSheet.getRange(1, 1, footerSheet.getLastRow(), 1);
var footerData = footerRange.getValues();
console.log(`Getting email footer text`);
var footer = footerData.join('\n');
return footer;
}
/*** HELPER FUNCTIONS ***/
function debug(string) {
if (DEBUG == true) {
Logger.log("** "+string);
}
}
function alert(string) {
Logger.log("### "+string);
}
// convert cost value to money data type
function toMoney(m) {
return m * 1000000;
}
// convert money data type to cost value
function convertMoney(m) {
return m / 1000000;
}
function isInArray(value, array) {
return array.indexOf(value) > -1;
}
function getTodaysDate() {
return new Date();
}
function getStartDate(period) {
var startDate;
var today = new Date();
switch(period) {
case "THIS_YEAR" :
startDate = today.getFullYear()+"-01-01";
break;
case "LAST_7_DAYS" :
startDate = dateToISOString(new Date(Date.now() - 7*864e5)); // 864e5 == 86400000 == 24*60*60*1000
break;
case "LAST_30_DAYS" :
startDate = dateToISOString(new Date(Date.now() - 30*864e5)); // 864e5 == 86400000 == 24*60*60*1000
break;
case "LAST_60_DAYS" :
startDate = dateToISOString(new Date(Date.now() - 60*864e5));
break;
case "LAST_90_DAYS" :
startDate = dateToISOString(new Date(Date.now() - 90*864e5));
break;
case "LAST_12_MONTHS" :
startDate = dateToISOString(new Date(Date.now() - 365*864e5));
break;
default:
startDate ="2021-01-01";
}
debug("startDate: "+startDate);
return startDate;
}
function getEndDate(period) {
var endDate;
var yesterday = new Date(Date.now() - 864e5); // 864e5 == 86400000 == 24*60*60*1000
switch(period) {
case "LAST_YEAR" :
endDate = "lastDayLastYear"; //TODO
break;
case "LAST_MONTH" :
endDate = "lastDayLastMonth" //TODO
break;
default:
endDate = dateToISOString(yesterday);
}
debug("endDate: "+endDate);
return endDate;
}
function dateToISOString(date) {
var year = date.getUTCFullYear();
var month = date.getUTCMonth()+1;
if (month < 10) {
month = "0"+month;
}
var day = date.getUTCDate();
if (day < 10) {
day = "0"+day;
}
var ISOString = year+"-"+month+"-"+day;
return ISOString;
}
Join thousands of PPC geeks who already have access: