AdWords CPCs After Bid Modifiers

var DEBUG = 0;

function main() {
 
  var spreadsheetName = "Bid Ranges";
  var spreadsheetUrl = "";
  var accountManagers = "";
  var overWriteOldData = 1;
  var sheetNames = ["Campaigns", "Keywords"];
  var folderNames = "";
  
  
  var destinationSpreadsheet = setUpReportInGoogleSheets(spreadsheetUrl, spreadsheetName, accountManagers, overWriteOldData, sheetNames, folderNames);
  var spreadsheet = destinationSpreadsheet.spreadsheet;
  var keywordSheet = spreadsheet.getSheetByName("Keywords");
  var campaignSheet = spreadsheet.getSheetByName("Campaigns");
  
  keywordSheet.appendRow(["Campaign", "Ad Group", "Keyword", "Cpc Bid", "Lowest Possible Cpc", "Highest Possible Cpc", "First Page Cpc", "First Position Cpc", "Top Of PageCpc"]);
  campaignSheet.appendRow(["Campaign", "Lowest Mobile Modifier", "Highest Mobile Modifier", "Lowest Dayparting Modifier", "Highest Dayparting Modifier", "Lowest Geo Modifier", "Highest Geo Modifier", "Lowest Aggregate Modifier", "Highest Aggregate Modifier"]);
  
  var bidAdjustmentMap = buildMapOfBidAdjustments(campaignSheet);
  
  var report = AdWordsApp.report(
    'SELECT CampaignName, CampaignId, AdGroupName, AdGroupId, CpcBid, FirstPageCpc, FirstPositionCpc, TopOfPageCpc, Criteria ' +
    'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE  Cost > 0 ' +
    'AND CampaignId IN ' + JSON.stringify(bidAdjustmentMap.campaignIdList) + ' ' +
    'AND AdGroupStatus = ENABLED ' +
    'DURING LAST_30_DAYS ');
  var rows = report.rows();
  
  while(rows.hasNext()) {
    var row = rows.next();
    var criteria = row['Criteria'];
    var adGroupId = row['AdGroupId'];
    var campaignId = row['CampaignId'];
    var adGroupName = row['AdGroupName'];
    var campaignName = row['CampaignName'];
    var cpcBid = row['CpcBid'];
    var firstPageCpc = row['FirstPageCpc'];
    var firstPositionCpc = row['FirstPositionCpc'];
    var topOfPageCpc = row['TopOfPageCpc'];
    
   
    
    var lowestLocationModifier = bidAdjustmentMap.campaigns[campaignId].lowestLocationModifier;
    var highestLocationModifier = bidAdjustmentMap.campaigns[campaignId].highestLocationModifier;
    var lowestAdScheduleModifier = bidAdjustmentMap.campaigns[campaignId].lowestAdScheduleModifier;
    var highestAdScheduleModifier = bidAdjustmentMap.campaigns[campaignId].highestAdScheduleModifier;
    
    if(bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId]) {
      var lowestMobileModifier = bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId].lowestAdGroupMobileModifier;
      var highestMobileModifier = bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId].highestAdGroupMobileModifier;
    } 
    else {
      var lowestMobileModifier = bidAdjustmentMap.campaigns[campaignId].lowestMobileModifier;
      var highestMobileModifier = bidAdjustmentMap.campaigns[campaignId].highestMobileModifier;
    }
    
    var highestBidAdjustment = highestLocationModifier * highestAdScheduleModifier * highestMobileModifier;
    var lowestBidAdjustment = lowestLocationModifier * lowestAdScheduleModifier * lowestMobileModifier;
    
    //Logger.log(highestLocationModifier + " " + highestAdScheduleModifier + " " + highestMobileModifier)
    
    var highestPossibleCpc = highestBidAdjustment * cpcBid;
    var lowestPossibleCpc = lowestBidAdjustment * cpcBid;
    //Logger.log(criteria + " bid: " + cpcBid + " (" + lowestPossibleCpc + " - " + highestPossibleCpc + ")" + " (" + highestBidAdjustment + " - " + lowestBidAdjustment + ")");
    keywordSheet.appendRow([campaignName, adGroupName, "'" + criteria, cpcBid, lowestPossibleCpc.toFixed(2), highestPossibleCpc.toFixed(2), firstPageCpc, firstPositionCpc, topOfPageCpc]);
  }
  Logger.log("Results are ready at " + destinationSpreadsheet.url);
}

