Last active
July 24, 2019 03:56
-
-
Save siliconvallaeys/74df8ed933f05550e13ac61e4defc927 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment