Nessa série especial, reunimos alguns scripts úteis para você automatizar e aumentar o retorno de suas campanhas no Google Ads. Confira abaixo como automatizar o Google Ads para Geração de Relatórios.
1. Relatório de Resumo da Conta – por Google Ads. Utilize esse script para criar relatórios em uma planilha do Google. Nesta planilha você tem uma visão do desempenho de toda a conta do Google Ads e, além disso, envia um e-mail todos os dias com estatísticas das contas atuais.
// 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
//
// //www.apache.org/licenses/LICENSE-2.0
//
// 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
* //developers.google.com/adwords/scripts/docs/solutions/account-summary
* for more details.
*
* @author AdWords Scripts Team [[email protected]]
*
* @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 SPREADSHEET_URL = ‘YOUR_SPREADSHEET_URL’;
/**
* Configuration to be used for running reports.
*/
var REPORTING_OPTIONS = {
// Comment out the following line to default to the latest reporting version.
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:
* //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’},
{columnName: ‘Clicks’, displayName: ‘Clicks’}
];
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=’#3c78d8′>”,
‘<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(), ‘</h1>’,
‘</td>’,
‘</tr>’,
‘</table>’,
‘<table width=800 cellpadding=0 border=0 cellspacing=0>’,
“<tr bgcolor=’#ddd’>”,
‘<td></td>’,
“<td style=’font: 12pt verdana, sans-serif; ” +
‘padding: 5px 0px 5px 5px; background-color: #ddd; ‘ +
“text-align: left’>Yesterday</td>”,
“<td style=’font: 12pt verdana, sans-serif; ” +
‘padding: 5px 0px 5px 5px; background-color: #ddd; ‘ +
“text-align: left’>Two Days Ago</td>”,
“<td style=’font: 12pt verdana, sans-serif; ” +
‘padding: 5px 0px 5x 5px; background-color: #ddd; ‘ +
“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 (‘[email protected]’ == 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;
}
Como Configurar
2. Relatório de Desempenho do Anúncio – por Google Ads. Receba relatórios de desempenhos de seus anúncios em uma planilha do Google com esse script. Toda vez que esse script for executado ele cria um novo relatório no Google Drive. Faça testes e compare os desempenhos de um novo título ou URL.
// 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
//
// //www.apache.org/licenses/LICENSE-2.0
//
// 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 Ad Performance Report
*
* @overview The Ad Performance Report generates a Google Spreadsheet that
* contains ad performance stats like Impressions, Cost, Click Through Rate,
* etc. as several distribution charts for an advertiser account. See
* //developers.google.com/adwords/scripts/docs/solutions/ad-performance
* for more details.
*
* @author AdWords Scripts Team [[email protected]]
*
* @version 1.0.1
*
* @changelog
* – version 1.1
* – Updated to use expanded text ads.
* – version 1.0.1
* – Improvements to time zone handling.
* – version 1.0
* – Released initial version.
*/
// Comma-separated list of recipients. Comment out to not send any emails.
var RECIPIENT_EMAIL = ‘[email protected]’;
// URL of the default spreadsheet template. This should be a copy of
// //goo.gl/21FW5i
var SPREADSHEET_URL = ‘YOUR_SPREADSHEET_URL’;
/**
* This script computes an Ad performance report
* and outputs it to a Google spreadsheet.
*/
function main() {
Logger.log(‘Using template spreadsheet – %s.’, SPREADSHEET_URL);
var spreadsheet = copySpreadsheet(SPREADSHEET_URL);
Logger.log(‘Generated new reporting spreadsheet %s based on the template ‘ +
‘spreadsheet. The reporting data will be populated here.’,
spreadsheet.getUrl());
var headlineSheet = spreadsheet.getSheetByName(‘Headline’);
headlineSheet.getRange(1, 2, 1, 1).setValue(‘Date’);
headlineSheet.getRange(1, 3, 1, 1).setValue(new Date());
var finalUrlSheet = spreadsheet.getSheetByName(‘Final Url’);
finalUrlSheet.getRange(1, 2, 1, 1).setValue(‘Date’);
finalUrlSheet.getRange(1, 3, 1, 1).setValue(new Date());
spreadsheet.getRangeByName(‘account_id_headline’).setValue(
AdWordsApp.currentAccount().getCustomerId());
spreadsheet.getRangeByName(‘account_id_final_url’).setValue(
AdWordsApp.currentAccount().getCustomerId());
// Only include ad types on the headline sheet for which the concept of a
// headline makes sense.
outputSegmentation(headlineSheet, ‘Headline’, function(ad) {
var headline;
// There is no AdTypeSpace method for textAd
if (ad.getType() === ‘TEXT_AD’) {
headline = ad.getHeadline();
} else if (ad.isType().expandedTextAd()) {
var eta = ad.asType().expandedTextAd();
headline = eta.getHeadlinePart1() + ‘ – ‘ + eta.getHeadlinePart2();
} else if (ad.isType().gmailMultiProductAd()) {
var gmailMpa = ad.asType().gmailMultiProductAd();
headline = gmailMpa.getHeadline();
} else if (ad.isType().gmailSinglePromotionAd()) {
var gmailSpa = ad.asType().gmailSinglePromotionAd();
headline = gmailSpa.getHeadline();
} else if (ad.isType().responsiveDisplayAd()) {
var responsiveDisplayAd = ad.asType().responsiveDisplayAd();
headline = responsiveDisplayAd.getLongHeadline();
}
return headline;
});
outputSegmentation(finalUrlSheet, ‘Final Url’, function(ad) {
return ad.urls().getFinalUrl();
});
Logger.log(‘Ad performance report available at\n’ + spreadsheet.getUrl());
if (validateEmailAddress(RECIPIENT_EMAIL)) {
MailApp.sendEmail(RECIPIENT_EMAIL,
‘Ad Performance Report is ready’,
spreadsheet.getUrl());
}
}
/**
* Retrieves the spreadsheet identified by the URL.
* @param {string} spreadsheetUrl The URL of the spreadsheet.
* @return {SpreadSheet} The spreadsheet.
*/
function copySpreadsheet(spreadsheetUrl) {
var spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl).copy(
‘Ad Performance Report – ‘ +
getDateStringInTimeZone(‘MMM dd, yyyy HH:mm:ss z’));
// Make sure the spreadsheet is using the account’s timezone.
spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
return spreadsheet;
}
/**
* Generates statistical data for this segment.
* @param {Sheet} sheet Sheet to write to.
* @param {string} segmentName The Name of this segment for the header row.
* @param {function(AdWordsApp.Ad): string} segmentFunc Function that returns
* a string used to segment the results by.
*/
function outputSegmentation(sheet, segmentName, segmentFunc) {
// Output header row.
var rows = [];
var header = [
segmentName,
‘Num Ads’,
‘Impressions’,
‘Clicks’,
‘CTR (%)’,
‘Cost’
];
rows.push(header);
var segmentMap = {};
// Compute data.
var adIterator = AdWordsApp.ads()
.forDateRange(‘LAST_WEEK’)
.withCondition(‘Impressions > 0’).get();
while (adIterator.hasNext()) {
var ad = adIterator.next();
var stats = ad.getStatsFor(‘LAST_WEEK’);
var segment = segmentFunc(ad);
// In the case of the headline segmentation segmentFunc will return null
// where there is no headline e.g. an HTML5 ad or other non-text ad, for
// which metrics are therefore not aggregated.
if (segment) {
if (!segmentMap[segment]) {
segmentMap[segment] =
{numAds: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
}
var data = segmentMap[segment];
data.numAds++;
data.totalImpressions += stats.getImpressions();
data.totalClicks += stats.getClicks();
data.totalCost += stats.getCost();
}
}
// Write data to our rows.
for (var key in segmentMap) {
if (segmentMap.hasOwnProperty(key)) {
var ctr = 0;
if (segmentMap[key].numAds > 0) {
ctr = (segmentMap[key].totalClicks /
segmentMap[key].totalImpressions) * 100;
}
var row = [
key,
segmentMap[key].numAds,
segmentMap[key].totalImpressions,
segmentMap[key].totalClicks,
ctr.toFixed(2),
segmentMap[key].totalCost];
rows.push(row);
}
}
sheet.getRange(3, 2, rows.length, 6).setValues(rows);
}
/**
* 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 email address
* to make sure that it’s set up properly. Throws a descriptive error message
* if validation fails.
*
* @param {string} emailAddress The email address to send the results.
* @return {Spreadsheet} The email address, if it is not the default fake one.
* @throws {Error} If the email address has not been changed from the default.
*/
function validateEmailAddress(emailAddress) {
if (emailAddress == ‘[email protected]’) {
throw new Error(‘Please specify a valid email or leave empty to not’ +
‘ send any email.’);
}
return emailAddress;
}
/**
* 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(spreadsheeturl) {
if (spreadsheeturl == ‘YOUR_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 spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
}
Como configurar
3. Relatório de Grupos de Anúncios Rebaixados – por Google Ads. Encontre facilmente grupos de anúncios que estão com seu desempenho em declínio, assim, você pode otimizar seu grupo de anúncios ou pausa-los. O script deve ser rodado semanalmente para uma performance melhor.
// 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
//
// //www.apache.org/licenses/LICENSE-2.0
//
// 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 Declining AdGroups
*
* @overview The Declining AdGroups script fetches ad groups in an advertiser
* account, whose performance is considered to be worsening. By default, ad
* groups whose Click Through Rate has been decreasing for three consecutive
* weeks is considered worsening. A more sophisticated measure of
* “worsening” may be developed if required. See
* //developers.google.com/adwords/scripts/docs/solutions/declining-adgroups
* for more details.
*
* @author AdWords Scripts Team [[email protected]]
*
* @version 1.0.2
*
* @changelog
* – version 1.0.2
* – Added validation for spreadsheet URL.
* – version 1.0.1
* – Improvements to time zone handling.
* – version 1.0
* – Released initial version.
*/
var SPREADSHEET_URL = ‘YOUR_SPREADSHEET_URL’;
function main() {
Logger.log(‘Using spreadsheet – %s.’, SPREADSHEET_URL);
var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
var sheet = spreadsheet.getSheets()[0];
spreadsheet.getRangeByName(‘account_id’).setValue(
AdWordsApp.currentAccount().getCustomerId());
sheet.getRange(1, 2, 1, 1).setValue(‘Date’);
sheet.getRange(1, 3, 1, 1).setValue(new Date());
sheet.getRange(7, 1, sheet.getMaxRows() – 7, sheet.getMaxColumns()).clear();
var adGroupsIterator = AdWordsApp.adGroups()
.withCondition(“Status = ‘ENABLED'”)
.withCondition(“CampaignStatus = ‘ENABLED'”)
.forDateRange(‘LAST_7_DAYS’)
.orderBy(‘Ctr ASC’)
.withLimit(100)
.get();
var today = getDateStringInPast(0);
var oneWeekAgo = getDateStringInPast(7);
var twoWeeksAgo = getDateStringInPast(14);
var threeWeeksAgo = getDateStringInPast(21);
var reportRows = [];
while (adGroupsIterator.hasNext()) {
var adGroup = adGroupsIterator.next();
// Let’s look at the trend of the ad group’s CTR.
var statsThreeWeeksAgo = adGroup.getStatsFor(threeWeeksAgo, twoWeeksAgo);
var statsTwoWeeksAgo = adGroup.getStatsFor(twoWeeksAgo, oneWeekAgo);
var statsLastWeek = adGroup.getStatsFor(oneWeekAgo, today);
// Week over week, the ad group is declining – record that!
if (statsLastWeek.getCtr() < statsTwoWeeksAgo.getCtr() &&
statsTwoWeeksAgo.getCtr() < statsThreeWeeksAgo.getCtr()) {
reportRows.push([adGroup.getCampaign().getName(), adGroup.getName(),
statsLastWeek.getCtr(), statsLastWeek.getCost(),
statsTwoWeeksAgo.getCtr(), statsTwoWeeksAgo.getCost(),
statsThreeWeeksAgo.getCtr(), statsThreeWeeksAgo.getCost()]);
}
}
if (reportRows.length > 0) {
sheet.getRange(7, 2, reportRows.length, 8).setValues(reportRows);
sheet.getRange(7, 4, reportRows.length, 1).setNumberFormat(‘#0.00%’);
sheet.getRange(7, 6, reportRows.length, 1).setNumberFormat(‘#0.00%’);
sheet.getRange(7, 8, reportRows.length, 1).setNumberFormat(‘#0.00%’);
sheet.getRange(7, 5, reportRows.length, 1).setNumberFormat(‘#,##0.00’);
sheet.getRange(7, 7, reportRows.length, 1).setNumberFormat(‘#,##0.00’);
sheet.getRange(7, 9, reportRows.length, 1).setNumberFormat(‘#,##0.00’);
}
var email = spreadsheet.getRangeByName(‘email’).getValue();
if (email) {
var body = [];
body.push(‘The Ctr of the following ad groups is declining over the ‘ +
‘last three weeks.\n’);
body.push(‘Full report at ‘ + SPREADSHEET_URL + ‘\n\n’);
for (var i = 0; i < reportRows.length; i++) {
body.push(reportRows[i][0] + ‘ > ‘ + reportRows[i][1]);
body.push(‘ ‘ + ctr(reportRows[i][6]) + ‘ > ‘ + ctr(reportRows[i][4]) +
‘ > ‘ + ctr(reportRows[i][2]) + ‘\n’);
}
MailApp.sendEmail(email, ” +
reportRows.length + ‘ ad groups are declining in AdWords account ‘ +
AdWordsApp.currentAccount().getCustomerId(), body.join(‘\n’));
}
}
function ctr(number) {
return parseInt(number * 10000) / 10000 + ‘%’;
}
// Returns YYYYMMDD-formatted date.
function getDateStringInPast(numDays, date) {
date = date || new Date();
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var past = new Date(date.getTime() – numDays * MILLIS_PER_DAY);
return getDateStringInTimeZone(‘yyyyMMdd’, past);
}
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.
*
* @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(spreadsheeturl) {
if (spreadsheeturl == ‘YOUR_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(spreadsheeturl);
}
Como Configurar
4. Relatório de desempenho por palavra-chave – por Google Ads. Este script gera relatórios com vários gráficos de distribuição, como por exemplo, posição média e índice de qualidade. Toda vez que o script é executado ele cria um novo relatório no Google Drive.
// Copyright 2016, 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
//
// //www.apache.org/licenses/LICENSE-2.0
//
// 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 Keyword Performance Report
*
* @overview The Keyword Performance Report script generates a Google
* Spreadsheet that contains keyword performance stats like quality score
* and average position of ads, as well as several distribution charts for
* an advertiser account. See
* //developers.google.com/adwords/scripts/docs/solutions/keyword-performance
* for more details.
*
* @author AdWords Scripts Team [[email protected]]
*
* @version 1.0.4
*
* @changelog
* – version 1.0.4
* – Fixed issue of calling getRangeByName on spreadsheet vs sheet.
* – version 1.0.3
* – Refactored to improve readability. Added documentation.
* – version 1.0.2
* – Added validation for spreadsheet url and email address.
* – version 1.0.1
* – Improvements to time zone handling.
* – version 1.0
* – Released initial version.
*/
/**
* Comma-separated list of recipients. Comment out to not send any emails.
*/
var RECIPIENT_EMAIL = ‘[email protected]’;
// URL of the default spreadsheet template. This should be a copy of
// //goo.gl/oR5VmF
var SPREADSHEET_URL = ‘YOUR_SPREADSHEET_URL’;
/**
* The size of the quality score map to output.
* DO NOT change this value.
*/
var QUALITY_SCORE_MAP_SIZE = 10;
/**
* The size of the position map to output.
* DO NOT change this value.
*/
var POSITION_MAP_SIZE = 12;
/**
* This script computes a keyword performance report
* and outputs it to a Google spreadsheet. The spreadsheet
* url is logged and emailed.
*/
function main() {
validateEmail(RECIPIENT_EMAIL);
Logger.log(‘Using template spreadsheet – %s.’, SPREADSHEET_URL);
var spreadsheet = copySpreadsheet(SPREADSHEET_URL);
Logger.log(
‘Generated new reporting spreadsheet %s based on the template ‘ +
‘spreadsheet. The reporting data will be populated here.’,
spreadsheet.getUrl());
spreadsheet.getRangeByName(‘date_label’).setValue(‘Date’);
spreadsheet.getRangeByName(‘date_value’).setValue(new Date());
spreadsheet.getRangeByName(‘account_id’)
.setValue(AdWordsApp.currentAccount().getCustomerId());
outputQualityScoreData(spreadsheet);
outputPositionData(spreadsheet);
Logger.log(
‘Keyword performance report available at\n’ + spreadsheet.getUrl());
if (RECIPIENT_EMAIL) {
MailApp.sendEmail(
RECIPIENT_EMAIL, ‘Keyword Performance Report is ready’,
spreadsheet.getUrl());
}
}
/**
* Retrieves the spreadsheet identified by the URL.
* @param {string} spreadsheetUrl The URL of the spreadsheet.
* @return {SpreadSheet} The spreadsheet.
*/
function copySpreadsheet(spreadsheetUrl) {
var spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl)
.copy(
‘Keyword Performance Report – ‘ +
getDateStringInTimeZone(‘MMM dd, yyyy HH:mm:ss z’));
// Make sure the spreadsheet is using the account’s timezone.
spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
return spreadsheet;
}
/**
* Gets an iterator for keywords that had impressions last week.
* @return {Iterator} an iterator of the keywords
*/
function getLastWeekKeywordsWithPositiveImpressions() {
return AdWordsApp.keywords()
.forDateRange(‘LAST_WEEK’)
.withCondition(‘Impressions > 0’)
.get();
}
/**
* Outputs Quality score related data to the spreadsheet
* @param {Spreadsheet} spreadsheet The sheet to output to.
*/
function outputQualityScoreData(spreadsheet) {
// Output header row
var header = [
‘Quality Score’, ‘Num Keywords’, ‘Impressions’, ‘Clicks’, ‘CTR (%)’, ‘Cost’
];
spreadsheet.getRangeByName(‘quality_score_headings’).setValues([header]);
// Initialize
var qualityScoreMap = getEmptyStatMap(QUALITY_SCORE_MAP_SIZE);
// Compute data
computeQualityData(
getLastWeekKeywordsWithPositiveImpressions(), qualityScoreMap);
// Output data to spreadsheet
var rows = [];
for (var key in qualityScoreMap) {
var ctr = calculateCtr(qualityScoreMap[key]);
var row = [
key, qualityScoreMap[key].numKeywords,
qualityScoreMap[key].totalImpressions, qualityScoreMap[key].totalClicks,
ctr.toFixed(2), qualityScoreMap[key].totalCost
];
rows.push(row);
}
spreadsheet.getRangeByName(‘quality_score_body’).setValues(rows);
}
/**
* Outputs average position related data to the spreadsheet.
* @param {Spreadsheet} spreadsheet The spreadsheet to output to.
*/
function outputPositionData(spreadsheet) {
// Output header row
headerRow = [];
var header = [
‘Avg Position’, ‘Num Keywords’, ‘Impressions’, ‘Clicks’, ‘CTR (%)’, ‘Cost’
];
headerRow.push(header);
spreadsheet.getRangeByName(‘position_headings’).setValues(headerRow);
// Initialize
var positionMap = getEmptyStatMap(POSITION_MAP_SIZE);
// Compute data
computePositionData(
getLastWeekKeywordsWithPositiveImpressions(), positionMap);
// Output data to spreadsheet
var rows = [];
for (var key in positionMap) {
var ctr = calculateCtr(positionMap[key]);
var mapSizeLessOne = POSITION_MAP_SIZE – 1;
var row = [
key <= mapSizeLessOne ? key – 1 + ‘ to ‘ + key : ‘>’ + mapSizeLessOne,
positionMap[key].numKeywords, positionMap[key].totalImpressions,
positionMap[key].totalClicks, ctr.toFixed(2), positionMap[key].totalCost
];
rows.push(row);
}
spreadsheet.getRangeByName(‘position_body’).setValues(rows);
}
/**
* Calculates the click through rate given an entry from a map.
* @param {object} mapEntry – an entry from the map
* @return {number} the click through rate
*/
function calculateCtr(mapEntry) {
var ctr = 0;
if (mapEntry.numKeywords > 0) {
ctr = (mapEntry.totalClicks / mapEntry.totalImpressions) * 100;
}
return ctr;
}
/**
* Gets an empty stat map.
* @param {number} size – the number of entries in the stat map.
* @return {array} the empty quality stat map.
*/
function getEmptyStatMap(size) {
var qualityScoreMap = [];
for (i = 1; i <= size; i++) {
qualityScoreMap[i] =
{numKeywords: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
}
return qualityScoreMap;
}
/**
* Uses the given keyword iterator and populates the given quality score map.
* @param {Iterator} keywordIterator – the keywords to use for getting scores.
* @param {array} qualityScoreMap – the score map to fill with keyword data.
*/
function computeQualityData(keywordIterator, qualityScoreMap) {
while (keywordIterator.hasNext()) {
var keyword = keywordIterator.next();
var stats = keyword.getStatsFor(‘LAST_WEEK’);
var data = qualityScoreMap[keyword.getQualityScore()];
if (data) {
data.numKeywords++;
data.totalImpressions += stats.getImpressions();
data.totalClicks += stats.getClicks();
data.totalCost += stats.getCost();
}
}
}
/**
* Uses the given keyword iterator and populates the given position map.
* @param {Iterator} keywordIterator – the keywords to use for getting scores.
* @param {array} positionMap – the map to fill with keyword data.
*/
function computePositionData(keywordIterator, positionMap) {
while (keywordIterator.hasNext()) {
var keyword = keywordIterator.next();
var stats = keyword.getStatsFor(‘LAST_WEEK’);
var index =
Math.min(Math.ceil(stats.getAveragePosition()), POSITION_MAP_SIZE);
var data = positionMap[index];
data.numKeywords++;
data.totalImpressions += stats.getImpressions();
data.totalClicks += stats.getClicks();
data.totalCost += stats.getCost();
}
}
/**
* 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 email and throws a descriptive error if the user
* has not changed the email from the default fake address.
*
* @param {string} email The email address.
* @throws {Error} If the email is the default fake address.
*/
function validateEmail(email) {
if (email == ‘[email protected]’) {
throw new Error(‘Please use a valid email address.’);
}
}
/**
* 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(spreadsheeturl) {
if (spreadsheeturl == ‘YOUR_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(spreadsheeturl);
}
Como Configurar
5. Relatório da Consulta da Pesquisa – Por Google Ads. Este é um bom script para gerar insights de novas palavras-chave que estão gerando resultado para sua conta ou para negativar termos que usuários estão pesquisando sem correlação com seu negócio. O script utiliza o Relatório de desempenho da consulta da pesquisa para encontrar esses termos.
// 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
//
// //www.apache.org/licenses/LICENSE-2.0
//
// 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 Search Query Report
*
* @overview The Search Query Report script uses the Search Query Performance
* Report to find undesired search terms and add them as negative (or
* positive) exact keywords. See
* //developers.google.com/adwords/scripts/docs/solutions/search-query
* for more details.
*
* @author AdWords Scripts Team [[email protected]]
*
* @version 1.0.1
*
* @changelog
* – version 1.0.1
* – Upgrade to API version v201609.
* – version 1.0
* – Released initial version.
*/
// Minimum number of impressions to consider “enough data”
var IMPRESSIONS_THRESHOLD = 100;
// Cost-per-click (in account currency) we consider an expensive keyword.
var AVERAGE_CPC_THRESHOLD = 1; // $1
// Threshold we use to decide if a keyword is a good performer or bad.
var CTR_THRESHOLD = 0.5; // 0.5%
// If ctr is above threshold AND our conversion cost isn’t too high,
// it’ll become a positive keyword.
var COST_PER_CONVERSION_THRESHOLD = 10; // $10
// One currency unit is one million micro amount.
var MICRO_AMOUNT_MULTIPLIER = 1000000;
/**
* Configuration to be used for running reports.
*/
var REPORTING_OPTIONS = {
// Comment out the following line to default to the latest reporting version.
apiVersion: ‘v201705’
};
function main() {
var report = AdWordsApp.report(
‘SELECT Query, Clicks, Cost, Ctr, ConversionRate,’ +
‘ CostPerConversion, Conversions, CampaignId, AdGroupId ‘ +
‘ FROM SEARCH_QUERY_PERFORMANCE_REPORT ‘ +
‘ WHERE ‘ +
‘ Conversions > 0’ +
‘ AND Impressions > ‘ + IMPRESSIONS_THRESHOLD +
‘ AND AverageCpc > ‘ +
(AVERAGE_CPC_THRESHOLD * MICRO_AMOUNT_MULTIPLIER) +
‘ DURING LAST_7_DAYS’, REPORTING_OPTIONS);
var rows = report.rows();
var negativeKeywords = {};
var positiveKeywords = {};
var allAdGroupIds = {};
// Iterate through search query and decide whether to
// add them as positive or negative keywords (or ignore).
while (rows.hasNext()) {
var row = rows.next();
if (parseFloat(row[‘Ctr’]) < CTR_THRESHOLD) {
addToMultiMap(negativeKeywords, row[‘AdGroupId’], row[‘Query’]);
allAdGroupIds[row[‘AdGroupId’]] = true;
} else if (parseFloat(row[‘CostPerConversion’]) <
COST_PER_CONVERSION_THRESHOLD) {
addToMultiMap(positiveKeywords, row[‘AdGroupId’], row[‘Query’]);
allAdGroupIds[row[‘AdGroupId’]] = true;
}
}
// Copy all the adGroupIds from the object into an array.
var adGroupIdList = [];
for (var adGroupId in allAdGroupIds) {
adGroupIdList.push(adGroupId);
}
// Add the keywords as negative or positive to the applicable ad groups.
var adGroups = AdWordsApp.adGroups().withIds(adGroupIdList).get();
while (adGroups.hasNext()) {
var adGroup = adGroups.next();
if (negativeKeywords[adGroup.getId()]) {
for (var i = 0; i < negativeKeywords[adGroup.getId()].length; i++) {
adGroup.createNegativeKeyword(
‘[‘ + negativeKeywords[adGroup.getId()][i] + ‘]’);
}
}
if (positiveKeywords[adGroup.getId()]) {
for (var i = 0; i < positiveKeywords[adGroup.getId()].length; i++) {
var keywordOperation = adGroup.newKeywordBuilder()
.withText(‘[‘ + positiveKeywords[adGroup.getId()][i] + ‘]’)
.build();
}
}
}
}
function addToMultiMap(map, key, value) {
if (!map[key]) {
map[key] = [];
}
map[key].push(value);
}
Como Configurar
6. Relatório Kratu – por Google Ads. Em nível de MCC, o script traz um relatório geral de suas múltiplas contas. Além disso, os resultados são mostrados visualmente com um mapa de calor.
// 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
//
// //www.apache.org/licenses/LICENSE-2.0
//
// 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 Kratu
*
* @overview The Kratu script is a flexible MCC-level report showing several
* performance signals for each account visually as a heat map. See
* //developers.google.com/adwords/scripts/docs/solutions/kratu
* for more details.
*
* @author AdWords Scripts Team [[email protected]]
*
* @version 1.0.2
*
* @changelog
* – version 1.0.2
* – Fixed bug with run frequency to allow the script to run daily.
* – version 1.0.1
* – Added validation for external spreadsheet setup.
* – Updated reporting version to v201609.
* – version 1.0
* – Released initial version.
*/
var CONFIG = {
// URL to the main / template spreadsheet
SPREADSHEET_URL: ‘YOUR_SPREADSHEET_URL’
};
/**
* Configuration to be used for running reports.
*/
var REPORTING_OPTIONS = {
// Comment out the following line to default to the latest reporting version.
apiVersion: ‘v201705’
};
/**
* Main method, coordinate and trigger either new report creation or continue
* unfinished report.
*/
function main() {
init();
if (spreadsheetManager.hasUnfinishedRun()) {
continueUnfinishedReport();
} else {
var reportFrequency = settingsManager.getSetting(‘ReportFrequency’, true);
var lastReportStart = spreadsheetManager.getLastReportStartTimestamp();
if (!lastReportStart ||
dayDifference(lastReportStart, getTimestamp()) >= reportFrequency) {
startNewReport();
} else {
debug(‘Nothing to do’);
}
}
}
/**
* Initialization procedures to be done before anything else.
*/
function init() {
spreadsheetManager.readSignalDefinitions();
settingsManager.readSettings();
}
/**
* Continues an unfinished report. This happens whenever there are accounts
* that are not processed within the last report. This method picks these
* up, processes them and marks the report as completed if no accounts are
* left.
*/
function continueUnfinishedReport() {
debug(‘Continuing unfinished report: ‘ +
spreadsheetManager.getCurrentRunSheet().getUrl());
var iterator = spreadsheetManager.getUnprocessedAccountIterator();
var processed = 0;
while (iterator.hasNext() &&
processed++ < settingsManager.getSetting(‘NumAccountsProcess’, true)) {
var account = iterator.next();
processAccount(account);
}
writeAccountDataToSpreadsheet();
if (processed > 0 && spreadsheetManager.allAccountsProcessed()) {
debug(‘All accounts processed, marking report as complete’);
// Remove protection from sheets, allow changes again
spreadsheetManager.removeProtection();
spreadsheetManager.markRunAsProcessed();
sendEmail();
}
debug(‘Processed ‘ + processed + ‘ accounts’);
}
/**
* Creates a new report by copying the report template to a new spreadsheet,
* gathering all accounts under the MCC and mark them as not processed.
* Please note that this method will not actually process any accounts.
*/
function startNewReport() {
debug(‘Creating new report’);
// Protect the sheets that shouldn’t be changed during execution
spreadsheetManager.setProtection();
// Delete all account info
spreadsheetManager.clearAllAccountInfo();
// Iterate over accounts
var accountSelector = MccApp.accounts();
var accountLabel = settingsManager.getSetting(‘AccountLabel’, false);
if (accountLabel) {
accountSelector.withCondition(“LabelNames CONTAINS ‘” + accountLabel + “‘”);
}
var accountIterator = accountSelector.get();
while (accountIterator.hasNext()) {
var account = accountIterator.next();
debug(‘Adding account: ‘ + account.getCustomerId());
spreadsheetManager.addAccount(account.getCustomerId());
}
// Now add the run
var newRunSheet = spreadsheetManager.addRun();
debug(‘New report created at ‘ + newRunSheet.getUrl());
}
/**
* Processes a single account.
*
* @param {object} account the AdWords account object
*/
function processAccount(account) {
debug(‘- Processing ‘ + account.getCustomerId());
MccApp.select(account);
signalManager.processAccount(account);
spreadsheetManager.markAccountAsProcessed(account.getCustomerId());
}
/**
* After processing & gathering data for all accounts,
* write it to the spreadsheet.
*/
function writeAccountDataToSpreadsheet() {
var accountInfos = signalManager.getAccountInfos();
spreadsheetManager.writeHeaderRow();
for (var i = 0; i < accountInfos.length; i++) {
var accountInfo = accountInfos[i];
spreadsheetManager.writeDataRow(accountInfo);
}
}
/**
* Sends email if an email was provided in the settings.
* Otherwise does nothing.
*/
var sendEmail = function() {
var recipientEmail = settingsManager.getSetting(‘RecipientEmail’, false);
if (recipientEmail) {
MailApp.sendEmail(recipientEmail,
‘Kratu Report is ready’,
spreadsheetManager.getCurrentRunSheet().getUrl());
debug(‘Email sent to ‘ + recipientEmail);
}
};
/**
* Returns the number of days between two timestamps.
*
* @param {number} time1 the newer (more recent) timestamps
* @param {number} time2 the older timestamps
* @return {number} number of full days between the given dates
*/
var dayDifference = function(time1, time2) {
return parseInt((time2 – time1) / (24 * 3600 * 1000));
};
/**
* Returns the current timestamp.
*
* @return {number} the current timestamp
*/
function getTimestamp() {
return new Date().getTime();
}
/**
* Module for calculating account signals and infos to be shown in the report.
*
* @return {object} callable functions corresponding to the available
* actions
*/
var signalManager = (function() {
var accountInfos = new Array();
/**
* Processes one account, which in 2 steps adds an accountInfo object
* to the list.
* – Calculate the raw signals
* – Postprocess the raw signals (normalize scores, …)
*
* @param {object} account the AdWords account object
*/
var processAccount = function(account) {
var rawSignals = calculateRawSignals(account);
var accountInfo = {
account: account,
rawSignals: rawSignals
};
processSignals(accountInfo);
accountInfos.push(accountInfo);
};
/**
* Returns an array of all processed accounts so far. These are ordered by
* decreasing score.
*
* @return {object} array of the accountInfo objects
*/
var getAccountInfos = function() {
accountInfos.sort(function(a, b) {
return b.score – a.score;
});
return accountInfos;
};
/**
* Normalizes a raw signal value based in the signal’s definition
* (min, max values).
*
* @param {object} signalDefinition definition of the signal
* @param {number} value numeric value of that signal
* @return {number} the normalized value
*/
var normalize = function(signalDefinition, value) {
var min = signalDefinition.min;
var max = signalDefinition.max;
if (signalDefinition.direction == ‘High’) {
if (value >= max)
return 1;
if (value <= min)
return 0;
return (value – min) / (max – min);
} else if (signalDefinition.direction == ‘Low’) {
if (value >= max)
return 0;
if (value <= min)
return 1;
return 1 – ((value – min) / (max – min));
} else {
return value;
}
};
/**
* Post-processes the raw signals.
*
* @param {object} accountInfo the object storing all info about that account
* (including raw signals)
*/
var processSignals = function(accountInfo) {
var signalDefinitions = spreadsheetManager.getSignalDefinitions();
var sumWeights = spreadsheetManager.getSumWeights();
var sumScore = 0;
accountInfo.signals = {};
for (var i = 0; i < signalDefinitions.length; i++) {
var signalDefinition = signalDefinitions[i];
if (signalDefinition.includeInReport == ‘Yes’) {
var value = accountInfo.rawSignals[signalDefinition.name];
accountInfo.signals[signalDefinition.name] = {
definition: signalDefinition,
value: value,
displayValue: value
};
if (signalDefinition.type == ‘Number’) {
var normalizedValue = normalize(signalDefinition, value);
var signalScore = normalizedValue * signalDefinition.weight;
sumScore += signalScore;
accountInfo.signals[signalDefinition.name].normalizedValue =
normalizedValue;
accountInfo.signals[signalDefinition.name].signalScore = signalScore;
}
}
}
accountInfo.scoreSum = sumScore;
accountInfo.scoreWeights = sumWeights;
accountInfo.score = sumScore / sumWeights;
};
/**
* Calculate the raw signals.
*
* @param {object} account the AdWords account object
* @return {object} an associative array containing raw signals
* (as name -> value pairs)
*/
var calculateRawSignals = function(account) {
// Use reports for signal creation, dynamically create an AWQL query here
var signalDefinitions = spreadsheetManager.getSignalDefinitions();
var signalFields = [];
for (var i = 0; i < signalDefinitions.length; i++) {
var signalDefinition = signalDefinitions[i];
signalFields.push(signalDefinition.name);
}
var query = ‘SELECT ‘ + signalFields.join(‘,’) +
‘ FROM ACCOUNT_PERFORMANCE_REPORT DURING ‘ +
settingsManager.getSetting(‘ReportPeriod’, true);
var report = AdWordsApp.report(query, REPORTING_OPTIONS);
var rows = report.rows();
// analyze the rows (should be only one)
var rawSignals = {};
while (rows.hasNext()) {
var row = rows.next();
for (var i = 0; i < signalDefinitions.length; i++) {
var signalDefinition = signalDefinitions[i];
var value = row[signalDefinition.name];
if (value.indexOf(‘%’) > -1) {
value = parseFloat(value) / 100.0;
}
rawSignals[signalDefinition.name] = value;
}
}
return rawSignals;
};
// Return the external interface.
return {
processAccount: processAccount,
getAccountInfos: getAccountInfos
};
})();
/**
* Module for interacting with the spreadhsheets. Offers several
* functions that other modules can use when storing / retrieving data
* In general, there are two spreadsheets involved:
* – a main spreadsheet containing processing information, settings
* and a template for the reports
* – a report spreadsheet for each run (one loop over all accounts)
*
* @return {object} callable functions corresponding to the available
* actions
*/
var spreadsheetManager = (function() {
validateConfig();
var spreadsheet = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
var currentRunSheet = null;
var accountsTab = spreadsheet.getSheetByName(‘Accounts’);
var historyTab = spreadsheet.getSheetByName(‘History’);
var signalsTab = spreadsheet.getSheetByName(‘Signals’);
var settingsTab = spreadsheet.getSheetByName(‘Settings’);
var templateTab = spreadsheet.getSheetByName(‘Template’);
var processedAccounts = 0;
var signalDefinitions;
var sumWeights;
/**
* Adds protection and notes to all sheets that should not be
* changed while a report is being processed.
*/
var setProtection = function() {
setSheetProtection(signalsTab);
setSheetProtection(settingsTab);
setSheetProtection(templateTab);
};
/**
* Adds protection and notes to a sheet / tab.
*
* @param {object} the sheet to add protection to
*/
var setSheetProtection = function(tab) {
var protection = tab.protect().setDescription(tab.getName() +
‘ Protection’);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}
tab.getRange(‘A1’).setNote(‘A report is currently being executed, ‘ +
‘you can not edit this sheet until it is finished.’);
};
/**
* Adds a protection and notes to all sheets that should not be
* changed while a report is being processed.
*/
var removeProtection = function() {
removeSheetProtection(signalsTab);
removeSheetProtection(settingsTab);
removeSheetProtection(templateTab);
};
/**
* Remove the protection from a sheet / tab.
*
* @param {object} the sheet to remove protection from
*/
var removeSheetProtection = function(tab) {
var protection = tab.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
if (protection && protection.canEdit()) {
protection.remove();
}
tab.clearNotes();
};
/**
* Reads and returns the range of settings in the main spreadsheet.
*
* @return {object} the range object containing all settings
*/
var readSettingRange = function() {
return settingsTab.getRange(2, 1, settingsTab.getLastRow(), 3);
};
/**
* Read and return the signal definitions as defined in the Signals tab
* of the general spreadsheet. See below for how a signal definition object
* looks like.
*
* @param {object} range the range of cells
* @return {object} an array of signal definition objects
*/
var readSignalDefinitions = function() {
signalDefinitions = new Array();
var range = signalsTab.getRange(2, 1, signalsTab.getLastRow(), 9);
var values = range.getValues();
for (var i = 0; i < range.getNumRows(); i++) {
if (values[i][0] == ”)
continue;
var signalDefinition = {
name: values[i][0],
displayName: values[i][1],
includeInReport: values[i][2],
type: values[i][3],
direction: values[i][4],
format: values[i][5],
weight: values[i][6],
min: values[i][7],
max: values[i][8]
};
signalDefinitions.push(signalDefinition);
}
calculateSumWeights();
debug(‘Using ‘ + signalDefinitions.length + ‘ signals’);
};
/**
* Returns an array of signal definitions to work with.
*
* @return {object} array of signal definitions to work with
*/
var getSignalDefinitions = function() {
return signalDefinitions;
};
/**
* Returns the sum of weights of all signal definitions
*
* @return {number} sum of weights of all signal definitions
*/
var getSumWeights = function() {
return sumWeights;
};
/**
* Calculates the overall sum of score weights for normalization of the score.
*/
var calculateSumWeights = function() {
sumWeights = 0;
for (var i = 0; i < signalDefinitions.length; i++) {
var signalDefinition = signalDefinitions[i];
if (signalDefinition.type == ‘Number’ &&
signalDefinition.includeInReport == ‘Yes’) {
sumWeights += signalDefinition.weight;
}
}
};
/**
* Adds a “run” (loop over all accounts) to the general spreadsheet.
*/
var addRun = function() {
// use formatted date in spreadsheet name and date cell
var timezone = AdWordsApp.currentAccount().getTimeZone();
var formattedDate = Utilities.formatDate(new Date(),
timezone, ‘MMM dd, yyyy’);
var runSpreadsheet = spreadsheet.copy(spreadsheet.getName() +
‘ – ‘ + formattedDate);
runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName(‘Accounts’));
runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName(‘History’));
runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName(‘Settings’));
runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName(‘Parameters’));
runSpreadsheet.deleteSheet(runSpreadsheet.getSheetByName(‘Signals’));
runSpreadsheet.getSheetByName(‘Template’).setName(‘Report’);
removeSheetProtection(runSpreadsheet.getSheetByName(‘Report’));
historyTab.appendRow([getTimestamp(), null, runSpreadsheet.getUrl()]);
historyTab.getRange(historyTab.getLastRow(), 1, 1, 3).clearFormat();
runSpreadsheet.getRangeByName(‘AccountID’).setValue(
AdWordsApp.currentAccount().getCustomerId());
runSpreadsheet.getRangeByName(‘Date’).setValue(formattedDate);
return runSpreadsheet;
};
/**
* Checks if there is an unfinished (=not all accounts processed yet)
* report in the run history list.
*
* @return {boolean} whether there is an unfinished report
*/
var hasUnfinishedRun = function() {
var lastRow = historyTab.getLastRow();
// has no run at all
if (lastRow == 1) {
return false;
}
var lastRunEndDate = historyTab.getRange(lastRow, 2, 1, 1).getValue();
if (lastRunEndDate) {
return false;
}
return true;
};
/**
* Marks the current report (a.k.a run) as finished by adding an end date.
*/
var markRunAsProcessed = function() {
var lastRow = historyTab.getLastRow();
if (lastRow > 1) {
historyTab.getRange(lastRow, 2, 1, 1).setValue(getTimestamp());
}
};
/**
* Returns the start timestamp of the last unfinished report.
*
* @return {number} the timestamp of the last unfinished report (null if
* there is none)
*/
var getLastReportStartTimestamp = function() {
var lastRow = historyTab.getLastRow();
if (lastRow > 1) {
return historyTab.getRange(lastRow, 1, 1, 1).getValue();
} else {
return null;
}
};
/**
* Returns the current run sheet to be used for report generation.
* This is always the last one in the History tab of the general sheet.
*
* @return {object} the current run sheet
*/
var getCurrentRunSheet = function() {
if (currentRunSheet != null)
return currentRunSheet;
var range = historyTab.getRange(historyTab.getLastRow(), 3, 1, 1);
var url = range.getValue();
currentRunSheet = SpreadsheetApp.openByUrl(url);
return currentRunSheet;
};
/**
* Adds an account to the list of ‘known’ accounts.
*
* @param {string} cid the cid of the account
*/
var addAccount = function(cid) {
var maxRow = accountsTab.appendRow([cid]);
accountsTab.getRange(accountsTab.getLastRow(), 1, 1, 2).clearFormat();
};
/**
* Marks an account as processed in the general sheet. Like this,
* the script can be executed several times and will always
* run for a batch of unprocessed accounts.
*
* @param {string} cid the customer id of the account that has been processed
*/
var markAccountAsProcessed = function(cid) {
var range = accountsTab.getRange(2, 1, accountsTab.getLastRow() – 1, 2);
var values = range.getValues();
for (var i = 0; i < range.getNumRows(); i++) {
var rowCid = values[i][0];
if (cid == rowCid) {
accountsTab.getRange(i + 2, 2).setValue(getTimestamp());
processedAccounts++;
}
}
};
/**
* Clears the list of ‘known’ accounts.
*/
var clearAllAccountInfo = function() {
var lastRow = accountsTab.getLastRow();
if (lastRow > 1) {
accountsTab.deleteRows(2, lastRow – 1);
}
};
/**
* Creates a selector for the next batch of accounts that are not
* processed yet.
*
* @return {object} a selector that can be used for parallel processing or
* getting an iterator
*/
var getUnprocessedAccountIterator = function() {
var accounts = getUnprocessedAccounts();
var selector = MccApp.accounts().withIds(accounts);
var iterator = selector.get();
return iterator;
};
/**
* Reads and returns the next batch of unprocessed accounts from the general
* spreadsheet.
*
* @return {object} an array of unprocessed cids
*/
var getUnprocessedAccounts = function() {
var accounts = [];
var range = accountsTab.getRange(2, 1, accountsTab.getLastRow() – 1, 2);
for (var i = 0; i < range.getNumRows(); i++) {
var cid = range.getValues()[i][0];
var processed = range.getValues()[i][1];
if (processed != ” || accounts.length >=
settingsManager.getSetting(‘NumAccountsProcess’, true)) {
continue;
}
accounts.push(cid);
}
return accounts;
};
/**
* Scans the list of accounts and returns true if all of them
* are processed.
*
* @return {boolean} true, if all accounts are processed
*/
var allAccountsProcessed = function() {
var range = accountsTab.getRange(2, 1, accountsTab.getLastRow() – 1, 2);
for (var i = 0; i < range.getNumRows(); i++) {
var cid = range.getValues()[i][0];
var processed = range.getValues()[i][1];
if (processed) {
continue;
}
return false;
}
return true;
};
/**
* Writes the data headers (signal names) in the current run sheet.
*/
var writeHeaderRow = function() {
var sheet = getCurrentRunSheet();
var reportTab = sheet.getSheetByName(‘Report’);
var row = [”];
for (var i = 0; i < signalDefinitions.length; i++) {
var signalDefinition = signalDefinitions[i];
if (signalDefinition.includeInReport == ‘Yes’) {
row.push(signalDefinition.displayName);
}
}
row.push(‘Score’);
var range = reportTab.getRange(4, 1, 1, row.length);
range.setValues([row]);
range.clearFormat();
range.setFontWeight(‘bold’);
range.setBackground(‘#38c’);
range.setFontColor(‘#fff’);
};
/**
* Writes a row of data (signal values) in the current run sheet.
*
* @param {object} accountInfo the accountInfo object containing the
* calculated signals
*/
var writeDataRow = function(accountInfo) {
// prepare the data
var sheet = getCurrentRunSheet();
var tab = sheet.getSheetByName(‘Report’);
var row = [”];
for (var i = 0; i < signalDefinitions.length; i++) {
var signalDefinition = signalDefinitions[i];
if (signalDefinition.includeInReport == ‘Yes’) {
var displayValue =
accountInfo.signals[signalDefinition.name].displayValue;
row.push(displayValue);
}
}
row.push(accountInfo.score);
// write it
tab.appendRow(row);
// now do the formatting
var currentRow = tab.getLastRow();
var rowRange = tab.getRange(currentRow, 1, 1, row.length);
rowRange.clearFormat();
// arrays for number formats and colors, first fill them with values
// and later apply to the row
var dataRange = tab.getRange(currentRow, 2, 1, row.length – 1);
var fontColors = [[]];
var backgroundColors = [[]];
var numberFormats = [[]];
var colIndex = 0;
for (var i = 0; i < signalDefinitions.length; i++) {
var signalDefinition = signalDefinitions[i];
if (signalDefinition.includeInReport == ‘Yes’) {
var value = accountInfo.signals[signalDefinition.name].value;
var displayValue =
accountInfo.signals[signalDefinition.name].displayValue;
var normalizedValue =
accountInfo.signals[signalDefinition.name].normalizedValue;
var colors = [2];
if (signalDefinition.type == ‘Number’) {
numberFormats[0][colIndex] = signalDefinition.format;
colors = getNumberColors(normalizedValue);
} else if (signalDefinition.type == ‘String’) {
colors = getStringColors(value);
}
fontColors[0][colIndex] = colors[0];
backgroundColors[0][colIndex] = colors[1];
colIndex++;
}
}
// formatting for the score (last column)
numberFormats[0][colIndex] = ‘0.00%’;
var scoreColors = getNumberColors(accountInfo.score);
fontColors[0][colIndex] = scoreColors[0];
backgroundColors[0][colIndex] = scoreColors[1];
// now actually apply the formats
dataRange.setNumberFormats(numberFormats);
dataRange.setFontColors(fontColors);
dataRange.setBackgroundColors(backgroundColors);
};
/**
* Helper method for creating the array of colors based on the given
* setting names.
*
* @param {string} settingFontColor name of the setting to use as font color
* @param {string} settingBackgroundColor name of the setting to use as
* background color
* return {object} an array with the colors to apply
* (index 0 -> font color, index 1 -> background color)
*/
var getColors = function(settingFontColor, settingBackgroundColor) {
var colors = [];
colors[0] = settingsManager.getSetting(settingFontColor, false);
colors[1] = settingsManager.getSetting(settingBackgroundColor, false);
return colors;
};
/**
* Helper method for returning the “string” colors for a certain value.
*
* @param {string} stringValue the value of the cell
* return {object} an array with the colors to apply
* (index 0 -> font color, index 1 -> background color)
*/
var getStringColors = function(stringValue) {
return getColors(‘StringFgColor’, ‘StringBgColor’);
};
/**
* Helper method for applying the “number” format to a certain range.
* Numeric value cells have different formats depending on their score value
* (defined by the settings), this method applies these formats.
*
* @param {number} numericValue the value of the cell
* return {object} an array with the colors to apply
* (index 0 -> font color, index 1 -> background color)
*/
var getNumberColors = function(numericValue) {
var level1MinValue = settingsManager.getSetting(‘Level1MinValue’, false);
var level2MinValue = settingsManager.getSetting(‘Level2MinValue’, false);
var level3MinValue = settingsManager.getSetting(‘Level3MinValue’, false);
var level4MinValue = settingsManager.getSetting(‘Level4MinValue’, false);
var level5MinValue = settingsManager.getSetting(‘Level5MinValue’, false);
if (level5MinValue && numericValue > level5MinValue) {
return getColors(‘Level5FgColor’, ‘Level5BgColor’);
} else if (level4MinValue && numericValue > level4MinValue) {
return getColors(‘Level4FgColor’, ‘Level4BgColor’);
} else if (level3MinValue && numericValue > level3MinValue) {
return getColors(‘Level3FgColor’, ‘Level3BgColor’);
} else if (level2MinValue && numericValue > level2MinValue) {
return getColors(‘Level2FgColor’, ‘Level2BgColor’);
} else if (level1MinValue && numericValue > level1MinValue) {
return getColors(‘Level1FgColor’, ‘Level1BgColor’);
}
// if no level reached, no coloring
var defaultColors = [null, null];
return defaultColors;
};
// Return the external interface.
return {
setProtection: setProtection,
removeProtection: removeProtection,
readSettingRange: readSettingRange,
readSignalDefinitions: readSignalDefinitions,
getSignalDefinitions: getSignalDefinitions,
getSumWeights: getSumWeights,
addRun: addRun,
hasUnfinishedRun: hasUnfinishedRun,
markRunAsProcessed: markRunAsProcessed,
getLastReportStartTimestamp: getLastReportStartTimestamp,
getCurrentRunSheet: getCurrentRunSheet,
addAccount: addAccount,
markAccountAsProcessed: markAccountAsProcessed,
clearAllAccountInfo: clearAllAccountInfo,
getUnprocessedAccountIterator: getUnprocessedAccountIterator,
allAccountsProcessed: allAccountsProcessed,
writeHeaderRow: writeHeaderRow,
writeDataRow: writeDataRow
};
})();
/**
* Module responsible for maintaining a list of common settings. These
* settings are read from the general spreadsheet (using the
* spreadsheetManager) and are then retrieved by other modules during
* processing.
*
* @return {object} callable functions corresponding to the available
* actions
*/
var settingsManager = (function() {
var settings = [];
/**
* Reads the settings from the general spreadsheet.
*/
var readSettings = function() {
var settingsRange = spreadsheetManager.readSettingRange();
for (var i = 1; i <= settingsRange.getNumRows(); i++) {
var key = settingsRange.getCell(i, 1).getValue();
var type = settingsRange.getCell(i, 2).getValue();
var value = settingsRange.getCell(i, 3).getValue();
if (type == ‘Color’) {
value = settingsRange.getCell(i, 3).getBackground();
}
if (!key || !value) {
continue;
}
var setting = {
key: key,
type: type,
value: value
};
settings.push(setting);
}
debug(‘Read ‘ + settings.length + ‘ settings’);
};
/**
* Returns the value of a particular setting.
*
* @param {string} key the name of the setting
* @param {boolean} mandatory flag indicating this is a mandatory setting
* (has to return a value)
* @return {object} the value of the setting
*/
var getSetting = function(key, mandatory) {
for (var i = 0; i < settings.length; i++) {
var setting = settings[i];
if (setting.key == key && setting.value)
return setting.value;
}
if (mandatory) {
throw ‘Setting \” + key + ‘\’ is not set!’;
}
return null;
};
// Return the external interface.
return {
readSettings: readSettings,
getSetting: getSetting
};
})();
/**
* Wrapper for Logger.log.
*
* @param {string} t The text to log
*/
function debug(t) {
Logger.log(t);
}
/**
* Validates the provided spreadsheet URL to make sure that it’s set up
* properly. Throws a descriptive error message if validation fails.
*
* @throws {Error} If the spreadsheet URL hasn’t been set
*/
function validateConfig() {
if (CONFIG.SPREADSHEET_URL == ‘YOUR_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.’);
}
}
Como Configurar
7. PageSpeed Insights: análise para dispositivos móveis – Por Google Ads. O Google tem enfatizado a experiência mobile cada vez mais em suas atualizações, sendo assim, esse script traz um relatório do Google Page Insights para suas páginas de destino.
// 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
//
// //www.apache.org/licenses/LICENSE-2.0
//
// 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.
/**
* @fileoverview Mobile Performance from PageSpeed Insights – Single Account
*
* Produces a report showing how well landing pages are set up for mobile
* devices and highlights potential areas for improvement. See :
* //developers.google.com/adwords/scripts/docs/solutions/mobile-pagespeed
* for more details.
*
* @author AdWords Scripts Team [[email protected]]
*
* @version 1.3.3
*
* @changelog
* – version 1.3.3
* – Added guidance for desktop analysis.
* – version 1.3.2
* – Bugfix to improve table sorting comparator.
* – version 1.3.1
* – Bugfix for handling the absence of optional values in PageSpeed response.
* – version 1.3
* – Removed the need for the user to take a copy of the spreadsheet.
* – Added the ability to customize the Campaign and Ad Group limits.
* – version 1.2.1
* – Improvements to time zone handling.
* – version 1.2
* – Bug fix for handling empty results from URLs.
* – Error reporting in spreadsheet for failed URL fetches.
* – version 1.1
* – Updated the comments to be in sync with the guide.
* – version 1.0
* – Released initial version.
*/
// See “Obtaining an API key” at
// //developers.google.com/adwords/scripts/docs/solutions/mobile-pagespeed
var API_KEY = ‘INSERT_PAGESPEED_API_KEY_HERE’;
var EMAIL_RECIPIENTS = [‘INSERT_EMAIL_ADDRESS_HERE’];
// If you wish to add extra URLs to checked, list them here as a
// comma-separated list eg. [‘//abc.xyz’, ‘//www.google.com’]
var EXTRA_URLS_TO_CHECK = [];
// By default, the script returns analysis of how the site performs on mobile.
// Change the following from ‘mobile’ to ‘desktop’ to perform desktop analysis.
var PLATFORM_TYPE = ‘mobile’;
/**
* The URL of the template spreadsheet for each report created.
*/
var SPREADSHEET_TEMPLATE =
‘//docs.google.com/spreadsheets/d/1SKLXUiorvgs2VuPKX7NGvcL68pv3xEqD7ZcqsEwla4M/edit’;
var PAGESPEED_URL =
‘//www.googleapis.com/pagespeedonline/v2/runPagespeed?’;
/*
* The maximum number of Campaigns to sample within the account.
*/
var CAMPAIGN_LIMIT = 50000;
/*
* The maximum number of Ad Groups to sample within the account.
*/
var ADGROUP_LIMIT = 50000;
/**
* These are the sampling limits for how many of each element will be examined
* in each AdGroup.
*/
var KEYWORD_LIMIT = 20;
var SITELINK_LIMIT = 20;
var AD_LIMIT = 30;
/**
* Specifies the amount of time in seconds required to do the URL fetching and
* result generation. As this is the last step, entities in the account will be
* iterated over until this point.
*/
var URL_FETCH_TIME_SECS = 8 * 60;
/**
* Specifies the amount of time in seconds required to write to and format the
* spreadsheet.
*/
var SPREADSHEET_PREP_TIME_SECS = 4 * 60;
/**
* Represents the number of retries to use with the PageSpeed service.
*/
var MAX_RETRIES = 3;
/**
* The main entry point for execution.
*/
function main() {
if (!defaultsChanged()) {
Logger.log(‘Please change the default configuration values and retry’);
return;
}
var accountName = AdWordsApp.currentAccount().getName();
var urlStore = getUrlsFromAccount();
var result = getPageSpeedResultsForUrls(urlStore);
var spreadsheet = createPageSpeedSpreadsheet(accountName +
‘: PageSpeed Insights – Mobile Analysis’, result);
spreadsheet.addEditors(EMAIL_RECIPIENTS);
sendEmail(spreadsheet.getUrl());
}
/**
* Sends an email to the user with the results of the run.
*
* @param {string} url URL of the spreadsheet.
*/
function sendEmail(url) {
var footerStyle = ‘color: #aaaaaa; font-style: italic;’;
var scriptsLink = ‘//developers.google.com/adwords/scripts/’;
var subject = ‘AdWords PageSpeed URL-Sampling Script Results – ‘ +
getDateStringInTimeZone(‘dd MMM yyyy’);
var htmlBody = ‘<html><body>’ +
‘<p>Hello,</p>’ +
‘<p>An AdWords Script has run successfully and the output is available ‘ +
‘here:’ +
‘<ul><li><a href=”‘ + url +
‘”>AdWords PageSpeed URL-Sampling Script Results</a></li></ul></p>’ +
‘<p>Regards,</p>’ +
‘<span style=”‘ + footerStyle + ‘”>This email was automatically ‘ +
‘generated by <a href=”‘ + scriptsLink + ‘”>AdWords Scripts</a>.<span>’ +
‘</body></html>’;
var body = ‘Please enable HTML to view this report.’;
var options = {htmlBody: htmlBody};
MailApp.sendEmail(EMAIL_RECIPIENTS, subject, body, options);
}
/**
* Checks to see that placeholder defaults have been changed.
*
* @return {boolean} true if placeholders have been changed, false otherwise.
*/
function defaultsChanged() {
if (API_KEY == ‘INSERT_PAGESPEED_API_KEY_HERE’ ||
SPREADSHEET_TEMPLATE == ‘INSERT_SPREADSHEET_URL_HERE’ ||
JSON.stringify(EMAIL_RECIPIENTS) ==
JSON.stringify([‘INSERT_EMAIL_ADDRESS_HERE’])) {
return false;
}
return true;
}
/**
* Creates a new PageSpeed spreadsheet and populates it with result data.
*
* @param {string} name The name to give to the spreadsheet.
* @param {Object} pageSpeedResult The result from PageSpeed, and the number of
* URLs that could have been chosen from.
* @return {Spreadsheet} The newly-created spreadsheet.
*/
function createPageSpeedSpreadsheet(name, pageSpeedResult) {
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_TEMPLATE).copy(name);
spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
var data = pageSpeedResult.table;
var activeSheet = spreadsheet.getActiveSheet();
var rowStub = spreadsheet.getRangeByName(‘ResultRowStub’);
var top = rowStub.getRow();
var left = rowStub.getColumn();
var cols = rowStub.getNumColumns();
if (data.length > 2) { // No need to extend the template if num rows <= 2
activeSheet.insertRowsAfter(
spreadsheet.getRangeByName(‘EmptyUrlRow’).getRow(), data.length);
rowStub.copyTo(activeSheet.getRange(top + 1, left, data.length – 2, cols));
}
// Extend the formulas and headings to accommodate the data.
if (data.length && data[0].length > 4) {
var metricsRange = activeSheet
.getRange(top – 6, left + cols, data.length + 5, data[0].length – 4);
activeSheet.getRange(top – 6, left + cols – 1, data.length + 5)
.copyTo(metricsRange);
// Paste in the data values.
activeSheet.getRange(top – 1, left, data.length, data[0].length)
.setValues(data);
// Move the ‘Powered by AdWords Scripts’ to right corner of table.
spreadsheet.getRangeByName(‘PoweredByText’).moveTo(activeSheet.getRange(1,
data[0].length + 1, 1, 1));
// Set summary – date and number of URLs chosen from.
var summaryDate = getDateStringInTimeZone(‘dd MMM yyyy’);
spreadsheet.getRangeByName(‘SummaryDate’).setValue(‘Summary as of ‘ +
summaryDate + ‘. Results drawn from ‘ + pageSpeedResult.totalUrls +
‘ URLs.’);
}
// Add errors if they exist
if (pageSpeedResult.errors.length) {
var nextRow = spreadsheet.getRangeByName(‘FirstErrorRow’).getRow();
var errorRange = activeSheet.getRange(nextRow, 2,
pageSpeedResult.errors.length, 2);
errorRange.setValues(pageSpeedResult.errors);
}
return spreadsheet;
}
/**
* This function takes a collection of URLs as provided by the UrlStore object
* and gets results from the PageSpeed service. However, two important things :
* (1) It only processes a handful, as determined by URL_LIMIT.
* (2) The URLs returned from iterating on the UrlStore are in a specific
* order, designed to produce as much variety as possible (removing the need
* to process all the URLs in an account.
*
* @param {UrlStore} urlStore Object containing URLs to process.
* @return {Object} An object with three properties: ‘table’ – the 2d table of
* results, ‘totalUrls’ – the number of URLs chosen from, and errors.
*/
function getPageSpeedResultsForUrls(urlStore) {
var count = 0;
// Associative array for column headings and contextual help URLs.
var headings = {};
var errors = {};
// Record results on a per-URL basis.
var pageSpeedResults = {};
var urlTotalCount = 0;
for (var url in urlStore) {
if (hasRemainingTimeForUrlFetches()) {
var result = getPageSpeedResultForSingleUrl(url);
if (!result.error) {
pageSpeedResults[url] = result.pageSpeedInfo;
var columnsResult = result.columnsInfo;
// Loop through each heading element; the PageSpeed Insights API
// doesn’t always return URLs for each column heading, so aggregate
// these across each call to get the most complete list.
var columnHeadings = Object.keys(columnsResult);
for (var i = 0, lenI = columnHeadings.length; i < lenI; i++) {
var columnHeading = columnHeadings[i];
if (!headings[columnHeading] || (headings[columnHeading] &&
headings[columnHeading].length <
columnsResult[columnHeading].length)) {
headings[columnHeading] = columnsResult[columnHeading];
}
}
} else {
errors[url] = result.error;
}
count++;
}
urlTotalCount++;
}
var tableHeadings = [‘URL’, ‘Speed’, ‘Usability’];
var headingKeys = Object.keys(headings);
for (var y = 0, lenY = headingKeys.length; y < lenY; y++) {
tableHeadings.push(headingKeys[y]);
}
var table = [];
var pageSpeedResultsUrls = Object.keys(pageSpeedResults);
for (var r = 0, lenR = pageSpeedResultsUrls.length; r < lenR; r++) {
var resultUrl = pageSpeedResultsUrls[r];
var row = [toPageSpeedHyperlinkFormula(resultUrl)];
var data = pageSpeedResults[resultUrl];
for (var j = 1, lenJ = tableHeadings.length; j < lenJ; j++) {
row.push(data[tableHeadings[j]]);
}
table.push(row);
}
// Present the table back in the order worst-performing-first.
table.sort(function(first, second) {
var f1 = isNaN(parseInt(first[1])) ? 0 : parseInt(first[1]);
var f2 = isNaN(parseInt(first[2])) ? 0 : parseInt(first[2]);
var s1 = isNaN(parseInt(second[1])) ? 0 : parseInt(second[1]);
var s2 = isNaN(parseInt(second[2])) ? 0 : parseInt(second[2]);
if (f1 + f2 < s1 + s2) {
return -1;
} else if (f1 + f2 > s1 + s2) {
return 1;
}
return 0;
});
// Add hyperlinks to all column headings where they are available.
for (var h = 0, lenH = tableHeadings.length; h < lenH; h++) {
// Sheets cannot have multiple links in a single cell at the moment :-/
if (headings[tableHeadings[h]] &&
typeof(headings[tableHeadings[h]]) === ‘object’) {
tableHeadings[h] = ‘=HYPERLINK(“‘ + headings[tableHeadings[h]][0] +
‘”,”‘ + tableHeadings[h] + ‘”)’;
}
}
// Form table from errors
var errorTable = [];
var errorKeys = Object.keys(errors);
for (var k = 0; k < errorKeys.length; k++) {
errorTable.push([errorKeys[k], errors[errorKeys[k]]]);
}
table.unshift(tableHeadings);
return {
table: table,
totalUrls: urlTotalCount,
errors: errorTable
};
}
/**
* Given a URL, returns a spreadsheet formula that displays the URL yet links to
* the PageSpeed URL for examining this.
*
* @param {string} url The URL to embed in the Hyperlink formula.
* @return {string} A string representation of the spreadsheet formula.
*/
function toPageSpeedHyperlinkFormula(url) {
return ‘=HYPERLINK(“‘ +
‘//developers.google.com/speed/pagespeed/insights/?url=’ + url +
‘&tab=’ + PLATFORM_TYPE +'”,”‘ + url + ‘”)’;
}
/**
* Creates an object of results metrics from the parsed results of a call to
* the PageSpeed service.
*
* @param {Object} parsedPageSpeedResponse The object returned from PageSpeed.
* @return {Object} An associative array with entries for each metric.
*/
function extractResultRow(parsedPageSpeedResponse) {
var urlScores = {};
if (parsedPageSpeedResponse.ruleGroups) {
var ruleGroups = parsedPageSpeedResponse.ruleGroups;
// At least one of the SPEED or USABILITY properties will exist, but not
// necessarily both.
urlScores.Speed = ruleGroups.SPEED ? ruleGroups.SPEED.score : ‘-‘;
urlScores.Usability = ruleGroups.USABILITY ?
ruleGroups.USABILITY.score : ‘-‘;
}
if (parsedPageSpeedResponse.formattedResults &&
parsedPageSpeedResponse.formattedResults.ruleResults) {
var resultParts = parsedPageSpeedResponse.formattedResults.ruleResults;
for (var partName in resultParts) {
var part = resultParts[partName];
urlScores[part.localizedRuleName] = part.ruleImpact;
}
}
return urlScores;
}
/**
* Extracts the headings for the metrics returned from PageSpeed, and any
* associated help URLs.
*
* @param {Object} parsedPageSpeedResponse The object returned from PageSpeed.
* @return {Object} An associative array used to store column-headings seen in
* the response. This can take two forms:
* (1) {‘heading’:’heading’, …} – this form is where no help URLs are
* known.
* (2) {‘heading’: [url1, …]} – where one or more URLs is returned that
* provides help on the particular heading item.
*/
function extractColumnsInfo(parsedPageSpeedResponse) {
var columnsInfo = {};
if (parsedPageSpeedResponse.formattedResults &&
parsedPageSpeedResponse.formattedResults.ruleResults) {
var resultParts = parsedPageSpeedResponse.formattedResults.ruleResults;
for (var partName in resultParts) {
var part = resultParts[partName];
if (!columnsInfo[part.localizedRuleName]) {
columnsInfo[part.localizedRuleName] = part.localizedRuleName;
}
// Find help URLs in the response
var summary = part.summary;
if (summary && summary.args) {
var argList = summary.args;
for (var i = 0, lenI = argList.length; i < lenI; i++) {
var arg = argList[i];
if ((arg.type) && (arg.type == ‘HYPERLINK’) &&
(arg.key) && (arg.key == ‘LINK’) &&
(arg.value)) {
columnsInfo[part.localizedRuleName] = [arg.value];
}
}
}
if (part.urlBlocks) {
var blocks = part.urlBlocks;
var urls = [];
for (var j = 0, lenJ = blocks.length; j < lenJ; j++) {
var block = blocks[j];
if (block.header) {
var header = block.header;
if (header.args) {
var args = header.args;
for (var k = 0, lenK = args.length; k < lenK; k++) {
var argument = args[k];
if ((argument.type) &&
(argument.type == ‘HYPERLINK’) &&
(argument.key) &&
(argument.key == ‘LINK’) &&
(argument.value)) {
urls.push(argument.value);
}
}
}
}
}
if (urls.length > 0) {
columnsInfo[part.localizedRuleName] = urls;
}
}
}
}
return columnsInfo;
}
/**
* Extracts a suitable error message to display for a failed URL. The error
* could be passed in in the nested PageSpeed error format, or there could have
* been a more fundamental error in the fetching of the URL. Extract the
* relevant message in each case.
*
* @param {string} errorMessage The error string.
* @return {string} A formatted error message.
*/
function formatErrorMessage(errorMessage) {
var formattedMessage = null;
if (!errorMessage) {
formattedMessage = ‘Unknown error message’;
} else {
try {
var parsedError = JSON.parse(errorMessage);
// This is the nested structure expected from PageSpeed
if (parsedError.error && parsedError.error.errors) {
var firstError = parsedError.error.errors[0];
formattedMessage = firstError.message;
} else if (parsedError.message) {
formattedMessage = parsedError.message;
} else {
formattedMessage = errorMessage.toString();
}
} catch (e) {
formattedMessage = errorMessage.toString();
}
}
return formattedMessage;
}
/**
* Calls the PageSpeed API for a single URL, and attempts to parse the resulting
* JSON. If successful, produces an object for the metrics returned, and an
* object detailing the headings and help URLs seen.
*
* @param {string} url The URL to run PageSpeed for.
* @return {Object} An object with pageSpeed metrics, column-heading info
* and error properties.
*/
function getPageSpeedResultForSingleUrl(url) {
var parsedResponse = null;
var errorMessage = null;
var retries = 0;
while ((!parsedResponse || parsedResponse.responseCode !== 200) &&
retries < MAX_RETRIES) {
errorMessage = null;
var fetchResult = checkUrl(url);
if (fetchResult.responseText) {
try {
parsedResponse = JSON.parse(fetchResult.responseText);
break;
} catch (e) {
errorMessage = formatErrorMessage(e);
}
} else {
errorMessage = formatErrorMessage(fetchResult.error);
}
retries++;
Utilities.sleep(1000 * Math.pow(2, retries));
}
if (!errorMessage) {
var columnsInfo = extractColumnsInfo(parsedResponse);
var urlScores = extractResultRow(parsedResponse);
}
return {
pageSpeedInfo: urlScores,
columnsInfo: columnsInfo,
error: errorMessage
};
}
/**
* Gets the most representative URL that would be used on a mobile device
* taking into account Upgraded URLs.
*
* @param {Entity} entity An AdWords entity such as an Ad, Keyword or Sitelink.
* @return {string} The URL.
*/
function getMobileUrl(entity) {
var urls = entity.urls();
var url = null;
if (urls) {
if (urls.getMobileFinalUrl()) {
url = urls.getMobileFinalUrl();
} else if (urls.getFinalUrl()) {
url = urls.getFinalUrl();
}
}
if (!url) {
switch (entity.getEntityType()) {
case ‘Ad’:
case ‘Keyword’:
url = entity.getDestinationUrl();
break;
case ‘Sitelink’:
case ‘AdGroupSitelink’:
case ‘CampaignSitelink’:
url = entity.getLinkUrl();
break;
default:
Logger.log(‘No URL found’ + entity.getEntityType());
}
}
if (url) {
url = encodeURI(decodeURIComponent(url));
}
return url;
}
/**
* Determines whether there is enough remaining time to continue iterating
* through the account.
*
* @return {boolean} Returns true if there is enough time remaining to continue
* iterating.
*/
function hasRemainingTimeForAccountIteration() {
var remainingTime = AdWordsApp.getExecutionInfo().getRemainingTime();
return remainingTime > SPREADSHEET_PREP_TIME_SECS + URL_FETCH_TIME_SECS;
}
/**
* Determines whether there is enough remaining time to continue fetching URLs.
*
* @return {boolean} Returns true if there is enough time remaining to continue
* fetching.
*/
function hasRemainingTimeForUrlFetches() {
var remainingTime = AdWordsApp.getExecutionInfo().getRemainingTime();
return remainingTime > SPREADSHEET_PREP_TIME_SECS;
}
/**
* Iterates through all the available Campaigns and AdGroups, to a limit of
* defined in CAMPAIGN_LIMIT and ADGROUP_LIMIT until the time limit is reached
* allowing enough time for the post-iteration steps, e.g. fetching and
* analysing URLs and building results.
*
* @return {UrlStore} An UrlStore object with URLs from the account.
*/
function getUrlsFromAccount() {
var urlStore = new UrlStore(EXTRA_URLS_TO_CHECK);
var campaigns = AdWordsApp.campaigns()
.forDateRange(‘LAST_30_DAYS’)
.withCondition(‘Status = “ENABLED”‘)
.orderBy(‘Clicks DESC’)
.withLimit(CAMPAIGN_LIMIT)
.get();
while (campaigns.hasNext() && hasRemainingTimeForAccountIteration()) {
var campaign = campaigns.next();
var campaignUrls = getUrlsFromCampaign(campaign);
urlStore.addUrls(campaignUrls);
}
var adGroups = AdWordsApp.adGroups()
.forDateRange(‘LAST_30_DAYS’)
.withCondition(‘Status = “ENABLED”‘)
.orderBy(‘Clicks DESC’)
.withLimit(ADGROUP_LIMIT)
.get();
while (adGroups.hasNext() && hasRemainingTimeForAccountIteration()) {
var adGroup = adGroups.next();
var adGroupUrls = getUrlsFromAdGroup(adGroup);
urlStore.addUrls(adGroupUrls);
}
return urlStore;
}
/**
* Work through an ad group’s members in the account, but only up to the maximum
* specified by the SITELINK_LIMIT.
*
* @param {AdGroup} adGroup The adGroup to process.
* @return {!Array.<string>} A list of URLs.
*/
function getUrlsFromAdGroup(adGroup) {
var uniqueUrls = {};
var sitelinks =
adGroup.extensions().sitelinks().withLimit(SITELINK_LIMIT).get();
while (sitelinks.hasNext()) {
var sitelink = sitelinks.next();
var url = getMobileUrl(sitelink);
if (url) {
uniqueUrls[url] = true;
}
}
return Object.keys(uniqueUrls);
}
/**
* Work through a campaign’s members in the account, but only up to the maximum
* specified by the AD_LIMIT, KEYWORD_LIMIT and SITELINK_LIMIT.
*
* @param {Campaign} campaign The campaign to process.
* @return {!Array.<string>} A list of URLs.
*/
function getUrlsFromCampaign(campaign) {
var uniqueUrls = {};
var url = null;
var sitelinks = campaign
.extensions().sitelinks().withLimit(SITELINK_LIMIT).get();
while (sitelinks.hasNext()) {
var sitelink = sitelinks.next();
url = getMobileUrl(sitelink);
if (url) {
uniqueUrls[url] = true;
}
}
var ads = campaign.ads().forDateRange(‘LAST_30_DAYS’)
.withCondition(‘Status = “ENABLED”‘)
.orderBy(‘Clicks DESC’)
.withLimit(AD_LIMIT)
.get();
while (ads.hasNext()) {
var ad = ads.next();
url = getMobileUrl(ad);
if (url) {
uniqueUrls[url] = true;
}
}
var keywords = campaign.keywords().forDateRange(‘LAST_30_DAYS’)
.withCondition(‘Status = “ENABLED”‘)
.orderBy(‘Clicks DESC’)
.withLimit(KEYWORD_LIMIT)
.get();
while (keywords.hasNext()) {
var keyword = keywords.next();
url = getMobileUrl(keyword);
if (url) {
uniqueUrls[url] = true;
}
}
return Object.keys(uniqueUrls);
}
/**
* 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);
}
/**
* UrlStore – this is an object that takes URLs, added one by one, and then
* allows them to be iterated through in a particular order, which aims to
* maximise the variety between the returned URLs.
*
* This works by splitting the URL into three parts: host, path and params
* In comparing two URLs, most weight is given if the hosts differ, then if the
* paths differ, and finally if the params differ.
*
* UrlStore sets up a tree with 3 levels corresponding to the above. The full
* URL exists at the leaf level. When a request is made for an iterator, a copy
* is taken, and a path through the tree is taken, using the first host. Each
* entry is removed from the tree as it is used, and the layers are rotated with
* each call such that the next call will result in a different host being used
* (where possible).
*
* Where opt_manualUrls are supplied at construction time, these will take
* precedence over URLs added subsequently to the object.
*
* @param {?Array.<string>=} opt_manualUrls An optional list of URLs to check.
* @constructor
*/
function UrlStore(opt_manualUrls) {
this.manualUrls = opt_manualUrls || [];
this.paths = {};
this.re = /^(https?:\/\/[^\/]+)([^?#]*)(.*)$/;
}
/**
* Adds a URL to the UrlStore.
*
* @param {string} url The URL to add.
*/
UrlStore.prototype.addUrl = function(url) {
if (!url || this.manualUrls.indexOf(url) > -1) {
return;
}
var matches = this.re.exec(url);
if (matches) {
var host = matches[1];
var path = matches[2];
var param = matches[3];
if (!this.paths[host]) {
this.paths[host] = {};
}
var hostObj = this.paths[host];
if (!path) {
path = ‘/’;
}
if (!hostObj[path]) {
hostObj[path] = {};
}
var pathObj = hostObj[path];
pathObj[url] = url;
}
};
/**
* Adds multiple URLs to the UrlStore.
*
* @param {!Array.<string>} urls The URLs to add.
*/
UrlStore.prototype.addUrls = function(urls) {
for (var i = 0; i < urls.length; i++) {
this.addUrl(urls[i]);
}
};
/**
* Creates and returns an iterator that tries to iterate over all available
* URLs return them in an order to maximise the difference between them.
*
* @return {UrlStoreIterator} The new iterator object.
*/
UrlStore.prototype.__iterator__ = function() {
return new UrlStoreIterator(this.paths, this.manualUrls);
};
var UrlStoreIterator = (function() {
function UrlStoreIterator(paths, manualUrls) {
this.manualUrls = manualUrls.slice();
this.urls = objectToArray_(paths);
}
UrlStoreIterator.prototype.next = function() {
if (this.manualUrls.length) {
return this.manualUrls.shift();
}
if (this.urls.length) {
return pick_(this.urls);
} else {
throw StopIteration;
}
};
function rotate_(a) {
if (a.length < 2) {
return a;
} else {
var e = a.pop();
a.unshift(e);
}
}
function pick_(a) {
if (typeof a[0] === ‘string’) {
return a.shift();
} else {
var element = pick_(a[0]);
if (!a[0].length) {
a.shift();
} else {
rotate_(a);
}
return element;
}
}
function objectToArray_(obj) {
if (typeof obj !== ‘object’) {
return obj;
}
var a = [];
for (var k in obj) {
a.push(objectToArray_(obj[k]));
}
return a;
}
return UrlStoreIterator;
})();
/**
* Runs the PageSpeed fetch.
*
* @param {string} url
* @return {Object} An object containing either the successful response from the
* server, or an error message.
*/
function checkUrl(url) {
var result = null;
var error = null;
var fullUrl = PAGESPEED_URL + ‘key=’ + API_KEY + ‘&url=’ + encodeURI(url) +
‘&prettyprint=false&strategy=mobile’;
var params = {muteHttpExceptions: true};
try {
var pageSpeedResponse = UrlFetchApp.fetch(fullUrl, params);
if (pageSpeedResponse.getResponseCode() === 200) {
result = pageSpeedResponse.getContentText();
} else {
error = pageSpeedResponse.getContentText();
}
} catch (e) {
error = e.message;
}
return {
responseText: result,
error: error
};
}
Como Configurar
8. Armazene o Índice de Qualidade de sua Conta, Campanha, Grupo de Anúncios e Palavras-chave – Por Russel Savage. Tenha o histórico do Índice de Qualidade organizado por datas em uma planilha do Google. Mensure o desenvolvimento de seu Índice de Qualidade com o decorrer do tempo.
/************************************
* Store Account, Campaign, and AdGroup Level Quality Score
* Version 2.3
* ChangeLog v2.3
* – Solved #NUM! issue by filtering out — values
* ChangeLog v2.2
* – Updated KeywordText to Criteria
* ChangeLog v2.1
* – Ignore negatives
* ChangeLog v2.0
* – Rewrote for speed using the reporting api
* – Added ability to store data in .csv file
* – Added the ability for custom date ranges
* – Added the ability for Spreadsheet Names
* ChangeLog v1.3
* – Updated writeToSpreadsheet function
* – Added keyword level reporting
* ChangeLog v1.2
* – Changed status to ENABLED
* ChangeLog v1.1
* – Added APPEND option
* – Added ability to create spreadsheet sheets
* – Updated logic for faster spreadsheet insertion
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************/
var DECIMALS = 4; //this will give you 4 decimal places of accuracy
//You can set this to anything in this list: TODAY, YESTERDAY, LAST_7_DAYS,
// THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS,
// LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH
var DATE_RANGE = ‘LAST_30_DAYS’;
// Or you can set this to any number of days you like. it overrides the DATE_RANGE set above
var LAST_N_DAYS = 0;
var CSV_FILE_PREFIX = “”; //Set this if you want to write to a set of CSV files, one for each account level.
var SPREADSHEET_URL = “”; //Set this if you have the url of a spreadsheet you want to update
var SPREADSHEET_NAME = “”; //Set this if you want to write to the name of a spreadsheet instead
function main() {
var isCSV = (CSV_FILE_PREFIX !== “”);
var allData = getKeywordsReport();
var tabs = [‘Account’,’Campaign’,’AdGroup’,’Keyword’];
for(var i in tabs) {
var tab = tabs[i];
var dataToWrite = [];
var cols = getCols(tab);
var rowKeys = getRowKeys(tab,Object.keys(allData));
for(var x in rowKeys) {
var rowArray = [];
var key = rowKeys[x];
var row = allData[key];
for(var y in cols) {
rowArray.push(row[cols[y]]);
}
dataToWrite.push(rowArray);
}
if(isCSV) {
writeDataToCSV(tab,dataToWrite);
} else {
writeDataToSpreadsheet(tab,dataToWrite);
}
}
}
function getRowKeys(tab,allKeys) {
return allKeys.filter(function(e) { return (e.indexOf(tab) >= 0); });
}
function getCols(tab) {
return {
‘Account’ : [‘Date’,’Account’,’ImpsWeightedQS’],
‘Campaign’: [‘Date’,’Account’,’Campaign’,’ImpsWeightedQS’],
‘AdGroup’ : [‘Date’,’Account’,’Campaign’,’AdGroup’,’ImpsWeightedQS’],
‘Keyword’ : [‘Date’,’Account’,’Campaign’,’AdGroup’,’Keyword’,’QS’,’ImpsWeightedQS’]
}[tab];
}
// Super fast spreadsheet insertion
function writeDataToSpreadsheet(tab,toWrite) {
//This is where i am going to store all my data
var spreadsheet;
if(SPREADSHEET_NAME) {
var fileIter = DriveApp.getFilesByName(SPREADSHEET_NAME);
if(fileIter.hasNext()) {
var file = fileIter.next();
spreadsheet = SpreadsheetApp.openById(file.getId());
} else {
spreadsheet = SpreadsheetApp.create(SPREADSHEET_NAME);
}
} else if(SPREADSHEET_URL) {
spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
} else {
throw ‘You need to set at least one of the SPREADSHEET_URL or SPREADSHEET_NAME variables.’;
}
var sheet = spreadsheet.getSheetByName(tab);
if(!sheet) {
sheet = spreadsheet.insertSheet(tab);
sheet.appendRow(getCols(tab));
}
var lastRow = sheet.getLastRow();
var numRows = sheet.getMaxRows();
if((numRows-lastRow) < toWrite.length) {
sheet.insertRowsAfter((lastRow == 0) ? 1 : lastRow,toWrite.length-numRows+lastRow);
}
var range = sheet.getRange(lastRow+1,1,toWrite.length,toWrite[0].length);
range.setValues(toWrite);
}
function writeDataToCSV(tab,toWrite) {
if(!toWrite) { return; }
var fileName = CSV_FILE_PREFIX + ‘_’ + tab + ‘.csv’;
var file;
var fileIter = DriveApp.getFilesByName(fileName);
if(fileIter.hasNext()) {
file = fileIter.next();
} else {
file = DriveApp.createFile(fileName, formatCsvRow(getCols(tab)));
}
var fileData = file.getBlob().getDataAsString();
for(var i in toWrite) {
fileData += formatCsvRow(toWrite[i]);
}
file.setContent(fileData);
return file.getUrl();
}
function formatCsvRow(row) {
for(var i in row) {
if(row[i].toString().indexOf(‘”‘) == 0) {
row[i] = ‘””‘+row[i]+'””‘;
}
if(row[i].toString().indexOf(‘+’) == 0) {
row[i] = “‘”+row[i];
}
if(row[i].toString().indexOf(‘,’) >= 0 &&
row[i].toString().indexOf(‘”””‘) != 0)
{
row[i] = (‘”‘+row[i]+'”‘);
}
}
return row.join(‘,’)+’\n’;
}
function getKeywordsReport() {
var theDate = DATE_RANGE;
if(LAST_N_DAYS != 0) {
theDate = getDateDaysAgo(LAST_N_DAYS)+’,’+getDateDaysAgo(1);
}
Logger.log(‘Using date range: ‘+theDate);
var OPTIONS = { includeZeroImpressions : true };
var cols = [‘ExternalCustomerId’,
‘CampaignId’,’CampaignName’,
‘AdGroupId’,’AdGroupName’,
‘Id’,’Criteria’,’KeywordMatchType’,
‘IsNegative’,’Impressions’, ‘QualityScore’];
var report = ‘KEYWORDS_PERFORMANCE_REPORT’;
var query = [‘select’,cols.join(‘,’),’from’,report,
‘where AdNetworkType1 = SEARCH’,
‘and CampaignStatus = ENABLED’,
‘and AdGroupStatus = ENABLED’,
‘and Status = ENABLED’,
‘during’,theDate].join(‘ ‘);
var results = {};
var reportIter = AdWordsApp.report(query, OPTIONS).rows();
while(reportIter.hasNext()) {
var row = reportIter.next();
if(row.QualityScore == “–“) { continue; }
if(row.IsNegative == true || row.IsNegative === ‘true’) { continue; }
loadHashEntry(‘Account:’+row.ExternalCustomerId,row,results);
loadHashEntry(‘Campaign:’+row.CampaignId,row,results);
loadHashEntry(‘AdGroup:’+[row.CampaignId,row.AdGroupId].join(‘-‘),row,results);
loadHashEntry(‘Keyword:’+[row.CampaignId,row.AdGroupId,row.Id].join(‘-‘),row,results);
}
var dateStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), ‘yyyy-MM-dd’);
for(var i in results) {
results[i][‘Date’] = dateStr;
results[i][‘ImpsWeightedQS’] = (results[i][‘totalImps’] === 0) ? 0 : round(results[i][‘ImpsWeightedQS’]/results[i][‘totalImps’]);
}
return results;
}
function loadHashEntry(key,row,results) {
if(!results[key]) {
results[key] = {
QS : 0,
ImpsWeightedQS : 0,
totalImps : 0,
Account : null,
Campaign : null,
AdGroup : null,
Keyword : null
};
}
results[key].QS = parseFloat(row.QualityScore);
results[key].ImpsWeightedQS += (parseFloat(row.QualityScore)*parseFloat(row.Impressions));
results[key].totalImps += parseFloat(row.Impressions);
results[key].Account = row.ExternalCustomerId;
results[key].Campaign = row.CampaignName;
results[key].AdGroup = row.AdGroupName;
results[key].Keyword = (row.KeywordMatchType === ‘Exact’) ? ‘[‘+row.Criteria+’]’ :
(row.KeywordMatchType === ‘Phrase’) ? ‘”‘+row.Criteria+'”‘ : row.Criteria;
}
//A helper function to return the number of days ago.
function getDateDaysAgo(days) {
var thePast = new Date();
thePast.setDate(thePast.getDate() – days);
return Utilities.formatDate(thePast, AdWordsApp.currentAccount().getTimeZone(), ‘yyyyMMdd’);
}
function round(val) {
var divisor = Math.pow(10,DECIMALS);
return Math.round(val*divisor)/divisor;
}
9. Relatório de Performance da Campanha e Palavra-chave – Por Russel Savage. Tenha uma planilha do Google com relatório do mês passado, semanais, mensais de suas campanhas e de suas palavras-chave. Tudo que você precisa fazer é criar uma planilha do Google e colocar sua URL no script.
/************************************
* Campaign and Keyword Summary Report
* Version: 1.2
* Changelog v1.2 – Fixed INVALID_PREDICATE_ENUM_VALUE
* ChangeLog v1.1 – Removed apiVersion from reporting call
* Created By: Russ Savage
* FreeAdWordsScripts.com
************************************/
var SPREADSHEET_URL = “PASTE GOOGLE SPREADSHEET URL HERE”;
function main() {
//These names are important. change them with caution
var tabs = [‘camp_perf_7_days’,’camp_perf_mtd’,’camp_perf_last_month’,’keyword_perf_7_days’,’keyword_perf_7_days_daily’];
for(var i in tabs) {
var results = runQuery(tabs[i]);
writeToSpreadsheet(tabs[i],results);
}
}
//Helper function to get or create the spreadsheet
function getSheet(tab) {
var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet;
try {
sheet = s_sheet.getSheetByName(tab);
if(!sheet) {
sheet = s_sheet.insertSheet(tab, 0);
}
} catch(e) {
sheet = s_sheet.insertSheet(tab, 0);
}
return sheet
}
//Function to write the rows of the report to the sheet
function writeToSpreadsheet(tab,rows) {
var to_write = convertRowsToSpreadsheetRows(tab,rows);
var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = getSheet(tab);
sheet.clear();
var numRows = sheet.getMaxRows();
if(numRows < to_write.length) {
sheet.insertRows(1,to_write.length-numRows);
}
var range = sheet.getRange(1,1,to_write.length,to_write[0].length);
range.setValues(to_write);
}
//A generic function used to build and run the report query
function runQuery(tab) {
var API_VERSION = { includeZeroImpressions : false };
var cols = getColumns(tab);
var report = getReport(tab);
var date_range = getDateRange(tab);
var where = getWhereClause(tab);
var query = [‘select’,cols.join(‘,’),’from’,report,where,’during’,date_range].join(‘ ‘);
var report_iter = AdWordsApp.report(query, API_VERSION).rows();
var rows = [];
while(report_iter.hasNext()) {
rows.push(report_iter.next());
}
return rows;
}
//This function will convert row data into a format easily pushed into a spreadsheet
function convertRowsToSpreadsheetRows(tab,rows) {
var cols = getColumns(tab);
var ret_val = [cols];
for(var i in rows) {
var r = rows[i];
var ss_row = [];
for(var x in cols) {
ss_row.push(r[cols[x]]);
}
ret_val.push(ss_row);
}
return ret_val;
}
//Based on the tab name, this returns the report type to use for the query
function getReport(tab) {
if(tab.indexOf(‘camp_’) == 0) {
return ‘CAMPAIGN_PERFORMANCE_REPORT’;
}
if(tab.indexOf(‘keyword_’) == 0) {
return ‘KEYWORDS_PERFORMANCE_REPORT’;
}
throw new Exception(‘tab name not recognized: ‘+tab);
}
//Based on the tab name, this returns the where clause for the query
function getWhereClause(tab) {
if(tab.indexOf(‘camp_’) == 0) {
return ‘where CampaignStatus = ENABLED’;
}
if(tab.indexOf(‘keyword_’) == 0) {
return ‘where CampaignStatus = ENABLED and AdGroupStatus = ENABLED and Status = ENABLED’;
}
throw new Exception(‘tab name not recognized: ‘+tab);
}
//Based on the tab name, this returns the columns to add into the report
function getColumns(tab) {
var ret_array = [];
if(tab.indexOf(‘daily’) >= 0) {
ret_array.push(‘Date’);
}
ret_array.push(‘CampaignName’);
ret_array.push(‘CampaignStatus’);
if(tab.indexOf(‘keyword_’) == 0) {
ret_array = ret_array.concat([‘AdGroupName’,
‘AdGroupStatus’,
‘Id’,
‘KeywordText’,
‘KeywordMatchType’]);
}
return ret_array.concat([‘Clicks’,
‘Impressions’,
‘Ctr’,
‘AverageCpc’,
‘Cost’,
‘AveragePosition’,
‘Conversions’,
‘ConversionRate’,
‘ConversionValue’]);
}
//Based on the tab name, this returns the date range for the data.
function getDateRange(tab) {
if(tab.indexOf(‘7_days’) >= 0) {
return ‘LAST_7_DAYS’;
}
if(tab.indexOf(‘mtd’) >= 0) {
return ‘THIS_MONTH’;
}
if(tab.indexOf(‘last_month’) >= 0) {
return ‘LAST_MONTH’;
}
throw new Exception(‘tab name not recognized: ‘+tab);
}
10. Exporte suas métricas diárias – Por Sean Dolan. Esse script exporta suas métricas diárias para uma planilha do Google, assim, você pode facilmente armazenar seus relatórios.
function main() {
/*
Make a copy of the spreadsheet listed below and save it to your own Google Drive.
Template – //docs.google.com/spreadsheet/ccckey=0Ao4Qdm9yCtaGdHpiWmpYSWoxcE9Wd0dEV0xDY3l5UGc#gid=0
Take the url of your new spreadsheet and enter it in the ssURL field below
*/
var ssURL = “spreadsheet.com”;
var codeURL = “//s3.amazonaws.com/ppc-hero-tools/daily-metrics-log.js”;
var code = “”;
function getCode(url) {
var stuff = UrlFetchApp.fetch(url).getContentText();
return stuff;
}
var code = getCode(codeURL);
eval(code);
}
11. Projeções Mensais – por Sean Dolan. Utilize a ferramenta do PPCHero para enviar um e-mail com projeções mensais dos seus gastos com este script.
function main() {
//include your e-mail below
var email = “[email protected]”;
//include the name of the account to specify the account in the e-mail
var accountName = “your account name”;
var codeURL = “//s3.amazonaws.com/ppc-hero-tools/monthly-projections.js”;
var code = “”;
function getCode(url) {
var stuff = UrlFetchApp.fetch(url).getContentText();
return stuff;
}
var code = getCode(codeURL);
eval(code);
}
12. Auditor de Índice de Qualidade – Por Derek Martin. O script facilita sua análise do Índice de Qualidade de suas campanhas trazendo um comparativo com o CTR, Custo e Conversão. Assim você pode traçar uma correlação com o desempenho e identificar formas de otimizar sua campanha.
/**************************************************************************************
* AdWords Optimization — Quality Score Performance Checker
* This script audits an account’s quality score performance and creates a table that shows the
* distribution of CTR, Cost, & Conversins against Quality Score.
* Version 1.0
* Created By: Derek Martin
* DerekMartinLA.com
**************************************************************************************/
function main() {
var keywordList = [];
var resultsList = [];
// iterate over all keywords in an account
var kwIter = AdWordsApp.keywords().withCondition(‘Status = ENABLED’).withCondition(“Impressions > 0”).forDateRange(“LAST_30_DAYS”).get();
while (kwIter.hasNext()) {
var keyword = kwIter.next();
var keywordName = keyword.getText();
var keywordQualityScore = keyword.getQualityScore();
var keywordMatchType = keyword.getMatchType();
var keywordStats = keyword.getStatsFor(“LAST_30_DAYS”);
var keywordCtr = keywordStats.getCtr();
var keywordCost =keywordStats.getCost();
var keywordConversions = keywordStats.getConvertedClicks();
var newResult = new keywordTrack(keywordName, keywordMatchType, keywordQualityScore, keywordCtr, keywordCost, keywordConversions);
//info(newResult);
keywordList.push(newResult);
} // end of keyword iteration
// info(‘there are ‘ + keywordList.length + ‘ keywords for review.’);
resultsList = analyzeKeywords(keywordList);
displayResults(resultsList);
} // end of main function
function keywordTrack (name, matchType, qualityScore, ctr, cost, conversions) {
this.name = name;
this.matchType = matchType;
this.qualityScore = qualityScore;
this.ctr = ctr;
this.cost = cost;
this.conversions = conversions
} // end of keyword track
function qualityScoreTrack (qualityScore, ctr, cost, conversions) {
this.qualityScore = qualityScore;
this.ctr = ctr;
this.cost = cost;
this.conversions = conversions;
} // end of qualityScoreTrack
function analyzeKeywords(list) {
var kwList = list;
var qsList = [];
var qsGroup = [];
var totalCtr = 0;
var ctrCount = 0;
var totalCost =0;
var costCount = 0;
var totalConversions = 0;
var conversionsCount = 0;
var averageCtr = 0;
var averageCost = 0;
var averageConversions = 0;
for (i = 1; i <= 10; i++) {
qsGroup = _.where(kwList, {qualityScore: i });
if (_.isEmpty(qsGroup)) {
var result = new qualityScoreTrack(i, 0);
qsList.push(result);
} else {
var k = 0;
for each (qualityScore in qsGroup) {
totalCtr += qsGroup[k].ctr;
totalCost += qsGroup[k].cost;
totalConversions += qsGroup[k].conversions;
k++;
} // end of for each statement
ctrCount = qsGroup.length;
averageCtr = (totalCtr / ctrCount) * 100;
costCount = qsGroup.length;
averageCost = (totalCost / costCount);
conversionsCount = qsGroup.length;
averageConversions = (totalConversions / conversionsCount);
var result = new qualityScoreTrack(i, averageCtr, totalCost, totalConversions);
qsList.push(result);
totalCtr = 0;
averageCtr = 0;
ctrCount = 0;
totalCost = 0;
averageCost = 0;
costCount = 0;
totalConversions = 0;
averageConversions = 0;
conversionsCount = 0;
} // end of if statement
} // end of for loop
return qsList;
} // end of analyzeKeywords
function displayResults(results) {
var list = results;
//info(list);
var account = AdWordsApp.currentAccount().getName().split(“-“);
info(‘Below is the 30 Day Quality Score CTR Analysis for ‘ + account[0]);
for (var i = 1; i <= 10; i++) {
info(‘CTR: (‘+ i + ‘/10) – ‘ + _.str.toNumber(list[i-1].ctr,2) + ‘%’);
info(‘Cost: (‘+ i + ‘/10) – $’ + _.str.toNumber(list[i-1].cost, 4));
info(‘Conversions: (‘+ i + ‘/10) – ‘ + _.str.toNumber(list[i-1].conversions, 2));
info(“”);
} // end of for statement
} // end of results
/* UTILITY FUNCTIONS */
function info(msg) {
Logger.log(msg);
}
function warn(msg) {
Logger.log(‘WARNING: ‘+msg);
}
/* UNDERSCORE LIBRARIES */
// Underscore.js 1.6.0
// //underscorejs.org
// (c) 2009-2014 Jeremy Ashkenas, DocumentCloud and Investigative Reporters & Editors
// Underscore may be freely distributed under the MIT license.
(function(){var n=this,t=n._,r={},e=Array.prototype,u=Object.prototype,i=Function.prototype,a=e.push,o=e.slice,c=e.concat,l=u.toString,f=u.hasOwnProperty,s=e.forEach,p=e.map,h=e.reduce,v=e.reduceRight,g=e.filter,d=e.every,m=e.some,y=e.indexOf,b=e.lastIndexOf,x=Array.isArray,w=Object.keys,_=i.bind,j=function(n){return n instanceof j?n:this instanceof j?void(this._wrapped=n):new j(n)};”undefined”!=typeof exports?(“undefined”!=typeof module&&module.exports&&(exports=module.exports=j),exports._=j):n._=j,j.VERSION=”1.6.0″;var A=j.each=j.forEach=function(n,t,e){if(null==n)return n;if(s&&n.forEach===s)n.forEach(t,e);else if(n.length===+n.length){for(var u=0,i=n.length;i>u;u++)if(t.call(e,n[u],u,n)===r)return}else for(var a=j.keys(n),u=0,i=a.length;i>u;u++)if(t.call(e,n[a[u]],a[u],n)===r)return;return n};j.map=j.collect=function(n,t,r){var e=[];return null==n?e:p&&n.map===p?n.map(t,r):(A(n,function(n,u,i){e.push(t.call(r,n,u,i))}),e)};var O=”Reduce of empty array with no initial value”;j.reduce=j.foldl=j.inject=function(n,t,r,e){var u=arguments.length>2;if(null==n&&(n=[]),h&&n.reduce===h)return e&&(t=j.bind(t,e)),u?n.reduce(t,r):n.reduce(t);if(A(n,function(n,i,a){u?r=t.call(e,r,n,i,a):(r=n,u=!0)}),!u)throw new TypeError(O);return r},j.reduceRight=j.foldr=function(n,t,r,e){var u=arguments.length>2;if(null==n&&(n=[]),v&&n.reduceRight===v)return e&&(t=j.bind(t,e)),u?n.reduceRight(t,r):n.reduceRight(t);var i=n.length;if(i!==+i){var a=j.keys(n);i=a.length}if(A(n,function(o,c,l){c=a?a[–i]:–i,u?r=t.call(e,r,n[c],c,l):(r=n[c],u=!0)}),!u)throw new TypeError(O);return r},j.find=j.detect=function(n,t,r){var e;return k(n,function(n,u,i){return t.call(r,n,u,i)?(e=n,!0):void 0}),e},j.filter=j.select=function(n,t,r){var e=[];return null==n?e:g&&n.filter===g?n.filter(t,r):(A(n,function(n,u,i){t.call(r,n,u,i)&&e.push(n)}),e)},j.reject=function(n,t,r){return j.filter(n,function(n,e,u){return!t.call(r,n,e,u)},r)},j.every=j.all=function(n,t,e){t||(t=j.identity);var u=!0;return null==n?u:d&&n.every===d?n.every(t,e):(A(n,function(n,i,a){return(u=u&&t.call(e,n,i,a))?void 0:r}),!!u)};var k=j.some=j.any=function(n,t,e){t||(t=j.identity);var u=!1;return null==n?u:m&&n.some===m?n.some(t,e):(A(n,function(n,i,a){return u||(u=t.call(e,n,i,a))?r:void 0}),!!u)};j.contains=j.include=function(n,t){return null==n?!1:y&&n.indexOf===y?n.indexOf(t)!=-1:k(n,function(n){return n===t})},j.invoke=function(n,t){var r=o.call(arguments,2),e=j.isFunction(t);return j.map(n,function(n){return(e?t:n[t]).apply(n,r)})},j.pluck=function(n,t){return j.map(n,j.property(t))},j.where=function(n,t){return j.filter(n,j.matches(t))},j.findWhere=function(n,t){return j.find(n,j.matches(t))},j.max=function(n,t,r){if(!t&&j.isArray(n)&&n[0]===+n[0]&&n.length<65535)return Math.max.apply(Math,n);var e=-1/0,u=-1/0;return A(n,function(n,i,a){var o=t?t.call(r,n,i,a):n;o>u&&(e=n,u=o)}),e},j.min=function(n,t,r){if(!t&&j.isArray(n)&&n[0]===+n[0]&&n.length<65535)return Math.min.apply(Math,n);var e=1/0,u=1/0;return A(n,function(n,i,a){var o=t?t.call(r,n,i,a):n;u>o&&(e=n,u=o)}),e},j.shuffle=function(n){var t,r=0,e=[];return A(n,function(n){t=j.random(r++),e[r-1]=e[t],e[t]=n}),e},j.sample=function(n,t,r){return null==t||r?(n.length!==+n.length&&(n=j.values(n)),n[j.random(n.length-1)]):j.shuffle(n).slice(0,Math.max(0,t))};var E=function(n){return null==n?j.identity:j.isFunction(n)?n:j.property(n)};j.sortBy=function(n,t,r){return t=E(t),j.pluck(j.map(n,function(n,e,u){return{value:n,index:e,criteria:t.call(r,n,e,u)}}).sort(function(n,t){var r=n.criteria,e=t.criteria;if(r!==e){if(r>e||r===void 0)return 1;if(e>r||e===void 0)return-1}return n.index-t.index}),”value”)};var F=function(n){return function(t,r,e){var u={};return r=E(r),A(t,function(i,a){var o=r.call(e,i,a,t);n(u,o,i)}),u}};j.groupBy=F(function(n,t,r){j.has(n,t)?n[t].push(r):n[t]=[r]}),j.indexBy=F(function(n,t,r){n[t]=r}),j.countBy=F(function(n,t){j.has(n,t)?n[t]++:n[t]=1}),j.sortedIndex=function(n,t,r,e){r=E(r);for(var u=r.call(e,t),i=0,a=n.length;a>i;){var o=i+a>>>1;r.call(e,n[o])<u?i=o+1:a=o}return i},j.toArray=function(n){return n?j.isArray(n)?o.call(n):n.length===+n.length?j.map(n,j.identity):j.values(n):[]},j.size=function(n){return null==n?0:n.length===+n.length?n.length:j.keys(n).length},j.first=j.head=j.take=function(n,t,r){return null==n?void 0:null==t||r?n[0]:0>t?[]:o.call(n,0,t)},j.initial=function(n,t,r){return o.call(n,0,n.length-(null==t||r?1:t))},j.last=function(n,t,r){return null==n?void 0:null==t||r?n[n.length-1]:o.call(n,Math.max(n.length-t,0))},j.rest=j.tail=j.drop=function(n,t,r){return o.call(n,null==t||r?1:t)},j.compact=function(n){return j.filter(n,j.identity)};var M=function(n,t,r){return t&&j.every(n,j.isArray)?c.apply(r,n):(A(n,function(n){j.isArray(n)||j.isArguments(n)?t?a.apply(r,n):M(n,t,r):r.push(n)}),r)};j.flatten=function(n,t){return M(n,t,[])},j.without=function(n){return j.difference(n,o.call(arguments,1))},j.partition=function(n,t){var r=[],e=[];return A(n,function(n){(t(n)?r:e).push(n)}),[r,e]},j.uniq=j.unique=function(n,t,r,e){j.isFunction(t)&&(e=r,r=t,t=!1);var u=r?j.map(n,r,e):n,i=[],a=[];return A(u,function(r,e){(t?e&&a[a.length-1]===r:j.contains(a,r))||(a.push(r),i.push(n[e]))}),i},j.union=function(){return j.uniq(j.flatten(arguments,!0))},j.intersection=function(n){var t=o.call(arguments,1);return j.filter(j.uniq(n),function(n){return j.every(t,function(t){return j.contains(t,n)})})},j.difference=function(n){var t=c.apply(e,o.call(arguments,1));return j.filter(n,function(n){return!j.contains(t,n)})},j.zip=function(){for(var n=j.max(j.pluck(arguments,”length”).concat(0)),t=new Array(n),r=0;n>r;r++)t[r]=j.pluck(arguments,””+r);return t},j.object=function(n,t){if(null==n)return{};for(var r={},e=0,u=n.length;u>e;e++)t?r[n[e]]=t[e]:r[n[e][0]]=n[e][1];return r},j.indexOf=function(n,t,r){if(null==n)return-1;var e=0,u=n.length;if(r){if(“number”!=typeof r)return e=j.sortedIndex(n,t),n[e]===t?e:-1;e=0>r?Math.max(0,u+r):r}if(y&&n.indexOf===y)return n.indexOf(t,r);for(;u>e;e++)if(n[e]===t)return e;return-1},j.lastIndexOf=function(n,t,r){if(null==n)return-1;var e=null!=r;if(b&&n.lastIndexOf===b)return e?n.lastIndexOf(t,r):n.lastIndexOf(t);for(var u=e?r:n.length;u–;)if(n[u]===t)return u;return-1},j.range=function(n,t,r){arguments.length<=1&&(t=n||0,n=0),r=arguments[2]||1;for(var e=Math.max(Math.ceil((t-n)/r),0),u=0,i=new Array(e);e>u;)i[u++]=n,n+=r;return i};var R=function(){};j.bind=function(n,t){var r,e;if(_&&n.bind===_)return _.apply(n,o.call(arguments,1));if(!j.isFunction(n))throw new TypeError;return r=o.call(arguments,2),e=function(){if(!(this instanceof e))return n.apply(t,r.concat(o.call(arguments)));R.prototype=n.prototype;var u=new R;R.prototype=null;var i=n.apply(u,r.concat(o.call(arguments)));return Object(i)===i?i:u}},j.partial=function(n){var t=o.call(arguments,1);return function(){for(var r=0,e=t.slice(),u=0,i=e.length;i>u;u++)e[u]===j&&(e[u]=arguments[r++]);for(;r<arguments.length;)e.push(arguments[r++]);return n.apply(this,e)}},j.bindAll=function(n){var t=o.call(arguments,1);if(0===t.length)throw new Error(“bindAll must be passed function names”);return A(t,function(t){n[t]=j.bind(n[t],n)}),n},j.memoize=function(n,t){var r={};return t||(t=j.identity),function(){var e=t.apply(this,arguments);return j.has(r,e)?r[e]:r[e]=n.apply(this,arguments)}},j.delay=function(n,t){var r=o.call(arguments,2);return setTimeout(function(){return n.apply(null,r)},t)},j.defer=function(n){return j.delay.apply(j,[n,1].concat(o.call(arguments,1)))},j.throttle=function(n,t,r){var e,u,i,a=null,o=0;r||(r={});var c=function(){o=r.leading===!1?0:j.now(),a=null,i=n.apply(e,u),e=u=null};return function(){var l=j.now();o||r.leading!==!1||(o=l);var f=t-(l-o);return e=this,u=arguments,0>=f?(clearTimeout(a),a=null,o=l,i=n.apply(e,u),e=u=null):a||r.trailing===!1||(a=setTimeout(c,f)),i}},j.debounce=function(n,t,r){var e,u,i,a,o,c=function(){var l=j.now()-a;t>l?e=setTimeout(c,t-l):(e=null,r||(o=n.apply(i,u),i=u=null))};return function(){i=this,u=arguments,a=j.now();var l=r&&!e;return e||(e=setTimeout(c,t)),l&&(o=n.apply(i,u),i=u=null),o}},j.once=function(n){var t,r=!1;return function(){return r?t:(r=!0,t=n.apply(this,arguments),n=null,t)}},j.wrap=function(n,t){return j.partial(t,n)},j.compose=function(){var n=arguments;return function(){for(var t=arguments,r=n.length-1;r>=0;r–)t=[n[r].apply(this,t)];return t[0]}},j.after=function(n,t){return function(){return–n<1?t.apply(this,arguments):void 0}},j.keys=function(n){if(!j.isObject(n))return[];if(w)return w(n);var t=[];for(var r in n)j.has(n,r)&&t.push(r);return t},j.values=function(n){for(var t=j.keys(n),r=t.length,e=new Array(r),u=0;r>u;u++)e[u]=n[t[u]];return e},j.pairs=function(n){for(var t=j.keys(n),r=t.length,e=new Array(r),u=0;r>u;u++)e[u]=[t[u],n[t[u]]];return e},j.invert=function(n){for(var t={},r=j.keys(n),e=0,u=r.length;u>e;e++)t[n[r[e]]]=r[e];return t},j.functions=j.methods=function(n){var t=[];for(var r in n)j.isFunction(n[r])&&t.push(r);return t.sort()},j.extend=function(n){return A(o.call(arguments,1),function(t){if(t)for(var r in t)n[r]=t[r]}),n},j.pick=function(n){var t={},r=c.apply(e,o.call(arguments,1));return A(r,function(r){r in n&&(t[r]=n[r])}),t},j.omit=function(n){var t={},r=c.apply(e,o.call(arguments,1));for(var u in n)j.contains(r,u)||(t[u]=n[u]);return t},j.defaults=function(n){return A(o.call(arguments,1),function(t){if(t)for(var r in t)n[r]===void 0&&(n[r]=t[r])}),n},j.clone=function(n){return j.isObject(n)?j.isArray(n)?n.slice():j.extend({},n):n},j.tap=function(n,t){return t(n),n};var S=function(n,t,r,e){if(n===t)return 0!==n||1/n==1/t;if(null==n||null==t)return n===t;n instanceof j&&(n=n._wrapped),t instanceof j&&(t=t._wrapped);var u=l.call(n);if(u!=l.call(t))return!1;switch(u){case”[object String]”:return n==String(t);case”[object Number]”:return n!=+n?t!=+t:0==n?1/n==1/t:n==+t;case”[object Date]”:case”[object Boolean]”:return+n==+t;case”[object RegExp]”:return n.source==t.source&&n.global==t.global&&n.multiline==t.multiline&&n.ignoreCase==t.ignoreCase}if(“object”!=typeof n||”object”!=typeof t)return!1;for(var i=r.length;i–;)if(r[i]==n)return e[i]==t;var a=n.constructor,o=t.constructor;if(a!==o&&!(j.isFunction(a)&&a instanceof a&&j.isFunction(o)&&o instanceof o)&&”constructor”in n&&”constructor”in t)return!1;r.push(n),e.push(t);var c=0,f=!0;if(“[object Array]”==u){if(c=n.length,f=c==t.length)for(;c–&&(f=S(n[c],t[c],r,e)););}else{for(var s in n)if(j.has(n,s)&&(c++,!(f=j.has(t,s)&&S(n[s],t[s],r,e))))break;if(f){for(s in t)if(j.has(t,s)&&!c–)break;f=!c}}return r.pop(),e.pop(),f};j.isEqual=function(n,t){return S(n,t,[],[])},j.isEmpty=function(n){if(null==n)return!0;if(j.isArray(n)||j.isString(n))return 0===n.length;for(var t in n)if(j.has(n,t))return!1;return!0},j.isElement=function(n){return!(!n||1!==n.nodeType)},j.isArray=x||function(n){return”[object Array]”==l.call(n)},j.isObject=function(n){return n===Object(n)},A([“Arguments”,”Function”,”String”,”Number”,”Date”,”RegExp”],function(n){j[“is”+n]=function(t){return l.call(t)==”[object “+n+”]”}}),j.isArguments(arguments)||(j.isArguments=function(n){return!(!n||!j.has(n,”callee”))}),”function”!=typeof/./&&(j.isFunction=function(n){return”function”==typeof n}),j.isFinite=function(n){return isFinite(n)&&!isNaN(parseFloat(n))},j.isNaN=function(n){return j.isNumber(n)&&n!=+n},j.isBoolean=function(n){return n===!0||n===!1||”[object Boolean]”==l.call(n)},j.isNull=function(n){return null===n},j.isUndefined=function(n){return n===void 0},j.has=function(n,t){return f.call(n,t)},j.noConflict=function(){return n._=t,this},j.identity=function(n){return n},j.constant=function(n){return function(){return n}},j.property=function(n){return function(t){return t[n]}},j.matches=function(n){return function(t){if(t===n)return!0;for(var r in n)if(n[r]!==t[r])return!1;return!0}},j.times=function(n,t,r){for(var e=Array(Math.max(0,n)),u=0;n>u;u++)e[u]=t.call(r,u);return e},j.random=function(n,t){return null==t&&(t=n,n=0),n+Math.floor(Math.random()*(t-n+1))},j.now=Date.now||function(){return(new Date).getTime()};var T={escape:{“&”:”&”,”<“:”<”,”>”:”>”,'”‘:”"”,”‘”:”'”}};T.unescape=j.invert(T.escape);var I={escape:new RegExp(“[“+j.keys(T.escape).join(“”)+”]”,”g”),unescape:new RegExp(“(“+j.keys(T.unescape).join(“|”)+”)”,”g”)};j.each([“escape”,”unescape”],function(n){j[n]=function(t){return null==t?””:(“”+t).replace(I[n],function(t){return T[n][t]})}}),j.result=function(n,t){if(null==n)return void 0;var r=n[t];return j.isFunction(r)?r.call(n):r},j.mixin=function(n){A(j.functions(n),function(t){var r=j[t]=n[t];j.prototype[t]=function(){var n=[this._wrapped];return a.apply(n,arguments),z.call(this,r.apply(j,n))}})};var N=0;j.uniqueId=function(n){var t=++N+””;return n?n+t:t},j.templateSettings={evaluate:/<%([\s\S]+?)%>/g,interpolate:/<%=([\s\S]+?)%>/g,escape:/<%-([\s\S]+?)%>/g};var q=/(.)^/,B={“‘”:”‘”,”\\”:”\\”,”\r”:”r”,”\n”:”n”,” “:”t”,”\u2028″:”u2028″,”\u2029″:”u2029″},D=/\\|’|\r|\n|\t|\u2028|\u2029/g;j.template=function(n,t,r){var e;r=j.defaults({},r,j.templateSettings);var u=new RegExp([(r.escape||q).source,(r.interpolate||q).source,(r.evaluate||q).source].join(“|”)+”|$”,”g”),i=0,a=”__p+='”;n.replace(u,function(t,r,e,u,o){return a+=n.slice(i,o).replace(D,function(n){return”\\”+B[n]}),r&&(a+=”‘+\n((__t=(“+r+”))==null?”:_.escape(__t))+\n'”),e&&(a+=”‘+\n((__t=(“+e+”))==null?”:__t)+\n'”),u&&(a+=”‘;\n”+u+”\n__p+='”),i=o+t.length,t}),a+=”‘;\n”,r.variable||(a=”with(obj||{}){\n”+a+”}\n”),a=”var __t,__p=”,__j=Array.prototype.join,”+”print=function(){__p+=__j.call(arguments,”);};\n”+a+”return __p;\n”;try{e=new Function(r.variable||”obj”,”_”,a)}catch(o){throw o.source=a,o}if(t)return e(t,j);var c=function(n){return e.call(this,n,j)};return c.source=”function(“+(r.variable||”obj”)+”){\n”+a+”}”,c},j.chain=function(n){return j(n).chain()};var z=function(n){return this._chain?j(n).chain():n};j.mixin(j),A([“pop”,”push”,”reverse”,”shift”,”sort”,”splice”,”unshift”],function(n){var t=e[n];j.prototype[n]=function(){var r=this._wrapped;return t.apply(r,arguments),”shift”!=n&&”splice”!=n||0!==r.length||delete r[0],z.call(this,r)}}),A([“concat”,”join”,”slice”],function(n){var t=e[n];j.prototype[n]=function(){return z.call(this,t.apply(this._wrapped,arguments))}}),j.extend(j.prototype,{chain:function(){return this._chain=!0,this},value:function(){return this._wrapped}}),”function”==typeof define&&define.amd&&define(“underscore”,[],function(){return j})}).call(this);
//# sourceMappingURL=underscore-min.map
// Underscore.string
!function(e,t){“use strict”;var n=t.prototype.trim,r=t.prototype.trimRight,i=t.prototype.trimLeft,s=function(e){return e*1||0},o=function(e,t){if(t<1)return””;var n=””;while(t>0)t&1&&(n+=e),t>>=1,e+=e;return n},u=[].slice,a=function(e){return e==null?”\\s”:e.source?e.source:”[“+p.escapeRegExp(e)+”]”},f={lt:”<“,gt:”>”,quot:'”‘,apos:”‘”,amp:”&”},l={};for(var c in f)l[f[c]]=c;var h=function(){function e(e){return Object.prototype.toString.call(e).slice(8,-1).toLowerCase()}var n=o,r=function(){return r.cache.hasOwnProperty(arguments[0])||(r.cache[arguments[0]]=r.parse(arguments[0])),r.format.call(null,r.cache[arguments[0]],arguments)};return r.format=function(r,i){var s=1,o=r.length,u=””,a,f=[],l,c,p,d,v,m;for(l=0;l<o;l++){u=e(r[l]);if(u===”string”)f.push(r[l]);else if(u===”array”){p=r[l];if(p[2]){a=i[s];for(c=0;c<p[2].length;c++){if(!a.hasOwnProperty(p[2][c]))throw new Error(h(‘[_.sprintf] property “%s” does not exist’,p[2][c]));a=a[p[2][c]]}}else p[1]?a=i[p[1]]:a=i[s++];if(/[^s]/.test(p[8])&&e(a)!=”number”)throw new Error(h(“[_.sprintf] expecting number but found %s”,e(a)));switch(p[8]){case”b”:a=a.toString(2);break;case”c”:a=t.fromCharCode(a);break;case”d”:a=parseInt(a,10);break;case”e”:a=p[7]?a.toExponential(p[7]):a.toExponential();break;case”f”:a=p[7]?parseFloat(a).toFixed(p[7]):parseFloat(a);break;case”o”:a=a.toString(8);break;case”s”:a=(a=t(a))&&p[7]?a.substring(0,p[7]):a;break;case”u”:a=Math.abs(a);break;case”x”:a=a.toString(16);break;case”X”:a=a.toString(16).toUpperCase()}a=/[def]/.test(p[8])&&p[3]&&a>=0?”+”+a:a,v=p[4]?p[4]==”0″?”0″:p[4].charAt(1):” “,m=p[6]-t(a).length,d=p[6]?n(v,m):””,f.push(p[5]?a+d:d+a)}}return f.join(“”)},r.cache={},r.parse=function(e){var t=e,n=[],r=[],i=0;while(t){if((n=/^[^\x25]+/.exec(t))!==null)r.push(n[0]);else if((n=/^\x25{2}/.exec(t))!==null)r.push(“%”);else{if((n=/^\x25(?:([1-9]\d*)\$|\(([^\)]+)\))?(\+)?(0|'[^$])?(-)?(\d+)?(?:\.(\d+))?([b-fosuxX])/.exec(t))===null)throw new Error(“[_.sprintf] huh?”);if(n[2]){i|=1;var s=[],o=n[2],u=[];if((u=/^([a-z_][a-z_\d]*)/i.exec(o))===null)throw new Error(“[_.sprintf] huh?”);s.push(u[1]);while((o=o.substring(u[0].length))!==””)if((u=/^\.([a-z_][a-z_\d]*)/i.exec(o))!==null)s.push(u[1]);else{if((u=/^\[(\d+)\]/.exec(o))===null)throw new Error(“[_.sprintf] huh?”);s.push(u[1])}n[2]=s}else i|=2;if(i===3)throw new Error(“[_.sprintf] mixing positional and named placeholders is not (yet) supported”);r.push(n)}t=t.substring(n[0].length)}return r},r}(),p={VERSION:”2.3.0″,isBlank:function(e){return e==null&&(e=””),/^\s*$/.test(e)},stripTags:function(e){return e==null?””:t(e).replace(/<\/?[^>]+>/g,””)},capitalize:function(e){return e=e==null?””:t(e),e.charAt(0).toUpperCase()+e.slice(1)},chop:function(e,n){return e==null?[]:(e=t(e),n=~~n,n>0?e.match(new RegExp(“.{1,”+n+”}”,”g”)):[e])},clean:function(e){return p.strip(e).replace(/\s+/g,” “)},count:function(e,n){return e==null||n==null?0:t(e).split(n).length-1},chars:function(e){return e==null?[]:t(e).split(“”)},swapCase:function(e){return e==null?””:t(e).replace(/\S/g,function(e){return e===e.toUpperCase()?e.toLowerCase():e.toUpperCase()})},escapeHTML:function(e){return e==null?””:t(e).replace(/[&<>”‘]/g,function(e){return”&”+l[e]+”;”})},unescapeHTML:function(e){return e==null?””:t(e).replace(/\&([^;]+);/g,function(e,n){var r;return n in f?f[n]:(r=n.match(/^#x([\da-fA-F]+)$/))?t.fromCharCode(parseInt(r[1],16)):(r=n.match(/^#(\d+)$/))?t.fromCharCode(~~r[1]):e})},escapeRegExp:function(e){return e==null?””:t(e).replace(/([.*+?^=!:${}()|[\]\/\\])/g,”\\$1″)},splice:function(e,t,n,r){var i=p.chars(e);return i.splice(~~t,~~n,r),i.join(“”)},insert:function(e,t,n){return p.splice(e,t,0,n)},include:function(e,n){return n===””?!0:e==null?!1:t(e).indexOf(n)!==-1},join:function(){var e=u.call(arguments),t=e.shift();return t==null&&(t=””),e.join(t)},lines:function(e){return e==null?[]:t(e).split(“\n”)},reverse:function(e){return p.chars(e).reverse().join(“”)},startsWith:function(e,n){return n===””?!0:e==null||n==null?!1:(e=t(e),n=t(n),e.length>=n.length&&e.slice(0,n.length)===n)},endsWith:function(e,n){return n===””?!0:e==null||n==null?!1:(e=t(e),n=t(n),e.length>=n.length&&e.slice(e.length-n.length)===n)},succ:function(e){return e==null?””:(e=t(e),e.slice(0,-1)+t.fromCharCode(e.charCodeAt(e.length-1)+1))},titleize:function(e){return e==null?””:t(e).replace(/(?:^|\s)\S/g,function(e){return e.toUpperCase()})},camelize:function(e){return p.trim(e).replace(/[-_\s]+(.)?/g,function(e,t){return t.toUpperCase()})},underscored:function(e){return p.trim(e).replace(/([a-z\d])([A-Z]+)/g,”$1_$2″).replace(/[-\s]+/g,”_”).toLowerCase()},dasherize:function(e){return p.trim(e).replace(/([A-Z])/g,”-$1″).replace(/[-_\s]+/g,”-“).toLowerCase()},classify:function(e){return p.titleize(t(e).replace(/_/g,” “)).replace(/\s/g,””)},humanize:function(e){return p.capitalize(p.underscored(e).replace(/_id$/,””).replace(/_/g,” “))},trim:function(e,r){return e==null?””:!r&&n?n.call(e):(r=a(r),t(e).replace(new RegExp(“^”+r+”+|”+r+”+$”,”g”),””))},ltrim:function(e,n){return e==null?””:!n&&i?i.call(e):(n=a(n),t(e).replace(new RegExp(“^”+n+”+”),””))},rtrim:function(e,n){return e==null?””:!n&&r?r.call(e):(n=a(n),t(e).replace(new RegExp(n+”+$”),””))},truncate:function(e,n,r){return e==null?””:(e=t(e),r=r||”…”,n=~~n,e.length>n?e.slice(0,n)+r:e)},prune:function(e,n,r){if(e==null)return””;e=t(e),n=~~n,r=r!=null?t(r):”…”;if(e.length<=n)return e;var i=function(e){return e.toUpperCase()!==e.toLowerCase()?”A”:” “},s=e.slice(0,n+1).replace(/.(?=\W*\w*$)/g,i);return s.slice(s.length-2).match(/\w\w/)?s=s.replace(/\s*\S+$/,””):s=p.rtrim(s.slice(0,s.length-1)),(s+r).length>e.length?e:e.slice(0,s.length)+r},words:function(e,t){return p.isBlank(e)?[]:p.trim(e,t).split(t||/\s+/)},pad:function(e,n,r,i){e=e==null?””:t(e),n=~~n;var s=0;r?r.length>1&&(r=r.charAt(0)):r=” “;switch(i){case”right”:return s=n-e.length,e+o(r,s);case”both”:return s=n-e.length,o(r,Math.ceil(s/2))+e+o(r,Math.floor(s/2));default:return s=n-e.length,o(r,s)+e}},lpad:function(e,t,n){return p.pad(e,t,n)},rpad:function(e,t,n){return p.pad(e,t,n,”right”)},lrpad:function(e,t,n){return p.pad(e,t,n,”both”)},sprintf:h,vsprintf:function(e,t){return t.unshift(e),h.apply(null,t)},toNumber:function(e,n){if(e==null||e==””)return 0;e=t(e);var r=s(s(e).toFixed(~~n));return r===0&&!e.match(/^0+$/)?Number.NaN:r},numberFormat:function(e,t,n,r){if(isNaN(e)||e==null)return””;e=e.toFixed(~~t),r=r||”,”;var i=e.split(“.”),s=i[0],o=i[1]?(n||”.”)+i[1]:””;return s.replace(/(\d)(?=(?:\d{3})+$)/g,”$1″+r)+o},strRight:function(e,n){if(e==null)return””;e=t(e),n=n!=null?t(n):n;var r=n?e.indexOf(n):-1;return~r?e.slice(r+n.length,e.length):e},strRightBack:function(e,n){if(e==null)return””;e=t(e),n=n!=null?t(n):n;var r=n?e.lastIndexOf(n):-1;return~r?e.slice(r+n.length,e.length):e},strLeft:function(e,n){if(e==null)return””;e=t(e),n=n!=null?t(n):n;var r=n?e.indexOf(n):-1;return~r?e.slice(0,r):e},strLeftBack:function(e,t){if(e==null)return””;e+=””,t=t!=null?””+t:t;var n=e.lastIndexOf(t);return~n?e.slice(0,n):e},toSentence:function(e,t,n,r){t=t||”, “,n=n||” and “;var i=e.slice(),s=i.pop();return e.length>2&&r&&(n=p.rtrim(t)+n),i.length?i.join(t)+n+s:s},toSentenceSerial:function(){var e=u.call(arguments);return e[3]=!0,p.toSentence.apply(p,e)},slugify:function(e){if(e==null)return””;var n=”ąàáäâãåæćęèéëêìíïîłńòóöôõøùúüûñçżź”,r=”aaaaaaaaceeeeeiiiilnoooooouuuunczz”,i=new RegExp(a(n),”g”);return e=t(e).toLowerCase().replace(i,function(e){var t=n.indexOf(e);return r.charAt(t)||”-“}),p.dasherize(e.replace(/[^\w\s-]/g,””))},surround:function(e,t){return[t,e,t].join(“”)},quote:function(e){return p.surround(e,'”‘)},exports:function(){var e={};for(var t in this){if(!this.hasOwnProperty(t)||t.match(/^(?:include|contains|reverse)$/))continue;e[t]=this[t]}return e},repeat:function(e,n,r){if(e==null)return””;n=~~n;if(r==null)return o(t(e),n);for(var i=[];n>0;i[–n]=e);return i.join(r)},levenshtein:function(e,n){if(e==null&&n==null)return 0;if(e==null)return t(n).length;if(n==null)return t(e).length;e=t(e),n=t(n);var r=[],i,s;for(var o=0;o<=n.length;o++)for(var u=0;u<=e.length;u++)o&&u?e.charAt(u-1)===n.charAt(o-1)?s=i:s=Math.min(r[u],r[u-1],i)+1:s=o+u,i=r[u],r[u]=s;return r.pop()}};p.strip=p.trim,p.lstrip=p.ltrim,p.rstrip=p.rtrim,p.center=p.lrpad,p.rjust=p.lpad,p.ljust=p.rpad,p.contains=p.include,p.q=p.quote,typeof exports!=”undefined”?(typeof module!=”undefined”&&module.exports&&(module.exports=p),exports._s=p):typeof define==”function”&&define.amd?define(“underscore.string”,[],function(){return p}):(e._=e._||{},e._.string=e._.str=p)}(this,String);
13. Checklist de sua conta do Google Ads – Por Russel Savage. Faça uma auditoria de sua conta com esse script. O script passará pelos pontos essenciais de sua conta do Google Ads e evidencia quais áreas precisam de uma maior atenção. Para ter acesso ao relatório é necessário rodar o script, “ver detalhes” e depois em logs.
/************************************
* AdWords Account Audit Checklist
* Version 1.1
* ChangeLog v1.1 – Fixed issue with extension selector.
* Based on the blog post by Phil Kowalski
* //www.wordstream.com/blog/ws/2013/07/02/adwords-account-audit-checklist
* Created By: Russ Savage
* FreeAdWordsScripts.com
************************************/
function main() {
//1. Campaigns
// a. Target the right locations
var includedLocList = [‘United States’,’Canada’]; // <– the list of places your campaigns should be targeting
verifyTargetedLocations(includedLocList);
var excludedLocList = [‘Europe’]; // <– the list of places your campaigns should be excluding
verifyExcludedLocations(excludedLocList);
// b. Language – Can’t be done using scripts yet 🙁
// c. Search vs Display
verifySearchAndDisplay();
// d. Check Mobile Strategy
verifyMobileModifiers();
//2. AdGroups
// a. Check for AdGroups with more than 20-30 keywords
var ADGROUP_SIZE = 25; // <– this is the max number of keywords you want in an AdGroup
verifyAdGroupSize(ADGROUP_SIZE);
// b. Check for topic. Difficult to do with scripts
// c. Check for ads
var NUMBER_OF_ADS = 3; // <– this is the minimum number of ads in an AdGroup
verifyAdGroupNumberOfAds(NUMBER_OF_ADS);
//3. Keywords
// a. Check for MatchTypes
printMatchTypes();
//4. Search Queries
// This analysis is probably worth it’s own script
//5. Other
// a. Conversion Tracking
verifyConversionTracking();
// b. AdExtensions
verifyAdExtensions();
}
function verifyConversionTracking() {
//Assume that if the account has not had a conversion in 7 days, something is wrong.
var campsWithConversions = AdWordsApp.campaigns()
.withCondition(‘Status = ENABLED’)
.forDateRange(‘LAST_7_DAYS’)
.withCondition(‘Conversions > 0’)
.get().totalNumEntities();
if(campsWithConversions == 0) {
warn(‘Account is probably missing conversion tracking.’);
}
}
function verifyAdExtensions() {
var campIter = AdWordsApp.campaigns().withCondition(‘Status = ENABLED’).get();
while(campIter.hasNext()) {
var camp = campIter.next();
var phoneNumExtCount = camp.extensions().phoneNumbers().get().totalNumEntities();
if(phoneNumExtCount == 0) {
warn(‘Campaign: “‘+camp.getName()+'” is missing phone number extensions.’);
}
var siteLinksExtCount = camp.extensions().sitelinks().get().totalNumEntities();
if(siteLinksExtCount < 6) {
warn(‘Campaign: “‘+camp.getName()+'” could use more site links. Currently has: ‘+siteLinksExtCount);
}
var mobileAppsExtCount = camp.extensions().mobileApps().get().totalNumEntities();
if(mobileAppsExtCount == 0) {
warn(‘Campaign: “‘+camp.getName()+'” is missing mobile apps extension.’);
}
}
}
function printMatchTypes() {
var numBroad = AdWordsApp.keywords()
.withCondition(‘Status = ENABLED’)
.withCondition(‘AdGroupStatus = ENABLED’)
.withCondition(‘CampaignStatus = ENABLED’)
.withCondition(‘KeywordMatchType = BROAD’)
.get().totalNumEntities();
var numPhrase = AdWordsApp.keywords()
.withCondition(‘Status = ENABLED’)
.withCondition(‘AdGroupStatus = ENABLED’)
.withCondition(‘CampaignStatus = ENABLED’)
.withCondition(‘KeywordMatchType = PHRASE’)
.get().totalNumEntities();
var numExact = AdWordsApp.keywords()
.withCondition(‘Status = ENABLED’)
.withCondition(‘AdGroupStatus = ENABLED’)
.withCondition(‘CampaignStatus = ENABLED’)
.withCondition(‘KeywordMatchType = EXACT’)
.get().totalNumEntities();
var total = numBroad+numPhrase+numExact;
var percBroad = Math.round(numBroad/total*100);
var percPhrase = Math.round(numPhrase/total*100);
var percExact = Math.round(numExact/total*100);
info(‘Out of a total of: ‘+total+’ active keywords in your account:’);
info(‘\tBroad: ‘+numBroad+’ or ‘+percBroad+’%’);
info(‘\tPhrase: ‘+numPhrase+’ or ‘+percPhrase+’%’);
info(‘\tExact: ‘+numExact+’ or ‘+percExact+’%’);
}
function verifyAdGroupNumberOfAds(requiredNumberOfAds) {
var agIter = AdWordsApp.adGroups()
.withCondition(‘Status = ENABLED’)
.withCondition(‘CampaignStatus = ENABLED’)
.get();
while(agIter.hasNext()) {
var ag = agIter.next();
var adCount = ag.ads().withCondition(‘Status = ENABLED’).get().totalNumEntities();
if(adCount < requiredNumberOfAds) {
warn(‘Campaign: “‘+ag.getCampaign().getName()+'” AdGroup: “‘+ag.getName()+'” does not have enough ads: ‘+adCount);
}
if(adCount > (requiredNumberOfAds+2)) {
warn(‘Campaign: “‘+ag.getCampaign().getName()+'” AdGroup: “‘+ag.getName()+'” has too many ads: ‘+adCount);
}
}
}
function verifyAdGroupSize(size) {
var agIter = AdWordsApp.adGroups()
.withCondition(‘Status = ENABLED’)
.withCondition(‘CampaignStatus = ENABLED’)
.get();
while(agIter.hasNext()) {
var ag = agIter.next();
var kwSize = ag.keywords().withCondition(‘Status = ENABLED’).get().totalNumEntities();
if(kwSize >= size) {
warn(‘Campaign: “‘+ag.getCampaign().getName()+'” AdGroup: “‘+ag.getName()+'” has too many keywords: ‘+kwSize);
}
}
}
function verifyMobileModifiers() {
var campIter = AdWordsApp.campaigns().withCondition(‘Status = ENABLED’).get();
while(campIter.hasNext()) {
var camp = campIter.next();
var desktop = camp.targeting().platforms().desktop().get().next();
//var tablet = camp.targeting().platforms().tablet().get().next();
var mobile = camp.targeting().platforms().mobile().get().next();
//check for mobile modifiers
if(desktop.getBidModifier() == 1 && mobile.getBidModifier() == 1) {
warn(‘Campaign: “‘+camp.getName()+'” has no mobile modifier set.’);
}
}
}
function verifyTargetedLocations(locList) {
var campIter = AdWordsApp.campaigns().withCondition(‘Status = ENABLED’).get();
while(campIter.hasNext()) {
var camp = campIter.next();
var locIter = camp.targeting().targetedLocations().get();
reportOnLocations(camp,locIter,locList);
}
}
function verifyExcludedLocations(locList) {
var campIter = AdWordsApp.campaigns().withCondition(‘Status = ENABLED’).get();
while(campIter.hasNext()) {
var camp = campIter.next();
var locIter = camp.targeting().excludedLocations().get();
reportOnLocations(camp,locIter,locList);
}
}
function reportOnLocations(camp,locIter,locList) {
var campLocList = [];
while(locIter.hasNext()) {
var loc = locIter.next();
campLocList.push(loc.getName());
if(!locList) {
warn(‘Campaign: “‘+camp.getName()+'” targeting: “‘+loc.getName()+'”‘);
}
}
if(locList && campLocList.sort() != locList.sort()) {
for(var i in campLocList) {
if(locList.indexOf(campLocList[i]) == -1) {
warn(‘Campaign: “‘+camp.getName()+'” incorrectly targeting: “‘+campLocList[i]+'”‘);
}
}
for(var i in locList) {
if(campLocList.indexOf(locList[i]) == -1) {
warn(‘Campaign: “‘+camp.getName()+'” not targeting: “‘+locList[i]+'”‘);
}
}
}
}
function verifySearchAndDisplay() {
var API_VERSION = { includeZeroImpressions : false };
var cols = [‘CampaignId’,’CampaignName’,’AdNetworkType1′,’Impressions’];
var report = ‘CAMPAIGN_PERFORMANCE_REPORT’;
var query = [‘select’,cols.join(‘,’),’from’,report,’during’,’LAST_30_DAYS’].join(‘ ‘);
var results = {}; // { campId : { agId : [ row, … ], … }, … }
var reportIter = AdWordsApp.report(query, API_VERSION).rows();
while(reportIter.hasNext()) {
var row = reportIter.next();
if(results[row.CampaignId]) {
warn(‘Campaign: “‘+row.CampaignName+'” is targeting the Display and Search networks.’);
} else {
results[row.CampaignId] = row;
}
}
return results;
}
function warn(msg) {
Logger.log(‘WARNING: ‘+msg);
}
function info(msg) {
Logger.log(msg);
}
Grupo de Brasileiros presentes no evento Inbound 2018.
A Apple em 2017 havia implementado em todos os dispositivos com iOS o “Intelligence Tracking Prevention”, uma iniciativa importante para anunciantes do Google Ads pois reduz o tempo de conversão para apenas 1 dia. Isso porque utiliza um cookie secundário.
A partir de Outubro de 2018, palavras-chave de correspondência exata no 
Segundo o 
