06 juni 2014

Adwords script: Budget vs Spend rapportage

Dit Adwords script stuurt periodiek een e-mailrapportage van Budget vs Spend per account: het laat zien of je op koers bent om het afgesproken budget te besteden. Indien dit niet het geval is (overspend/underspend) wordt dit aangegeven en krijg je een suggestie voor een nieuw dagbudget. Het kan zowel worden gebruikt in MCC's als individuele accounts.

! De nieuwste versie van dit script vind je hier: http://remkovanderzwaag.nl/blog/adwords-script-budget-vs-spend-rapportage !

Aanpassingen

  • 12 juni 2014: de twee scripts zijn omgebouwd naar één Hybrid script

Waarom heb je dit script nodig?

  • Wanneer je accounts voor klanten managed middels een Adwords-MCC, wil je continu weten hoe je er per account voor staat qua Ad Spend. Maar het script werkt ook voor losse accounts.
  • Google geeft je geen updates over het verloop van account-budgetten in de maand. Je hebt standaard dus weinig zicht op je Ad Spend. 
  • Dit script geeft inzicht in je bestedingspatroon per account: het stuurt periodiek een e-mailrapportage met een overzicht van Spend vs Budget. Hierin wordt per account tevens een nieuw dagbudget geadviseerd voor de rest van de maand.

Scripts

Hoe werkt het script?

  • Dit is een 'Hybrid' script. Het werkt zowel op MCC-niveau (voor alle accounts genoemd in de spreadsheet) of op account niveau (voor het huidige account).
  • Het script kijkt, adhv een in te stellen budget, per account naar de gewenste Target Spend (wat je op dat moment had mogen uitgeven) vs Actual Spend (wat je in werkelijkheid hebt uitgegeven) en stuurt een e-mailrapportage wanneer de afwijking (die je per account kunt instellen) te groot is. Oftewel: wanneer op een bepaald moment te veel of te weinig is gespendeerd.
  • Tenzij je voor een Adwords-account gebruik maakt van Budgetorders (wat vaak om administratieve redenen niet zo is), kun je binnen Adwords geen maandbudget opgeven. 
  • Deze informatie dien je daarom zelf op te geven: voor MCC's in een Google Spreadsheet (bekijk een voorbeeld), voor accounts in het script zelf. Hier kun je ook de marges aangeven: hoeveel procent mag je Spend afwijken van Budget voordat het wordt beschouwd als overspend/underspend. Wanneer in de spreadsheet geen marge is opgegeven, worden de defaults uit het script gebruikt. 
  • Momenteel worden alleen budgetten per maand ondersteund.
  • Actual Spend (uitgaven) wordt opgehaald op het exacte moment dat het script draait.
  • Target spend wordt berekend op basis van UTC tijd, afgerond op het huidige uur. Afhankelijk van de zomertijd wijkt die 1 tot 2 uur af van de locale tijd. 13.48 wordt dus berekend alsof het 12 uur is (in wintertijd).

MCC's

  • Het script checkt binnen de spreadsheet de Klant-ID's (fixed op kolom B) en zoekt hier de juist informatie bij (Actual Spend), samen met het opgegeven Budget wordt de rest van de waarden berekend. Let op: Google Scripts is momenteel beperkt tot het kunnen afhandelen van 50 accounts per run. Je kunt dus max 50 accounts in de spreadsheet plaatsen (de rest wordt simpelweg niet verwerkt).
  • Voor (grote) MCC's is een importfunctie beschikbaar; hiermee importeer je al je Klantnamen en Klant-ID's automatisch in het juiste format in een Google Spreadsheet. Hiervoor dien je 1) op Google Drive een Spreadsheet aan te maken met schrijfrechten, 2) het Spreadsheet-ID te kopieren in het script, 3) prefillSpreadsheet = true in te stellen en 4) een voorbeeld van het script uit te voeren. Wanneer de import is gelukt, zet je prefillSpreadsheet = false.

