Bước 1: Tạo bản sao Google sheet báo cáo hiệu suất Adwords theo link bên dưới
1.1 Tạo bản sao
Truy cập tài khoản Google Adwords
//
/** * @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 * https://developers.google.com/adwords/scripts/docs/solutions/account-summary * for more details. * * @author AdWords Scripts Team [adwords-scripts@googlegroups.com] * * @version 1.1 * * @changelog * – version 1.1 * – Add user-updateable fields, and ensure report row ordering. * – version 1.0.4 * – version 1.0.3 * – 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 SPREADSHEET_URL = ‘YOUR_SPREADSHEET_URL’;
/** * Configuration to be used for running reports. */ var REPORTING_OPTIONS = { apiVersion: ‘v201705’ };
/** * 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: * https://developers.google.com/adwords/api/docs/appendix/reports/account-performance-report */ 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’}, ];
function main() { AdWordsApp.currentAccount().getCustomerId());
var yesterday = getYesterday();
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]; }))); } date.setDate(date.getDate() + 1); }
if (email) { sendEmail(email); } } }
/** * * @return {!Object} A lookup of existing dates. */ function getExistingDates() {
var data = sheet.getDataRange().getValues(); var existingDates = {}; data.slice(5).forEach(function(row) { existingDates[row[1]] = true; }); return existingDates; }
/** */ function sortReportRows() {
var data = sheet.getDataRange().getValues(); var reportRows = data.slice(5); if (reportRows.length) { reportRows.sort(function(rowA, rowB) { return -1; return 1; } else if (rowA[1] < rowB[1]) { return -1; return 1; } return 0; }); sheet.getRange(6, 1, reportRows.length, reportRows[0].length) .setValues(reportRows); } }
/** * */ 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( “<div style=’font: italic normal 10pt Times New Roman, serif; ” + “<div style=’font: normal 18pt verdana, sans-serif; ” + “<div style=’font: normal 18pt verdana, sans-serif; ” + “<td style=’font: 12pt verdana, sans-serif; ” + ‘padding: 5px 0px 5px 5px; background-color: #ddd; ‘ + “<td style=’font: 12pt verdana, sans-serif; ” + ‘padding: 5px 0px 5px 5px; background-color: #ddd; ‘ + “<td style=’font: 12pt verdana, sans-serif; ” + ‘padding: 5px 0px 5x 5px; background-color: #ddd; ‘ + REPORT_FIELDS.forEach(function(field) { html.push(emailRow( field.displayName, field.columnName, yesterdayRow, twoDaysAgoRow, weekAgoRow)); }); MailApp.sendEmail(email, ‘AdWords Account ‘ + AdWordsApp.currentAccount().getCustomerId() + ‘ Summary Report’, ”, {htmlBody: html.join(‘n’)}); }
function emailRow(title, column, yesterdayRow, twoDaysAgoRow, weekAgoRow) { var html = []; formatChangeString(yesterdayRow[column], twoDaysAgoRow[column]) + formatChangeString(yesterdayRow[column], weekAgoRow[column]) + 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 + ‘%’; }
} else { } }
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 {Date} yesterday The yesterday date. * * @return {Date} The date corresponding to the first day to check. */ 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) { return Utilities.formatDate(date, timeZone, format); }
/** * properly. Throws a descriptive error message if validation fails. * */ if (‘YOUR_SPREADSHEET_URL’ == SPREADSHEET_URL) { ‘ a link to a template in the associated guide for this script.’); } if (‘foo@example.com’ == email) { throw new Error(‘Please either set a custom email address in the’ + ‘ to send no email.’); } }
Tìm đoạn code sau var SPREADSHEET_URL = ‘YOUR_SPREADSHEET_URL’ thay URL bản sao bạn vừa tạo vào YOUR_SPREADSHEET_URL
Bước tiếp theo bạn nhấn vào ủy quyền ngay bây giờ và cho phép ( Allow)
Bước 3: chỉnh sửa lại lịch chạy script, bây giờ bạn có thể cho chạy script và vào file sheet để xem kết quả.
iSharedigital.com