Skip to content

Instantly share code, notes, and snippets.

@daniel-c05
Last active April 9, 2019 16:19
Show Gist options
  • Save daniel-c05/e337bd57f282269c5018 to your computer and use it in GitHub Desktop.
Save daniel-c05/e337bd57f282269c5018 to your computer and use it in GitHub Desktop.
AdWords Scripts - Evaluate Ad Text Line Performance
// Comma-separated list of recipients. Comment out to not send any emails.
var RECIPIENT_EMAIL = 'YOUR_EMAIL';
// URL of the default spreadsheet template. This should be a copy of http://goo.gl/pxaZio
var SPREADSHEET_URL = 'SPREADSHEET_URL';
/**
* This script computes an Ad performance report
* and outputs it to a Google spreadsheet
*/
function main() {
var spreadsheet = copySpreadsheet(SPREADSHEET_URL);
var headlineSheet = spreadsheet.getSheetByName('Headline');
headlineSheet.getRange(1, 2, 1, 1).setValue('Date');
headlineSheet.getRange(1, 3, 1, 1).setValue(new Date());
var descriptionLine1Sheet = spreadsheet.getSheetByName('Description Line 1');
descriptionLine1Sheet.getRange(1, 2, 1, 1).setValue('Date');
descriptionLine1Sheet.getRange(1, 3, 1, 1).setValue(new Date());
var descriptionLine2Sheet = spreadsheet.getSheetByName('Description Line 2');
descriptionLine2Sheet.getRange(1, 2, 1, 1).setValue('Date');
descriptionLine2Sheet.getRange(1, 3, 1, 1).setValue(new Date());
spreadsheet.getRangeByName('account_id_headline').setValue(
AdWordsApp.currentAccount().getCustomerId());
spreadsheet.getRangeByName('account_id_description_1').setValue(
AdWordsApp.currentAccount().getCustomerId());
spreadsheet.getRangeByName('account_id_description_2').setValue(
AdWordsApp.currentAccount().getCustomerId());
outputSegmentation(headlineSheet, 'Headline', function(ad) {
return ad.getHeadline();
});
outputSegmentation(descriptionLine1Sheet, 'Description Line 1', function(ad) {
return ad.getDescription1();
});
outputSegmentation(descriptionLine2Sheet, 'Description Line 2', function(ad) {
return ad.getDescription2();
});
Logger.log('Ad performance report available at\n' + spreadsheet.getUrl());
if (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) {
return SpreadsheetApp.openByUrl(spreadsheetUrl).copy(
'Ad Performance Report ' + new Date());
}
/**
* 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',
'Conversions',
'Conv. Rate (%)',
'Cost per Conv.'
];
rows.push(header);
var segmentMap = {};
// Compute data
var adIterator = AdWordsApp.ads()
.forDateRange('LAST_14_DAYS')
.withCondition('Impressions > 0').get();
while (adIterator.hasNext()) {
var ad = adIterator.next();
var stats = ad.getStatsFor('LAST_14_DAYS');
var segment = segmentFunc(ad);
if (!segmentMap[segment]) {
segmentMap[segment] = {
numAds: 0,
totalImpressions: 0,
totalClicks: 0,
totalCost: 0.0,
totalConversions: 0,
};
}
var data = segmentMap[segment];
data.numAds++;
data.totalImpressions += stats.getImpressions();
data.totalClicks += stats.getClicks();
data.totalCost += stats.getCost();
data.totalConversions += stats.getConvertedClicks();
}
// Write data to our rows.
for (var key in segmentMap) {
if (segmentMap.hasOwnProperty(key)) {
var ctr = 0;
var convRate = 0;
var costPerConv = 0;
if (segmentMap[key].numAds > 0) {
ctr = (segmentMap[key].totalClicks /
segmentMap[key].totalImpressions) * 100;
}
if (segmentMap[key].totalConversions > 0) {
convRate = (segmentMap[key].totalConversions /
segmentMap[key].totalClicks) * 100;
costPerConv = (segmentMap[key].totalCost /
segmentMap[key].totalConversions);
}
var row = [
key,
segmentMap[key].numAds,
segmentMap[key].totalImpressions,
segmentMap[key].totalClicks,
ctr.toFixed(2),
segmentMap[key].totalCost,
segmentMap[key].totalConversions,
convRate.toFixed(2),
costPerConv.toFixed(2)];
rows.push(row);
}
}
sheet.getRange(3, 2, rows.length, 9).setValues(rows);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment