We’ve recently decided to start writing and posting some scripts here for free, and we’re going to start with a very simple request we got from someone who wanted an easy way to track month to date spend for their all the accounts in their MCC.
I was surprised that we couldn’t find anything that does exactly this already online, although there are a lot of budget tracker scripts out there so I may well have missed one. That said, this only took 10 minutes to write anyway, so no harm done if it’s a duplicate!
This script takes just three parameters: a spreadsheet URL (the URL of the Google Sheet into which the data will go), the name of the actual sheet (i.e. the tab) in the Google Sheet for the data, and the date range. It then outputs a few metrics into the sheet. Set this to run daily and you’ve got yourself an MCC-level budget tracker!
As you can see from the code below, there’s really not much to it – so feel free to tweak it by adding additional stats (hint: add a dot after account.getStatsFor(config.dateRange) and you’ll get a dropdown with all of the different stats you can add).
The way it’s currently set up the script doesn’t make any changes, so unless you start making changes to the actual logic of the script you can safely experiment. That said, it’s always worth first previewing the script before running it, and you’ll be able to see if it’s making any changes by looking on the changes tab.
Let us know if you have any suggestions for scripts or feedback on this one!
/*
_ ___ _ _ _____ ___ _
/_\ / __| | | |_ _/ _ \ (_)___
/ _ \ (__| |_| | | || (_) || / _ \
/_/ \_\___|\___/ |_| \___(_)_\___/
*/
var config = {
// Paste the full URL of the sheet you'd like to update here
spreadsheetUrl: 'https://docs.google.com/spreadsheets/d/1u4CAV_6BuCW1gKPsdfv_ytM53GwUHtlNd3ieGqZ_8A7vj8/',
// The name of the sheet you want the data to go into - make sure this is blank as the script will clear everything in the sheet every time it runs
sheetName: 'Sheet1',
// The date range for the data you'd like to pull
// Options: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH, ALL_TIME
dateRange: 'TODAY',
}
function main() {
var data = [['Account ID', 'Campaigns', 'Cost', 'Clicks', 'Conv.', 'Impr.']];
var accountIterator = AdsManagerApp.accounts().get();
while (accountIterator.hasNext()) {
var accountSelected = accountIterator.next();
AdsManagerApp.select(accountSelected);
var account = AdsApp.currentAccount();
var numCampaigns = AdsApp.campaigns().withCondition('Status != REMOVED').get().totalNumEntities();
var cost = account.getStatsFor(config.dateRange).getCost();
var clicks = account.getStatsFor(config.dateRange).getClicks();
var conversions = account.getStatsFor(config.dateRange).getConversions();
var impressions = account.getStatsFor(config.dateRange).getImpressions();
data.push([account.getCustomerId(), numCampaigns, cost, clicks, conversions, impressions]);
}
var spread = SpreadsheetApp.openByUrl(config.spreadsheetUrl);
var sheet = spread.getSheetByName(config.sheetName);
sheet.clear();
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}