Created
April 14, 2016 18:50
-
-
Save siliconvallaeys/9062c6f35a7eace8342965f9d7e8fe9f to your computer and use it in GitHub Desktop.
Set AdWords Budgets To Meet Monthly Targets With Day Of Week Allocation
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
// Copyright 2015, Google Inc. All Rights Reserved. | |
// | |
// Licensed under the Apache License, Version 2.0 (the "License"); | |
// you may not use this file except in compliance with the License. | |
// You may obtain a copy of the License at | |
// | |
// http://www.apache.org/licenses/LICENSE-2.0 | |
// | |
// Unless required by applicable law or agreed to in writing, software | |
// distributed under the License is distributed on an "AS IS" BASIS, | |
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
// See the License for the specific language governing permissions and | |
// limitations under the License. | |
/** | |
* @name Flexible Budgets | |
* | |
* @overview The Flexible budgets script dynamically adjusts campaign budget for | |
* an advertiser account with a custom budget distribution scheme on a daily | |
* basis. See | |
* https://developers.google.com/adwords/scripts/docs/solutions/flexible-budgets | |
* for more details. | |
* | |
* @author AdWords Scripts Team [adwords-scripts@googlegroups.com] | |
* | |
* @version 1.0 | |
* | |
* @changelog | |
* - version 1.0 | |
* - Released initial version. | |
* | |
* Copyright 2016, Optmyzr Inc. All Rights Reserved. | |
* @version 2.0 - Changes by Optmyzr.com | |
* | |
* @changelog | |
* - version 2.0 | |
* - calculate budgets by day of week history | |
* - calculate front-loaded budgets | |
* - automatically recurring monthly budgets | |
* - roll-over unused budget from last month | |
* | |
*/ | |
var currentSetting = new Object(); | |
currentSetting.totalBudget = 500; // Enter the target monthly budget here, e.g. 500 | |
currentSetting.rollOverBudgets = 1; // set to 1 to roll over budget that was not used last month; set to 0 to NOT roll over budgets | |
currentSetting.changeBudgets = 0; // set to 1 to update budgets; set to 0 to leave budgets unchanged (useful while testing) | |
currentSetting.budgetAllocationMode = "calculateDowWeightedBudget"; // budget allocation mode - see Google documentation | |
currentSetting.campaignNameIs = "My campaign name"; // name of the campaign for which this budget applies; leave blank for all campaigns | |
emailAddress = "example@example.com"; | |
// Advanced Settings | |
currentSetting.budgetPeriod = "Monthly"; | |
currentSetting.numWeeksForAverages = 8; | |
currentSetting.numDaysToTestIfLastPeriodWasActive = 7; | |
currentSetting.verbose = 1; | |
currentSetting.debug = 0; | |
currentSetting.campaignLabel = ""; | |
if(typeof currentSetting.campaignNameIs == "undefined") currentSetting.campaignNameIs = ""; | |
if(typeof currentSetting.campaignLabel == "undefined") currentSetting.campaignLabel = ""; | |
VERBOSE = currentSetting.verbose; | |
DEBUG = currentSetting.debug; | |
currentSetting.changes = new Array(); | |
function main() { | |
var numCampaignsEvaluated = 0; | |
// Get campaigns with right name | |
var campaignNameCaseSensitive = 0; | |
var campaignNameExactMatch = 1; | |
var campaignNameSelectorString = getCampaignsByName(currentSetting.campaignNameIs, campaignNameCaseSensitive, campaignNameExactMatch); | |
switch(currentSetting.budgetPeriod) { | |
case "Weekly Sun-Sat": | |
//Logger.log("weekly sun"); | |
currentSetting.dateRange = "THIS_WEEK_SUN_TODAY"; | |
break; | |
case "Weekly Mon-Sun": | |
//Logger.log("weekly mon"); | |
currentSetting.dateRange = "THIS_WEEK_MON_TODAY"; | |
break; | |
case "Monthly": | |
if(VERBOSE) Logger.log("Using monthly budgets..."); | |
currentSetting.thisDateRange = "THIS_MONTH"; | |
currentSetting.previousDateRange = "LAST_MONTH"; | |
// first few days of last month -- used to check if campaign was active last month | |
var today = new Date(); | |
var activeTestStartDate = new Date(today.getFullYear(), today.getMonth()-1, 1); | |
var activeTestEndDate = new Date(today.getFullYear(), today.getMonth()-1, currentSetting.numDaysToTestIfLastPeriodWasActive); | |
currentSetting.dateRangeToTestIfCampaignWasActiveLastPeriod = dateToString(activeTestStartDate) + "," + dateToString(activeTestEndDate); | |
// | |
var reportDates = getReportDates("THIS_MONTH"); | |
var start = reportDates.startDate; | |
var end = reportDates.lastPossibleDate; | |
//Logger.log(start + " - " + end); | |
// | |
var reportDates = getReportDates("LAST_MONTH"); | |
var previousStart = reportDates.startDate; | |
var previousEnd = reportDates.endDate; | |
//Logger.log(previousStart + " - " + previousEnd); | |
break; | |
case "Custom": | |
var tempDate = new Date(); | |
var startDate = new Date(start); | |
var endDate = new Date(end); | |
var numDays = datediff(startDate, endDate); | |
Logger.log("numDays: " + numDays); | |
currentSetting.thisDateRange = dateToString(startDate) + "," + dateToString(endDate); | |
var previousStartDate = new Date(tempDate.setDate(startDate.getDate() - numDays)); | |
var previousEndDate = new Date(tempDate.setDate(endDate.getDate() - numDays)); | |
currentSetting.previousDateRange = dateToString(previousStartDate) + "," + dateToString(previousEndDate); | |
Logger.log(currentSetting.thisDateRange); | |
Logger.log(currentSetting.previousDateRange); | |
break; | |
} | |
if(currentSetting.budgetAllocationMode.toLowerCase().indexOf("calculatedowweightedbudget") != -1) { | |
calculateDowFluctuations(); | |
} | |
// SEARCH AND DISPLAY CAMPAIGNS | |
var iterator = AdWordsApp.campaigns() | |
.withCondition("Status = ENABLED") | |
.withCondition(campaignNameSelectorString.forScripts) | |
.get(); | |
if(VERBOSE) Logger.log(""); | |
if(VERBOSE) Logger.log("------------------------------------"); | |
if(VERBOSE) Logger.log("| Calculating New Budgets... |"); | |
if(VERBOSE) Logger.log("------------------------------------"); | |
if(VERBOSE) Logger.log(""); | |
while(iterator.hasNext()){ | |
numCampaignsEvaluated++; | |
var item = iterator.next(); | |
var name = item.getName(); | |
var id = item.getId(); | |
currentSetting.campaignId = id; | |
var currentPeriodCost = item.getStatsFor(currentSetting.thisDateRange).getCost(); | |
var lastPeriodCost = item.getStatsFor(currentSetting.previousDateRange).getCost(); | |
if(VERBOSE) Logger.log(""); | |
if(VERBOSE) Logger.log("CAMPAIGN: " + name); | |
if(VERBOSE) Logger.log("------------------------------------"); | |
if(currentSetting.rollOverBudgets) { | |
if(VERBOSE) Logger.log(" This campaign rolls over unused budgets from the previous cycle to the current cycle."); | |
var impressionsToCheckIfCampaignWasActive = item.getStatsFor(dateToString(activeTestStartDate), dateToString(activeTestEndDate)).getImpressions(); | |
if(impressionsToCheckIfCampaignWasActive) { | |
var rolledOverBudget = currentSetting.totalBudget - lastPeriodCost; | |
currentSetting.fullTotalBudget = currentSetting.totalBudget + rolledOverBudget; | |
if(VERBOSE) Logger.log(" Budget to roll over: " + rolledOverBudget); | |
if(VERBOSE) Logger.log(" Full total budget: " + currentSetting.fullTotalBudget); | |
} else { | |
if(VERBOSE) Logger.log(" Because this campaign did not appear to have been active at the start of the previous cycle, no budgets will be rolled over during this cycle."); | |
if(VERBOSE) Logger.log(" We saw no impressions for this campaign in the date range " + dateToString(activeTestStartDate) + " - " + dateToString(activeTestEndDate) + " and that is how we determined this campaign does not qualify to roll over a budget in this cycle."); | |
var rolledOverBudget = 0; | |
currentSetting.fullTotalBudget = currentSetting.totalBudget + rolledOverBudget; | |
} | |
} else { | |
currentSetting.fullTotalBudget = currentSetting.totalBudget; | |
} | |
if(currentSetting.budgetAllocationMode.toLowerCase().indexOf("dow") != -1) { | |
setNewBudget(calculateDowWeightedBudget, id, currentSetting.fullTotalBudget, start, end); | |
} else if(currentSetting.budgetAllocationMode.toLowerCase().indexOf("evenly") != -1) { | |
setNewBudget(calculateBudgetEvenly, id, currentSetting.fullTotalBudget, start, end); | |
} else if(currentSetting.budgetAllocationMode.toLowerCase().indexOf("backloaded") != -1) { | |
setNewBudget(calculateBackLoadedBudget, id, currentSetting.fullTotalBudget, start, end); | |
} else if(currentSetting.budgetAllocationMode.toLowerCase().indexOf("frontloaded") != -1) { | |
setNewBudget(calculateFrontLoadedBudget, id, currentSetting.fullTotalBudget, start, end); | |
} | |
} | |
var logNotes = ""; | |
var emailNotes = ""; | |
var numChanges = currentSetting.changes.length; | |
var logNotes = logNotes + numCampaignsEvaluated + " campaigns evaluated. " + numChanges + " with new budgets."; | |
var emailNotes = emailNotes + numCampaignsEvaluated + " campaigns evaluated. " + numChanges + " with new budgets:<br/><br/>"; | |
if(numChanges) var emailNotes = emailNotes + "<ul>"; | |
for(var changeCounter = 0; changeCounter < currentSetting.changes.length; changeCounter++){ | |
var change = currentSetting.changes[changeCounter]; | |
var campaignName = change.campaignName; | |
var oldBudget = change.oldBudget; | |
var newBudget = change.newBudget; | |
var emailNotes = emailNotes + "<li>Campaign: " + campaignName + " old budget: " + oldBudget + " new budget: " + newBudget + "</li>"; | |
} | |
if(numChanges) { | |
var emailNotes = emailNotes + "</ul>"; | |
sendEmailNotifications(emailAddress, "budgets", emailNotes, "notification" ); | |
} | |
} | |
function calculateDowFluctuations() { | |
if(VERBOSE) Logger.log("------------------------------------"); | |
if(VERBOSE) Logger.log("| DAY OF WEEK DATA |"); | |
if(VERBOSE) Logger.log("------------------------------------"); | |
if(VERBOSE) Logger.log("Here are the fluctuations in cost for each day of the week for each campaign."); | |
if(VERBOSE) Logger.log("This was calculated using cost data from the past " + currentSetting.numWeeksForAverages + " weeks."); | |
if(VERBOSE) Logger.log("This data is only used if you choose the budget method called 'allocate based on typical day of week performance'"); | |
var daysOfWeek = new Object(); | |
daysOfWeek.Monday = 1; | |
daysOfWeek.Tuesday = 2; | |
daysOfWeek.Wednesday = 3; | |
daysOfWeek.Thursday = 4; | |
daysOfWeek.Friday = 5; | |
daysOfWeek.Saturday = 6; | |
daysOfWeek.Sunday = 7; | |
var weekdayNames = new Array(); | |
weekdayNames[1] = "Monday"; | |
weekdayNames[2] = "Tuesday"; | |
weekdayNames[3] = "Wednesday"; | |
weekdayNames[4] = "Thursday"; | |
weekdayNames[5] = "Friday"; | |
weekdayNames[6] = "Saturday"; | |
weekdayNames[7] = "Sunday"; | |
var tempDate = new Date(); | |
var today = new Date(); | |
var numDays = currentSetting.numWeeksForAverages * 7; | |
//Logger.log("numDays: " + numDays); | |
var startDate = new Date(tempDate.setDate(today.getDate() - numDays)); | |
var tempDate = new Date(); | |
var endDate = new Date(tempDate.setDate(today.getDate() - 1)); | |
var dateRangeToUse = dateToString(startDate) + "," + dateToString(endDate); | |
//Logger.log(dateRangeToUse); | |
var report = AdWordsApp.report( | |
'SELECT CampaignId, CampaignName, Cost, DayOfWeek ' + | |
'FROM CAMPAIGN_PERFORMANCE_REPORT ' + | |
'WHERE CampaignStatus = ENABLED ' + | |
'DURING ' + dateRangeToUse + ' '); | |
currentSetting.campaignMap = new Array(); | |
var rows = report.rows(); | |
while (rows.hasNext()) { | |
var row = rows.next(); | |
var cost = parseFloat(row['Cost']); | |
var dow = row['DayOfWeek']; | |
var dowAsNumber = daysOfWeek[dow]; | |
var id = row['CampaignId']; | |
var campaignName = row['CampaignName']; | |
//Logger.log(dow + ": " + cost); | |
if(!currentSetting.campaignMap[id]) { | |
currentSetting.campaignMap[id] = new Object(); | |
currentSetting.campaignMap[id].name = campaignName; | |
currentSetting.campaignMap[id].averageDailyCost = 0; | |
currentSetting.campaignMap[id].averageWeeklyCost = 0; | |
currentSetting.campaignMap[id].dowCosts = new Array(); | |
} | |
currentSetting.campaignMap[id].dowCosts[dowAsNumber] = cost; | |
} | |
// iterate to get total cost and average daily | |
for(var campaignId in currentSetting.campaignMap) { | |
var campaign = currentSetting.campaignMap[campaignId]; | |
var campaignName = campaign.name; | |
//Logger.log(campaignName); | |
var totalCost = 0; | |
for(var dow in campaign.dowCosts) { | |
var cost = campaign.dowCosts[dow]; | |
totalCost += cost; | |
//Logger.log(dow + ": " + cost); | |
} | |
campaign.averageDailyCost = totalCost / 7; | |
campaign.averageWeeklyCost = totalCost; | |
//Logger.log(" avg: " + campaign.averageDailyCost); | |
} | |
// iterate to get percentage fluctuation | |
for(var campaignId in currentSetting.campaignMap) { | |
var campaign = currentSetting.campaignMap[campaignId]; | |
var campaignName = campaign.name; | |
var dailyCost = campaign.averageDailyCost; | |
var weeklyCost = campaign.averageWeeklyCost; | |
if(weeklyCost) { | |
if(VERBOSE) Logger.log(""); | |
if(VERBOSE) Logger.log("CAMPAIGN: " + campaignName + " (id: " + campaignId + ")"); | |
if(VERBOSE) Logger.log("------------------------------------------"); | |
for(var dow in campaign.dowCosts) { | |
var cost = campaign.dowCosts[dow]; | |
var percentOfNormal = cost / dailyCost; | |
var percentCostForWeek = cost / weeklyCost; | |
if(VERBOSE) Logger.log(" - " + weekdayNames[dow] + " cost is: " + cost + " (" + 100*percentCostForWeek.toFixed(2) + "% of the weekly total)"); | |
} | |
if(VERBOSE) Logger.log(" TOTAL COST: " + weeklyCost.toFixed(2)); | |
} | |
} | |
} | |
function setNewBudget(budgetFunction, campaignId, totalBudget, start, end) { | |
var thisChange = new Object(); | |
var today = new Date(); | |
if (today < start) { | |
if(VERBOSE) Logger.log("Today's date is before the start date of your budget so we are not setting a budget yet."); | |
return; | |
} | |
var campaign = AdWordsApp.campaigns(). | |
withCondition('CampaignId = "' + campaignId + '"'). | |
get(). | |
next(); | |
var campaignName = campaign.getName(); | |
var oldBudget = campaign.getBudget(); | |
var costSoFar = campaign.getStatsFor(dateToString(start), dateToString(end)). | |
getCost(); | |
var daysSoFar = datediff(start, today) - 1; | |
var totalDays = datediff(start, end) + 1; | |
//Logger.log(dateToString(start) + " - " + dateToString(end)); | |
if(VERBOSE) Logger.log(" Cost so far: " + costSoFar + " after " + daysSoFar + " days."); | |
if(VERBOSE) Logger.log(" Target cost: " + totalBudget + " in " + totalDays + " days."); | |
var newBudget = budgetFunction(costSoFar, totalBudget, daysSoFar, totalDays); | |
if(VERBOSE) Logger.log(" Old budget: " + oldBudget.toFixed(2)); | |
if(VERBOSE) Logger.log(" --> New budget: " + newBudget.toFixed(2)); | |
thisChange.campaignName = campaignName; | |
thisChange.oldBudget = oldBudget; | |
thisChange.newBudget = newBudget; | |
if(oldBudget != newBudget) { | |
currentSetting.changes.push(thisChange); | |
} | |
if(currentSetting.changeBudgets) { | |
campaign.setBudget(newBudget); | |
if(VERBOSE) Logger.log(" The new budget has been applied."); | |
} else { | |
if(VERBOSE) Logger.log(" No changes were made. Update the script settings if you want to apply new budgets."); | |
} | |
} | |
function calculateDowWeightedBudget(costSoFar, totalBudget, daysSoFar, totalDays) { | |
if(VERBOSE) Logger.log(" New budget is calculated by how much is typically spent on each day of the week. This helps prevent saving too much budget for a day of the week when you normally don't accrue a high cost."); | |
var daysRemaining = totalDays - daysSoFar; | |
//Logger.log("days remaining: " + daysRemaining); | |
var budgetRemaining = totalBudget - costSoFar; | |
//Logger.log("budgetRemaining: " + budgetRemaining); | |
var weeklyCost = currentSetting.campaignMap[currentSetting.campaignId].averageWeeklyCost; | |
if (daysRemaining <= 0) { | |
return budgetRemaining; | |
} else if(weeklyCost) { | |
//Logger.log("weeklyCost: " + weeklyCost); | |
var timeZone = AdWordsApp.currentAccount().getTimeZone(); | |
var thisDow = parseInt(Utilities.formatDate(new Date(), timeZone, "u")); | |
if(DEBUG) Logger.log("thisDow: " + thisDow); | |
var extraDaysRemaining = daysRemaining % 7; | |
var numWeeksRemaining = (daysRemaining - extraDaysRemaining) / 7; | |
//Logger.log("numWeeksRemaining: " + numWeeksRemaining); | |
//Logger.log("extraDaysRemaining: " + extraDaysRemaining); | |
var totalBudgetUnitsRemaining = parseFloat(0); | |
for(var dayCounter = 0; dayCounter < extraDaysRemaining; dayCounter++) { | |
var dow = (thisDow + dayCounter)%7; | |
//Logger.log("dow: " + dow); | |
var possibleBudgetForDow = parseFloat(currentSetting.campaignMap[currentSetting.campaignId].dowCosts[dow] / weeklyCost); | |
totalBudgetUnitsRemaining += possibleBudgetForDow; | |
if(DEBUG) Logger.log(" " + dayCounter + ". possibleBudgetForDow: " + possibleBudgetForDow); | |
if(DEBUG) Logger.log(" " + dayCounter + ". totalBudgetUnitsRemaining: " + totalBudgetUnitsRemaining); | |
} | |
totalBudgetUnitsRemaining += numWeeksRemaining; | |
if(DEBUG) Logger.log(" Budget Units Remaining In Cycle: " + totalBudgetUnitsRemaining.toFixed(2)); | |
var todaysAllocationOfRemainingBudgetUnits = currentSetting.campaignMap[currentSetting.campaignId].dowCosts[thisDow] / weeklyCost / totalBudgetUnitsRemaining; | |
if(DEBUG) Logger.log(" Percentage Of Budget Units To Spend Today: " + 100*todaysAllocationOfRemainingBudgetUnits.toFixed(2) + "%"); | |
if(todaysAllocationOfRemainingBudgetUnits) { | |
// this DOW has data that is not 0 | |
return budgetRemaining * todaysAllocationOfRemainingBudgetUnits; | |
} else { | |
if(VERBOSE) Logger.log(" This date of the week has no historical data so we are allocating budget evenly. If you do not want to run ads this day of the week, please use dayparting."); | |
return budgetRemaining / daysRemaining; | |
} | |
} else { | |
if(VERBOSE) Logger.log(" Because this campaign is too new and we don't have day of week performance data yet, we are allocating budget evenly for now. We will automatically start using day of week data when it is available for this campaign."); | |
return budgetRemaining / daysRemaining; | |
} | |
} | |
function calculateBudgetEvenly(costSoFar, totalBudget, daysSoFar, totalDays) { | |
if(VERBOSE) Logger.log(" New budget is calculated by taking the remaining budget and dividing it evenly across all remaining days."); | |
var daysRemaining = totalDays - daysSoFar; | |
var budgetRemaining = totalBudget - costSoFar; | |
if (daysRemaining <= 0) { | |
return budgetRemaining; | |
} else { | |
return budgetRemaining / daysRemaining; | |
} | |
} | |
function calculateBackLoadedBudget(costSoFar, totalBudget, daysSoFar, totalDays) { | |
if(VERBOSE) Logger.log(" New budget is calculated by reserving most of the budget for use towards the end of the cycle."); | |
var daysRemaining = totalDays - daysSoFar; | |
var budgetRemaining = totalBudget - costSoFar; | |
if (daysRemaining <= 0) { | |
return budgetRemaining; | |
} else { | |
return budgetRemaining / (2 * daysRemaining - 1); | |
} | |
} | |
function calculateFrontLoadedBudget(costSoFar, totalBudget, daysSoFar, totalDays) { | |
if(VERBOSE) Logger.log(" New budget is calculated by using up most of the budget towards the start of the cycle."); | |
var daysRemaining = totalDays - daysSoFar; | |
var budgetRemaining = totalBudget - costSoFar; | |
if (daysRemaining <= 0) { | |
return budgetRemaining; | |
} else { | |
return budgetRemaining / (0.5 * daysRemaining); | |
} | |
} | |
/** | |
* Returns number of days between two dates, rounded up to nearest whole day. | |
*/ | |
function datediff(from, to) { | |
var millisPerDay = 1000 * 60 * 60 * 24; | |
return Math.ceil((to - from) / millisPerDay); | |
} | |
function dateToString(date) { | |
return date.getFullYear() + zeroPad(date.getMonth() + 1) + | |
zeroPad(date.getDate()); | |
} | |
function zeroPad(n) { | |
if (n < 10) { | |
return '0' + n; | |
} else { | |
return '' + n; | |
} | |
} | |
function getReportDates(time) { | |
var timeZone = AdWordsApp.currentAccount().getTimeZone(); | |
var today = new Date() | |
var reportDates = new Object(); | |
switch(time) { | |
case "LAST_30_DAYS": | |
var startDate = new Date().setDate(today.getDate()-30); | |
var endDate = new Date().setDate(today.getDate()-1); | |
break; | |
case "LAST_14_DAYS": | |
var startDate = new Date().setDate(today.getDate()-14); | |
var endDate = new Date().setDate(today.getDate()-1); | |
break; | |
case "LAST_7_DAYS": | |
var startDate = new Date().setDate(today.getDate()-7); | |
var endDate = new Date().setDate(today.getDate()-1); | |
break; | |
case "TODAY": | |
var startDate = new Date().setDate(today); | |
var endDate = new Date().setDate(today); | |
break; | |
case "YESTERDAY": | |
var startDate = new Date().setDate(today.getDate()-1); | |
var endDate = new Date().setDate(today.getDate()-1); | |
break; | |
case "THIS_MONTH": | |
var startDate = new Date(today.getFullYear(), today.getMonth(), 1); | |
var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate()); | |
var lastPossibleDate = new Date(today.getFullYear(), today.getMonth()+1, 0); | |
var reportLastPossibleDate = Utilities.formatDate(lastPossibleDate, timeZone, "yyyyMMdd"); | |
reportDates.reportLastPossibleDate = reportLastPossibleDate; | |
reportDates.lastPossibleDate = lastPossibleDate; | |
break; | |
case "LAST_MONTH": | |
var startDate = new Date(today.getFullYear(), today.getMonth()-1, 1); | |
var endDate = new Date(today.getFullYear(), today.getMonth(), 0); | |
//var niceStartDate = Utilities.formatDate(new Date(startDate), timeZone, "yyyy-MM-dd"); | |
//var niceEndDate = Utilities.formatDate(new Date(endDate), timeZone, "yyyy-MM-dd"); | |
break; | |
case "THIS_WEEK_SUN_TODAY": | |
var tempDate = new Date(); | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay())); | |
var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate()); | |
break; | |
case "THIS_WEEK_MON_TODAY": | |
var tempDate = new Date(); | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() + 1)); | |
var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate()); | |
break; | |
case "LAST_WEEK": // mon-sun | |
var tempDate = new Date(); | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 6)); | |
var tempDate = new Date(); | |
var endDate = new Date(tempDate.setDate(today.getDate() - today.getDay())); | |
break; | |
case "LAST_WEEK_SUN_SAT": // mon-sun | |
var tempDate = new Date(); | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 7)); | |
var tempDate = new Date(); | |
var endDate = new Date(tempDate.setDate(today.getDate() - today.getDay() -1)); | |
break; | |
case "LAST_BUSINESS_WEEK": // mon-fri | |
var tempDate = new Date(); | |
var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 6)); | |
var tempDate = new Date(); | |
var endDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 2)); | |
break; | |
} | |
var niceStartDate = Utilities.formatDate(new Date(startDate), timeZone, "yyyy-MM-dd"); | |
var niceEndDate = Utilities.formatDate(new Date(endDate), timeZone, "yyyy-MM-dd"); | |
if(DEBUG == 1) Logger.log("start date: " + niceStartDate); | |
if(DEBUG == 1) Logger.log("end date: " + niceEndDate); | |
var reportStartDate = Utilities.formatDate(new Date(startDate), timeZone, "yyyyMMdd"); | |
var reportEndDate = Utilities.formatDate(new Date(endDate), timeZone, "yyyyMMdd"); | |
reportDates.startDate = startDate; | |
reportDates.endDate = endDate; | |
reportDates.niceStartDate = niceStartDate; | |
reportDates.niceEndDate = niceEndDate; | |
reportDates.reportStartDate = reportStartDate; | |
reportDates.reportEndDate = reportEndDate; | |
return(reportDates); | |
} | |
/* | |
// Function sendEmailNotifications (emailAddresses, subject, body, emailType ) | |
// ------------------------------------------------- | |
// emailType can be: notification or warning | |
// Adds the correct text to both the subject and body to indicate if this is a warning or notification | |
// Adds text to indicate if the script ran in preview mode | |
*/ | |
function sendEmailNotifications(emailAddresses, subject, body, emailType ) { | |
if(emailType.toLowerCase().indexOf("warning") != -1) { | |
var finalSubject = "[Warning] " + subject + " - " + AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")" | |
} else if(emailType.toLowerCase().indexOf("notification") != -1) { | |
var finalSubject = "[Notification] " + subject + " - " + AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")" | |
} | |
if(AdWordsApp.getExecutionInfo().isPreview()) { | |
var finalBody = "<b>This script ran in preview mode. No changes were made to your account.</b><br/>" + body; | |
} else { | |
var finalBody = body; | |
} | |
MailApp.sendEmail({ | |
to:emailAddresses, | |
subject: finalSubject, | |
htmlBody: finalBody | |
}); | |
if(DEBUG == 1) Logger.log("email sent to " + emailAddresses + ": " + finalSubject); | |
} | |
/* | |
// function getCampaignsByName(campaignName, caseSensitive, exactMatch) | |
// -------------------------------------------- | |
// | |
// builds the query string to be used with AWQL for fetching a report where the campaignName matches | |
// | |
// campaignNameSelector.forReports | |
// campaignNameSelector.forScripts | |
// | |
// usage: if(campaignNameIncludes) var campaignNameSelectorString = getCampaignsByName(campaignNameIncludes, campaignNameCaseSensitive, campaignNameExactMatch); | |
// | |
*/ | |
function getCampaignsByName(campaignName, caseSensitive, exactMatch) { | |
var campaignNameSelector = new Object(); | |
campaignNameSelector.forReports = "Name != ''" ; | |
campaignNameSelector.forScripts = "Name != ''"; | |
if(campaignName) { | |
if(exactMatch) { | |
var operator = "="; | |
} else { | |
if (caseSensitive) { | |
var operator = "CONTAINS"; | |
} else { | |
var operator = "CONTAINS_IGNORE_CASE"; | |
} | |
} | |
if(campaignName.indexOf("'") != -1) { | |
var needle = '"' + campaignName + '"'; | |
} else { | |
var needle = "'" + campaignName + "'"; | |
} | |
campaignNameSelector.forReports = "CampaignName " + operator + " " + needle; | |
campaignNameSelector.forScripts = "CampaignName " + operator + " " + needle; | |
//Logger.log("campaignNameSelectorString: " + campaignNameSelectorString); | |
} | |
return(campaignNameSelector); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I would also know how to select specific accounts, an array of ["campaign 1", "campaign 2"] (or just "campaign 1", "campaign 2") doesn't seem to work. Nor can I get the selection for all campaigns to work with "". Looking forward to some help :-)