Google Ads Script: Budget Management
Advertisement
Google Ads Script is now getting smarter, thanks to their API where we can extend with ease, it gives us enough ability to extend and create our own comfort.
This time, I have given a task to create a Budget Management Google Ads Script and email to certain Project Manager when Pacing is > 100% or above the given budget, handy, isn’t it? of course.
Let’s get started.
Variables
This part contains lists of our script variables we can pull and used at the later part of the scripts.
/**
* Budget Management Google Ads Script
*/
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
/**
* And our spreadsheet look like this
*
* +-----+-------+----------------+-------+-----+--------+-------+-----------+--------+--------+
* | I/O | CID | Agency Name | Start | End | Budget | Spent | Yesterday | Target | Pacing |
* +-----+-------+----------------+-------+-----+--------+-------+-----------+--------+--------+
*
*/
var RECIPIENT = 'contact@ryansutana.name';
// Spreadsheet Columns
var START_ROW = 2, // Don't change, unless you want to ruin the script
IO_COL = 1,
CUSTOMER_ID_COL = 2,
AGENCY_COL = 3,
START_COL = 4,
END_COL = 5,
BUDGET_COL = 6,
SPENT_COL = 7,
YESTERDDAY_COL = 8,
TARGET_COL = 9,
PACING_COL = 10;
Main Function
In Google Ads Script it reads the main() function primarily, without it, your code won’t be executed and run properly.
Here we’ve added timezone conversion, email notifications and getting of Google Drive column values.
function main() {
var now = new Date(),
timeZone = AdsApp.currentAccount().getTimeZone(),
todayDate = Utilities.formatDate(now, timeZone, 'MMMM dd, yyyy HH:mm');
// Send email notifications when the script started to run
var args = {
recipient: RECIPIENT,
subject: "Script Run",
body: "The script runs successfully on" + todayDate,
};
sendEmailNofication( args );
// Get spreadsheet
var spreadsheet = validateAndGetSpreadsheet();
// Make sure the spreadsheet is using the account's timezone.
spreadsheet.setSpreadsheetTimeZone( AdWordsApp.currentAccount().getTimeZone() );
var sheet = spreadsheet.getSheetByName( "Adwords" );
var timeZone = AdWordsApp.currentAccount().getTimeZone();
// Get the number of rows and column
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
// Loop through all rows in the Spreadsheet
for( var i = START_COL; i <= lastRow; i++ ) {
var isIO = sheet.getRange(i, IO_COL).getValue(),
customerID = sheet.getRange(i, CUSTOMER_ID_COL).getValue(),
agencyName = sheet.getRange(i, AGENCY_COL).getValue(),
startDate = sheet.getRange(i, START_COL).getValue(),
endDate = sheet.getRange(i, END_COL).getValue(),
overallSpent = sheet.getRange(i, SPENT_COL),
yesterdaySpent = sheet.getRange(i, YESTERDDAY_COL),
pacingPercent = sheet.getRange(i, PACING_COL).getValue();
// Get formatted date
var formattedStartDate = new Date( startDate ),
formattedendDate = new Date( endDate );
// Get day of week
var startTimestamp = parseInt(Utilities.formatDate(formattedStartDate, timeZone, "YMMdd"), 10),
endTimestamp = parseInt(Utilities.formatDate(formattedendDate, timeZone, "YMMdd"), 10);
// If Pacing is > 1 0r 110% send an Email
var pacingVal = pacingPercent * 100,
pacingVal = Utilities.formatString( "%.2f", pacingVal );
var args = {
recipient: RECIPIENT,
subject: "Action Required Budget Pacing > 100%",
body: "The current '"+ agencyName +"' monthly budget pacing is currently "+ pacingVal +" which is over 100%."
};
// Don't send email inPreview mode
if ( !AdsApp.getExecutionInfo().isPreview() && pacingPercent > 1 ) {
sendEmailNofication( args );
}
// Retrieve all children accounts.
var accountIterator = AdsManagerApp.accounts().get();
// Iterate through the account list.
while (accountIterator.hasNext()) {
var account = accountIterator.next();
// check if the customer ID matches from the ID
// added in Spreadsheet
if( customerID == account.getCustomerId() ) {
// Get stats for the child account ranging
var rangeStats = account.getStatsFor(startTimestamp.toString(), endTimestamp.toString());
overallSpent.setValue( rangeStats.getCost() );
// Get stats for the child account yesterday
var yesterdayStats = account.getStatsFor("YESTERDAY");
yesterdaySpent.setValue( yesterdayStats.getCost() );
} // end if
} // end while
} // end for
} // end main
sendEmailNofication() function
Here I’ve written a tiny snippet to send email notification and can be filled with values at the later when calling the function, like the recipient, subject and email body.
function sendEmailNofication( args ) {
// check if args has something in it
if( ! args )
return;
MailApp.sendEmail( args.recipient, args.subject, args.body );
};
Spreadsheet Verification
Just to make sure our script works without a hitch, we’ll first verify should we added a correct spreadsheet file.
/**
* Validates the provided spreadsheet URL
* to make sure that it's set up properly. Throws a descriptive error message
* if validation fails.
*
* @param {string} spreadsheeturl The URL of the spreadsheet to open.
* @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
* @throws {Error} If the spreadsheet URL hasn't been set
*/
function validateAndGetSpreadsheet() {
if ( '#' != SPREADSHEET_URL) {
throw new Error('Please specify a valid Spreadsheet URL. You can find' +
' a link to a template in the associated guide for this script.');
}
return SpreadsheetApp.openByUrl( SPREADSHEET_URL );
}
Complete Code
Below is the complete code you can copy and paste and have a go.
/**
* Budget Management Google Ads Script
*/
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
/**
* And our spreadsheet look like this
*
* +-----+-------+----------------+-------+-----+--------+-------+-----------+--------+--------+
* | I/O | CID | Agency Name | Start | End | Budget | Spent | Yesterday | Target | Pacing |
* +-----+-------+----------------+-------+-----+--------+-------+-----------+--------+--------+
*
*/
var RECIPIENT = 'contact@ryansutana.name';
// Spreadsheet Columns
var START_ROW = 2, // Don't change, unless you want to ruin the script
IO_COL = 1,
CUSTOMER_ID_COL = 2,
AGENCY_COL = 3,
START_COL = 4,
END_COL = 5,
BUDGET_COL = 6,
SPENT_COL = 7,
YESTERDDAY_COL = 8,
TARGET_COL = 9,
PACING_COL = 10;
function main() {
var now = new Date(),
timeZone = AdsApp.currentAccount().getTimeZone(),
todayDate = Utilities.formatDate(now, timeZone, 'MMMM dd, yyyy HH:mm');
// Send email notifications when the script started to run
var args = {
recipient: RECIPIENT,
subject: "Script Run",
body: "The script runs successfully on" + todayDate,
};
sendEmailNofication( args );
// Get spreadsheet
var spreadsheet = validateAndGetSpreadsheet();
// Make sure the spreadsheet is using the account's timezone.
spreadsheet.setSpreadsheetTimeZone( AdWordsApp.currentAccount().getTimeZone() );
var sheet = spreadsheet.getSheetByName( "Adwords" );
var timeZone = AdWordsApp.currentAccount().getTimeZone();
// Get the number of rows and column
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
// Loop through all rows in the Spreadsheet
for( var i = START_COL; i <= lastRow; i++ ) {
var isIO = sheet.getRange(i, IO_COL).getValue(),
customerID = sheet.getRange(i, CUSTOMER_ID_COL).getValue(),
agencyName = sheet.getRange(i, AGENCY_COL).getValue(),
startDate = sheet.getRange(i, START_COL).getValue(),
endDate = sheet.getRange(i, END_COL).getValue(),
overallSpent = sheet.getRange(i, SPENT_COL),
yesterdaySpent = sheet.getRange(i, YESTERDDAY_COL),
pacingPercent = sheet.getRange(i, PACING_COL).getValue();
// Get formatted date
var formattedStartDate = new Date( startDate ),
formattedendDate = new Date( endDate );
// Get day of week
var startTimestamp = parseInt(Utilities.formatDate(formattedStartDate, timeZone, "YMMdd"), 10),
endTimestamp = parseInt(Utilities.formatDate(formattedendDate, timeZone, "YMMdd"), 10);
// If Pacing is > 1 0r 110% send an Email
var pacingVal = pacingPercent * 100,
pacingVal = Utilities.formatString( "%.2f", pacingVal );
var args = {
recipient: RECIPIENT,
subject: "Action Required Budget Pacing > 100%",
body: "The current '"+ agencyName +"' monthly budget pacing is currently "+ pacingVal +" which is over 100%."
};
// Don't send email inPreview mode
if ( !AdsApp.getExecutionInfo().isPreview() && pacingPercent > 1 ) {
sendEmailNofication( args );
}
// Retrieve all children accounts.
var accountIterator = AdsManagerApp.accounts().get();
// Iterate through the account list.
while (accountIterator.hasNext()) {
var account = accountIterator.next();
// check if the customer ID matches from the ID
// added in Spreadsheet
if( customerID == account.getCustomerId() ) {
// Get stats for the child account ranging
var rangeStats = account.getStatsFor(startTimestamp.toString(), endTimestamp.toString());
overallSpent.setValue( rangeStats.getCost() );
// Get stats for the child account yesterday
var yesterdayStats = account.getStatsFor("YESTERDAY");
yesterdaySpent.setValue( yesterdayStats.getCost() );
} // end if
} // end while
} // end for
} // end main
function sendEmailNofication( args ) {
// check if args has something in it
if( ! args )
return;
MailApp.sendEmail( args.recipient, args.subject, args.body );
};
/**
* Validates the provided spreadsheet URL
* to make sure that it's set up properly. Throws a descriptive error message
* if validation fails.
*
* @param {string} spreadsheeturl The URL of the spreadsheet to open.
* @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
* @throws {Error} If the spreadsheet URL hasn't been set
*/
function validateAndGetSpreadsheet() {
if ( '#' != SPREADSHEET_URL) {
throw new Error('Please specify a valid Spreadsheet URL. You can find' +
' a link to a template in the associated guide for this script.');
}
return SpreadsheetApp.openByUrl( SPREADSHEET_URL );
}
Happy Coding ^_^
Advertisement