MCC Flexible Budgets

[Mindaugas Kalnius]

Hello,

I've been using Flexible Budgets - Manager Account by Google for quite some time now, with great success, set up multiple interesting tools to automate budget management and my clients became reliant on them as a main budget distribution logic for Google Ads campaigns. I've tweaked my version to make it a little better by swapping campaignName with campaignId to work easier with multiple accounts and campaigns that have name changes but recently i've ran into trouble with this script.

The error is as follows:

InputError: Invalid input: -9.27862068965517. Expected a positive number at Oa.check (adsapp_compiled:403:11) at ow.setAmount (adsapp_compiled:10548:22) at Object.<anonymous> (adsapp_compiled:19051:54)

It worked well on previous lines but when a script sees that the value is negative, expects it to be positive it then breaks the entire script, meanwhile, before the new Google Ads scripts experience it just logged in the results that the new campaign budget is {{newBudget}}, returned value of course was negative budget but it left the previously set budget and continued to run through other campaigns. At the current scripts version it does not.

I would like to have the previous functionality, to put it briefly:

Instead of breaking the script, continue with next campaign in the list.

Later on, when such an error occurs, I will attempt to create a custom function for it, I'm not yet sure what I want, if I want that campaign to be paused because it is obviously overspending or I will want to get notified by email when such an error happens.

What I am asking of you is, can you help me and look into the script, help me rewrite it into a version which would not break the script when newBudget value is negative? 

I am not that good with scripts to build this out myself as quickly as I need it.

Inserting the script below:

var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1-qwXrn3Z7Y3uVbnNEbLVbUlLNYVKInAlfXhBjtxV9aE/edit#gid=0';

// 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: 3,
  campaignId: 5,
  startDate: 7,
  endDate: 8,
  totalBudget: 9,
  results: 10
};

// Actual config (without header and margin) starts from this row
var CONFIG_START_ROW = 4;

function main() {
  // Uncomment the following function to test your budget strategy function
  testBudgetStrategy(calculateBudgetEvenly, 10, 500);
  setNewBudget(calculateBudgetEvenly);
}

// Core logic for calculating and setting campaign daily budget
function setNewBudget(budgetFunc) {
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
  var sheet = spreadsheet.getSheets()[0];

  var endRow = sheet.getLastRow();

  var mccAccount = AdsApp.currentAccount();
  sheet.getRange(1, 6).setValue(mccAccount.getCustomerId());

  for (var i = CONFIG_START_ROW; i <= endRow; i++) {
    Logger.log('Working on the line %s', i);

    var accountId = sheet.getRange(i, COLUMN.accountId).getValue();
    var campaignId = sheet.getRange(i, COLUMN.campaignId).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.results);

    var accountIter = AdsManagerApp.accounts().withIds([accountId]).get();
    if (!accountIter.hasNext()) {
      resultCell.setValue('Unrecognized Acc');
      continue;
    }
    var account = accountIter.next();
    AdsManagerApp.select(account);

    var campaign = getCampaign(campaignId);
    if (!campaign) {
      resultCell.setValue('Unrecognized Camp.');
      continue;
    }

    var today = new Date();
    if (today < startDate) {
      resultCell.setValue('Budget did not start');
      continue;
    }
    if (today > endDate) {
      resultCell.setValue('Budget ended');
      continue;
    }

  var costSoFar = campaign.getStatsFor(
      getDateStringInTimeZone('yyyyMMdd', startDate),
      getDateStringInTimeZone('yyyyMMdd', endDate)).getCost();
    var daysSoFar = datediff(startDate, today);
    var totalDays = datediff(startDate, endDate);
    var newBudget = budgetFunc(costSoFar, totalBudget, daysSoFar, totalDays);
    campaign.getBudget().setAmount(newBudget);
    Logger.log('AccountId=%s, campaignId=%s, StartDate=%s, EndDate=%s, ' +
               'CostSoFar=%s, DaysSoFar=%s, TotalDays=%s, NewBudget=%s',
               accountId, campaignId, startDate, endDate,
               costSoFar, daysSoFar, totalDays, newBudget);
    resultCell.setValue('New Daily Budget ' + newBudget);
  }

  // update "Last execution" timestamp
  sheet.getRange(1, 5).setValue(today);
  AdsManagerApp.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);
}

