Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Script to aggregate your broad match keywords
/**
*
* Broad-match keyword aggregator script
* This script will group equivalent broad match keywords and label based on performence
*
* Version: 1.1
* Updated 2016-10-11: replaced 'ConvertedClicks' with 'Conversions'
* Google AdWords Script maintained by brainlabsdigital.com
*
**/
function main(){
var ACCOUNT_WIDE = false;
//Defines whether the script looks at campaign-level or account-level broad match duplicate keywords
var METRIC = "AverageCpc";
//Select the metric which will determine which duplicate keyword will be kept, choose from "Ctr", "QualityScore", "Impressions", "Conversions", "AverageCpc"
var CAMPAIGN_INCLUDE_FILTER = []; // e.g var CAMPAIGN_INCLUDE_FILTER = ["hey", "jude"];
//Campaign filter which will include any campaign with any of the included strings in the campaign name. Case insensitive matching
var CAMPAIGN_EXCLUDE_FILTER = []; // e.g var CAMPAIGN_EXCLUDE_FILTER = ["hey", "jude"];
//Campaign filter which will exclude any campaign with any of the included strings in the campaign name. Case insensitive matching
var DATE_RANGE = "LAST_30_DAYS";
//Choose one from TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH
var KEEP_LABEL = "DuplicateBroadKeyword_Enable";
//Label one keyword from each duplicate group
var PAUSE_LABEL = "DuplicateBroadKeyword_Pause";
//Label all keywords which don't have the best statistic from selected
labelDuplicates(ACCOUNT_WIDE, CAMPAIGN_INCLUDE_FILTER, CAMPAIGN_EXCLUDE_FILTER, DATE_RANGE, METRIC, KEEP_LABEL, PAUSE_LABEL);
}
function labelDuplicates(ACCOUNT_WIDE, CAMPAIGN_INCLUDE_FILTER, CAMPAIGN_EXCLUDE_FILTER, DATE_RANGE, METRIC, KEEP_LABEL, PAUSE_LABEL) {
//Create labels
AdWordsApp.createLabel(KEEP_LABEL);
AdWordsApp.createLabel(PAUSE_LABEL);
//Metric data-validation
var allowedMetrics = ["Ctr", "QualityScore", "Impressions", "Conversions", "AverageCpc"];
var allowedMetrics_lowerCase = allowedMetrics.map(function (str){return str.toLowerCase()});
var metricIndex = allowedMetrics_lowerCase.indexOf(METRIC.toLowerCase());
if(metricIndex === -1){
var error = "Metric '" + METRIC + "' not recognised, please set to one from '" + allowedMetrics.join("', '") + "'.";
Logger.log(error);
throw error;
return;
}
var METRIC = allowedMetrics[metricIndex];
//Generate list of included campaigns
var includeCampaignIds = [];
var campaignIncludes = CAMPAIGN_INCLUDE_FILTER.map(function (str){return str.toLowerCase()});
var campaignIterator = AdWordsApp.campaigns()
.withCondition("CampaignStatus = ENABLED")
.get();
while(campaignIterator.hasNext()){
var campaign = campaignIterator.next();
var campaignId = campaign.getId();
var campaignName = campaign.getName();
var campaignNameLower = campaignName.toLowerCase();
var flag = false;
for(var i = 0; i < campaignIncludes.length; i++){
if(campaignNameLower.indexOf(campaignIncludes[i]) !== -1){
flag = true;
break;
}
}
if(flag){
includeCampaignIds.push(campaignId);
}
}
//Construct AWQL report query
var selectQuery = 'SELECT CampaignName, CampaignId, Id, AdGroupId, Criteria, ' + METRIC + ' ';
var fromQuery = 'FROM KEYWORDS_PERFORMANCE_REPORT ';
var whereQuery = "WHERE KeywordMatchType = BROAD AND AdNetworkType1 = SEARCH ";
if(includeCampaignIds.length > 0){
whereQuery += "AND CampaignId IN [" + includeCampaignIds.join(",") + "] ";
}
for(var i = 0; i < CAMPAIGN_EXCLUDE_FILTER.length; i++){
whereQuery += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + CAMPAIGN_EXCLUDE_FILTER[i] + "' ";
}
var duringQuery = "DURING " + DATE_RANGE;
var query = selectQuery
+ fromQuery
+ whereQuery
+ duringQuery;
//Generate report
var report = AdWordsApp.report(query);
//Poll report rows
var campaignKeywords = {};
var rows = report.rows();
while(rows.hasNext()){
var row = rows.next();
var keywordId = row['Id'];
var adGroupId = row['AdGroupId'];
var campaignId = row['CampaignId'];
var keywordText = row['Criteria'].toLowerCase();
var metricStat = parseFloat(row[METRIC].replace(/,/g, ""));
if(METRIC.toLowerCase() === "AverageCpc".toLowerCase()){
if(metricStat > 0){
metricStat = 1 / metricStat;
}
}
var stats = {metric: metricStat};
if(ACCOUNT_WIDE) campaignId = 1;
if(typeof(campaignKeywords[campaignId]) === "undefined"){
campaignKeywords[campaignId] = [];
}
campaignKeywords[campaignId].push(parseKeyword(keywordId,adGroupId,keywordText,stats));
}
//Establish duplicate keyword groups
if (ACCOUNT_WIDE === true){
var keywordGroups = {};
}
for(var campaignId in campaignKeywords){
if (ACCOUNT_WIDE === false) {
var keywordGroups = {};
}
var campaignKeywordsList = campaignKeywords[campaignId];
var keywordArray = [];
for(var keyword in campaignKeywordsList){
keywordArray.push(campaignKeywordsList[keyword]["Text"]);
}
for(var keyword in campaignKeywordsList){
var keywordText = campaignKeywordsList[keyword]["Text"];
var firstIndex = keywordArray.indexOf(keywordText);
var lastIndex = keywordArray.lastIndexOf(keywordText);
//push the dupes into dupe groups
if(firstIndex !== lastIndex){
if(typeof(keywordGroups[keywordText]) === "undefined") {
keywordGroups[keywordText]=[];
}
keywordGroups[keywordText].push(campaignKeywordsList[keyword]);
}
}
if (ACCOUNT_WIDE === true) {
continue;
}
labelKeywords(keywordGroups, KEEP_LABEL, PAUSE_LABEL);
}
if (ACCOUNT_WIDE === true) {
labelKeywords(keywordGroups, KEEP_LABEL, PAUSE_LABEL);
}
}
function parseKeyword(keywordId,adGroupId,keywordText, stats){
var keyword = {};
keyword["KeywordId"] = keywordId;
keyword["AdGroupId"] = adGroupId;
keyword["Id"] = [adGroupId, keywordId];
keyword["Text"] = orderKeyword(keywordText);
keyword["Stats"] = stats;
return keyword;
}
function orderKeyword(keywordText){
//Splitting the words
var keywordTextArray = keywordText.trim().split(" ");
//Sort keyword components
var sortedKeywordComponents = keywordTextArray.sort();
//Turn sorted strings into one word
var sortedKeyword = sortedKeywordComponents.join(" ");
return sortedKeyword;
}
function labelKeywords(keywordGroups, KEEP_LABEL, PAUSE_LABEL) {
for (var keywordText in keywordGroups) {
//cycle through each group to pick best of the bunch
var maxMetric = -1;
var bestKeyword = [];
for (var keyword in keywordGroups[keywordText]) {
if (parseFloat(keywordGroups[keywordText][keyword]["Stats"]["metric"]) > maxMetric) {
maxMetric = keywordGroups[keywordText][keyword]["Stats"]["metric"];
bestKeyword[0] = keywordGroups[keywordText][keyword];
}
}
var indexOfBest = keywordGroups[keywordText].indexOf(bestKeyword[0]);
keywordGroups[keywordText].splice(indexOfBest, 1);
//label all groups with pause/unpause labels
var keywordIterator = AdWordsApp.keywords().withIds([bestKeyword[0]["Id"]]).get();
keywordIterator.next().applyLabel(KEEP_LABEL);
var keywordIdArray = [];
for (keyword in keywordGroups[keywordText]) {
keywordIdArray.push(keywordGroups[keywordText][keyword]["Id"]);
}
var keywordIterator = AdWordsApp.keywords().withIds(keywordIdArray).get();
while (keywordIterator.hasNext()){
var keyword = keywordIterator.next();
keyword.applyLabel(PAUSE_LABEL);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.