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!

Wednesday, 11 September 2013

Balancing Budgets with Scripts

We all know that AdWords budgets should be led by your Return on Investment.  If your AdWords Campaigns are returning a net profit you should be spending as much as is possible so that all your Campaigns hit a 100% Impression Share to maximise the chance of conversions.  However in some cases, for one reason or another, a client may request that you adhere to a fixed budget per month so it's possible you could be faced with multiple Campaigns, all of which are "Limited by Budget".  So, if you can't give the Campaigns the budget they deserve and you can't persuade the client to increase the budget, what can you do?

I was faced with this situation recently when building a new Account structure for a client.  They have a wide range of products and even just selecting the top five best sellers left me with all of the Campaigns limited by budget.  No matter how I juggled their allocated portions of the budget, no matter how I reduced CPC or paused Keywords I couldn't give any of the Campaigns a decent share without reducing the others to a budget too small to be worth running.  Since this was a new structure, all I really needed to do was give each Campaign a fair chance to perform over a period.  I considered using Automated Rules to change budgets on a daily basis but you can only do this on specific days:  I could give CampaignA a high budget on a Monday, CampaignB a high budget on a Tuesday and so on, but this wouldn't test CampaignA's potential performance on the other days of the week.  What I wanted was an automatic way to rotate the "high budget allocation" evenly through all the Campaigns over the course of a month.  By the end of that period, I should have enough data to know which Campaigns were performing and be able to apply budgets more sensibly.

Scripts and Google Docs to the rescue!

One of the most powerful features of AdWords Scripts is that they can access a Docs Spreadsheet and read data from the cells therein.  So I prepared a spreadsheet with my Campaigns in the first column and the days of the month (1-31) to the right.  In each cell I allocated a budget for the day for each Campaign.  Using this system I was able to say that CampaignA has a high budget on certain dates rather than specific days of the week.  As the month progresses (so long as your changes aren't on a 7-day cycle!) the Campaigns should receive their "high budget allocation" on different days of the week.

The full code for this script is at the bottom of this post, along with a link to a spreadsheet template you can use.

Once you've grasped the idea of using external spreadsheets, other opportunities come to mind.  You could replicate the idea of varying budgets by day of the week.  This is possible in Automated Rules but it's a lot easier to edit and set up the budgets when you can see them all laid out in a spreadsheet.  You could pause or enable Campaigns, Groups or Keywords depending on any schedule you fancy, again, much more easily read through a spreadsheet than by rules and you can run Scripts as many times a day as you like.

There are some great ideas on the Scripting Documentation pages and the Preview Option makes it easy (and safe) to test your ideas before you apply them to your Account.

So, here's the full script for my "High Budget Allocation".  You are welcome to copy this script and use it or modify it as you see fit but please bear in mind I hold no responsibility for any actions you take and you must always test the results before applying them to your Account.  Scripts are very powerful and issue no warnings so it's vital you test thoroughly before running live and check your Account immediately after a few test runs to make sure the results are what you expect.

function main() {
  var NUMCAMPAIGNS=7;  //this number needs to be +1 on your actual # of Campaigns
  var spreadsheetUrl = "SPREADSHEETURL";  //enter your spreadsheet URL here.
  var budgetSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getActiveSheet();
  var range =   budgetSheet.getRange(1, 1, NUMCAMPAIGNS, 35);
  var values = range.getValues();   // this is now a 2d array with the data in it.
  //get the day of the month as an integer and add 1 for first two columns offset
  var d = new Date();
  var dayMonth = d.getDate();
  var chooseDay=Number(dayMonth)+1;

function setBudgets(values,chooseDay,NUMCAMPAIGNS) {
  //loops througn values (spreadsheet rows) setting Campaign Budgets
  //NOTE: only lists *changes* - if the budget is already at the specified value it isn't set.
  var i=1;
  while(i<(NUMCAMPAIGNS)) {
    var campaignName=values[i][0];
    var budget=values[i][chooseDay];
    var campaign = AdWordsApp.campaigns().
      withCondition('CampaignName = "' + campaignName + '"').

A template spreadsheet you can use is here.  Please make a copy of this spreadsheet within your own Drive and then make sure you share it with the Account running AdWords.  You'll need to replace the Campaign names with your own and set your own budgets before testing.

It's too early to tell if my experiment will work, but at least I had fun making this script.