function buildMapOfBidAdjustments(campaignSheet) {
  
  var bidAdjustmentMap = new Object();
  bidAdjustmentMap.campaignIdList = new Array();
  bidAdjustmentMap.campaigns = new Array();
  
  var campaignIterator = AdWordsApp.campaigns().get();
  //Logger.log('Total campaigns found : ' + campaignIterator.totalNumEntities());
  while (campaignIterator.hasNext()) {
    var lowestLocationModifier = 1;
    var highestLocationModifier = 1;
    
    var lowestAdScheduleModifier = 1;
    var highestAdScheduleModifier = 1;
    
    var lowestMobileModifier = 1;
    var highestMobileModifier = 1;
    
    var campaign = campaignIterator.next();
    var campaignName = campaign.getName();
    var campaignId = campaign.getId();
    var targeting = campaign.targeting();
    
    bidAdjustmentMap.campaignIdList.push(campaignId);
    
    bidAdjustmentMap.campaigns[campaignId] = new Object();
    bidAdjustmentMap.campaigns[campaignId].adGroups = new Array();
    bidAdjustmentMap.campaigns[campaignId].campaignId = campaignId;
    bidAdjustmentMap.campaigns[campaignId].campaignName = campaignName;
    
    var locationIterator = targeting.targetedLocations().get();
    while(locationIterator.hasNext()) {
      var location = locationIterator.next();
      var modifier = location.getBidModifier();
      if(modifier < lowestLocationModifier) lowestLocationModifier = modifier;
      if(modifier > highestLocationModifier) highestLocationModifier = modifier;
    }
    
    var mobile = targeting.platforms().mobile().get();
    while(mobile.hasNext()) {
      var modifier = mobile.next().getBidModifier();
      if(modifier < lowestMobileModifier) lowestMobileModifier = modifier;
      if(modifier > highestMobileModifier) highestMobileModifier = modifier;
    }
    
    
    var adScheduleIterator = targeting.adSchedules().get();
    while(adScheduleIterator.hasNext()) {
      var adSchedule = adScheduleIterator.next();
      var modifier = adSchedule.getBidModifier();
      //Logger.log("  Ad Schedule modifier: " + modifier);
      if(modifier < lowestAdScheduleModifier) lowestAdScheduleModifier = modifier;
      if(modifier > highestAdScheduleModifier) highestAdScheduleModifier = modifier;
    }
    
    /*
    Logger.log("Campaign: " + campaignName);
    Logger.log("   lowestLocationModifier: " + lowestLocationModifier);
    Logger.log("   highestLocationModifier: " + highestLocationModifier);
    
    Logger.log("   lowestAdScheduleModifier: " + lowestAdScheduleModifier);
    Logger.log("   highestAdScheduleModifier: " + highestAdScheduleModifier);
    
    Logger.log("   lowestMobileModifier: " + lowestMobileModifier);
    Logger.log("   highestMobileModifier: " + highestMobileModifier);
    */
    var lowestTotalModifier = lowestLocationModifier * lowestAdScheduleModifier * lowestMobileModifier;
    var highestTotalModifier = highestLocationModifier * highestAdScheduleModifier * highestMobileModifier;
    campaignSheet.appendRow([campaignName, lowestMobileModifier, highestMobileModifier, lowestAdScheduleModifier, highestAdScheduleModifier, lowestLocationModifier, highestLocationModifier, lowestTotalModifier, highestTotalModifier]);
  
    
    bidAdjustmentMap.campaigns[campaignId].lowestLocationModifier = lowestLocationModifier;
    bidAdjustmentMap.campaigns[campaignId].highestLocationModifier = highestLocationModifier;
    bidAdjustmentMap.campaigns[campaignId].lowestAdScheduleModifier = lowestAdScheduleModifier;
    bidAdjustmentMap.campaigns[campaignId].highestAdScheduleModifier = highestAdScheduleModifier;
    bidAdjustmentMap.campaigns[campaignId].lowestMobileModifier = lowestMobileModifier;
    bidAdjustmentMap.campaigns[campaignId].highestMobileModifier = highestMobileModifier;
    
    var adGroups = campaign.adGroups().withCondition("Status = ENABLED").get();
    while(adGroups.hasNext()) {
      var lowestAdGroupMobileModifier = lowestMobileModifier;
      var highestAdGroupMobileModifier = highestMobileModifier;
      
      var adGroup = adGroups.next();
     
      var adGroupName = adGroup.getName();
      var adGroupId = adGroup.getId();
      
      bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId] = new Object();
      bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId].adGroupId = adGroupId;
      bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId].adGroupName = adGroupName;
      
      bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId].highestAdGroupMobileModifier = highestMobileModifier;
      bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId].lowestAdGroupMobileModifier = lowestMobileModifier;
        
      
      var modifier = adGroup.getMobileBidModifier();
      //Logger.log("ag mobile mod: " + modifier);
      
      if(modifier < lowestAdGroupMobileModifier) {
        lowestAdGroupMobileModifier = modifier;
        //Logger.log("    AG: " + adGroupName + " lowest Mobile Modifier: " + lowestAdGroupMobileModifier);
        bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId].lowestAdGroupMobileModifier = lowestAdGroupMobileModifier;
      }
      else if(modifier > highestAdGroupMobileModifier) {
        highestAdGroupMobileModifier = modifier;
        //Logger.log("    AG: " + adGroupName + " highest Mobile Modifier: " + highestAdGroupMobileModifier);
        bidAdjustmentMap.campaigns[campaignId].adGroups[adGroupId].highestAdGroupMobileModifier = highestAdGroupMobileModifier;
      } 
      
    }
    
  }
  return bidAdjustmentMap;
}

