/** * @name Detect Duplicate Keywords * * @overview Identify duplicate keywords in the AdWords account, including close variants. * * Please send in your comment, feedback, criticism or occasional Thank You * to support@karooya.com * * @author Karooya Team [support@karooya.com] * * All AdWords scripts published by Karooya are available here * https://www.karooya.com/blog/category/karooya-adwords-script/ * * Copyright (c) 2018 - Karooya Technologies Pvt. Ltd. All Rights Reserved. * https://www.karooya.com * * The script was last modified on: 22 October, 2018 * *Get the demonstrartion HERE - https://www.wao.co.il/google-ads-script-duplicate-keywords * */ // URL of the sheet to which the data is to be copied var SPREADSHEET_URL = 'https://docs.google.com/YOUR-SPREADSHEET-URL-HERE'; // Specify a date range for the report var DATE_RANGE = "LAST_90_DAYS"; // Other allowed values are: LAST__DAYS (Ex: LAST_90_DAYS) or TODAY, YESTERDAY, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_WEEK, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH // Or specify a custom date range: // Format is yyyy-mm-dd var USE_CUSTOM_DATE_RANGE = false; var START_DATE = "2016-02-01"; // Example "2016-02-01" var END_DATE = "2016-03-01"; // Example "2016-02-29" // Set this to true to only look at currently active campaigns. // Set to false to include campaigns that had impressions but are currently paused. var IGNORE_PAUSED_CAMPAIGNS = true; // Set this to true to only look at currently active ad groups. // Set to false to include ad groups that had impressions but are currently paused. var IGNORE_PAUSED_ADGROUPS = true; var IGNORE_PAUSED_KEYWORDS = true; /* * * Please update the language setting ACCOUNT_LANGUAGE_CODE below if * account's primary language is other than English. No need to change anything for English. * The language codes are as follows. * * * ar: Arabic * bg: Bulgarian * cz: Czech * da: Danish * nl: Dutch * en: English * fi: Finnish * fr: French * de: German * hu: Hungarian * no: Norwegian * pt: Portuguese * ro: Romanian * ru: Russian * es: Spanish * sv: Swedish * tr: Turkish * */ var ACCOUNT_LANGUAGE_CODE = 'en'; // Laguage of the account. Default is English. // The script is expected to work with following API version var API_VERSION = { apiVersion: 'v201806' } /*-- More filter for MCC account --*/ //Is your account a MCC account var IS_MCC_ACCOUNT = false; var FILTER_ACCOUNTS_BY_LABEL = false; var ACCOUNT_LABEL_TO_SELECT = "INSERT_LABEL_NAME_HERE"; /*---------------------------------*/ ////////////////////////////////////////////////////////////////////////////// function main() { var spreadsheet = getSpreadsheet(SPREADSHEET_URL); if (spreadsheet == null) { return; } if (!IS_MCC_ACCOUNT) { processCurrentAccount(spreadsheet); } else { var childAccounts = getManagedAccounts(); while(childAccounts .hasNext()) { var childAccount = childAccounts .next() MccApp.select(childAccount); processCurrentAccount(spreadsheet); } } trackEventInAnalytics(); Logger.log("Done!"); } function getManagedAccounts() { var accountSelector = MccApp.accounts(); if (FILTER_ACCOUNTS_BY_LABEL) { accountSelector = accountSelector.withCondition("LabelNames CONTAINS '" + ACCOUNT_LABEL_TO_SELECT + "'") } return accountSelector.get(); } function processCurrentAccount(spreadsheet) { var accountName = AdWordsApp.currentAccount().getName(); Logger.log("Accesing AdWord account: " + accountName); var campaignIDs = getSearchOnlyCampaignIDs(); if (campaignIDs.length > 0) { Logger.log("Found " + campaignIDs.length + " SearchOnly campaigns"); } else { Logger.log("Did not find any SearchOnly campaigns! Skiping account."); return; } Logger.log("Fetching keyword performance data from AdWords.."); var keywordReport = getKeywordReport(campaignIDs); Logger.log("Computing.."); var duplicateKeywordGroupsArr = compute(keywordReport); Logger.log("Exporting results to spreadsheet.."); // Export keyword level stats to a spreadsheet var newSheetName = accountName; var sheet = spreadsheet.getSheetByName(newSheetName); if (sheet != null) { sheet.clear(); } else { sheet = spreadsheet.insertSheet(newSheetName, 0); } exportToSpreadsheet(duplicateKeywordGroupsArr, sheet, accountName); } function getSearchOnlyCampaignIDs() { var campaignIDs = new Array(); var campaignSelector = AdWordsApp.campaigns().withCondition("TargetContentNetwork = FALSE"); if (IGNORE_PAUSED_CAMPAIGNS) { campaignSelector = campaignSelector.withCondition("Status = ENABLED"); } else { campaignSelector = campaignSelector.withCondition("Status IN ['ENABLED','PAUSED']"); } var campaignIterator = campaignSelector.get(); while (campaignIterator.hasNext()) { var campaign = campaignIterator.next(); campaignIDs.push(campaign.getId()); } return campaignIDs; } function compute(keywordReport) { var reportIterator = keywordReport.rows(); var keywordArray = new Array(); while (reportIterator.hasNext()) { var kw = reportIterator.next(); keywordArray.push(kw); } Logger.log("Total keywords found: " + keywordArray.length); var singleWordTerms = getUniqueTerms(keywordArray); Logger.log("Found: " + singleWordTerms.length + " single word terms."); var termToStemmedWordMap = fetchStemmedWords(singleWordTerms); Logger.log("Grouping keywords.."); var dupeKwGroupsArr = findDuplicateKeywords(termToStemmedWordMap, keywordArray); if (dupeKwGroupsArr.length > 0) { Logger.log("Found " + dupeKwGroupsArr.length + " duplicate keyword groups."); } else { Logger.log("There are no duplicate keywords in the current account."); } return dupeKwGroupsArr; } function findDuplicateKeywords(termToStemmedWordMap, keywordArray) { var uniqueKwGroupsMap = {}; for (var i=0; i 1) { var totalImpressions = 0; for(var kwIdx=0; kwIdx val2) retVal = 1; else retVal = 0; if (reverse) { retVal = -1 * retVal; } return retVal; } } /* ******************************************* */ /* * Tracks the execution of the script as an event in Google Analytics. * Sends the script name and a random UUID (it is just a random number, required by Analytics). * The event information just tells us that somewhere someone ran this script. * Credit for the idea goes to Russel Savage, who posted his version at http://www.freeadwordsscripts.com/2013/11/track-adwords-script-runs-with-google.html. * and to Martin Roettgerding, as we learnt the trick from his script at http://www.ppc-epiphany.com/2016/03/11/introducing-the-quality-score-tracker-v3-0 */ function trackEventInAnalytics() { // Create the random UUID from 30 random hex numbers gets them into the format xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (with y being 8, 9, a, or b). var uuid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {var r = Math.random()*16|0,v=c=='x'?r:r&0x3|0x8;return v.toString(16);}); var url = "http://www.google-analytics.com/collect?v=1&t=event&tid=UA-46662882-1&cid=" + uuid + "&ec=AdWords%20Scripts&ea=Script%20Execution&el=Detect%20Duplicate%20Keywords"; UrlFetchApp.fetch(url); }