Script to email you hourly with the cost/performance so far today.
/** | |
* | |
* Hourly Email Updates | |
* | |
* This script emails you every hour with totals for selected performance metrics (like cost) | |
* for your account for the day so far. | |
* | |
* Version: 1.1 | |
* Updated 2016-10-11: removed 'ConvertedClicks' | |
* Google AdWords Script maintained on brainlabsdigital.com | |
* | |
**/ | |
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~// | |
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~// | |
//Options | |
var campaignNameDoesNotContain = []; | |
// Use this if you want to exclude some campaigns. | |
// For example ["Display"] would ignore any campaigns with 'Display' in the name, | |
// while ["Display","Shopping"] would ignore any campaigns with 'Display' or | |
// 'Shopping' in the name. | |
// Leave as [] to not exclude any campaigns. | |
var campaignNameContains = []; | |
// Use this if you only want to look at some campaigns. | |
// For example ["Brand"] would only look at campaigns with 'Brand' in the name, | |
// while ["Brand","Generic"] would only look at campaigns with 'Brand' or 'Generic' | |
// in the name. | |
// Leave as [] to include all campaigns. | |
var email = ["aa@example.com"]; | |
// The email address you want the hourly update to be sent to. | |
// If you'd like to send to multiple addresses then have them separated by commas, | |
// for example ["aa@example.com", "bb@example.com"] | |
var metricsToReport = ["Cost", "Impressions", "Clicks"]; | |
// Allowed fields: "Impressions", "Clicks", "Cost", | |
// "Conversions", "ConversionValue" | |
var currencySymbol = "£"; | |
// Used for formatting in the email. | |
var thousandsSeparator = ","; | |
// Numbers will be formatted with this as the thousands separator. | |
// eg If this is ",", 1000 will appear in the email as 1,000 | |
// If this is ".", 1000 will appear in the email as 1.000 | |
// If this is "" 1000 will appear as 1000. | |
var decimalMark = "."; | |
// Numbers will be formatted with this as the decimal mark | |
// eg if this is ".", one and a half will appear in the email as 1.5 | |
// and if this is "," it will be 1,5 | |
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~// | |
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~// | |
// Functions | |
function main() { | |
// Get the campaign IDs (based on campaignNameDoesNotContain and campaignNameContains) | |
var campaignIds = getCampaignIds(); | |
var localDate = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd"); | |
var localTime = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "HH:mm"); | |
Logger.log("Date: " + localDate); | |
Logger.log("Time: " + localTime); | |
// Check the given metrics, and make sure they are trimmed and correctly capitalised | |
var allowedFields = ["Conversions", "ConversionValue", "Impressions", "Clicks", "Cost"]; | |
var metrics = checkFieldNames(allowedFields, metricsToReport); | |
// Get the total metrics for today | |
var totals = getMetrics("TODAY", campaignIds, metrics) | |
// Assemble the email message | |
var subject = AdWordsApp.currentAccount().getName() + " Hourly Email"; | |
if (totals["Cost"] != undefined) { | |
subject += " - Cost is " + formatNumber(totals["Cost"], true); | |
} | |
var message = "Metrics for " + localDate + " at " + localTime + "\n"; | |
for (var i=0; i<metrics.length; i++) { | |
var isCurrency = (metrics[i] == "Cost" || metrics[i] == "ConversionValue"); | |
message += metrics[i] + " = " + formatNumber(totals[metrics[i]], isCurrency) + "\n"; | |
Logger.log(metrics[i] + " = " + totals[metrics[i]]); | |
} | |
// Send the email | |
MailApp.sendEmail(email.join(','), subject, message); | |
Logger.log("Message to " + email.join(',') + " sent."); | |
} | |
// Get the IDs of campaigns which match the given options | |
function getCampaignIds() { | |
var whereStatement = "WHERE CampaignStatus IN ['ENABLED','PAUSED','REMOVED'] AND Impressions > 0 "; | |
var whereStatementsArray = []; | |
var campaignIds = []; | |
for (var i=0; i<campaignNameDoesNotContain.length; i++) { | |
whereStatement += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain[i].replace(/"/g,'\\\"') + "' "; | |
} | |
if (campaignNameContains.length == 0) { | |
whereStatementsArray = [whereStatement]; | |
} else { | |
for (var i=0; i<campaignNameContains.length; i++) { | |
whereStatementsArray.push(whereStatement + 'AND CampaignName CONTAINS_IGNORE_CASE "' + campaignNameContains[i].replace(/"/g,'\\\"') + '" '); | |
} | |
} | |
for (var i=0; i<whereStatementsArray.length; i++) { | |
var adTextReport = AdWordsApp.report( | |
"SELECT CampaignId " + | |
"FROM CAMPAIGN_PERFORMANCE_REPORT " + | |
whereStatementsArray[i] + | |
"DURING TODAY"); | |
var rows = adTextReport.rows(); | |
while (rows.hasNext()) { | |
var row = rows.next(); | |
campaignIds.push(row['CampaignId']); | |
} | |
} | |
if (campaignIds.length == 0) { | |
throw("No campaigns found with the given settings."); | |
} | |
Logger.log(campaignIds.length + " campaigns found"); | |
return campaignIds; | |
} | |
// Verify that all field names are valid, and return a list of them with the | |
// correct capitalisation | |
function checkFieldNames(allowedFields, givenFields) { | |
var allowedFieldsLowerCase = allowedFields.map(function (str){return str.toLowerCase()}); | |
var wantedFields = []; | |
var unrecognisedFields = []; | |
for (var i=0; i<givenFields.length; i++) { | |
var fieldIndex = allowedFieldsLowerCase.indexOf(givenFields[i].toLowerCase().replace(" ","").trim()); | |
if(fieldIndex === -1){ | |
unrecognisedFields.push(fields[i]); | |
} else { | |
wantedFields.push(allowedFields[fieldIndex]); | |
} | |
} | |
if (unrecognisedFields.length > 0) { | |
throw unrecognisedFields.length + " field(s) not recognised: '" + unrecognisedFields.join("', '") + | |
"'. Please choose from '" + allowedFields.join("', '") + "'."; | |
} | |
return wantedFields; | |
} | |
// Formats a number with the specified thousand separator and decimal mark | |
// Adds the currency symbol and two decimal places if isCurrency is true | |
function formatNumber(number, isCurrency) { | |
if (isCurrency) { | |
var formattedNumber = number.toFixed(2); | |
formattedNumber = formattedNumber.substr(0,formattedNumber.length-3); | |
formattedNumber = formattedNumber.split('').reverse().join('').replace(/(...)/g,"$1 ").trim().split('').reverse().join('').replace(/ /g,thousandsSeparator); | |
formattedNumber = currencySymbol + formattedNumber + decimalMark + number.toFixed(2).substr(-2); | |
} else { | |
var formattedNumber = number.toFixed(0).split('').reverse().join('').replace(/(...)/g,"$1 ").trim().split('').reverse().join('').replace(/ /g,thousandsSeparator); | |
} | |
return formattedNumber; | |
} | |
// Get totals for the listed metrics in the given campaigns in the given date range | |
function getMetrics(dateRange, campaignIds, metrics) { | |
// Initialise the object that will store the metrics' data | |
var totals = {}; | |
for (var i=0; i<metrics.length; i++) { | |
totals[metrics[i]] = 0; | |
} | |
var report = AdWordsApp.report( | |
'SELECT ' + metrics.join(', ') + " " + | |
'FROM CAMPAIGN_PERFORMANCE_REPORT ' + | |
'WHERE Impressions > 0 AND CampaignId IN [' + campaignIds.join(',') + '] ' + | |
'DURING ' + dateRange); | |
var rows = report.rows(); | |
while (rows.hasNext()) { | |
var row = rows.next(); | |
for (var i=0; i<metrics.length; i++) { | |
totals[metrics[i]] += parseFloat(row[metrics[i]].replace(/,/g, "")); | |
} | |
} | |
return totals; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment