I love trying and testing out new keywords, but I usually forget to clean up after myself. According to Larry Kim’s article about Keyword Hoarding, I’m not alone. What he found was that around 98 percent of the keywords in your account don’t really do much except slow things down. Finding that needle in the haystack is a little easier when the haystack is 98 percent smaller.
Luckily, we have the power of AdWords Scripts to help us clear out the clutter and get rid of the duds. Let’s write an MCC-level AdWords Script to clean up after ourselves as we load and test new keywords.
A Sample Summary Email for this Script
I encourage you to follow along as we build the script, but for those who can’t wait, feel free to jump to the full script at the end of this post.
Initial Script Design
It turns out that 98 percent of the keywords in your account don’t really do anything. This script is going to attempt to find those useless keywords and clear them out.
We have a few things to keep in mind when building this script.
First, deleting things can be a dangerous thing to do with scripting. We never want to be surprised when a keyword disappears. Let’s make sure we build in a period of time where we are warned about our keywords being deleted beforehand, so we can stop it if we want to. We can apply labels to those keywords to give users in the account a warning that the keyword will soon be deleted.
Second, we need a way to stop the script from deleting keywords that are near and dear to us for some reason. Some things you just aren’t ready to part with, so let’s also add a label that, when used, makes sure the script will ignore it.
Third, we probably don’t want the script to delete keywords that were recently added to the account and don’t have any activity yet. That’s a little tricky, since keywords don’t have a creation date that is easily accessible, but we can use the reporting API to look back in time.
And finally, we want the script to keep us up to date via email about all the changes it is making. Because this is an MCC-level script, if we leverage the executeInParallel function, we can collect the results from each account and generate a summary email of the changes.
So with that in mind, the logic of our script is probably going to look something like this:
- For each account in our MCC…
- Figure out what keywords have existed for enough time to be considered duds.
- Figure out how many of those keywords haven’t done anything for us lately.
- For each keyword that’s a dud…
- If this is a Saved keyword, ignore it.
- If this is the first time we’ve seen it, give it a warning label saying it will be deleted soon.
- If it already has a label, reduce the number of days by 1.
- If the days remaining hits 0, delete the keyword.
- Report on what happened.
This seemingly simple script has turned out to be a little more complicated than we might have thought, so we’d better get started.
Configuration
For something like this to work, we’ll need a few configuration options. First, we need to know how to classify a dud. I’d stick with anything that has zero conversions, but feel free to pick whatever metric from the Keywords Report works for you.
We’ll also need to know how far back to look at data and how many days we want to give ourselves before the script automatically deletes the keyword. Since this is going to run across a number of accounts, let’s add the ability to specify unique configurations for each one with a default to fall back to. Our initial MCC script should look something like this:
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) | |
); | |
} |
The ability to send configuration information to each executeInParallel script is relatively new and a much welcome addition. Now we just need to write a function to run in each account, and one more to gather the results and send an email with what happened.
Working With Labels
This script is going to leverage labels to help identify keywords that will soon be deleted, as well as ones we want to save. In order to do this, we’ll need to create a few labels, one for each of the days a keyword will be in limbo as specified by the configuration. Here’s a quick helper function that does the job nicely:
/*********** | |
* 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.'); | |
} | |
} | |
} |
We started small, but now let’s work on finding the keywords we are going to be clearing out.
Stepping Back In Time
The performance of a keyword is always measured over a period of time which we have already specified in our configuration file. Since AdWords doesn’t let us filter by creation date in our queries, we need to look at a Keyword report from the oldest date in the range to find all the keywords that have been around since then. This way, we can use those keywords to make sure we don’t delete anything that’s too young and hasn’t had enough time to generate data.
/*********** | |
* 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'); | |
} |
I’ve tried to document the code as much as possible to let you know what is going on. This function will return a mapping of unique identifiers that we can use to filter our other data.
Finding The Duds
Finally, we get to the function where we find all the duds. These keywords that just don’t cut it are the clutter we’ve been looking for. The logic for this is almost identical to that of the function above, except we need to add the criteria for finding the duds. We also want to make sure we are looking across the entire time frame, not just a single day.
/*********** | |
* 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(' '); | |
} |
Processing The Duds
Now we have the IDs we need to start processing things. This code will run through each keyword in the list, determine what label to apply, if any, and collect all the changes that need to be made into a set of lists. In order to keep the speed up, we are first going to gather all the changes we want to make on the account and then apply those changes all at once.
/*********** | |
* 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().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) { | |
// This pulls the number of days from the label | |
var daysLeft = parseInt(match[0]); | |
if(daysLeft == 1) { | |
// If it was the last day, delete it | |
changes_to_make.kw_to_delete.push(kw); | |
} else { | |
// Otherwise, drop the count by 1 day | |
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'}); | |
} | |
// Sometimes these things can run long on large accounts. | |
// Break early if we are running out of time so we have some | |
// time to apply the changes. If you still run out of time, try | |
// increasing the value here (in seconds). | |
if(AdWordsApp.getExecutionInfo().getRemainingTime() < 120) { | |
Logger.log('Leaving early!'); | |
break; | |
} | |
} | |
} |
While we’re here, we should probably write the function that is going to actually apply the changes we just collected. All the function does is apply each of the changes in tight loops so that we can take advantage of any batch processing behind the scenes.
/*********** | |
* 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); | |
} | |
} |
Putting It All Together
Now that we have all the functions we need, let’s put them all together in the function we will be calling from our executeInParallel method in our main function. Since we’ve already written most of this, even though this function does the majority of processing for our script, it is relatively short.
/*********** | |
* 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: [] | |
}; | |
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) | |
} |
Redemption
Some of you may have already seen a problem with our script so far. What happens when one of the keywords in limbo gets a conversion and is no longer in the penalty box? Our code so far would ignore those keywords and keep the limbo labels in place. We can fix that pretty quickly with this script to grab the keywords for each label, check if they are in the duds list, and if not, remove the label.
/*********** | |
* 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()); | |
} | |
} | |
} | |
} | |
} |
We can add the call to this function in our findKeywordsToDelete function just before we start processing the duds (line 28).
Reporting Our Results
For something like this, we want to generate a nice summary report to send in an email. You’ll notice that at the end of the previous function, there is a call to generateSummaryReport. The last part of this script is the function to gather all the data from the accounts and generate a nice-looking email to send out. The code itself isn’t complicated, but there is a lot of formatting for the HTML part of the email that takes up a lot of space. Check it out.
/*********** | |
* 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; | |
} |
Conclusion
Well, what started out as a simple keyword deletion script morphed into something pretty complex. You can find a copy of the full Keyword Cleanup script here. Once you schedule it, you should receive an email with the results nicely formatted letting you know where the clutter is. You can log into your account and use the labels report to easily find the keywords that are going to disappear soon. If you have any issues getting this to work, feel free to let me know on Twitter.
The post Keep Your Accounts Clutter-Free With This AdWords Script appeared first on Search Engine Land.
from SEO Rank Video Blog http://ift.tt/1PClb74
via IFTTT
No comments:
Post a Comment