Budget Automation Tool

Overview

Spreadsheet-based tool that can be used to automate simple campaign budgets in Google Ads. There are four different ways that the budgets can be calculated – more details below. The sheet both pulls data and posts changes to the AdWords account with two scripts that can be found below. Would not be effected by any Supermetrics outages :)

Note that this should be continuously monitored after first implementing and (ideally) used in combination with the budget capping script to ensure that you don't go overbudget. If it's linked to the Budget Tracker, you can just check on it in the report, like any other account, though!

And all budgeting strategies will let you pick and choose how much of the budget is up for automation and which campaigns are eligible to have their budgets changed (so you can leave out $500/month for Brand campaigns, for example, and those will never have their daily budgets changed by this tool).

Make A Copy Of The Sheet

Getting Set Up

- - Make a copy of the sheet using the button above
  - On the "General Settings Tab," you're going to do the following:
    - - Under "Link Authorizations," follow the instructions in the red cells. They will turn green when everything's all set.
      - Select an account from the "Select Account" dropdown. Note that this will really only be used for matching up budgets listed in the budget tracker document. If you don't want to use those budgets, you can select a different way for budgets to be calculated (below) or manually add the budget in the "Budget Settings" tab.
      - Select the way that you want budgets to be calculated in the "Select Budget Type" dropdown. More info on each of these different ways of calculating the budget at the bottom of the page.
        Pre-Set Weights By Campaign:
        Using Budget From Budget Tracker Sheet: 25% for Campaign X, 45% for Y, 30% for Z
        Pre-Set Budgets By Campaign:
        Manually set budgets by campaign with a Start & End Date.
        You can set the Start & End Date to "Month Start" and "Month End" if the campaign is going to continue to spend on an ongoing basis.
        Pre-Set Weights By Campaign & Week Of Month:
        Using Budget From Budget Tracker Sheet: 10% for Week 1, 20% for Week 2, 30% for Week 3, 40% for Week 4 AND (for each week) 25% for Campaign X, 45% for Y, 30% for Z
        Performance-Weighted Budgets:
        Semi-weighted combination of Impressions, Clicks, Conversions, Conversion Value, and Cost with a minimum allocation that you can set.
      - Google Ads Script time! Just need to copy and paste two scripts into your account and change the URLs in the script to the URL of your new sheet. Instructions below.

Google Ads Scripts

To get started, go to your Google Ads account, click Tools > Bulk Actions > Scripts

You'll be creating two new scripts. The instructions are below!

Campaign Reporting Script

This script will just download both data from this month and the last 90 days to the spreadsheet. This will provide the most up-to-date info for Campaign Names, performance, and Campaign Status when all the calculations are run and the upload is assembled.

You just need to create a new script, name it "Campaign Reporting Script" (or whatever), paste in the code below, and swap the SPREADSHEET_URL_HERE_LEAVE_QUOTES_IN_PLACE code below with the full URL of your spreadsheet.

Once that's done, hit "Save," then "Run" > "Run Without Preview," then "Close." Running this script will just update the data on the spreadsheet and allow you to make edits to the budget calculation settings in the next section.

On the script page, set the "Frequency" of this script to Daily at 8am and you're all set!

Note: it doesn't actually matter what time these scripts run – or even how frequently they run, as long as the reporting script goes off an hour before the upload script!

// Reference: https://developers.google.com/adwords/api/docs/guides/awql

var SheetURL = "SPREADSHEET_URL_HERE_LEAVE_QUOTES_IN_PLACE";

var ThisMonthTab = "Campaign Performance | This Month";

var LastNinetyTab = "Campaign Performance | Last 90 Days";

// GET LAST 90 Days

var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

var now = new Date();

var startDate = new Date(now.getTime() - 90 * MILLIS_PER_DAY);

var endDate = new Date(now.getTime() - 0 * MILLIS_PER_DAY);

var from = Utilities.formatDate(startDate, "GMT-5", "yyyyMM")+'01';

var to = Utilities.formatDate(endDate, "GMT-5", "yyyyMMdd");

Logger.log(from);

Logger.log(to);

var DateRange = from+','+to;

var QUERIES = [

{'query' : 'SELECT CampaignId, CampaignName, Labels, ServingStatus, CampaignStatus, BiddingStrategyType, EnhancedCpcEnabled, Amount, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, CostPerConversion, ConversionValue ' +

'FROM CAMPAIGN_PERFORMANCE_REPORT ' +

'WHERE CampaignStatus = "ENABLED" AND ServingStatus = "SERVING" ' +

'DURING THIS_MONTH',

'spreadsheetUrl' : SheetURL,

'tabName' : ThisMonthTab,

'reportVersion' : 'v201809'

'FROM CAMPAIGN_PERFORMANCE_REPORT ' +

'WHERE CampaignStatus = "ENABLED" AND ServingStatus = "SERVING" ' +

'DURING ' + DateRange,

'spreadsheetUrl' : SheetURL,

'tabName' : LastNinetyTab,

'reportVersion' : 'v201809'

}

];

