Updated Adwords Script: Budget vs Spend report

We've builld an Adwords script to report on Budget vs Spend some while ago (you can find the old version here, in Dutch). Goal was (and is) to gain insight in Ad Spend throughout a month for one or multiple acocunts, when compared to a budget (goal). I've received a tremendous amount of feedback on this script (some of which in the comments, which for some reason I don't get notified about, so sorry about the delay). I was suprised by some people translating the original blogpost and using the script succesfully, in other words there was quite some feedback from abroad. So I decided to write this little update in English.

Here is how it works. I'll try to sum up all features.

Or, go straight to the script.

• This is a 'Hybrid' script: it works on both account-level and MCC-level.​
• In most cases you'll need an external Google Spreadsheet. Example. You can define budgets, overspend/underspend ratio and which campaigns to include (using labels).
• You can define a budget either in the script (account-level) or in the spreadsheet (MCC-level), more on that later on
• NEW: You can choose which campaigns or ad groups to report on using labels
• Each time the script runs, it downloads your Spend for that exact moment. At the same time it calculates your Target Spend until the last full hour (this can be delayed max 2 hours because of time differences).
• The script builds a simple report and sends it to an email address of your choosing.
• My use case: every monday when I start working, an email is waiting for me and shows me clean and simple which accounts need attention when it concerns spend. At the 25th of the month, I receive another report. Right on time to do some final finetuning before the end of the month.

Example report:

Workflow

MCC-level

• First you'll need to build a spreadsheet (again, example here)
• For (large) MCC's we've build an account-import feature. To use this 1) Build a spreadsheet 2) Add the spreadsheet-ID to the script 3) Set prefillSpreadsheet to TRUE and run a preview. You'll see that data is now added to the spreadsheet. 4) Set prefillSpreadsheet to FALSE. The import is ready.
• Now you'll need to set a monthly budget for every account and, if you want, overspend/underspend ratio (however in most cases this can be done in the script with a default).
• NEW: in the spreadsheet, you can add multiple rows per client (with separate budgets) and define a label per row. The script will search for these labels on your Adwords campaigns and ad groups and only report on campaigns/ad groups matching these labels.
• You can now set the neccessary settings in the script (see below).
• You can run the script now and you'll receive your first Budget vs Spend report.

Account-level

• If you wish to report on the total spend of your account (all campaigns), you don't need a spreadsheet. You can simply insert a budget in the script (defaultBudget, see below) and run the script in your account. Just set some settings and your done.
• However if you wish to report on specific campaigns only, you need to build a spreadsheet (again, example here) and insert the spreadsheet-ID in the script. Note that the example shows different clients for MCC-use. When used on account-level there is obviously just one client, so one row is enough. If you wish to report on specific campaignsor ad groups only, you have to define a label per row. The script will search for these labels on your Adwords campaigns / ad groups and only report on campaigns / ad groups matching these labels.
• You can run the script now and you'll receive your first Budget vs Spend report.

Settings

• spreadsheetId: the ID of your Google Spreadsheet. You can find the ID here (bold): https:// docs.google.com/a/remkovanderzwaag.nl/spreadsheets/d/1jiYkI6xWOnJMqxIil67o0qPu5AhxCNFHp6e7uaJ4dLo. Note that you'll need to give the script access to your spreadsheet. To achieve this, either share it publicly (nobody knows the URL) or add your Adwords-login as a user with read-only permissions.
• prefillSpreadsheet: set to TRUE if you want to automatically import all MCC-acoucnts in the Spreadsheet. To use this 1) Build a spreadsheet 2) Add the spreadsheet-ID to the script 3) Set prefillSpreadsheet to TRUE and run a preview. You'll see that data is now added to the spreadsheet. 4) Set prefillSpreadsheet to FALSE. The import is ready.
• emailAddr: email adress you want to receive the report on. Separate multiple emailadresses with a comma (eg info@company.com, steve@company.com)
• emailSubject: subject of the email
• onlyReportProblems: set to TRUE to only include accounts that have deviations (underspend/overspend).
• ignoreNoBudgetCampaigns: set to TRUE (default) to only include accounts which have a budget in the spreadsheet.
• alwaysReport: set to FALSE to only receive reports when there are deviations (underspend/overspend) for budgets. Only works when onlyReportProblems = FALSE.
• defaultBudget: the budget when used on account-level. Set to 0 when using on MCC-level.
• defaultOver: the percentage (10% = 0.1) that the Actual Spend needs to exceed the Target Spend (budget) before being reported as a warning. Used as default for all accounts in the spreadsheet.
• defaultUnder: the percentage (10% = 0.1) that the Target Spend (budget) needs to exceed the Actual Spend before being reported as a warning. Used as default for all accounts in the spreadsheet.
• addWeekCols: add empty colums for the day of the week (monday, tuesday etc.). This doesn't do anything except for building 5 empty columns in the report (someone requested this to be able to manually check off which campaigns he'd worked on that week).
• getYesterdayCost: display's cost of yesterday

Advanced

• With function mapRowToInfo you can use this script with spreadsheets in your own layout. 

Notes

• Scheduling: the script assumes lineair spend and thus doesn't consider seasonality, trends etc. Therefore it is recommended to run this script at least weekly (not daily).
• Max 50 accounts will be processed, this is a limitation of Adwords Scripts.
• YouTube spend is, unfortunately, not accessible through Adwords Scripts (or the API for that matter).

Script

This script in developed in collaboration with pdds.