Helps find when your stacked bids by match type are not following your stratregy

Manage Stacked or Tiered Keyword Bids.js

/* 
// AdWords Script: Check and set keyword bids for stacked (tiered) bid strategy
// -----------------------------------------------------------------------------
// Copyright 2017 Optmyzr Inc., All Rights Reserved
// 
// This script helps you compare bids for the same keyword in multiple match types
// It can output just keywords for which a less restrictive match type has equal or higher bids (mode 1)
// or it can output all keywords so you can see the bids by match type (mode 2)
//
// New bids can be generated and uploaded with the spreadsheet through Bulk Uploads. The file is NOT compatible with AdWords Editor.
//
// The script only checks active keywords with impressions during the selected date range.
//
// For more PPC management tools, visit www.optmyzr.com
//
*/

var STARTDATE = "20170201"; // enter date in format YYYYMMDD
var ENDDATE = "20170228"; // enter date in format YYYYMMDD
var SELECTOR1 = 'CampaignName CONTAINS ""'; // Limit the campaigns by adding a select clause between the double quotes
var SPREADSHEETURL = "new"; // put in 'new' or the actual URL of the spreadsheet to use
var OVERWRITE = false; // set to 'true' if you want data to be appended, 'false' if you want old data cleared first
var OPTIMIZATION = 1;  // see two available types below
var SPREADSHEET_USER = "example@gmail.com"; // enter your own Google account email here

// Provide target ratios if you want to automate the suggestion of the correct bid
// This assumes the exact match bid is the correct bid and the other match types will be based on it.
var TARGET_PHRASE_EXACT_RATIO = 0.96; // e.g. to set the phrase bid to 80% of the exact bid, enter: 0.8
var TARGET_MODIFIED_BROAD_EXACT_RATIO = 0.92;
var TARGET_BROAD_EXACT_RATIO = 0.88;

var BIDS_MUST_FOLLOW_RATIOS_EXACTLY = 0; // Set to 1 if the bids must follow the above ratios precisely. When set to 0, a bid that is lower than expected will be deemed acceptable.


/*
OPTIMIZATION MODES

1: find keywords where one of the less restrictive match types has the same or a higher bid
2: list all keywords with the highest bid by match type

*/


var DEBUG = 1;

