AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Ads
4.1K members online now
4.1K 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