+var currentSetting = new Object();
+ currentSetting.enabledCampaignsOnly = 1;
+ currentSetting.enabledAdGroupsOnly = 1;
+ currentSetting.calculation1 = "?Ctr * ?ConversionRate";
+ currentSetting.keepHighestOrLowest = "highest";
+ currentSetting.sortBy = "Ctr"; //"clickShare, "impressionShare", "conversionShare", "convertedClicksShare";
+ currentSetting.showAllAds = 1;
+ currentSetting.devicePreference = "notMobilePreferred"; //"all", "mobilePreferred"
+ currentSetting.numAdsToKeep = 3;
+ currentSetting.optimizationMethod = "add n"; //"at least n", "add n";
+ currentSetting.time = "LAST_30_DAYS";
+ currentSetting.email = "example@example.com";
+ accountManagers = "example@example.com,example@example.com";
+ var campaignNameContains = "";
+ var campaignLabel = "";
+ currentSetting.adImpressionThreshold = 100;
+ currentSetting.showAdLabels = 0;
+ currentSetting.showAdGroupLabels = 0;
+ if(!currentSetting.keepHighestOrLowest) currentSetting.keepHighestOrLowest = "Highest";
+ if(!currentSetting.sortBy) currentSetting.sortBy = "Ctr";
+ currentSetting.sortBy = currentSetting.sortBy.replace(/ /g, '');
+ if(DEBUG==1) Logger.log("currentSetting.sortBy: " + currentSetting.sortBy);
+ if(!campaignNameContains) var campaignNameContains = "";
+ if(!campaignLabel) var campaignLabel = "";
+ if(!adLabel) var adLabel = "";
+ if(!currentSetting.pausedOrDeleted) currentSetting.pausedOrDeleted = "Pause";
+ if(!currentSetting.adImpressionThreshold) currentSetting.adImpressionThreshold = 0;
+ if(currentSetting.pausedOrDeleted == "Pause") {
+ currentSetting.pausedOrDeleted = "Paused";
+ currentSetting.pausedOrDeleted = "Removed";
+ if(currentSetting.optimizationMethod.toLowerCase().indexOf("add n") != -1) {
+ currentSetting.optMode = "addN";
+ } else if(currentSetting.optimizationMethod.toLowerCase().indexOf("at least n") != -1) {
+ currentSetting.optMode = "expandToN";
+ if(DEBUG==1) Logger.log("currentSetting.showAllAds: " + currentSetting.showAllAds);
+ var accountName = AdWordsApp.currentAccount().getName();
+ currentSetting.matchedCampaigns = getMatchingCampaignIds(campaignLabel);
+ if(adLabel) currentSetting.matchedAds = getMatchingAdIds(adLabel, campaignLabel);
+ var campaignNameCaseSensitive = 0;
+ var campaignNameExactMatch = 0;
+ if(campaignNameContains) currentSetting.campaignNameSelector = getCampaignsByName(campaignNameContains, campaignNameCaseSensitive, campaignNameExactMatch);
+ // --- SET UP SPREADSHEET -----
+ // Construct report name
+ var reportDate = new Date();
+ spreadsheetName = "Optmyzr Bulk Ads" + " - " + accountName;
+ var spreadsheet = SpreadsheetApp.create(spreadsheetName);
+ var spreadsheetUrl = spreadsheet.getUrl();
+ var allSheets = spreadsheet.getSheets();
+ for(var i=1,len=allSheets.length;i<len;i++){
+ spreadsheet.deleteSheet(allSheets[i]);
+ allSheets[0].setName(currentSetting.sheetName);
+ var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
+ var isSpreadsheetNew = 1;
+ // ADD ACCOUNT MANAGERS
+ if(accountManagers && accountManagers!=""){
+ var accountManagersArray = accountManagers.replace(/\s/g, "").split(",");
+ spreadsheet.addEditors(accountManagersArray);
+ currentSetting.analysisSheet = spreadsheet.getSheetByName(currentSetting.sheetName);
+ currentSetting.analysisSheet.appendRow(["Action", "Campaign", "Ad Group", "Ad Type", "Ad", "Headline Length", "Description Line 1", "Line 1 Length", "Description Line 2", "Line 2 Length", "Display URL", "Display URL Length", "Final URL", "Mobile Final URL", "Tracking Template", "Custom Parameter", "Clicks", "ClickShare", "Impressions", "ImpressionShare", "Ctr", "AveragePosition", "Cost", "AverageCpc", "ConversionValue", "ROAS", "Conversions", "ConversionShare", "ConversionRate", "CostPerConversion", "ConvertedClicks", "ConvertedClickShare", "clickConversionRate", "CostPerConvertedClick", "calculation1", "devicePreference", "adLabel", "adGroupLabel", "adId", "adGroupId"]);
+ currentSetting.analysisSheet.setFrozenRows(1);
+ buildAccountMap(campaignLabel, adLabel, campaignNameContains, currentSetting.time);
+ addCalculatedMetrics();
+ addCharacterCountColumns();
+ currentSetting.finished = 1;
+ addStyleToSheet(currentSetting.analysisSheet);
+ var notes = "Bulk Ad Sheet: <a href=\""+spreadsheetUrl+"\" target=\"_blank\">link</a>";
+ if(currentSetting.email) {
+ var subject = "New Ad Bulksheet Is Ready";
+ var body = "Your spreadsheet to bulk create new ads is ready: <a href=\""+spreadsheet.getUrl()+"\" target=\"_blank\">link</a><br/><br/>" + "Manage the Optmyzr Enhanced Script that created this report: https://tools.optmyzr.com/enhancedscript_settings/scriptSettings/61";
+ sendEmailNotifications(currentSetting.email, subject, body);
+ function getMatchingAdIds(label, campaignLabel)
+ var matchedAds = new Object();
+ matchedAds.ids = new Array();
+ var adIterator = AdWordsApp.ads()
+ .withCondition("LabelNames CONTAINS_ANY ['" + label + "']")
+ var adIterator = AdWordsApp.ads()
+ .withCondition("LabelNames CONTAINS_ANY ['" + label + "']")
+ .withCondition(matchedCampaigns.selectorStringForScripts)
+ while(adIterator.hasNext()) {
+ var ad = adIterator.next();
+ matchedAds.ids.push(adId);
+ matchedAds.selectorStringForReports = "AdId IN [" + matchedAds.ids + "]";
+ matchedAds.selectorStringForAdPerformanceReports = "Id IN [" + matchedAds.ids + "]";
+function buildAccountMap(campaignLabel, adLabel, campaignNameContains, time) {
+ currentSetting.accountMap = new Object();
+ currentSetting.accountMap.campaigns = new Array();
+ var cols = ['Headline',
+ 'CreativeFinalMobileUrls',
+ 'CreativeTrackingUrlTemplate',
+ 'CreativeUrlCustomParameters',
+ if(currentSetting.devicePreference.toLowerCase() == "mobile preferred") {
+ var query = ['SELECT ',
+ 'FROM AD_PERFORMANCE_REPORT',
+ 'WHERE DevicePreference = 30001',
+ 'AND Status = ENABLED'];
+ } else if (currentSetting.devicePreference.toLowerCase() == "not mobile preferred") {
+ var query = ['SELECT ',
+ 'FROM AD_PERFORMANCE_REPORT',
+ 'WHERE DevicePreference != 30001',
+ 'AND Status = ENABLED'];
+ var query = ['SELECT ',
+ 'FROM AD_PERFORMANCE_REPORT',
+ 'WHERE Status = ENABLED'];
+ if(campaignLabel) query.push('AND', currentSetting.matchedCampaigns.selectorStringForReports);
+ if(adLabel) query.push('AND', currentSetting.matchedAds.selectorStringForAdPerformanceReports);
+ if(campaignNameContains) query.push('AND', currentSetting.campaignNameSelector.forReports);
+ if(currentSetting.enabledCampaignsOnly) query.push('AND', 'CampaignStatus = ENABLED');
+ if(currentSetting.enabledAdGroupsOnly) query.push('AND', 'AdGroupStatus = ENABLED');
+ query.push('DURING', time);
+ var query = query.join(' ');
+ if(DEBUG == 1) Logger.log("query: " + query);
+ var report = AdWordsApp.report(query);
+ var rows = report.rows();
+ while(rows.hasNext()) {
+ currentSetting[key] = value;
+ if(currentSetting.DevicePreference == "--") {
+ currentSetting.DevicePreference = "All";
+ currentSetting.DevicePreference = "Mobile";
+ if(!currentSetting.accountMap.campaigns[currentSetting.CampaignName]) {
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName] = new Object();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups = new Array();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName] = new Object();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads = new Array();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].numTextAds = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].numMobilePreferredTextAds = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].numNonMobilePreferredTextAds = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].sortedAdList = new Array();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id] = new Object();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].adGroupId = currentSetting.AdGroupId;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].Impressions = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].Clicks = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].Conversions = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ConvertedClicks = 0;
+ } else if (!currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName]) {
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName] = new Object();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads = new Array();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].numTextAds = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].numMobilePreferredTextAds = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].numNonMobilePreferredTextAds = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].sortedAdList = new Array();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id] = new Object();
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].adGroupId = currentSetting.AdGroupId;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].Impressions = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].Clicks = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].Conversions = 0;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ConvertedClicks = 0;
+ } else if (!currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id]) {
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id] = new Object();
+ //Logger.log(currentSetting.AdType);
+ if(currentSetting.AdType == "Text ad") {
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].numTextAds++;
+ if(currentSetting.DevicePreference == "mobile preferred") {
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].numMobilePreferredTextAds++;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].numNonMobilePreferredTextAds++;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].headline = currentSetting.Headline;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].description1 = currentSetting.Description1;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].description2 = currentSetting.Description2;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].displayUrl = currentSetting.DisplayUrl;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].finalUrls = currentSetting.CreativeFinalUrls;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].finalMobileUrls = currentSetting.CreativeFinalMobileUrls;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].creativeTrackingUrlTemplate = currentSetting.CreativeTrackingUrlTemplate;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].creativeUrlCustomParameters = currentSetting.CreativeUrlCustomParameters;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].Clicks = parseInt(currentSetting.Clicks);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].Impressions = parseInt(currentSetting.Impressions);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].Cost = getFloat(currentSetting.Cost);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].Conversions = getFloat(currentSetting.Conversions);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].ConvertedClicks = parseInt(currentSetting.ConvertedClicks);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].ConversionValue = getFloat(currentSetting.ConversionValue);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].ConversionRate = getFloat(currentSetting.ConversionRate);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].ClickConversionRate = getFloat(currentSetting.ClickConversionRate);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].AverageCpc = getFloat(currentSetting.AverageCpc);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].Ctr = getFloat(currentSetting.Ctr);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].AveragePosition = getFloat(currentSetting.AveragePosition);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].devicePreference = currentSetting.DevicePreference;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].adType = currentSetting.AdType;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].CostPerConversion = currentSetting.Cost / currentSetting.Conversions;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].CostPerConvertedClick = currentSetting.Cost / currentSetting.convertedClicks;
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].adId = currentSetting.Id;
+ //Logger.log("ROAS: " + currentSetting.ConversionValue / currentSetting.Cost);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].ROAS = getFloat(currentSetting.ConversionValue / currentSetting.Cost);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].Impressions += parseInt(currentSetting.Impressions);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].Clicks += parseInt(currentSetting.Clicks);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].Conversions += getFloat(currentSetting.Conversions);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ConvertedClicks += parseInt(currentSetting.ConvertedClicks);;
+ if(currentSetting.calculation1){
+ var calculation = currentSetting.calculation1;
+ if(DEBUG==1) Logger.log("calculation: " + calculation);
+ var fullCalculation = calculation.replace(/\?/g, "currentSetting.");
+ if(DEBUG==1) Logger.log("fullCalculation: " + fullCalculation);
+ var output = eval(fullCalculation);
+ //var output = currentSetting.AveragePosition * currentSetting.AverageCpc * (1/currentSetting.Ctr) * (1/currentSetting.ConversionRate);
+ currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].calculation1 = output;
+ //Logger.log("Clicks: " + currentSetting.accountMap.campaigns[currentSetting.CampaignName].adGroups[currentSetting.AdGroupName].ads[currentSetting.Id].clicks);
+} // END buildAccountMap
+function writeToSheet(reportDates) {
+ //var label = currentSetting.label;
+ for(var campaignName in currentSetting.accountMap.campaigns) {
+ for (var adGroupName in currentSetting.accountMap.campaigns[campaignName].adGroups) {
+ var adGroupId = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].adGroupId;
+ if(currentSetting.showAdGroupLabels) var adGroupLabel = getLabelsForAdGroup(adGroupId);
+ var numTextAdsInAdGroup = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].numTextAds;
+ if(DEBUG==1) Logger.log("");
+ if(DEBUG==1) Logger.log(campaignName + " > " + adGroupName + " contains " + numTextAdsInAdGroup + " text ads (" + currentSetting.devicePreference + ")");
+ if(DEBUG==1) Logger.log("-----------------------");
+ if(currentSetting.optMode == "addN" || currentSetting.optMode == "expandToN") {
+ if(currentSetting.optMode == "expandToN") {
+ var numAdsToAdd = currentSetting.numAdsToKeep - numTextAdsInAdGroup;
+ } else if(currentSetting.optMode == "addN") {
+ var numAdsToAdd = currentSetting.numAdsToKeep;
+ if(currentSetting.optMode == "expandToN") {
+ var numAdsToAdd = currentSetting.numAdsToKeep - numTextAdsInAdGroup;
+ } else if(currentSetting.optMode == "addN") {
+ var numAdsToAdd = currentSetting.numAdsToKeep;
+ if(DEBUG==1) Logger.log("numAdsToAdd: " + numAdsToAdd);
+ var recommendedStatus = "add";
+ for(var counter = 0; counter < currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList.length; counter++) {
+ if(currentSetting.keepHighestOrLowest.toLowerCase().indexOf("lowest") != -1) {
+ var length = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList.length - 1 - counter;
+ var adId = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList[length];
+ var adId = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList[counter];
+ if(currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads[adId].Impressions >= currentSetting.adImpressionThreshold) {
+ // didn't find ad with enough data, so take the best alternative
+ if(counter == currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList.length + 1) {
+ if(currentSetting.keepHighestOrLowest.toLowerCase().indexOf("lowest") != -1) {
+ var length = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList.length - 1;
+ var adId = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList[length];
+ var adId = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList[0];
+ var thisAd = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads[adId];
+ var headline = thisAd.headline;
+ var description1 = thisAd.description1;
+ var description2 = thisAd.description2;
+ var finalUrls = thisAd.finalUrls;
+ var finalMobileUrls = thisAd.finalMobileUrls;
+ var displayUrl = thisAd.displayUrl;
+ var clicks = thisAd.Clicks;
+ var impressions = thisAd.Impressions;
+ var cost = thisAd.Cost;
+ var clicks = thisAd.Clicks;
+ var conversions = thisAd.Conversions;
+ var convertedClicks = thisAd.ConvertedClicks;
+ var clickConversionRate = thisAd.ClickConversionRate/100;
+ var conversionValue = thisAd.ConversionValue;
+ var ctr = thisAd.Ctr/100;
+ var conversionRate = thisAd.ConversionRate/100;
+ var costPerConversion = thisAd.CostPerConversion;
+ var costPerConvertedClick = thisAd.CostPerConvertedClick;
+ var calculation1 = thisAd.calculation1;
+ var clickShare = thisAd.ClickShare;
+ var impressionShare = thisAd.ImpressionShare;
+ var conversionShare = thisAd.ConversionShare;
+ var convertedClicksShare = thisAd.ConvertedClicksShare;
+ var averageCpc = thisAd.AverageCpc;
+ var averagePosition = thisAd.AveragePosition;
+ var conversionValue = thisAd.ConversionValue;
+ var roas = thisAd.ROAS;
+ var devicePreference = thisAd.devicePreference;
+ var adType = thisAd.adType;
+ var creativeTrackingUrlTemplate = thisAd.creativeTrackingUrlTemplate;
+ var creativeUrlCustomParameters = thisAd.creativeUrlCustomParameters;
+ if(!creativeTrackingUrlTemplate) creativeTrackingUrlTemplate = "";
+ if(!creativeUrlCustomParameters) creativeUrlCustomParameters = "";
+ if(DEBUG==1) Logger.log("Best ad's headline: " + headline + " > custom metric: " + calculation1);
+ if(currentSetting.showAdLabels) var adLabel = getLabelsForAd(adGroupId,adId);
+ for(var adsToAddCounter = 0; adsToAddCounter < numAdsToAdd; adsToAddCounter++) {
+ if(adType.toLowerCase().indexOf("text ad") != -1) {
+ currentSetting.analysisSheet.appendRow([recommendedStatus, campaignName, adGroupName, adType, headline, "", description1, "", description2, "", displayUrl, "", finalUrls, finalMobileUrls, creativeTrackingUrlTemplate, creativeUrlCustomParameters, clicks, clickShare, impressions, impressionShare, ctr, averagePosition, cost, averageCpc, conversionValue, roas, conversions, conversionShare, conversionRate, costPerConversion, convertedClicks, convertedClicksShare, clickConversionRate, costPerConvertedClick, calculation1, devicePreference, adLabel, adGroupLabel, adId, adGroupId, ""]);
+ if(currentSetting.showAllAds) {
+ var recommendedStatus = "set";
+ for(var adCounter = 0 ; adCounter < currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList.length ; adCounter++) {
+ var adId = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList[adCounter];
+ var thisAd = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads[adId];
+ var headline = thisAd.headline;
+ var description1 = thisAd.description1;
+ var description2 = thisAd.description2;
+ var finalUrls = thisAd.finalUrls;
+ var finalMobileUrls = thisAd.finalMobileUrls;
+ var displayUrl = thisAd.displayUrl;
+ var clicks = thisAd.Clicks;
+ var impressions = thisAd.Impressions;
+ var cost = thisAd.Cost;
+ var clicks = thisAd.Clicks;
+ var conversions = thisAd.Conversions;
+ var convertedClicks = thisAd.ConvertedClicks;
+ var clickConversionRate = thisAd.ClickConversionRate/100;
+ var conversionValue = thisAd.ConversionValue;
+ var ctr = thisAd.Ctr/100;
+ var conversionRate = thisAd.ConversionRate/100;
+ var costPerConversion = thisAd.CostPerConversion;
+ var costPerConvertedClick = thisAd.CostPerConvertedClick;
+ var calculation1 = thisAd.calculation1;
+ var clickShare = thisAd.ClickShare;
+ var impressionShare = thisAd.ImpressionShare;
+ var conversionShare = thisAd.ConversionShare;
+ var convertedClicksShare = thisAd.ConvertedClicksShare;
+ var averageCpc = thisAd.AverageCpc;
+ var averagePosition = thisAd.AveragePosition;
+ var conversionValue = thisAd.ConversionValue;
+ var roas = thisAd.ROAS;
+ var devicePreference = thisAd.devicePreference;
+ var adType = thisAd.adType;
+ var creativeTrackingUrlTemplate = thisAd.creativeTrackingUrlTemplate;
+ var creativeUrlCustomParameters = thisAd.creativeUrlCustomParameters;
+ if(!creativeTrackingUrlTemplate) creativeTrackingUrlTemplate = "";
+ if(!creativeUrlCustomParameters) creativeUrlCustomParameters = "";
+ if(currentSetting.showAdLabels) var adLabel = getLabelsForAd(adGroupId,adId);
+ if(adType.toLowerCase().indexOf("text ad") != -1) {
+ currentSetting.analysisSheet.appendRow([recommendedStatus, campaignName, adGroupName, adType, headline, "", description1, "", description2, "", displayUrl, "", finalUrls, finalMobileUrls, creativeTrackingUrlTemplate, creativeUrlCustomParameters, clicks, clickShare, impressions, impressionShare, ctr, averagePosition, cost, averageCpc, conversionValue, roas, conversions, conversionShare, conversionRate, costPerConversion, convertedClicks, convertedClicksShare, clickConversionRate, costPerConvertedClick, calculation1, devicePreference, adLabel, adGroupLabel, adId, adGroupId, ""]);
+ } // END if(currentSetting.showAllAds == 1)
+ } // END if(numTextAdsInAdGroup < currentSetting.numAdsToKeep)
+ } // END else if(currentSetting.optimizationMethod.toLowerCase().indexOf("too few experiments") != -1)
+ Logger.log("No valid optimization type was selected");
+ } // END for (var adGroupName in currentSetting.accountMap.campaigns[campaignName].adGroups)
+ } // END for(var campaignName in currentSetting.accountMap.campaigns)
+} // END function writeToSheet()
+function addStyleToSheet(dataSheet) {
+ var numRows = dataSheet.getDataRange().getNumRows();
+ var numColumns = dataSheet.getDataRange().getNumColumns();
+ //clicks, clickShare, impressions, impressionShare, ctr, averagePosition, cost, averageCpc, conversionValue, roas, conversions, conversionShare, conversionRate, costPerConversion, convertedClicks, convertedClicksShare, clickConversionRate, costPerConvertedClick, calculation1
+ dataSheet.getRange(2, 6, numRows, 1).setNumberFormat("#,##0"); // len
+ dataSheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0"); // len
+ dataSheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0"); // len
+ dataSheet.getRange(2, 12, numRows, 1).setNumberFormat("#,##0"); // len
+ dataSheet.getRange(2, 17, numRows, 1).setNumberFormat("#,##0"); //clicks
+ dataSheet.getRange(2, 18, numRows, 1).setNumberFormat("0.00%"); //clickShare
+ dataSheet.getRange(2, 19, numRows, 1).setNumberFormat("#,##0"); // imp
+ dataSheet.getRange(2, 20, numRows, 1).setNumberFormat("0.00%"); //Imp Share
+ dataSheet.getRange(2, 21, numRows, 1).setNumberFormat("0.00%"); // ctr
+ dataSheet.getRange(2, 22, numRows, 1).setNumberFormat("#,##0.00"); //avgPos
+ dataSheet.getRange(2, 23, numRows, 1).setNumberFormat("#,##0.00"); // cost
+ dataSheet.getRange(2, 24, numRows, 1).setNumberFormat("#,##0.00"); // avgCpc
+ dataSheet.getRange(2, 25, numRows, 1).setNumberFormat("#,##0.00"); // conv val
+ dataSheet.getRange(2, 26, numRows, 1).setNumberFormat("#,##0.00"); // roas
+ dataSheet.getRange(2, 27, numRows, 1).setNumberFormat("#,##0.00"); // conversions
+ dataSheet.getRange(2, 28, numRows, 1).setNumberFormat("0.00%"); // conv share
+ dataSheet.getRange(2, 29, numRows, 1).setNumberFormat("0.00%"); // conversion rate
+ dataSheet.getRange(2, 30, numRows, 1).setNumberFormat("#,##0.00"); // cost / conv
+ dataSheet.getRange(2, 31, numRows, 1).setNumberFormat("#,##0.00"); // converted clicks
+ dataSheet.getRange(2, 32, numRows, 1).setNumberFormat("0.00%"); // conv click share
+ dataSheet.getRange(2, 33, numRows, 1).setNumberFormat("0.00%"); // converted click rate
+ dataSheet.getRange(2, 34, numRows, 1).setNumberFormat("#,##0.00"); // cost / conv. click
+ dataSheet.getRange(2, 35, numRows, 1).setNumberFormat("#,##0.0000"); // calculation 1
+ //dataSheet.getRange(2, 1, numRows, numColumns).sort(4,5);
+ function addCharacterCountColumns() {
+ var numRows = currentSetting.analysisSheet.getDataRange().getNumRows() - 1;
+ currentSetting.analysisSheet.getRange(2, 8, numRows, 1).setFormulaR1C1("=LEN(R[0]C[-1])");
+ currentSetting.analysisSheet.getRange(2, 10, numRows, 1).setFormulaR1C1("=LEN(R[0]C[-1])");
+ currentSetting.analysisSheet.getRange(2, 12, numRows, 1).setFormulaR1C1("=LEN(R[0]C[-1])");
+ currentSetting.analysisSheet.getRange(2, 14, numRows, 1).setFormulaR1C1("=LEN(R[0]C[-1])");
+ for(var campaignName in currentSetting.accountMap.campaigns) {
+ for (var adGroupName in currentSetting.accountMap.campaigns[campaignName].adGroups) {
+ currentSetting.sortedAds = new Array();
+ for(var key in currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads) {
+ var value = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads[key][currentSetting.sortBy];
+ currentSetting.sortedAds.push({'key': key, 'value': value});
+ //Logger.log(key + " " + value);
+ currentSetting.sortedAds.sort(function(a,b) {
+ return b.value - a.value; // DESC sort
+ for(var i = 0; i < currentSetting.sortedAds.length; i++) {
+ var thisKey = currentSetting.sortedAds[i].key;
+ currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].sortedAdList.push(thisKey);
+} // END function sortAds()
+ function getLabelsForAd(adGroupId,adId) {
+ var uniqueId = new Array(adGroupId, adId);
+ uniqueAdId.push(uniqueId);
+ var adSelector = AdWordsApp
+ var adIterator = adSelector.get();
+ while (adIterator.hasNext()) {
+ Logger.log("Labels: Ad Found");
+ var ad = adIterator.next();
+ var labelIterator = ad.labels()
+ while (labelIterator.hasNext()) {
+ var label = labelIterator.next();
+ labelName += label.getName();
+ } // END function getLabelsForAd(adId,adGroupId)
+ function getLabelsForAdGroup(adGroupId) {
+ var uniqueAdGroupId = [];
+ uniqueAdGroupId.push(adGroupId);
+ var adGroupSelector = AdWordsApp
+ .withIds(uniqueAdGroupId);
+ var adGroupIterator = adGroupSelector.get();
+ while (adGroupIterator.hasNext()) {
+ Logger.log("Labels: Ad Group Found");
+ var adGroup = adGroupIterator.next();
+ var labelIterator = adGroup.labels()
+ while (labelIterator.hasNext()) {
+ var label = labelIterator.next();
+ labelName += label.getName();
+ } // END function getExperimentLabel(adId,adGroupId)
+ function addCalculatedMetrics() {
+ for(var campaignName in currentSetting.accountMap.campaigns) {
+ for (var adGroupName in currentSetting.accountMap.campaigns[campaignName].adGroups) {
+ var adGroupId = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].adGroupId;
+ var adGroupClicks = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].Clicks;
+ var adGroupImpressions = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].Impressions;
+ var adGroupConversions = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].Conversions;
+ var adGroupConvertedClicks = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ConvertedClicks;
+ //Logger.log("adGroupClicks: " + adGroupClicks);
+ //Logger.log("adGroupImpressions: " + adGroupImpressions);
+ for(var adId in currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads) {
+ var thisAd = currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads[adId];
+ var adClicks = thisAd.Clicks;
+ //Logger.log("adClicks: " + adClicks);
+ var adImpressions = thisAd.Impressions;
+ var adConversions = thisAd.Conversions;
+ var adConvertedClicks = thisAd.ConvertedClicks;
+ var clickShare = parseFloat(adClicks / adGroupClicks);
+ if(adGroupImpressions) {
+ var impressionShare = parseFloat(adImpressions / adGroupImpressions);
+ var impressionShare = 0;
+ if(adGroupConversions) {
+ var conversionShare = parseFloat(adConversions / adGroupConversions);
+ var conversionShare = 0;
+ if(adConvertedClicks) {
+ var convertedClicksShare = parseFloat(adConvertedClicks / adConvertedClicks);
+ var convertedClicksShare = 0;
+ currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads[adId].ClickShare = clickShare;
+ currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads[adId].ImpressionShare = impressionShare;
+ currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads[adId].ConversionShare = conversionShare;
+ currentSetting.accountMap.campaigns[campaignName].adGroups[adGroupName].ads[adId].ConvertedClicksShare = convertedClicksShare;
+ if(DEBUG==1) Logger.log("clickShare: " + clickShare);
+ if(DEBUG==1) Logger.log("impressionShare: " + impressionShare);
+ if(DEBUG==1) Logger.log("conversionShare: " + conversionShare);
+ if(DEBUG==1) Logger.log("convertedClicksShare: " + convertedClicksShare);
+function getFloat (input) {
+ if(!input || input == "" || typeof(input) === 'undefined') var input = "0.0";
+ input = input.toString();
+ var output = parseFloat(input.replace(/,/g, ""));
+function sendEmailNotifications(emailAddresses, subject, body ) {
+ var finalSubject = subject + " - " + AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")"
+ if(AdWordsApp.getExecutionInfo().isPreview()) {
+ var finalBody = "<b>This script ran in preview mode. No changes were made to your account.</b><br/>" + body;
+ if(DEBUG == 1) Logger.log("email sent to " + emailAddresses + ": " + finalSubject);
+function getCampaignsByLabel(label)
+ var matchedCampaigns = new Object();
+ matchedCampaigns.ids = new Array();
+ var campaignIterator = AdWordsApp.campaigns()
+ .withCondition("LabelNames CONTAINS_ANY ['" + label + "']")
+ while(campaignIterator.hasNext()) {
+ var campaign = campaignIterator.next();
+ var campaignId = campaign.getId();
+ var campaignName = campaign.getName();
+ matchedCampaigns.ids.push(campaignId);
+ matchedCampaigns.names.push(campaignName);
+ matchedCampaigns.selectorStringForReports = "CampaignId IN [" + matchedCampaigns.ids + "]";
+ matchedCampaigns.selectorStringForScripts = "CampaignName IN [" + matchedCampaigns.names.join('","') + "]";
+ Logger.log("matchedCampaigns.selectorStringForScripts: " + matchedCampaigns.selectorStringForScripts);
+ return(matchedCampaigns);
+function getCampaignsByName(campaignName, caseSensitive, exactMatch) {
+ var campaignNameSelector = new Object();
+ var operator = "CONTAINS";
+ var operator = "CONTAINS_IGNORE_CASE";
+ if(campaignName.indexOf("'") != -1) {
+ var needle = '"' + campaignName + '"';
+ var needle = "'" + campaignName + "'";
+ campaignNameSelector.forReports = "CampaignName " + operator + " " + needle;
+ campaignNameSelector.forScripts = "CampaignName " + operator + " " + needle;
+ return(campaignNameSelector);