function main() {
  
  var accountId = AdWordsApp.currentAccount().getCustomerId();
  var accountName = AdWordsApp.currentAccount().getName();
  var accountMap = new Array();
  
  // Set Up Spreadsheet
  
  if(SPREADSHEETURL.toLowerCase().indexOf("new") != -1) {
    var spreadsheet = SpreadsheetApp.create("Stacked KW Bids (" + accountName + ") stats for " + STARTDATE + "-" + ENDDATE);
    var spreadsheetUrl = spreadsheet.getUrl();
    var analysisSheet = spreadsheet.insertSheet("Keywords");
    analysisSheet.appendRow(["Keyword", "Keyword Match Type", "Sub Match Type", "Current Max Cpc", "Max. Cpc", "Campaign Name", "Ad Group Name", "Exact Match Max Cpc", "Phrase Match Max Cpc", "Modified Broad Max Cpc", "Broad Max Cpc", 
                           "Top Of Page Cpc", "First Page Cpc", "Clicks", "Impressions", "Cost", "Avg. Pos", "Conversions", "ConversionValue", "ROAS",
                           "Ad group ID", "Keyword ID", "Account ID"
                          ]);
    var sheet1 = spreadsheet.getSheetByName("Sheet1");
    spreadsheet.deleteSheet(sheet1);
  } else {
    var spreadsheetUrl = SPREADSHEETURL;
    var analysisSheet = spreadsheet.getSheetByName("Keywords");
  }
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  spreadsheet.addEditor(SPREADSHEET_USER);
  var url = spreadsheet.getUrl();
  //spreadsheet.addEditor(scriptOwner);
  //var accountManagersArray = accountManagers.split(",");
  //spreadsheet.addEditors(accountManagersArray);
 
  if(OVERWRITE){  
    analysisSheet.clear();
    analysisSheet.appendRow(["Keyword", "Keyword Match Type", "Sub Match Type", "Current Max Cpc", "Max. Cpc", "Campaign Name", "Ad Group Name", "Exact Match Max Cpc", "Phrase Match Max Cpc", "Modified Broad Max Cpc", "Broad Max Cpc", 
                             "Top Of Page Cpc", "First Page Cpc", "Clicks", "Impressions", "Cost", "Avg. Pos", "Conversions", "ConversionValue", "ROAS",
                             "Ad group ID", "Keyword ID", "Account ID"
                            ]);
  }
  
  
  
  
  analysisSheet.getRange("A1:Z1").setFontWeight("bold");
  analysisSheet.setFrozenRows(1);
  
  if(DEBUG) Logger.log("Results available at " + url);
 
  
  //PROCESS KW LEVEL BIDS
  var kwCounter = 0;
  var report1 = AdWordsApp.report(
     'SELECT CampaignId, AdGroupId, CampaignName, AdGroupName, Id, Criteria, KeywordMatchType, Clicks, Impressions, CpcBid, Conversions, Cost, ConversionValue, QualityScore, AverageCpc, AveragePosition, TopOfPageCpc, FirstPageCpc ' +
     'FROM    KEYWORDS_PERFORMANCE_REPORT' + ' ' +
     //'WHERE ' + idType + ' IN [' + idString + '] ' +
    'WHERE ' + SELECTOR1 + ' ' +
    'AND Impressions > 0' + ' ' +
    'AND IsNegative = FALSE' + ' ' +
    'AND Status != REMOVED' +  ' ' +
    //'AND KeywordText CONTAINS "+"' + ' ' +
     'DURING ' + STARTDATE + "," + ENDDATE );
  var rows1 = report1.rows();
  
  while (rows1.hasNext()) {
    kwCounter++;
    var row = rows1.next();
    var thisKeyword = new Object();
    for(var key in row) {
      var value = row[key];
      thisKeyword[key] = value;
    }
    
    thisKeyword.roas = thisKeyword.ConversionValue / thisKeyword.Cost;
    var uniqueId = thisKeyword.Criteria.replace(/\+/g, '');
    
    
    if(!accountMap[uniqueId]) {
      accountMap[uniqueId] = new Object();
      accountMap[uniqueId].numberOfMatches = 0;
      accountMap[uniqueId].maxBroadCpc = 0;
      accountMap[uniqueId].maxModifiedBroadCpc = 0;
      accountMap[uniqueId].maxPhraseCpc = 0;
      accountMap[uniqueId].maxExactCpc = 0;
      accountMap[uniqueId].matchingKeywords = new Array();
      
    } 
    
    
    if(thisKeyword.KeywordMatchType == "Broad") {
      if(thisKeyword.Criteria.toString().indexOf("+") != -1) {
        //Logger.log("MODIFIED BROAD");
        thisKeyword.subMatchType = "Modified Broad";
        if(accountMap[uniqueId].maxModifiedBroadCpc < thisKeyword.CpcBid) {
          accountMap[uniqueId].maxModifiedBroadCpc = thisKeyword.CpcBid;
        }
      } else {
        thisKeyword.subMatchType = "Broad";
        if(accountMap[uniqueId].maxBroadCpc < thisKeyword.CpcBid) {
          accountMap[uniqueId].maxBroadCpc = thisKeyword.CpcBid;
        }
      }
    } else if(thisKeyword.KeywordMatchType == "Phrase") {
      thisKeyword.subMatchType = "Phrase";
      if(accountMap[uniqueId].maxPhraseCpc < thisKeyword.CpcBid) {
        accountMap[uniqueId].maxPhraseCpc = thisKeyword.CpcBid;
      } 
    } else if(thisKeyword.KeywordMatchType == "Exact") {
      thisKeyword.subMatchType = "Exact";
      if(accountMap[uniqueId].maxExactCpc < thisKeyword.CpcBid) {
        accountMap[uniqueId].maxExactCpc = thisKeyword.CpcBid;
      }
    }
    
    accountMap[uniqueId].matchingKeywords.push(thisKeyword);
    accountMap[uniqueId].numberOfMatches++;
    
    //Logger.log(kwCounter + ". " + thisKeyword.Criteria.toString() + "(" + uniqueId + ")" + ": " + thisKeyword.subMatchType + " : " + thisKeyword.CpcBid);
    
    
    
  } // END while (rows1.hasNext())
  
  
  
  
    
  
  for(var uniqueId in accountMap) {
    //var keywordText = uniqueId;
    var keywordList = accountMap[uniqueId].matchingKeywords;
    var numberOfMatches = accountMap[uniqueId].numberOfMatches;
    var maxBroadCpc = accountMap[uniqueId].maxBroadCpc;
    var maxModifiedBroadCpc = accountMap[uniqueId].maxModifiedBroadCpc;
    var maxPhraseCpc = accountMap[uniqueId].maxPhraseCpc;
    var maxExactCpc = accountMap[uniqueId].maxExactCpc;
    //var uniqueIdParts = uniqueId.split("-");
    //var adGroupId = uniqueIdParts[0];
    //var keywordId = uniqueIdParts[1];
    //Logger.log(keywordText + " : " + maxBroadCpc);
    
    
    //Logger.log("keywordText: " + uniqueId + " (" + numberOfMatches + ")");
    if(numberOfMatches > 1) {
      for(var i = 0; i < keywordList.length; i++) {
        var thisKeyword = keywordList[i];
        var matchType = thisKeyword.KeywordMatchType;
        var maxCpc = thisKeyword.CpcBid;
        var subMatchType = thisKeyword.subMatchType;
        
        if(OPTIMIZATION == 1) {
          var bidIsWrong = 0;
          if(matchType.toLowerCase().indexOf("phrase") != -1) {
            if(TARGET_PHRASE_EXACT_RATIO) {
              if(maxExactCpc) {
                var suggestedBid = TARGET_PHRASE_EXACT_RATIO * maxExactCpc;
              } else {
                var suggestedBid = thisKeyword.CpcBid;
              }
            } else {
              if((maxPhraseCpc >= maxExactCpc && maxExactCpc)) bidIsWrong = 1;
            }
          } else if(matchType.toLowerCase().indexOf("broad") != -1) {
            if(subMatchType.toLowerCase().indexOf("modified") != -1) {
              if(TARGET_MODIFIED_BROAD_EXACT_RATIO) {
                if(maxExactCpc) {
                  var suggestedBid = TARGET_MODIFIED_BROAD_EXACT_RATIO * maxExactCpc;
                } else if(maxPhraseCpc) {
                  var suggestedBid = maxPhraseCpc * (1 + (TARGET_MODIFIED_BROAD_EXACT_RATIO - TARGET_PHRASE_EXACT_RATIO) / TARGET_PHRASE_EXACT_RATIO)
                } else {
                  var suggestedBid = thisKeyword.CpcBid;
                }
              } else {
               if((maxModifiedBroadCpc >= maxPhraseCpc && maxPhraseCpc) || (maxModifiedBroadCpc >= maxExactCpc && maxExactCpc)) bidIsWrong = 1;
              }
            } else {
              if(TARGET_BROAD_EXACT_RATIO) {
                if(maxExactCpc) {
                  var suggestedBid = TARGET_BROAD_EXACT_RATIO * maxExactCpc;
                } else if(maxPhraseCpc) {
                  var suggestedBid = maxPhraseCpc * (1 + (TARGET_MODIFIED_BROAD_EXACT_RATIO - TARGET_PHRASE_EXACT_RATIO) / TARGET_PHRASE_EXACT_RATIO)
                } else if (maxModifiedBroadCpc) {
                  var suggestedBid = maxModifiedBroadCpc * (1 + (TARGET_BROAD_EXACT_RATIO - TARGET_MODIFIED_BROAD_EXACT_RATIO) / TARGET_BROAD_EXACT_RATIO)
                } else {
                  var suggestedBid = thisKeyword.CpcBid;
                }
              } else {
               if((maxBroadCpc >= maxModifiedBroadCpc && maxModifiedBroadCpc) || (maxBroadCpc >= maxPhraseCpc && maxPhraseCpc) || (maxBroadCpc >= maxExactCpc && maxExactCpc)) bidIsWrong = 1;
              }
            } 
          } else if(matchType.toLowerCase().indexOf("exact") != -1){
            var suggestedBid = maxExactCpc;
          }
          if(!BIDS_MUST_FOLLOW_RATIOS_EXACTLY) {
            if(thisKeyword.CpcBid > suggestedBid) bidIsWrong = 1; // bid is allowed to be lower but not higher
          } else {
            if(thisKeyword.CpcBid != suggestedBid) bidIsWrong = 1;
          }
        } else {
          var suggestedBid = "";
        }
        
        
        //Logger.log(thisKeyword.KeywordText.toString() + "  matchType: " + matchType + " bid: $" + maxCpc);
        if(bidIsWrong) {
          analysisSheet.appendRow(["'"+thisKeyword.Criteria.toString(), thisKeyword.KeywordMatchType, subMatchType, thisKeyword.CpcBid, suggestedBid, thisKeyword.CampaignName, thisKeyword.AdGroupName, maxExactCpc, maxPhraseCpc, maxModifiedBroadCpc, maxBroadCpc, 
                                   thisKeyword.TopOfPageCpc, thisKeyword.FirstPageCpc, thisKeyword.Clicks, thisKeyword.Impressions, thisKeyword.Cost, thisKeyword.AveragePosition, thisKeyword.Conversions, thisKeyword.ConversionValue, thisKeyword.roas,
                                   thisKeyword.AdGroupId, thisKeyword.Id, accountId
                                  ]);
        }
      } // END for(var i = 0; i < keywordList.length; i++)
    } // END if(numberOfMatches > 1)
  
  } // END for(var uniqueId in accountMap)
}