Accounts

  • Het budget wordt uit het script gehaald (defaultBudget, zie onder) of uit de Spreadsheet, maar die heb je op accountniveau eigenlijk niet nodig.

Adwords Budget vs Spend report

Instellingen

Script-variabelen

  • spreadsheetId: het ID van de Google Spreadsheet. Je vindt deze in de URL: https:// docs.google.com/a/remkovanderzwaag.nl/spreadsheets/d/1jiYkI6xWOnJMqxIil67o0qPu5AhxCNFHp6e7uaJ4dLo/edit
  • prefillSpreadsheet: zet op true wanneer je alle MCC-accounts automatisch wilt importeren in je Spreadsheet, draai vervolgens een Voorbeeld en zet weer terug naar false. Let op: Adwords Scripts is beperkt tot het verwerken van maximaal 50 accounts.
  • emailAddr: e-mailadres waar het rapport naartoe moet worden gestuurd.
  • emailSubject: onderwerp van de e-mail.
  • onlyReportProblems: zet op true wanneer je in de rapportage alleen accounts wilt zien waar actie op moet worden ondernomen (die buiten de ingestelde marges vallen). Zet op false als je altijd alle accounts wilt rapporteren.
  • ignoreNoBudgetCampaigns: zet op true (default) wanneer je alleen campagnes wilt zien waarvoor je een budget hebt ingevuld in de spreadsheet. 
  • alwaysReport: zet op true wanneer je altijd een rapportage wilt ontvangen, zelfs als er geen accounts zijn die een actie behoeven. Werkt alleen wanneer onlyReportProblems = false.
  • defaultBudget: het default budget voor accounts in de spreadsheet. Voor MCC's: aangezien budgetten meestal van account tot account varieren, wordt geadviseerd dit op 0 te zetten. Wanneer je het script op account-niveau gebruikt, kun je hier je budget instellen.
  • defaultOver: het percentage (10% = 0.1) dat de Actual Spend over de Target Spend moet gaan voordat het wordt gerapporteerd als een warning. Wordt gebruikt als default voor alle accounts in de spreadsheet.
  • defaultUnder: het percentage (10% = 0.1) dat de Actual Spend onder de Target Spend moet gaan voordat het wordt gerapporteerd als een warning. Wordt gebruikt als default voor alle accounts in de spreadsheet.

Overig

  • Script-planning: het script gaat uit van een lineair bestedingspatroon en houdt dus geen rekening met seasonality, trends etc. Mede hierom wordt het geadviseerd het script wekelijks te draaien (en niet dagelijks).

Hybrid script

! De nieuwste versie van dit script vind je hier: http://remkovanderzwaag.nl/blog/adwords-script-budget-vs-spend-rapportage !


//------------------------------------------------
// Report on Budget vs Spend (Hybrid)
// Created by: Remko van der Zwaag & PDDS
// remkovanderzwaag.nl & pdds.nl
// More info (Dutch): http://goo.gl/11ThK4
// CHANGELOG
// 12-06-2014: Combined two separate scripts into one Hybrid version (for MCC and accounts)
//------------------------------------------------

var spreadsheetId = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';// Google Spreadsheet with account info, only for MCC
var prefillSpreadsheet = false;                                    // When set to true, gets all accounts from the MCC account
                                                                   // and automagically adds their name and id to the spreadsheet
                                                                   // Use once, doesn't check for existing records
                                                                   // switch back to false after use
                                                                   // PREFERABLY RUN USING PREVIEW (true), CHANGE TO false AND SAVE

var emailAddr = "your@email.com";                                  // Where you want the report sent, only works when
                                                                   // prefillSpreadsheet = false
var emailSubject = "Adwords spend report";                         // What the email should be titled
var onlyReportProblems = false;                                    // Email will only contain accounts with errors
var ignoreNoBudgetCampaigns = true;                                // Never show campaigns with a 0 budget
var alwaysReport = false;                                          // Send an email even if no accounts need to be reported on
                                                                   // For use in combination with the previous options

