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