/*
  // function setUpReportInGoogleSheets(spreadsheetUrl, spreadsheetName, accountManagers, overWriteOldData, sheetNames, targetFolder)
  //---------------------------------------
  // handles all the details of setting up the Google Spreadsheet to receive the report data
  //
  // output is an object
  // destinationSpreadsheet.overwrite
  // destinationSpreadsheet.isNew
  // destinationSpreadsheet.spreadsheet (the Google spreadsheet object)
  // destinationSpreadsheet.url
  */
  function setUpReportInGoogleSheets(spreadsheetUrl, spreadsheetName, accountManagers, overWriteOldData, sheetNames, folderNames) {
    
    var destinationSpreadsheet = new Object();
    if(folderNames) {
      var folderStructure = folderNames.split(",");
    } else {
      var folderStructure = new Array();
    }
    
    
    var targetFolder = DriveApp.getRootFolder();
    for(var i = 0; i < folderStructure.length; i++) {
      var folderName = folderStructure[i];
      if(folderName.toLowerCase().indexOf("[account id]") != -1) {
        folderName = AdWordsApp.currentAccount().getCustomerId();
      } else if(folderName.toLowerCase().indexOf("[account name]") != -1) {
        folderName = AdWordsApp.currentAccount().getName();
      }
      Logger.log("folderName: " + folderName);
      var foldersIterator = targetFolder.getFoldersByName(folderName);
      if (foldersIterator.hasNext()) {
        
        targetFolder = foldersIterator.next();
        Logger.log("Selected target folder: " + folderName);
      } else {
        if(DEBUG==1) Logger.log("Creating a new folder: " + folderName);
        targetFolder = targetFolder.createFolder(folderName);
      }
    }
    
    
    destinationSpreadsheet.overWrite = overWriteOldData;
    
    if(!spreadsheetUrl || spreadsheetUrl == "" || spreadsheetUrl == " " || spreadsheetUrl.toLowerCase().indexOf("new") != -1) var isNew = 1;
    destinationSpreadsheet.isNew = isNew;
    
    if(!sheetNames || !sheetNames[0]) {
      var sheetNames = new Array();
      sheetNames[0] = "Sheet 1";
    }
    
    if(isNew)
    {
      var spreadsheet = SpreadsheetApp.create(spreadsheetName);
      var id = spreadsheet.getId();
      var spreadsheetUrl = spreadsheet.getUrl();
      var file = DriveApp.getFileById(id);
      targetFolder.addFile(file);
      if(folderName) DriveApp.getRootFolder().removeFile(file);
    } 
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    destinationSpreadsheet.spreadsheet = spreadsheet;
    destinationSpreadsheet.url = spreadsheet.getUrl();
    
    // IF NEW -> REMOVE ALL SHEETS, THEN CREATE ALL SHEETS
    if(isNew){
      var allSheets = spreadsheet.getSheets(); 
      
      // remove
      for(var i=1,len=allSheets.length;i<len;i++){
        spreadsheet.deleteSheet(allSheets[i]);
      }
      
      // create
      allSheets[0].setName(sheetNames[0]);
      for(var sheetCounter = 1; sheetCounter < sheetNames.length; sheetCounter++) {
        var sheetName = sheetNames[sheetCounter];
        if(DEBUG == 1) Logger.log("sheet name: " + sheetName);
        spreadsheet.insertSheet(sheetName);
      }
    } else {
      // IF NOT NEW, MAKE SURE RIGHT SHEETS EXIST
      for(var sheetCounter = 0; sheetCounter < sheetNames.length; sheetCounter++) {
        var sheetName = sheetNames[sheetCounter];
        if(DEBUG == 1) Logger.log("checking if sheet with name exists: " + sheetName);
        try {
          var thisSheet = spreadsheet.getSheetByName(sheetName);
          if(!thisSheet) spreadsheet.insertSheet(sheetName);
        } catch (e) {
          Logger.log(e);
        }
      }
    }
    
    
    // ADD ACCOUNT MANAGERS
    if(accountManagers && accountManagers!=""){
      var accountManagersArray = accountManagers.replace(/\s/g, "").split(",");
      spreadsheet.addEditors(accountManagersArray);
    }
    
    // IF OVERWRITE, CLEAR SHEETS
    if(overWriteOldData) {
      for(var sheetCounter = 0; sheetCounter < sheetNames.length; sheetCounter++) {
        var sheetName = sheetNames[sheetCounter];
        if(DEBUG == 1) Logger.log("sheet name: " + sheetName);
        try {
          var thisSheet = spreadsheet.getSheetByName(sheetName);
          if(thisSheet) thisSheet.clear();
        } catch (e) {
          Logger.log(e);
        }
      }
    }
    return(destinationSpreadsheet);
  }