Skip to content

Instantly share code, notes, and snippets.

@siliconvallaeys
Last active July 24, 2019 03:56
Show Gist options
  • Save siliconvallaeys/74df8ed933f05550e13ac61e4defc927 to your computer and use it in GitHub Desktop.
Save siliconvallaeys/74df8ed933f05550e13ac61e4defc927 to your computer and use it in GitHub Desktop.
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