Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
The full Keyword Cleanup Script from my SearchEngineLand.com post. Be sure to change the email on line 30. See the blog post for more details.
function main() {
var config_options = {
'default' : {
metric : 'Conversions', // This metric will be used for determining duds
threshold : 0, // Duds are less than or equal to this threshold
days_ago : 90, // The timeframe of the data to consider, days ago to yesterday
days_in_limbo : 5 // The script will warn you for this many days before deleting something
}
// If you want, you can add account specific configurations too. If an account specific config
// is not found, the default one from above is used.
//,
//'234-234-2345' : {
// metric : 'Conversions',
// threshold : 0,
// days_ago : 20,
// days_in_limbo : 5
//}
};
MccApp.accounts().executeInParallel('findKeywordsToDelete', 'generateReport', JSON.stringify(config_options))
}
/***********
* Collects the reporting results from all accounts
* and generates a nicely formatted email. If there
* are errors for an account, it includes those
* in the email as well since an error in one account
* won't stop the entire script.
***********/
function generateReport(results) {
var NOTIFY = ['your_email@example.com'];
var total_deleted = 0;
var total_keywords = 0;
// This is some explanation that will go in the body of the email.
var email_html = '<p>This is a summary of the keywords that '+
'are in danger of or have already been deleted. '+
'You can stop the deletion process by removing '+
'the countdown label and applying the "Save" '+
'label to the keyword.</p>';
var htmlTables = [];
var errors = [];
for (var i = 0; i < results.length; i++) {
// If there was an error in the account, let's add it to the email
if(results[i].getStatus() != 'OK') {
errors.push(results[i].getCustomerId() + ': ' + results[i].getError());
continue;
}
// Otherwise, we pull out the results and format them as needed.
var object = JSON.parse(results[i].getReturnValue());
if(!object) { continue; }
htmlTables.push(generateHtmlTable(object));
// Here we are keeping track of the totals to use
// in the subject line later.
total_keywords += object.total_keywords;
total_deleted += object.keywords_deleted;
}
if(errors) {
email_html += '<p>The following accounts returned an error</p>';
email_html += '<p>'+errors.join('<br/>')+'</p>';
}
email_html += htmlTables.join('');
email_html += '<p>Generated on: '+
Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(),'MMMM dd, yyyy @ hh:mma z')+
' by the Keyword Cleanup Script.</p>';
// Let's build our subject line as needed
var subject = 'Keyword Cleanup Script';
if(total_deleted) {
subject += ' - ' + total_deleted+' KWs Deleted';
}
if(total_keywords) {
subject += ' - ' + total_keywords+' Will Be Deleted Soon';
}
if(errors) {
subject += ' - ' + errors.length +' Account Errors';
}
// And finally, we send the emails.
for(var i in NOTIFY) {
MailApp.sendEmail(NOTIFY[i], subject, 'See html body.', { htmlBody : email_html });
}
}
// This helper function takes a set of results
// and returns nicely formatted HTML for a table.
// You can change the formatting as needed.
function generateHtmlTable(results) {
var retVal = [];
retVal.push('<p>');
retVal.push('<table border="1" width="50%" style="border-collapse:collapse;">');
retVal.push('<tr>')
retVal.push('<td width="40%">'+results.account_id+'</td><td>'+results.account_name+'</td>');
retVal.push('</tr>')
retVal.push('<tr>')
retVal.push('<td width="40%">Deleted Keywords</td><td>'+results.keywords_deleted+'</td>');
retVal.push('</tr>')
var labels = Object.keys(results.label_stats).sort();
for(var i in labels) {
retVal.push('<tr>')
retVal.push('<td width="40%">'+labels[i]+'</td><td>'+results.label_stats[labels[i]]+'</td>');
retVal.push('</tr>')
}
retVal.push('</table>');
retVal.push('</p>');
return retVal.join('');
}
/***********
* This function returns a summary of the changes
* we made to the account which can then be formatted
* and emailed as needed.
***********/
function generateSummaryData(changes_to_make) {
var summaryData = {
// Account information
account_id: AdWordsApp.currentAccount().getCustomerId(),
account_name: AdWordsApp.currentAccount().getName(),
// Keywords we deleted (if any)
keywords_deleted: changes_to_make.kw_to_delete.length,
// Keywords that will soon be deleted (if any)
total_keywords: 0,
// Counts for each label
label_stats : {}
};
for(var i in changes_to_make.labels_to_add) {
var label_name = changes_to_make.labels_to_add[i].label;
if(!summaryData.label_stats[label_name]) { summaryData.label_stats[label_name] = 0; }
summaryData.label_stats[label_name]++;
summaryData.total_keywords++;
}
return summaryData;
}
/***********
* This is the function that will run in each account. We
* can leverage all the functions we wrote earlier to make
* this as short as possible.
***********/
function findKeywordsToDelete(optionalInput) {
// We are sending over a set of configs from the main
// function. Parse that config, check to see if there is
// an override for this account, or use the default.
var all_configs = JSON.parse(optionalInput);
var cust_id = AdWordsApp.currentAccount().getCustomerId();
var config_options = (all_configs[cust_id]) ? all_configs[cust_id] : all_configs['default'];
// Create our labels
createLabelsIfNeeded(config_options.days_in_limbo);
// Find the keywords old enough to be considered and then
// the keywords that we consider duds.
var keywords_old_enough = getKeywordsActiveDaysAgo(config_options.days_ago);
var duds = getDuds(config_options,keywords_old_enough);
// Let's get our object ready to store the changes we need to make
var changes_to_make = {
kw_to_delete: [],
labels_to_delete: [],
labels_to_add: []
};
checkForRedemption(duds,changes_to_make);
while(duds.length > 0) {
// withIds can only handle 10000 records at a time.
// This will chop the duds array into 10000 record chunks.
// If you don't do this, you will get a strange error when you
// try iterating through the list.
var duds_chunk = duds.splice(0,10000);
findChangesToMake(duds_chunk,changes_to_make,config_options.days_in_limbo);
// Similar to before. If we are running short on time, leave early to
// allow the script to process the changes.
if(AdWordsApp.getExecutionInfo().getRemainingTime() < 120) {
Logger.log('Leaving early!');
break;
}
}
// Apply all the changes we made
applyChanges(changes_to_make);
// Generate some summary data so that we have something
// to collect for the email.
var summaryData = generateSummaryData(changes_to_make);
return JSON.stringify(summaryData)
}
/***********
* This function verifies that the keywords already in limbo
* but are no longer a dud have the labels removed as needed.
**********/
function checkForRedemption(duds,changes_to_make) {
// An array works well for selectors, but
// it will be much easier to do lookups here if
// we transform the duds array into a map.
var dudsMap = {};
for(var i in duds) {
dudsMap[[duds[i][0],duds[i][1]].join('-')] = true;
}
var labelIter = AdWordsApp.labels().withCondition("Name STARTS_WITH 'Deleting in '").get();
while(labelIter.hasNext()) {
var label = labelIter.next();
var kwIter = label.keywords().get();
while(kwIter.hasNext()) {
var kw = kwIter.next();
var key = [kw.getAdGroup().getId(),kw.getId()].join('-');
if(!dudsMap[key]) {
// The keyword is no longer a dud. Let's remove the label.
// We have to find the label linked to this keyword though since
// we don't want to delete the label from the entire account.
var newLabelIter = kw.labels().withCondition("Name STARTS_WITH 'Deleting in '").get();
while(newLabelIter.hasNext()) {
changes_to_make.labels_to_delete.push(newLabelIter.next());
}
}
}
}
}
/***********
* Given a set of Ids, an object to store updates in, and the
* max number of days a keyword can be in limbo, this function
* iterates through your account and gathers the changes to be
* made. It also contains the logic to ignore keywords with the
* label "Save" on them. All changes are stored in the arrays within
* the object changes_to_make.
**********/
function findChangesToMake(duds,changes_to_make,max_days_in_limbo) {
// This is the label applied to "Save" a keyword
var SAVE_LABEL_TEXT = 'Save';
// This is the label format applied to keywords in limbo.
var LABEL_REGEXP = /Deleting in (\d+) days/g;
var kw_iter = AdWordsApp.keywords().withIds(duds).get();
while(kw_iter.hasNext()) {
var kw = kw_iter.next();
var labels = kw.labels().withCondition("Name STARTS_WITH 'Deleting in '").get();
var processed_label = false;
while(labels.hasNext()) {
var label = labels.next();
var label_text = label.getName();
if(label_text == SAVE_LABEL_TEXT) {
processed_label = true;
} else if(LABEL_REGEXP.test(label_text)) {
// This means the keyword was previously in limbo
processed_label = true;
var match = label_text.match(/\d+/g);
if(match) {
var daysLeft = parseInt(match[0]);
if(daysLeft == 1) {
changes_to_make.kw_to_delete.push(kw);
} else {
daysLeft--;
changes_to_make.labels_to_delete.push(label);
changes_to_make.labels_to_add.push({kw:kw,label:'Deleting in '+daysLeft+' days'});
}
} else {
throw 'Was not able to extract remaining days from label: '+label_text;
}
}
if(processed_label) { break; }
}
if(!processed_label) {
changes_to_make.labels_to_add.push({kw:kw,label:'Deleting in '+max_days_in_limbo+' days'});
}
if(AdWordsApp.getExecutionInfo().getRemainingTime() < 120) {
Logger.log('Bailing early!');
break;
}
}
}
/***********
* This function applies a set of changes provided.
* It utilizes tight loops to take advantage of any
* batch processing behind the scenes.
***********/
function applyChanges(changes_to_make) {
for(var i in changes_to_make.kw_to_delete) {
changes_to_make.kw_to_delete[i].remove();
}
for(var i in changes_to_make.labels_to_delete) {
changes_to_make.labels_to_delete[i].remove();
}
for(var i in changes_to_make.labels_to_add) {
changes_to_make.labels_to_add[i].kw.applyLabel(changes_to_make.labels_to_add[i].label);
}
}
/***********
* Helper function to create the labels in the account
* that will be used to keep track of Keywords in limbo.
***********/
function createLabelsIfNeeded(days_in_limbo) {
for(var i = 1; i<=days_in_limbo; i++) {
var label_name = 'Deleting in '+i+' days';
if(!AdWordsApp.labels().withCondition("Name = '"+label_name+"'").get().hasNext()) {
AdWordsApp.createLabel(label_name,
'These entities will be deleted in '+i+
' days. Created by an AdWords Script.');
}
}
}
/***********
* Find all the keywords that match a set of criteria. Those keywords
* will be filtered by the set of eligible keywords.
* It returns a list of AdGroup and Keyword Ids to use in a Selector.
***********/
function getDuds(options,eligible_keywords) {
var columns = ['CampaignId',
'AdGroupId',
'Id'];
// Let's add the metric we're using to find the duds
columns.push(options.metric);
var date_range = [
dateStringDaysAgo(options.days_ago),
dateStringDaysAgo(1)
].join(',');
var query_str = [
'SELECT',columns.join(','),
'FROM','KEYWORDS_PERFORMANCE_REPORT',
'WHERE',buildFilterClause(options),
'DURING',date_range
].join(' ');
var report = AdWordsApp.report(query_str);
var rows = report.rows();
var duds = [];
while (rows.hasNext()) {
var row = rows.next();
var key = [row.CampaignId,row.AdGroupId,row.Id].join('-');
// If the keyword isn't eligible, skip it
if(!eligible_keywords[key]) { continue; }
duds.push([row.AdGroupId,row.Id]);
}
return duds;
}
// Helper function to build the AWQL filter
function buildFilterClause(options) {
return [options.metric,'<=',options.threshold].join(' ');
}
/***********
* Find all the keywords that were active daysAgo
* Return those Ids in a mapping so we can easily filter other
* queries.
***********/
function getKeywordsActiveDaysAgo(daysAgo) {
// This will be a mapping of Ids we will return
var keyword_ids = {};
// We are only concerned with the Ids, so let's
// make this as small as possible.
var columns = ['ExternalCustomerId',
'CampaignId',
'AdGroupId',
'Id'];
var date_string = dateStringDaysAgo(daysAgo);
// Our date range is going to be a single day
var date_range = [date_string,date_string].join(',');
// A simple AWQL Query to grab active keywords from that day
var query_str = [
'SELECT',columns.join(','),
'FROM','KEYWORDS_PERFORMANCE_REPORT',
'WHERE Status = ENABLED',
'DURING',date_range
].join(' ');
var report = AdWordsApp.report(query_str);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
// Our Id will be a combination of Campaign Id, AdGroup Id, and Criteria (Keyword) Id
var key = [row.CampaignId,row.AdGroupId,row.Id].join('-');
keyword_ids[key] = true;
}
return keyword_ids;
}
/***********
* Helper function to calculate the date daysAgo
* Returns a string such as 20151125
***********/
function dateStringDaysAgo(daysAgo) {
var ONE_DAY = (24 * 60 * 60 * 1000);
var today = new Date();
var daysAgo = new Date(today.getTime() - (daysAgo * ONE_DAY));
return Utilities.formatDate(daysAgo, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
}
@thomasstr

This comment has been minimized.

Copy link

@thomasstr thomasstr commented Nov 20, 2015

Just a quick question. I have launched this script and have 36 accounts with 165 changes. But it is still running. Do you have any estimate of how long it takes for the script to complete?

@SuperShibui

This comment has been minimized.

Copy link

@SuperShibui SuperShibui commented Nov 24, 2015

I want to test this script on the regular account level. I'm getting the error > ReferenceError: "MccApp" is not defined. (line 19)

What can I do to correct this?

Thank you!

@gustavomeirelles

This comment has been minimized.

Copy link

@gustavomeirelles gustavomeirelles commented Nov 30, 2015

As SuperShibui i have the same question. What can i do to correct this error?

ReferenceError: "MccApp" is not defined. (line 10)

Thanks!

@charlehoffman

This comment has been minimized.

Copy link

@charlehoffman charlehoffman commented Dec 22, 2015

Get the same error? please advise

c

@vqtrung90

This comment has been minimized.

Copy link

@vqtrung90 vqtrung90 commented Jan 11, 2016

Hi Russell Savage,

Tks for sharing, Can i ask, how long Scripts complete :)

@alxrz

This comment has been minimized.

Copy link

@alxrz alxrz commented Mar 30, 2016

@gustavomeirelles apparently you're using script for a non manager account, instead of executeInParralel function use findKeywordsToDelete()

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment