I shared this script in a presentation at Pubcon earlier this week, but here it is in it’s full form. It’s meant to be a simple to understand demonstration script, so feel free to add to it if you have more experience with scripts.
A few notes. This assumes that you use “SKAG” in the name of any Single Keyword Ad Group that you make. If you don’t you’ll have to change line 25 to something that identifies these adgroups in your account, and change the SELECT statement to include that column if it’s not in the ad group name. It could use a label for example. If you don’t have any delineation to identify these SKAGs the script will not be able to work.
Also, I’m using logger to record all the changes. It wouldn’t be too much of a challenge to modify the script to output elsewhere.
Finally, the current configuration of the script needs only to run once a week. If you run it more frequently (or less frequently) it’s worth modifying the DURING clause to match the time period.
/**
* Configuration to be used for running reports.
*/
var REPORTING_OPTIONS = {
// Comment out the following line to default to the latest reporting version.
apiVersion: 'v201806'
};
function main() {
var report = AdWordsApp.report(
'SELECT Query, Clicks, Cost, Ctr,' +
' KeywordTextMatchingQuery, KeywordId, AdGroupName, AdGroupId, QueryMatchTypeWithVariant ' +
' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
' WHERE ' +
' QueryMatchTypeWithVariant = "NEAR_EXACT" ' +
' AND AdGroupName CONTAINS "SKAG" ' +
' DURING LAST_7_DAYS', REPORTING_OPTIONS);
var rows = report.rows();
var negativeKeywords = {};
var allAdGroupIds = {};
// Iterate through search query and add them to negative array
// Stripped out positive. Logging Query and KeywordText
while (rows.hasNext()) {
var row = rows.next();
Logger.log('The Query %s matched The - %s - SKAG', row['Query'], row['KeywordTextMatchingQuery']);
addToMultiMap(negativeKeywords, row['AdGroupId'], row['Query']);
allAdGroupIds[row['AdGroupId']] = true;
}
// Copy all the adGroupIds from the object into an array.
var adGroupIdList = [];
for (var adGroupId in allAdGroupIds) {
adGroupIdList.push(adGroupId);
}
// Add the keywords as negative or positive to the applicable ad groups.
var adGroups = AdWordsApp.adGroups().withIds(adGroupIdList).get();
while (adGroups.hasNext()) {
var adGroup = adGroups.next();
if (negativeKeywords[adGroup.getId()]) {
for (var i = 0; i < negativeKeywords[adGroup.getId()].length; i++) {
adGroup.createNegativeKeyword(
'[' + negativeKeywords[adGroup.getId()][i] + ']');
}
}
}
}
function addToMultiMap(map, key, value) {
if (!map[key]) {
map[key] = [];
}
map[key].push(value);
}
I hope you find it useful, especially if you’re a bit of a control freak like me.