How to create Month to date(MTD), last week or 7 days and LAST MONTH performance report.
366 views
Skip to first unread message
Ed G
May 8, 2018, 3:29:19 AM5/8/18
to AdWords Scripts Forum
I can't seem to figure it out how to create a performance report. The script below generates this report and sends to me via email. I current;y have it set up for today, 2 days ago and 7 days ago.
What I'd like to see is getting MTD, Last week (7 days) and last month metrics.
See image below to see what I am talking about.
and here is the code.
See below for code that needs to be edited to accomplish my goal.
// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
/**
* @name Account Summary Report
*
* @overview The Account Summary Report script generates an at-a-glance report
* showing the performance of an entire AdWords account. See
* for more details.
*
* @author AdWords Scripts Team [adwords-scripts-at-googlegroups.com]
*
* @version 1.1
*
* @changelog
* - version 1.1
* - Add user-updateable fields, and ensure report row ordering.
* - version 1.0.4
* - Improved code readability and comments.
* - version 1.0.3
* - Added validation for external spreadsheet setup.
* - version 1.0.2
* - Fixes date formatting bug in certain timezones.
* - version 1.0.1
* - Improvements to time zone handling.
* - version 1.0
* - Released initial version.
*/
var RECIPIENT_EMAIL = 'te...@gmail.com';
var SPREADSHEET_URL = 'MY GOOGLE SHEET FILE GOES HERE';
/**
* Configuration to be used for running reports.
*/
var REPORTING_OPTIONS = {
// Comment out the following line to default to the latest reporting version.
apiVersion: 'v201802'
};
/**
* To add additional fields to the report, follow the instructions at the link
* in the header above, and add fields to this variable, taken from the Account
* Performance Report reference:
*/
var REPORT_FIELDS = [
{columnName: 'Cost', displayName: 'Cost'},
{columnName: 'AverageCpc', displayName: 'Avg. CPC'},
{columnName: 'Ctr', displayName: 'CTR'},
{columnName: 'AveragePosition', displayName: 'Avg. Pos.'},
{columnName: 'Impressions', displayName: 'Impressions'},
{columnName: 'Clicks', displayName: 'Clicks'},
{columnName: 'SearchImpressionShare', displayName: 'Search Imp. Share'}
];
function main() {
Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
var spreadsheet = validateAndGetSpreadsheet();
spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
spreadsheet.getRangeByName('account_id_report').setValue(
AdWordsApp.currentAccount().getCustomerId());
var yesterday = getYesterday();
var date = getFirstDayToCheck(spreadsheet, yesterday);
var rows = [];
var existingDates = getExistingDates();
while (date.getTime() <= yesterday.getTime()) {
if (!existingDates[date]) {
var row = getReportRowForDate(date);
rows.push([new Date(date)].concat(REPORT_FIELDS.map(function(field) {
return row[field.columnName];
})));
spreadsheet.getRangeByName('last_check').setValue(date);
}
date.setDate(date.getDate() + 1);
}
if (rows.length > 0) {
writeToSpreadsheet(rows);
var email = spreadsheet.getRangeByName('email').getValue();
if (email) {
sendEmail(email);
}
}
}
/**
* Retrieves a lookup of dates for which rows already exist in the spreadsheet.
*
* @return {!Object} A lookup of existing dates.
*/
function getExistingDates() {
var spreadsheet = validateAndGetSpreadsheet();
var sheet = spreadsheet.getSheetByName('Report');
var data = sheet.getDataRange().getValues();
var existingDates = {};
data.slice(5).forEach(function(row) {
existingDates[row[1]] = true;
});
return existingDates;
}
/**
* Sorts the data in the spreadsheet into ascending date order.
*/
function sortReportRows() {
var spreadsheet = validateAndGetSpreadsheet();
var sheet = spreadsheet.getSheetByName('Report');
var data = sheet.getDataRange().getValues();
var reportRows = data.slice(5);
if (reportRows.length) {
reportRows.sort(function(rowA, rowB) {
if (!rowA || !rowA.length) {
return -1;
} else if (!rowB || !rowB.length) {
return 1;
} else if (rowA[1] < rowB[1]) {
return -1;
} else if (rowA[1] > rowB[1]) {
return 1;
}
return 0;
});
sheet.getRange(6, 1, reportRows.length, reportRows[0].length)
.setValues(reportRows);
}
}
/**
* Append the data rows to the spreadsheet.
*
* @param {Array<Array<string>>} rows The data rows.
*/
function writeToSpreadsheet(rows) {
var access = new SpreadsheetAccess(SPREADSHEET_URL, 'Report');
var emptyRow = access.findEmptyRow(6, 2);
if (emptyRow < 0) {
access.addRows(rows.length);
emptyRow = access.findEmptyRow(6, 2);
}
access.writeRows(rows, emptyRow, 2);
sortReportRows();
}
function sendEmail(email) {
var day = getYesterday();
var yesterdayRow = getReportRowForDate(day);
day.setDate(day.getDate() - 1);
var twoDaysAgoRow = getReportRowForDate(day);
day.setDate(day.getDate() - 5);
var weekAgoRow = getReportRowForDate(day);
var html = [];
html.push(
'<html>',
'<body>',
'<table width=800 cellpadding=0 border=0 cellspacing=0>',
'<tr>',
'<td colspan=2 align=right>',
"<div style='font: italic normal 10pt Times New Roman, serif; " +
"margin: 0; color: #666; padding-right: 5px;'>" +
'Powered by ADWORDS scripts</div>',
'</td>',
'</tr>',
"<tr bgcolor='#5B9BD5'>",
'<td width=500>',
"<div style='font: normal 18pt verdana, sans-serif; " +
"padding: 3px 10px; color: white'>Account Summary Report</div>",
'</td>',
'<td align=right>',
"<div style='font: normal 18pt verdana, sans-serif; " +
"padding: 3px 10px; color: white'>",
AdWordsApp.currentAccount().getCustomerId(), 'Chevy Campaign', '</h1>',
'</td>',
'</tr>',
'</table>',
'<table width=800 cellpadding=0 border=0 cellspacing=0>',
"<tr bgcolor='#404040'>",
'<td></td>',
"<td style='font: 12pt verdana, sans-serif; " +
'padding: 5px 0px 5px 5px; background-color: #404040; color:white; ' +
"text-align: left'>Yesterday</td>",
"<td style='font: 12pt verdana, sans-serif; " +
'padding: 5px 0px 5px 5px; color: white; background-color: #404040; ' +
"text-align: left'>Two Days Ago</td>",
"<td style='font: 12pt verdana, sans-serif; " +
'padding: 5px 0px 5x 5px; color: white; background-color: #404040; ' +
"text-align: left'>A week ago</td>",
'</tr>');
REPORT_FIELDS.forEach(function(field) {
html.push(emailRow(
field.displayName, field.columnName, yesterdayRow, twoDaysAgoRow,
weekAgoRow));
});
html.push('</table>', '</body>', '</html>');
MailApp.sendEmail(email, 'AdWords Account ' +
AdWordsApp.currentAccount().getCustomerId() + ' Summary Report', '',
{htmlBody: html.join('\n')});
}
function emailRow(title, column, yesterdayRow, twoDaysAgoRow, weekAgoRow) {
var html = [];
html.push('<tr>',
"<td style='padding: 5px 10px'>" + title + '</td>',
"<td style='padding: 0px 10px'>" + yesterdayRow[column] + '</td>',
"<td style='padding: 0px 10px'>" + twoDaysAgoRow[column] +
formatChangeString(yesterdayRow[column], twoDaysAgoRow[column]) +
'</td>',
"<td style='padding: 0px 10px'>" + weekAgoRow[column] +
formatChangeString(yesterdayRow[column], weekAgoRow[column]) +
'</td>',
'</tr>');
return html.join('\n');
}
function getReportRowForDate(date) {
var timeZone = AdWordsApp.currentAccount().getTimeZone();
var dateString = Utilities.formatDate(date, timeZone, 'yyyyMMdd');
return getReportRowForDuring(dateString + ',' + dateString);
}
function getReportRowForDuring(during) {
var report = AdWordsApp.report(
'SELECT ' +
REPORT_FIELDS
.map(function(field) {
return field.columnName;
})
.join(',') +
' FROM ACCOUNT_PERFORMANCE_REPORT ' +
'DURING ' + during,
REPORTING_OPTIONS);
return report.rows().next();
}
function formatChangeString(newValue, oldValue) {
var x = newValue.indexOf('%');
if (x != -1) {
newValue = newValue.substring(0, x);
var y = oldValue.indexOf('%');
oldValue = oldValue.substring(0, y);
}
var change = parseFloat(newValue - oldValue).toFixed(2);
var changeString = change;
if (x != -1) {
changeString = change + '%';
}
if (change >= 0) {
return "<span style='color: #38761d; font-size: 8pt'> (+" +
changeString + ')</span>';
} else {
return "<span style='color: #cc0000; font-size: 8pt'> (" +
changeString + ')</span>';
}
}
function SpreadsheetAccess(spreadsheetUrl, sheetName) {
this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
this.sheet = this.spreadsheet.getSheetByName(sheetName);
// what column should we be looking at to check whether the row is empty?
this.findEmptyRow = function(minRow, column) {
var values = this.sheet.getRange(minRow, column,
this.sheet.getMaxRows(), 1).getValues();
for (var i = 0; i < values.length; i++) {
if (!values[i][0]) {
return i + minRow;
}
}
return -1;
};
this.addRows = function(howMany) {
this.sheet.insertRowsAfter(this.sheet.getMaxRows(), howMany);
};
this.writeRows = function(rows, startRow, startColumn) {
this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).
setValues(rows);
};
}
/**
* Gets a date object that is 00:00 yesterday.
*
* @return {Date} A date object that is equivalent to 00:00 yesterday in the
* account's time zone.
*/
function getYesterday() {
var yesterday = new Date(new Date().getTime() - 24 * 3600 * 1000);
return new Date(getDateStringInTimeZone('MMM dd, yyyy 00:00:00 Z',
yesterday));
}
/**
* Returned the last checked date + 1 day, or yesterday if there isn't
* a specified last checked date.
*
* @param {Spreadsheet} spreadsheet The export spreadsheet.
* @param {Date} yesterday The yesterday date.
*
* @return {Date} The date corresponding to the first day to check.
*/
function getFirstDayToCheck(spreadsheet, yesterday) {
var last_check = spreadsheet.getRangeByName('last_check').getValue();
var date;
if (last_check.length == 0) {
date = new Date(yesterday);
} else {
date = new Date(last_check);
date.setDate(date.getDate() + 1);
}
return date;
}
/**
* Produces a formatted string representing a given date in a given time zone.
*
* @param {string} format A format specifier for the string to be produced.
* @param {date} date A date object. Defaults to the current date.
* @param {string} timeZone A time zone. Defaults to the account's time zone.
* @return {string} A formatted string of the given date in the given time zone.
*/
function getDateStringInTimeZone(format, date, timeZone) {
date = date || new Date();
timeZone = timeZone || AdWordsApp.currentAccount().getTimeZone();
return Utilities.formatDate(date, timeZone, format);
}
/**
* Validates the provided spreadsheet URL to make sure that it's set up
* properly. Throws a descriptive error message if validation fails.
*
* @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
*/
function validateAndGetSpreadsheet() {
if ('YOUR_SPREADSHEET_URL' == 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.');
}
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var email = spreadsheet.getRangeByName('email').getValue();
if ('f...@example.com' == email) {
throw new Error('Please either set a custom email address in the' +
' spreadsheet, or set the email field in the spreadsheet to blank' +
' to send no email.');
}
return spreadsheet;
}
Adrian Catambay (AdWords Scripts Team)
May 8, 2018, 4:58:15 AM5/8/18
to AdWords Scripts Forum
Hello Ed,
To modify the Account Summary report that is being sent to your email, to be like in your screenshot, I would suggest that you find lines 162-167 inside the sendEmail() function of the Account Summary Report solution script, and change it to the following lines of code below which makes use of the THIS_MONTH, LAST_WEEK, and LAST_MONTH date ranges:
var mtdRow = getReportRowForDuring('THIS_MONTH'); // gets report for MTD
var lastWeekRow = getReportRowForDuring('LAST_WEEK'); // gets report for last week
var lastMonthRow = getReportRowForDuring('LAST_MONTH'); // gets report for last monthand then, you would need to modify the column headers in the HTML code inside the sendEmail() function to "MTD", "Last Week", and "Last Month".
You may try the suggestions above to your solution script using the Preview button.
Please note that the suggested sample code above will only change the Account Summary report that is being sent to your email, and not affect the spreadsheet report. Since the Account Summary Report was developed to fetch yesterday's statistics, changing the date ranges reflected on your spreadsheet report may require extensive modification of the script on your end. Nonetheless, I would suggest using the THIS_MONTH, LAST_WEEK, and LAST_MONTH date ranges for your requirement.
Thanks,
Adrian
AdWords Scripts Team
Richard Roy
Jun 15, 2018, 3:58:14 PM6/15/18
to AdWords Scripts Forum
I need to run this type of report, but I would like to do it at the campaign level. Any thoughts or help appreciated.
Hiroyuki Miyauchi (AdWords Scripts Team)
Jun 18, 2018, 12:14:55 AM6/18/18
to AdWords Scripts Forum
Hello,
Since the Account Summary solution script uses Account Performance Report, you may want to use another plain report type which is the Campaign Performance Report.
You may utilize Campaign Performance Report, since the same fields Cost, AverageCpc, Ctr, AveragePosition, Impressions, Clicks and SearchImpressionShare are available as campaign level in the report. You may refer to this guide to know how to create a spreadsheet report and this guide for sending email.
Please let me know if you have further clarifications.
Regards,
Hiroyuki
AdWords Scripts Team
Reply all
Reply to author
Forward
0 new messages