// Produces a formatted string representing a given date in a given time zone.
function getDateStringInTimeZone(format, date, timeZone) {
  date = date || new Date();
  timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

/**
 * Finds a campaign by name, whether it is a regular, video, or shopping
 * campaign, by trying all in sequence until it finds one.
 *
 * @param {string} campaigId The campaign id to find.
 * @return {Object} The campaign found, or null if none was found.
 */
function getCampaign(campaignId) {
  var selectors = [AdsApp.campaigns(), AdsApp.videoCampaigns(),
      AdsApp.shoppingCampaigns()];
  for(var i = 0; i < selectors.length; i++) {
    var campaignIter = selectors[i].
        withCondition('CampaignId = "' + campaignId + '"').
        get();
    if (campaignIter.hasNext()) {
      return campaignIter.next();
    }
  }
  return null;
}

/**
 * Validates the provided spreadsheet URL to make sure that it's set up
 * properly. Throws a descriptive error message if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
    throw new Error('Provide Valid URL' +
        ' Template URL.');
  }
  return SpreadsheetApp.openByUrl(spreadsheeturl);
}

Thank you!

[Mindaugas Kalnius]

I actually solved this myself.

Changed this:

campaign.getBudget().setAmount(newBudget);
    Logger.log('AccountId=%s, campaignId=%s, StartDate=%s, EndDate=%s, ' +
               'CostSoFar=%s, DaysSoFar=%s, TotalDays=%s, NewBudget=%s',
               accountId, campaignId, startDate, endDate,
               costSoFar, daysSoFar, totalDays, newBudget);
    resultCell.setValue('New Daily Budget ' + newBudget);

To this:

    if(newBudget <=0) {
       var overSpendBudget = 1;
       campaign.getBudget().setAmount(overSpendBudget);

      Logger.log('AccountId=%s, campaignId=%s, StartDate=%s, EndDate=%s, ' +
               'CostSoFar=%s, DaysSoFar=%s, TotalDays=%s, NewBudget=%s',
               accountId, campaignId, startDate, endDate,

               costSoFar, daysSoFar, totalDays, overSpendBudget);
    resultCell.setValue('Budget was overspent, new budget set to ' + overSpendBudget);
    }
       else {

         campaign.getBudget().setAmount(newBudget);
    Logger.log('AccountId=%s, campaignId=%s, StartDate=%s, EndDate=%s, ' +
               'CostSoFar=%s, DaysSoFar=%s, TotalDays=%s, NewBudget=%s',
               accountId, campaignId, startDate, endDate,
               costSoFar, daysSoFar, totalDays, newBudget);
    resultCell.setValue('New Daily Budget ' + newBudget);
       }
  }

Tested it out, seems to work just fine.

What's weird though is that the script keeps running after going through all campaigns in the list. Is there a way to end it after the last campaign id is received and last budget is updated?

[Google Ads Scripts Forum]

Reposting the last inquiry (https://groups.google.com/g/adwords-scripts/c/f2jMPGHRgyU) from the forum as it wasn't routed to our support queue.

Regards,
Yasmin
Google Ads Scripts Team

[Google Ads Scripts Forum Advisor]

Hi,

 

This is Yasmin from the Google Ads scripts team.

 

I'm glad you found a solution to your initial concern and got it working on your end. That said, as per your latest query, please be informed that Google Ads scripts is based on Apps scripts. You could opt to reach out to their support team and check if they have a functionality which could work in Google Ads scripts.

 

Regards,

 

Yasmin Gabrielle Google Ads Scripts Team

ref:_00D1U1174p._5004Q2f2qPM:ref