[Adwords Script] Adwords budget tracker

Keeping track of your budgets when you’re running several accounts within an MCC client or agency side can be difficult.

We found checking client budgets daily tedious at Clicteq so as we did what we do with any repetitive tedious task, we automated it.

The script works by tracking your spend on a daily basis against your monthly budget. It then does two things.

  • It plots spend vs budget on a daily basis. By default, the script assumes that you want to spend your budget evenly throughout the month. If this is not the case you can manually spread your budget out throughout the month in the sheet.
  • It emails you to tell you how much of your budget you have spent. If you want updates daily then run the script daily, otherwise run the script weekly to get weekly budget updates.

The sheet that data is outputted to looks like this with the maximum budget shown by the blue line, the red line showing cumulative spend to date and the yellow line showing the average daily spend to hit the budget.

Script settings

First things first to run the script you will need to make a copy of this template here, which is where the graph above will be outputted. Copy this URL as you will need it when setting up the script within Google Ads.

You will also need to enter your monthly budget within the Google sheet just under the Clicteq logo.

There are 4 different variables at the top of the script that you can adjust:

Specify the sheet URL: Here you should enter the URL of the template that you have made a copy of. This is where the graph will be outputted

Specify the email for notifications: Enter the email address that you want the email notifications to be sent to within the quotation marks. Eg. user@clicteq.com

Specify the email’s subject line: This is the subject line of the email that you will get. We usually use the format {client name} | Budget tracker.

Specify which campaigns to include by selecting a word campaign names should contain. Leave blank (‘ ‘) to include everything. To Include certain campaigns enter them in quotation marks within the array. For example (‘campaign1′,’campaign2’).

/**
*
* The budget tracker plots spend against budget and outputs to a Google Sheet 
* We recommend running the script daily
*
* Version: 1.0
* maintained by Clicteq
*
**/

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

//Options

//Specify the sheet url
SHEETURL = 'https://docs.google.com/spreadsheets/d/16hQJI1lfI_-GuMbphuLB543IxNnl5P6na-sWKR1DbBQ/edit#gid=0'

//Specify the email for notifications
EMAIL = 'user@clicteq.com'

//Specify the email's subject line
EMAILSUBJECT = 'Cllient Budget Tracker'

//Specify which campaigns to include by selecting a word campaign names should contain. Leave blank ('') to include everything.
CAMPAIGNCONTAINS = ''

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

function getReport()
{
  if(CAMPAIGNCONTAINS != '')
  {
  var query = "SELECT Date, Cost from CAMPAIGN_PERFORMANCE_REPORT WHERE CampaignName CONTAINS " + CAMPAIGNCONTAINS +" DURING THIS_MONTH"
  }
  else
  {
    var query = "SELECT Date, Cost from CAMPAIGN_PERFORMANCE_REPORT DURING THIS_MONTH"
  }
  var rows = AdWordsApp.report(query).rows()
  
  return rows
  
}

function parseNumber(num)
{

num = num.replace(",","").toString()
  
return parseFloat(num)

}

function prepareRows(rows)
{
  var days = {}
  var today = Utilities.formatDate(new Date(), 'GMT', 'YYYY-MM-dd')
  
  while(rows.hasNext())
  {
    var row = rows.next()
  var existingDays = Object.keys(days)
    var day = row['Date']
    var cost = parseNumber(row['Cost'])
    if(day == today)
    {continue}
    
    day = day.slice(8,10)
    
    if(day.slice(0,1) == '0')
    {day = day.slice(1,2)}
 
    if(existingDays.indexOf(day) == -1)
    {
    	days[day] = cost

    }
    
    else
    {
      days[day] = days[day] + cost
    }
                    
  }
  

  return days
  
}

function daysInMonth (month, year) {
    return new Date(year, month, 0).getDate();
}

function writeToSheet(days)
{

var today = new Date()
var m = Utilities.formatDate(today, 'GMT', 'MM')
var y = Utilities.formatDate(today, 'GMT', 'yyyy')
var numOfDays = daysInMonth(m,y)

  var sheet = SpreadsheetApp.openByUrl(SHEETURL).getActiveSheet()
  var rangeFull = "D3:F33"
  var rangeString = "D3:F" + (numOfDays + 2).toFixed(0)
  var range = sheet.getRange(rangeString)
  var clientBudget = sheet.getRange("B5:B5").getValue()
  var rows = []
  var firstEntry = false
  
  sheet.getRange(rangeFull).clear()
  
  for(var i = 1; i < numOfDays + 1; i++)
  {
    var cells = []
    var cost = 0
     
    if(days[i] == undefined)
    {cells.push(i,clientBudget,'')
    rows.push(cells)
     continue
    }
    
    for(var j = 1; j<= i; j ++)
    {
      cost = cost + days[j]
     
    }
    
      if(!firstEntry)
      {
        if(days[i+1] == undefined)
        {var maxSpend = cost
        firstEntry = true}
      }
    
    cells.push(i,clientBudget,cost.toFixed(2))
    rows.push(cells)
  }

  range.setValues(rows)
  
  var spendLineString = "G3:G" + (numOfDays + 2).toFixed(0)
  var spendLineRange = sheet.getRange(spendLineString)
  spendLineRange.clear()
  spendLineRange.setFormula('E:E/'+numOfDays.toFixed(0)+'*D:D')
  
  return [maxSpend,clientBudget]
}

function main()

{

var rows = getReport()
var days = prepareRows(rows)
var data = writeToSheet(days)

var maxSpend = data[0]
var budget = data[1]
var budgetSpent = ((maxSpend/budget)*100).toFixed(0)
var accountName = AdWordsApp.currentAccount().getName()

var body = 'Your account '+ accountName + ' spent '+ budgetSpent + '% of the budget until yesterday'

MailApp.sendEmail(EMAIL, '', EMAILSUBJECT, body)

}

 

 

wesley parker
About wesley parker

Wesley is Founder and CEO at Clicteq. He currently manages a £6 Mil Adwords portfolio across a range of different sectors. He regulally features in leading search publications such as Search Engine Journal, Econsultancy and Certified Knowledge. You can follow him on Twitter or connect with him on Linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *