Google Ad Script to Manage Campaign Budgets
Today as advertisers utilizing Google Ads, it is a universal struggle to manage to a specific budget by campaign(s). Your options in the U.I. are singular: set a rule that excludes hourly. My question is why? Would it not be great to have a script to manage at the campaign level, via a sheet in 2019? It took some time but I figured it out, which you will see below. The ability to manage budgets in a single script with a sheet across multiple campaigns.
Before we continue onto the script, if you are set on rules not to say they are not great for budget management, here is an example of how to create a rule that has time setting limitations to pause or enable your campaigns based on a budget for campaigns. You can find additional details here with automated rules.
- Sign in to your Google Ads account.
- Go to the Campaigns, Ad Groups, or Keywords pages.
- Click the 3-dot icon above the statistics table.
- Select Create an automated rule.
- Select Pause or Enable from the “Type of rule” drop-down.
- Choose which Campaign Type
- To add a condition, click +ADD, under “Condition”.
- Define the frequency of your rule which is Once, Daily, Weekly or Monthly.
- Choose the type of email updates you’d like to receive on issues affecting your rule.
- Name your rule.
- Click Preview to ensure you’ve set up your rule to run the way you want. Previewing is just for verification and doesn’t make any permanent changes to your account.
- When you are done, click Save rule.
There are benefits to rules, in my mind for budget sake, not so extremely valuable. So that leaves us with paying for tools & or having a developer or engineer to build out some fancy scripts or internal platform that works with Google’s API. What if I do not have access to those resources and all I want to do is be an advertiser? I cannot, I have to learn coding and API stuff to include staying up-to-date on all of the U.I. changes and enhancements, which you can find in the PPC Hero Library.
Why you are reading and how the script works.
This script manages your budget at the campaign level by the hour via a google sheet, so you can scale. To my knowledge and I have scoured the web, this is the first for the public. I did not write the original script. Which can be found here by Google. I simply deleted a bunch of stuff with lots of trial and error over a 1 year period of time and re-worked a few things to get it to manage budgets instead of bids.
The script allows you the flexibility of setting your budgets and forgetting about them within reason. If you want to get crazy, you can do what I do and use this script with the pacing script with few custom google sheets so that you have your own budget management tool and bidder. I figured if someone can build a tool to do this stuff and make millions of dollars, there has to be a free way to do it.
I am certain, almost positive there is a better way to write this script and more than certain there is a way to do it at the MCC level to manage multiple accounts. I have not figured that out. It would be great for those that see this that are devs, and or engineers, to make adjustments to this script and share a free version at the MCC level with your own enhancements.
How to set up the script
Before you run the script here are a few things that you need to change.
- Make a new Google Sheet. This is where the script will know which campaigns to Pause or UnPause, once you have created a new sheet copy the URL.
- Ensure your Tab name is set to “Rules” if not please make sure you update this line where “Rules is changed to whatever your Tab Name is: var spreadsheetAccess = new SpreadsheetAccess(SPREADSHEET_URL, “Rules“)
- Still working in Google Sheets, update Row 2 with your account number “000-000-0000” replace with your own.
- Row 3 is the time period reference for your script to run, if you want to execute after 7 days, 1 week or a month. Reference the below table for valid inputs for this row as it relates to your goal with controlling your budgets.
- Starting in row 6, column C, you will want to add your budgets for that period
- Lastly, in row 6, column D, you will want to list your campaigns.
I hope this helps you all in better controlling your budgets. Almost forgot, you can change the campaign.pause in the script to campaign.enable and adjust the simple formula if you want to duplicate the script and create another script to enable campaigns.
Script
var SPREADSHEET_URL = "[https://docs.google.com/spreadsheets/d/1AbldNxsSPHkE0WcOY01AoM7EimF2_7XDrvI5FeLcarY/edit#gid=0]"; var spreadsheetAccess = new SpreadsheetAccess(SPREADSHEET_URL, "Rules"); var totalColumns; function main() { var columns = spreadsheetAccess.sheet.getRange(5, 2, 5, 100).getValues()[0]; for (var i = 0; i < columns.length; i ++) { if (columns[i].length == 0 || columns[i] == 'Results') { totalColumns = i; break; } } if (columns[totalColumns] != 'Results') { spreadsheetAccess.sheet.getRange(5, totalColumns + 2, 1, 1).setValue("Results"); } // clear the results column spreadsheetAccess.sheet.getRange(6, totalColumns + 2, 1000, 1).clear(); var row = spreadsheetAccess.nextRow(); while (row != null) { var budget; try { budget = parseBudget(row); } catch (ex) { logError(ex); row = spreadsheetAccess.nextRow(); continue; } var selector = AdWordsApp.campaigns(); for (var i = 2; i < totalColumns; i ++) { var header = columns[i]; var value = row[i]; if (!isNaN(parseFloat(value)) || value.length > 0) { if (header.indexOf("'") > 0) { value = value.replace(/\'/g,"\\'"); } else if (header.indexOf("\"") > 0) { value = value.replace(/"/g,"\\\""); } var condition = header.replace('?', value); selector.withCondition(condition); } } var campaigns = selector.get(); try { campaigns.hasNext(); } catch (ex) { logError(ex); row = spreadsheetAccess.nextRow(); continue; } var fetched = 0; var changed = 0; while (campaigns.hasNext()) { var campaign = campaigns.next(); var oldCost = campaign.getStatsFor("THIS_MONTH").getCost(); var action = row[0]; var newStatus; fetched ++; if (budget <= oldCost) { campaign.pause(); changed++ } } logResult("Fetched " + fetched + "\nChanged " + changed); row = spreadsheetAccess.nextRow(); } var now = new Date(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "MMM dd,yyyy HH:mm:ss")); } function parseBudget(row) { if (row[1].length == 0) { return null; } var limit = parseFloat(row[1]); if (isNaN(limit)) { throw "Bad Argument: must be a number."; } return limit;
} function logError(error) { spreadsheetAccess.sheet.getRange(spreadsheetAccess.currentRow(), totalColumns + 2, 1, 1) .setValue(error) .setFontColor('#c00') .setFontSize(8) .setFontWeight('bold');
}
function logResult(result) { spreadsheetAccess.sheet.getRange(spreadsheetAccess.currentRow(), totalColumns + 2, 1, 1) .setValue(result) .setFontColor('#444') .setFontSize(8) .setFontWeight('normal');
} function SpreadsheetAccess(spreadsheetUrl, sheetName) { this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl); this.sheet = this.spreadsheet.getSheetByName(sheetName); this.cells = this.sheet.getRange(6, 2, this.sheet.getMaxRows(), this.sheet.getMaxColumns()).getValues(); this.rowIndex = 0; this.nextRow = function() { for (; this.rowIndex < this.cells.length; this.rowIndex ++) { if (this.cells[this.rowIndex][0]) { return this.cells[this.rowIndex++]; } } return null; } this.currentRow = function() { return this.rowIndex + 5; }
}