function main() {

for(var i in QUERIES) {

var queryObject = QUERIES[i];

var query = queryObject.query;

var spreadsheetUrl = queryObject.spreadsheetUrl;

var tabName = queryObject.tabName;

var reportVersion = queryObject.reportVersion;

//Logger.log(spreadsheetUrl + " " + query);

var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

var sheet = spreadsheet.getSheetByName(tabName);

var report = AdWordsApp.report(query, {apiVersion: reportVersion});

report.exportToSheet(sheet);

}

Upload Script

This script will take the budgets output by the calculations on the "Set Budget" tab (it's hidden) and post them to the account like the Google Ads Editor.

You just need to create a new script, name it "Upload Script" (or whatever), paste in the code below, and swap the SPREADSHEET_URL_HERE_LEAVE_SINGLE_QUOTES_IN_PLACE code below with the full URL of your spreadsheet.

Once that's done, hit "Save," then "Close." On the script page, set the "Frequency" of this script to Daily at 9am and you're all set!

function main() {

// The format of this spreadsheet should match a valid bulk upload template.

// See https://developers.google.com/google-ads/scripts/docs/features/bulk-upload

// for the list of supported bulk upload templates.

var SPREADSHEET_URL = 'SPREADSHEET_URL_HERE_LEAVE_SINGLE_QUOTES_IN_PLACE';

var spreadSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

var sheet = spreadSheet.getSheetByName("Set Budget");

var upload = AdsApp.bulkUploads().newFileUpload(sheet);

upload.forCampaignManagement();

// Use upload.apply() to make changes without previewing.

upload.apply();

}

Budget Automation Methods

- - After the "General Settings" tab has the links approved, an account selected, a budget method selected, and you've placed the two scripts into the Google Ads account, you can move onto the "Budget Settings" tab.
    - - This tab should automatically hide all columns that are applicable to the other bidding strategies that you've selected. It may take up to 30 seconds to do this when you first create a copy of the sheet.
        If, for some reason, this sheet isn't showing the right Budgeting Strategy name at the top of the page: you can go to Tools > Script Editor
        From there, hit: Run > Run Function > onEdit
        It'll ask you for permissions, and once you're done you can close the script window and go back to the sheet.

Pre-Set Weights By Campaign:

- - - Using Budget From Budget Tracker Sheet: 25% for Campaign X, 45% for Y, 30% for Z
      - Note that the percentages that you assign won't actually have to add to 100. The calculation will just divide the updated total daily budget proportionally based on the total of all values listed in that range.
    - You can also just manually enter in a budget for the month in the "Budget Settings" tab where the link to the Budget Tracker will list this month's budget. But note that you'll have to change this every month.
    - And, finally, you can set aside a certain Dollar amount for the account up-front. Only the campaigns that you list out in the Budget Settings will be up for automation and get their budgets changed. Any not listed there will spend the budgets manually entered in AdWords and nothing else. Be sure to make this value a NEGATIVE value if you want it REMOVED from the total.
    - Ideal for accounts with a fixed set of campaigns that won't be changing frequently and that need to be balanced in a certain percentage of spend structure. These percentages can be updated any time manually.

Pre-Set Budgets By Campaign:

- - - Manually set budgets by campaign with a Start & End Date.
    - You can set the Start & End Date to "Month Start" and "Month End" if the campaign is going to continue to spend on an ongoing basis.
    - Campaign duration can only be 90 days or less for the current structure. If the window needs to be expanded, you can edit the Reporting script (see below) to expand the number of days considered by changing the days reported from "90" in the line below to the minimum duration needed:
      - var startDate = new Date(now.getTime() - 90 * MILLIS_PER_DAY);
    - Note: for campaigns with Start and End Dates, lifetime spend of the campaign within the last 90 days will be considered. So if the campaign spent $100 before the Start Date, that $100 will still be included in the calculation and your campaign will actually come up $100 short at the end date. So be sure to set the start date as far back as is relevant and include any historical spend on top of the budget if that might be an issue for your campaign.
    - Note: this will not impact campaigns that have gone past their end date. The only changes that get posted are budget changes to active campaigns (Campaign Status cannot be changed by this tool).
    - Ideal for theatres, YMCAs, or any account with lots of structured, fixed campaigns that are going on and off on a routine basis with a set budget to hit.

Pre-Set Weights By Campaign & Week Of Month:

- - - Using Budget From Budget Tracker Sheet: 10% for Week 1, 20% for Week 2, 30% for Week 3, 40% for Week 4 AND (for each week) 25% for Campaign X, 45% for Y, 30% for Z
    - This essentially works the same as the first option, but you can weight budgets by week in the automation to spend more during certain weeks of the month.
    - Note: technically this actually isn't calculating by the week of the month. Each "week" represents 7.5 days during the month and the budgets will be updated accordingly based on the percentage of the month's budget that it's trying to hit by the 7.5th day.

Performance-Weighted Budgets:

- - - Semi-weighted combination of Impressions, Clicks, Conversions, Conversion Value, and Cost with a minimum allocation that you can set.
    - The account needs Conversions in EVERY Campaign in the "Last 90 Days" for this strategy to work!
      - The more conversions, the better. Any campaign with no Conversions is automatically assigned a score of 0, and will receive the minimum budget possible.
    - Scoring for campaigns uses the following formula to provide a "score," and then the budget is allocated to each campaign proportionally according to their "score."

Note that the "All <Metrics>" values use Impressions, Clicks, Cost, Conversions, Value, from all campaigns in the account over the last 90 days.

Google Sites

Report abuse