// Report colors
var overspendColor = '#d09289';
var underspendColor = '#78e08a';

// These are default values. Values found in the spreadsheet will supercede these.
var defaultBudget = 0;    // Budget for the month in the AdWords account default currency
var defaultOver = 0.1;    // The amount the current tally can go over the running target budget (incl. today) before a warning is sent
var defaultUnder = 0.1;   // The amount the current tally can be under the running target budget. Set to 0 to ignore
                          // both are decimals representing percent: 0.1 = 10%, 1 = 100%, etc

// PLEASE DON'T TOUCH BELOW THIS LINE

function main() {
  try {
    // Uses parallel execution. Is limited to 50 accounts by Google.
    if (prefillSpreadsheet) {
      MccApp.accounts()
        .withLimit(50)
        .executeInParallel("getSSAccountInfo","saveSSAccountInfo");
    } else {
      MccApp.accounts()
        .withIds(getSpreadsheetIds())
        .withLimit(50)
        .executeInParallel("processAccount", "processReports");
    }
  } catch (e) {
    processReport(processAccount());
  }
}

// Get account name and id
function getSSAccountInfo() {
  var result = {
    custId: AdWordsApp.currentAccount().getCustomerId(),
    cust: AdWordsApp.currentAccount().getName()
  };
  Logger.log(result);
  return JSON.stringify(result);
}

// Save account info to the spreadsheet
function saveSSAccountInfo(response) {
  var ss;
  try {
    ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
  }
  ss = ss.getSheets()[0];
  ss.appendRow(["Account Name", "Account ID", "Budget", "Overspend Ratio", "Underspend Ratio"]);
  for (var i in response) {
    if(!response[i].getReturnValue()) { continue; }
    var rep = JSON.parse(response[i].getReturnValue());
    Logger.log(rep);
    ss.appendRow([rep.cust, rep.custId]);
  }
}

function processAccount() {
  var accountInfo = getAccountInfo(),
      currency = AdWordsApp.currentAccount().getCurrencyCode(),
      accountId = accountInfo.custId,
      budget = accountInfo.budget,
      over = accountInfo.over,
      under = accountInfo.under,
      account = accountInfo.cust;

  var cost = 0,
      date = new Date();

  // There is no good way to use the account's timezone
  // We use UTC, because it's close enough for use in Europe
  // The standard timezone is most likely PST

  // We adjust the current date so the amount of the day that
  // has past is taken into account when setting a target
  // Otherwise, running early in the day gives up to a day of
  // extra budget, even though there has been no opportunity to spend it.
  var today = date.getUTCDate() - (1 - date.getUTCHours()/23),
      days = 32 - new Date(date.getFullYear(), date.getMonth(), 32).getUTCDate();

  // This is a pretty naive way to plot this information
  // But unless your spending is significantly weighted within a month
  // it should be a decent predictor
  var partOfMonth = today/days,  // How far we are in the month
      maxInclTodayNoOver = partOfMonth * budget,      // The part of the budget allotted to the part of the month that has passed
      maxInclToday = maxInclTodayNoOver * (1 + over), // The amount of money that has to be spent to warrant a warning mail
      minInclToday = maxInclTodayNoOver * (1 - under);

  var output = [];

  output.push('Account: ' + account);

  // Get the campaigns for your account
  // You can add some conditions here, to limit the accounts counted etc.
  var campaignIterator = AdWordsApp.campaigns().get();

  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    // We only look for costs made in the current month
    var stats = campaign.getStatsFor("THIS_MONTH");
    cost += stats.getCost();
  }

  var diff = 0;
  if (cost > maxInclToday) diff = 1;
  if (cost < minInclToday && under !== 0) diff = -1;

  var diffLabel = {
    '-1': 'Underspend',
    '0': 'Within margins',
    '1': 'Overspend'
  };

  var remainingBudget = budget - maxInclTodayNoOver,
      delta = cost - maxInclTodayNoOver,
      daysLeft = days - today;

  // Format results as an object. processReports decides what to do
  var result = {
    reportable: (diff !== 0),
    cust: account,
    custId: accountId,
    budget: fMoney(currency, budget),
    budgetInt: budget,
    diff: diff,
    status: diffLabel[diff],
    target: fMoney(currency, maxInclTodayNoOver),
    actual: fMoney(currency, cost),
    delta: fMoney(currency, delta) + ' (' + twoDecPerc(delta/maxInclTodayNoOver) + ')',
    recommend: fMoney(currency, ((budget - cost) / daysLeft)),
  };

  return JSON.stringify(result);
}

