AdWords
2K members online now
2K members online now
Dive into advanced features like Remarketing, Flexible Bid Strategies, AdWords Editor, and AdWords Scripts
Guide Me
star_border
Reply

[SCRIPTS] Watching Campaigns budget usage

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello, I am testing new MCC script, for managing budget usage, between accounts.

Using the MCC Flexible Budgets template spreadsheet.
 
What i need, to expand possibilities of this script, with new columns for extra output data. It should be:
 
Count of days for given budget: 
Current day of budget (how many day passed):
Remaining account budget:  budgetRemaining ?
 
 
 
// Please fix the following variables if you need to reformat the spreadsheet
// column numbers of each config column. Column A in your spreadsheet has
// column number of 1, B has number of 2, etc.
var COLUMN = {
  accountId: 2,
  campaignName: 3,
  startDate: 4,
  endDate: 5,
  totalBudget: 6,
  todayBudget: 7,
};
 
// Actual config (without header and margin) starts from this row
var CONFIG_START_ROW = 5;
 
function main() {
  // Uncomment the following function to test your budget strategy function
  // testBudgetStrategy(calculateBudgetEvenly, 10, 500);
  setNewBudget(calculateBudgetWeighted);
}
 
// Core logic for calculating and setting campaign daily budget
function setNewBudget(budgetFunc) {
  var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheets()[0];
  var endRow = sheet.getLastRow();
 
  var mccAccount = AdWordsApp.currentAccount();
  sheet.getRange(2, 6, 1, 2).setValue(mccAccount.getCustomerId());
 
  for (var i = CONFIG_START_ROW; i <= endRow; i++) {
    Logger.log('Processing row %s', i);
 
    var accountId = sheet.getRange(i, COLUMN.accountId).getValue();
    var campaignName = sheet.getRange(i, COLUMN.campaignName).getValue();
    var startDate = new Date(sheet.getRange(i, COLUMN.startDate).getValue());
    var endDate = new Date(sheet.getRange(i, COLUMN.endDate).getValue());
    var totalBudget = sheet.getRange(i, COLUMN.totalBudget).getValue();
    var resultCell = sheet.getRange(i, COLUMN.todayBudget);
    
    var accountIter = MccApp.accounts().withIds([accountId]).get();
    if (!accountIter.hasNext()) {
      resultCell.setValue('Unknown account');
      continue;
    }
    var account = accountIter.next();
    MccApp.select(account);
 
    var campaignIter = AdWordsApp.campaigns()
        .withCondition('CampaignName = "' + campaignName + '"')
        .get();
    if (!campaignIter.hasNext()) {
      resultCell.setValue('Unknown campaign');
      continue;
    }
    var campaign = campaignIter.next();
 
    var today = new Date();
    if (today < startDate) {
      resultCell.setValue('Budget not started yet');
      continue;
    }
    if (today > endDate) {
      resultCell.setValue('Budget already finished');
      continue;
    }
 
    var costSoFar = campaign
        .getStatsFor(dateToString(startDate), dateToString(endDate)).getCost();
    var daysSoFar = datediff(startDate, today);
    var totalDays = datediff(startDate, endDate);
    var newBudget = budgetFunc(costSoFar, totalBudget, daysSoFar, totalDays);
    campaign.setBudget(newBudget);
    Logger.log('AccountId=%s, CampaignName=%s, StartDate=%s, EndDate=%s, ' +
               'CostSoFar=%s, DaysSoFar=%s, TotalDays=%s, NewBudget=%s',
               accountId, campaignName, startDate, endDate,
               costSoFar, daysSoFar, totalDays, newBudget);
    resultCell.setValue('Set today\'s budget to ' + newBudget);
  }
 
  // update "Last execution" timestamp
  sheet.getRange(1, 3).setValue(today);
  MccApp.select(mccAccount);
}
 
// One calculation logic that distributes remaining budget evenly
function calculateBudgetEvenly(costSoFar, totalBudget, daysSoFar, totalDays) {
  var daysRemaining = totalDays - daysSoFar;
  var budgetRemaining = totalBudget - costSoFar;
  if (daysRemaining <= 0) {
    return budgetRemaining;
  } else {
    return budgetRemaining / daysRemaining;
  }
}
 
// One calculation logic that distributes remaining budget in a weighted manner
function calculateBudgetWeighted(costSoFar, totalBudget, daysSoFar, totalDays) {
  var daysRemaining = totalDays - daysSoFar;
  var budgetRemaining = totalBudget - costSoFar;
  if (daysRemaining <= 0) {
    return budgetRemaining;
  } else {
    return budgetRemaining / (2 * daysRemaining - 1);
  }
}
 
// Test function to verify budget calculation logic
function testBudgetStrategy(budgetFunc, totalDays, totalBudget) {
  var daysSoFar = 0;
  var costSoFar = 0;
  while (daysSoFar <= totalDays + 2) {
    var newBudget = budgetFunc(costSoFar, totalBudget, daysSoFar, totalDays);
    Logger.log('Day %s of %s, new budget %s, cost so far %s',
               daysSoFar + 1, totalDays, newBudget, costSoFar);
    costSoFar += newBudget;
    daysSoFar += 1;
  }
}
 
// Return 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);
}
 
// Convert date to string of format "YYYYMMDD"
function dateToString(date) {
  return date.getFullYear() + zeroPad(date.getMonth() + 1) +
         zeroPad(date.getDate());
}
 
// Add leading 0 on month/day number with 1 digit
function zeroPad(n) {
  if (n < 10) {
    return '0' + n;
  } else {
    return '' + n;
  }
}
 
Current spreedsheet looks like this:
 
 
 
 

 

1 Expert replyverified_user

Re: [SCRIPTS] Watching Campaigns budget usage

Top Contributor
# 2
Top Contributor

Hi @Andre-lt the script is reasonably complex so it'll take some time and care to make the changes you want.  I don't have time to do this today but I'll take a look tomorrow if no-one else steps in in the meantime.

 

Jon

AdWords Top Contributor Google+ Profile | Partner Profile | AdWords Audits

Re: [SCRIPTS] Watching Campaigns budget usage

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭
Thanks Jon! Will wait for any advice