Wednesday, 25 September 2013

Using Every Penny with Scripts

Recently I was asked to oversee an Account with a small fixed budget and, as sometimes happens, I found that although the budget was small it wasn't always being fully spent each day.  Juggling with the daily budgets of each Campaign (as described in my previous post) wasn't an option because there really wasn't enough budget to go around but I didn't want to "lose" all the unspent budget.  While browsing through the Scripts documentation I came across the provided solution for Flexible Budgets.  This solution is designed to manage a fixed spend for a single Campaign running in a single period, but it looked ripe for adaptation to solve the problem of this small budget Account.

I've adapted the Script so that it attempts to spend an entire monthly budget across specified Campaigns, recalculating their daily budgets each time the script runs (ideally very early in the morning, e.g. 1am) to take into account the spend so far, the number of days remaining in the month and the percentage share each Campaign should receive of that remaining budget.  The script makes use of Labels to a) identify which Campaigns should be affected by the script and b) to define a percentage share.

For example, lets say you have three Campaigns A, B and C and a daily budget of $20 for September, a total monthly spend of $600.  At 1am on the 21st September these Campaigns should have spent $400 (20 days * $20) but they've actually only spent $280 so there is $320 remaining to spend in 10 days - a total budget of $32 per day.  To use the script below, you'd label each of these Campaigns with one label named "FlexBudget" and another defining a percentage for each Campaign, let's say we give A 50%, B 30% and C 20% (you should apply Labels without % signs - just the number).  When the script runs in this situation at 1am on the 21st September it would set the following budgets:

Campaign A - $16
Campaign B - $9.60
Campaign C - $6.40

which, if all is well, should equal $32!

This script will only be useful if you're working with a fixed budget per month and only when there's usually "unspent" monies and only when you can't clearly predict spend patterns for each Campaign but if it's not directly useful, it may give you ideas for modifications.

Note that you must label each Campaign you wish to be affected with "FlexBudget" and a "percentage" Label, e.g. 35.  If your percentages don't add up to 100 (either more or less) the script will still run but your spend won't be accurate so take care adding up the numbers.  The Script does include the option to "ignore" other labels (line 40) so take care here as well.

As always, you should check the operation of the script with a preview before running or scheduling and should check the results after running.  You'll probably want to adjust the percentage figures over time and to do this you'll need to create new Labels - a small pain but Labels are easy to create and delete.

Complete Script as follows:

/* Modified Flexible Budget Script 
** @param (number) TOTAL_BUDGET - the total sum to be spent in a single month
** Schedule to run around 1am (before "day" starts)

var TOTAL_BUDGET = 600;

var d = new Date();
var thisMonth=d.getMonth();
var thisYear=d.getFullYear();
// DAYS_SO_FAR is -1 because "today" hasn't happened yet
var DAYS_SO_FAR = d.getDate() - 1;
var TOTAL_DAYS = daysInMonth(thisMonth + 1, thisYear);

var START_DATE = new Date(thisYear, thisMonth, 1);
var END_DATE = new Date(thisYear, thisMonth, TOTAL_DAYS);

function main() {
  setNewBudget(calculateBudgetEvenly, TOTAL_BUDGET, START_DATE, END_DATE);

function setNewBudget(budgetFunction, totalBudget, start, end) {
  var costSoFar = 0;
  var campaignsToSet = [];
  var campaigns = AdWordsApp.campaigns()
    //operate only on Campaigns labelled with "FlexBudget"
    .withCondition('LabelNames CONTAINS_ANY ["FlexBudget"]')
  while(campaigns.hasNext()) {
    var thisCost = campaign.getStatsFor(dateToString(start), dateToString(end)).getCost();
    costSoFar += thisCost;
  for(var i=0;i
    var thisCampaign=campaignsToSet[i];
      // Ensure only the "percentage" Label is selected - you may need to add others if you use them
      .withCondition('Name NOT_IN ["FlexBudget"]')
    var percentage =;
    var newBudget = budgetFunction(costSoFar, totalBudget, percentage);

function calculateBudgetEvenly(costSoFar, totalBudget, percentage) {
  var daysRemaining = (TOTAL_DAYS - DAYS_SO_FAR);
  //set budget based upon percentage Label
  var budgetRemaining = (totalBudget - costSoFar)*(percentage / 100);
  if (daysRemaining <= 0) {
    return budgetRemaining;
  } else {
    return budgetRemaining / daysRemaining;

function daysInMonth(month, year) {
  //returns number of days in a given month of a given year
  return new Date(year, month, 0).getDate();

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;

Happy Scripting!
Post a Comment