Keeping track of your budgets when you’re running several accounts within an MCC client or agency-side can be difficult.
We found it tedious checking client budgets daily at DemandMore, so we did what we do with any repetitive 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 versus 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, 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 need to make a copy of this template here, which is where the graph above will be outputted. Copy this URL, as you need it when setting up the script within Google Ads.
You also need to enter your monthly budget within the Google Sheet, just under the DemandMore logo.
There are four 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 on line 17. This is where the graph will be outputted.
- Specify the email for notifications: Enter the email address that you want the notifications to be sent to within the quotation marks. For example, user@DemandMore.co.uk on line 20.
- Specify the email’s subject line: This is the subject line of the email that you are sent. We usually use the format {client name} | Budget tracker on line 23.
- Specify which campaigns to include by selecting a word that 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’) on line 26.
/**
*
* The budget tracker plots spend against budget and outputs to a Google Sheet
* We recommend running the script daily
*
* Version: 1.0
* maintained by DemandMore
*
**/
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//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)
}