// Process the results of a single
// Creates table, exports as html and sends to set emailaddress
function processReport(report) {
  // Define table(headers)
  var table = buildTable();
  rep = JSON.parse(report);

  // Skip campaign if budget is 0 and ignoreNoBudgetCampaigns is on
  if (ignoreNoBudgetCampaigns && rep.budgetInt === 0) { return; }
  // Only show records that have over/underspend if onlyReportProblems is true
  if (onlyReportProblems === false || rep.reportable === true) {
    var attrs = {};
    if (rep.diff === -1) { attrs.style = 'background-color: ' + underspendColor; }
    else if (rep.diff === 1) { attrs.style = 'background-color: ' + overspendColor; }
    add_row(table, rep, attrs);
  }
  sendEmail(table);
}

// Process the results of all the accounts
// Creates table, exports as html and sends to set emailaddress
function processReports(reports) {
  // Define table(headers)
  var table = buildTable();
  for (var i in reports) {
    if(!reports[i].getReturnValue()) { continue; }
    var rep = JSON.parse(reports[i].getReturnValue());
    // Skip campaign if budget is 0 and ignoreNoBudgetCampaigns is on
    if (ignoreNoBudgetCampaigns && rep.budgetInt === 0) { continue; }
    // Only show records that have over/underspend if onlyReportProblems is true
    if (onlyReportProblems === false || rep.reportable === true) {
      var attrs = {};
      if (rep.diff === -1) { attrs.style = 'background-color: ' + underspendColor; }
      else if (rep.diff === 1) { attrs.style = 'background-color: ' + overspendColor; }
      add_row(table, rep, attrs);
    }
  }
 sendEmail(table);
}

function sendEmail(table) {
   // Only send if there is something to report, or alwaysReport is set.
  if (alwaysReport || table.rows.length > 0) {
    var htmlBody = '<' + 'h1>' + emailSubject + '<' + '/h1>' + render_table(table, {border: 1, width: "95%", style: "border-collapse:collapse;"});
    MailApp.sendEmail(emailAddr, emailSubject, emailSubject, { htmlBody: htmlBody });
  }
}

function buildTable() {
  return create_table({
    cust: 'Customer',
    custId: 'Customer-ID',
    budget: 'Budget',
    status: 'Status',
    target: 'Target spend',
    actual: 'Actual spend',
    delta: 'Delta',
    recommend: 'Recommended daily spend',
  });
}

// Few formatting functions
function twoDec(i) {
  return parseFloat(i).toFixed(2);
}

function twoDecPerc(p) {
  return twoDec(p * 100) + '%';
}

function fMoney(currency, amount) {
  return currency + ' ' + twoDec(amount);
}

function getSpreadsheetIds() {
  var ids = [],
      ss,
      reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;
  
  try {
    ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
    return ids;
  }
  ss = ss.getSheets()[0];
  var rows = parseInt(ss.getLastRow());
  var range = ss.getRange("B1:B" + rows).getValues();
  for (var i = 0; i < rows; i++) {
    if (!reAWId.test(range[i][0])) {
      continue;
    }
    ids.push(range[i][0]);
  }
  return ids;
}

// Fetch info for current account from the spreadsheet
// MCC scripts don't seem to support shared state between
// Parallel executions, so we need to do this fresh for every account

// Uses default info from 'defaults' set in script, and replaces with
// values from spreadsheet where possible
function getAccountInfo() {
  var ss;
  var reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;
  var protoAccount = {
    custId: AdWordsApp.currentAccount().getCustomerId(),
    cust: AdWordsApp.currentAccount().getName(),
    budget: defaultBudget,
    over: defaultOver,
    under: defaultUnder
  };

  try {
    ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
    return protoAccount;
  }
  ss = ss.getSheets()[0];
  var rows = parseInt(ss.getLastRow());
  var range = ss.getRange("A1:E" + rows).getValues();

  for (var i = 0; i < rows; i++) {
    if (!reAWId.test(range[i][1]) || range[i][1] !== protoAccount.custId) {
      continue;
    }
    var account = {
      custId: range[i][1],
      cust: range[i][0],
      budget: range[i][2],
      over: range[i][3],
      under: range[i][4]
    };
    for(var key in account) {
      if (account[key] === '') {
        account[key] = protoAccount[key];
      }
    }
    return account;
  }
  return protoAccount;
}

// Instantiate a table object with given column names
// Either as array or object/hash
function create_table(cols) {
  var table = { head: [], rows: [], row_attrs: [], row_names: undefined};
  if (cols instanceof Array) {
    table.head = cols;
  } else if (cols instanceof Object) {
    var i = 0;
    table.row_names = {};
    for (var key in cols) {
      table.head.push(cols[key]);
      table.row_names[key] = i;
      i++;
    }
  }
  return table;
}

// Add a row to the table object
// Either an clean array or an object
// with correct parameter names
function add_row(table, row, attrs) {
  if (row instanceof Array) {
    table.rows.push(row);
    return;
  }
  if (table.row_names === undefined) {
    return;
  }
  var new_row = [];
  for (var key in row) {
    if (table.row_names[key] === undefined) {
      continue;
    }
    new_row[table.row_names[key]] = row[key];
  }
  table.rows.push(new_row);
  table.row_attrs.push(attrs);
}

// Log the contents of the table object in a semi readable format
function log_table(table) {
  Logger.log('----------------------------------');
  Logger.log(table.head.join(' | '));
  Logger.log('----------------------------------');
  for (var i in table.rows) {
    Logger.log(table.rows[i].join(' | '));
  }
  Logger.log('----------------------------------');
}

// Turn the table object into an HTML table
// Add attributes to the table tag with the attrs param
// Takes an object/hash
function render_table(table, attrs) {
  function render_tag(content, tag_name, attrs) {
    var attrs_str = '';
    if (attrs instanceof Object) {
      for (var attr in attrs) {
        attrs_str += [' ',attr,'="', attrs[attr], '"'].join('');
      }
    }
    var tag = ['<' + tag_name + attrs_str + '>'];
    tag.push(content);
    tag.push('<!--' + tag_name + '-->');
    return tag.join('');
  }
  function render_row(row, field, row_attrs) {
    if (field === undefined) {
      field = 'td';
    }
    var row_ar = new Array(table.head.length);
    for (var col in row) {
      row_ar.push(render_tag(row[col], field, row_attrs));
    }
    return render_tag(row_ar.join(''), 'tr');
  }
  var table_ar = [];
  table_ar.push(render_row(table.head, 'th'));
  for (var row in table.rows) {
    table_ar.push(render_row(table.rows[row], 'td', table.row_attrs[row]));
  }
  return render_tag(table_ar.join(''), 'table', attrs);
}

Deze scripts zijn ontwikkeld in samenwerking met pdds.

Facebook Like Google Plus One Linkedin Share Button
Adwords scripts

Gerelateerde berichten: