/******************************************
* ETA vs RSA Test
* @version: 1.0
* @author: Naman Jindal (Optmyzr)
* March 2, 2020
*******************************************/
var LOOKBACK_DAYS = 30; // Number of Days to look back for stats
var EMAILS = ['example@example.com']; // Array of Emails separated by comma
var CAMPAIGN_NAME_CONTAINS = ''; // Case Insensitive
var CAMPAIGN_NAME_DOES_NOT_CONTAIN = ''; // Case Insensitive
// Do not edit anything below this line
function main() {
var DATE_RANGE = getAdWordsFormattedDate(LOOKBACK_DAYS, 'yyyyMMdd') + ',' + getAdWordsFormattedDate(1, 'yyyyMMdd');
var adMap = {}, agMap = {};
var query = [
'SELECT Id, AdGroupId, AdType, Status FROM AD_PERFORMANCE_REPORT',
'WHERE CampaignStatus = ENABLED and AdGroupStatus = ENABLED',
'and AdType IN [RESPONSIVE_SEARCH_AD, EXPANDED_TEXT_AD]',
CAMPAIGN_NAME_CONTAINS ? 'and CampaignName CONTAINS_IGNORE_CASE "' + CAMPAIGN_NAME_CONTAINS + '"' : '',
CAMPAIGN_NAME_DOES_NOT_CONTAIN ? 'and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE "' + CAMPAIGN_NAME_DOES_NOT_CONTAIN + '"' : '',
'DURING', DATE_RANGE
].join(' ');
var rows = AdsApp.report(query, {'includeZeroImpressions': false }).rows();
while(rows.hasNext()) {
var row = rows.next();
var key = [row.AdGroupId, row.Id].join('-');
adMap[key] = row.AdType;
if(!agMap[row.AdGroupId] ) {
agMap[row.AdGroupId] = {
'Responsive search ad': 0, 'Expanded text ad': 0
};
}
if(row.Status == 'enabled') {
agMap[row.AdGroupId][row.AdType]++;
}
}
var agIds = Object.keys(agMap)
var initMap = {
'ETA': 0, 'RSA': 0,
'Responsive search ad': { 'Impressions': 0, 'Clicks': 0, 'Conversions': 0, 'Cost': 0, 'ConversionValue': 0 } ,
'Expanded text ad': { 'Impressions': 0, 'Clicks': 0, 'Conversions': 0, 'Cost': 0, 'ConversionValue': 0 }
};
var statsMap = {};
var query = [
'SELECT',
'Query, CampaignName, AdGroupName, AdGroupId, CreativeId, Impressions, Clicks, Conversions, Cost, ConversionValue',
'FROM SEARCH_QUERY_PERFORMANCE_REPORT',
'WHERE CampaignStatus = ENABLED and AdGroupStatus = ENABLED',
CAMPAIGN_NAME_CONTAINS ? 'and CampaignName CONTAINS_IGNORE_CASE "' + CAMPAIGN_NAME_CONTAINS + '"' : '',
CAMPAIGN_NAME_DOES_NOT_CONTAIN ? 'and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE "' + CAMPAIGN_NAME_DOES_NOT_CONTAIN + '"' : '',
agIds.length < 10000 ? 'and AdGroupId IN [' + agIds.join(',') + ']' : '',
'DURING', DATE_RANGE
].join(' ');
var rows = AdsApp.report(query, {'includeZeroImpressions': false }).rows();
while(rows.hasNext()) {
var row = rows.next();
var adKey = [row.AdGroupId, row.CreativeId].join('-');
var adType = adMap[adKey];
if(!initMap[adType]) {
continue;
}
var agRow = agMap[row.AdGroupId];
if(!agRow) {
agRow = { 'Responsive search ad': 0, 'Expanded text ad': 0 }
}
var key = [row.Query, row.AdGroupName, row.CampaignName].join('!~!');
if(!statsMap[key]) {
statsMap[key] = JSON.parse(JSON.stringify(initMap));
}
statsMap[key]['ETA'] = agRow['Expanded text ad'];
statsMap[key]['RSA'] = agRow['Responsive search ad'];
row.Impressions = parseFloat(row.Impressions);
row.Clicks = parseFloat(row.Clicks);
row.Cost = parseFloat(row.Cost.toString().replace(/,/g, ''));
row.Conversions = parseFloat(row.Conversions.toString().replace(/,/g, ''));
row.ConversionValue = parseFloat(row.ConversionValue.toString().replace(/,/g, ''));
statsMap[key][adType].Impressions += row.Impressions;
statsMap[key][adType].Clicks += row.Clicks;
statsMap[key][adType].Cost += row.Cost;
statsMap[key][adType].Conversions += row.Conversions;
statsMap[key][adType].ConversionValue += row.ConversionValue;
}
var output = [];
var results = {
'rsa': { 'incrementalClicks': 0, 'incrementalConversions': 0 },
'all': { 'incrementalClicks': 0, 'incrementalConversions': 0 }
}
for(var key in statsMap) {
var row = statsMap[key];
var out = key.split('!~!');
out.push(row['ETA'], row['RSA']);
var rsaRow = row['Responsive search ad'];
rsaRow.Ctr = rsaRow.Impressions > 0 ? rsaRow.Clicks / rsaRow.Impressions : 0;
rsaRow.CPI = rsaRow.Impressions > 0 ? rsaRow.Conversions / rsaRow.Impressions : 0;
rsaRow.CPA = rsaRow.Conversions > 0 ? rsaRow.Cost / rsaRow.Conversions : 0;
rsaRow.Cpc = rsaRow.Clicks > 0 ? rsaRow.Cost / rsaRow.Clicks : 0;
rsaRow.ROAS = rsaRow.Cost > 0 ? rsaRow.ConversionValue / rsaRow.Cost : 0;
out.push(rsaRow.Impressions, rsaRow.Clicks, rsaRow.Conversions, rsaRow.Ctr, rsaRow.CPI, rsaRow.Cost, rsaRow.CPA, rsaRow.ROAS);
var etaRow = row['Expanded text ad'];
etaRow.Ctr = etaRow.Impressions > 0 ? etaRow.Clicks / etaRow.Impressions : 0;
etaRow.CPI = etaRow.Impressions > 0 ? etaRow.Conversions / etaRow.Impressions : 0;
etaRow.CPA = etaRow.Conversions > 0 ? etaRow.Cost / etaRow.Conversions : 0;
etaRow.Cpc = etaRow.Clicks > 0 ? etaRow.Cost / etaRow.Clicks : 0;
etaRow.ROAS = etaRow.Cost > 0 ? etaRow.ConversionValue / etaRow.Cost : 0;
out.push(etaRow.Impressions, etaRow.Clicks, etaRow.Conversions, etaRow.Ctr, etaRow.CPI, etaRow.Cost, etaRow.CPA, etaRow.ROAS);
var incrementalClicks = rsaRow.Impressions * (rsaRow.Ctr - etaRow.Ctr);
var incrementalConversions = rsaRow.Impressions * (rsaRow.CPI - etaRow.CPI);
var incrementalCost = rsaRow.Clicks * (rsaRow.Cpc - etaRow.Cpc);
var recommendation = '';
if(row['ETA'] == 0) {
recommendation = 'Test ETAs';
} else if(row['RSA'] == 0) {
recommendation = 'Test RSAs';
} else if(incrementalConversions > 0) {
recommendation = 'SKAG query with existing RSA';
} else if(incrementalConversions < 0) {
recommendation = 'SKAG query with existing ETA';
} else if(etaRow.Impressions == 0) {
recommendation = 'Test ETAs';
} else if(rsaRow.Impressions == 0) {
recommendation = 'Test RSAs';
}
if(row['ETA'] == 0 && etaRow.Impressions == 0) {
results['rsa'].incrementalClicks += incrementalClicks;
results['rsa'].incrementalConversions += incrementalConversions;
}
if(etaRow.Impressions > 0 && rsaRow.Impressions > 0) {
results['all'].incrementalClicks += incrementalClicks;
results['all'].incrementalConversions += incrementalConversions;
}
out.push(incrementalClicks, incrementalConversions, incrementalCost, recommendation);
output.push(out);
}
if(!output.length) {
Logger.log('No Data found');
return;
}
var TEMPLATE_URL = 'https://docs.google.com/spreadsheets/d/1AAk_Vsdq0LezvezOarzhOzVXsQq5BN-EKMRtGX-eDVQ/edit';
var template = SpreadsheetApp.openByUrl(TEMPLATE_URL);
var now = getAdWordsFormattedDate(0, 'MMM d, yyyy')
var name = AdsApp.currentAccount().getName() + ' - ETA vs RSA (' + now + ')';
var ss = template.copy(name);
var reportUrl = ss.getUrl();
Logger.log('Report URL: ' + reportUrl);
ss.addEditors(EMAILS);
ss.getSheets()[0].getRange(3,1,output.length,output[0].length).setValues(output);
sendEmail(results, reportUrl);
}
function sendEmail(results, reportUrl) {
var sub = AdsApp.currentAccount().getName() + ' - ETA vs RSA Test Report';
var msg = 'Hi,\n\nPlease find below summary of latest test results:\n';
var rsaResults = results['rsa'];
if(rsaResults.incrementalClicks > 0) {
msg += 'You got ' + Math.round(rsaResults.incrementalClicks) + 'more clicks and ' + Math.round(rsaResults.incrementalConversions) + ' more convversions from queries that only triggered with RSA ads.\n';
}
var change = ''
if(results['all'].incrementalConversions > 0) {
change = 'gained';
} else if(results['all'].incrementalConversions < 0) {
change = 'lost';
}
if(change) {
msg += 'For queries that showed both RSA and ETA ads, you ' + change + ' ' + Math.round(results['all'].incrementalConversions) + ' conversions.\n';
}
msg += '\n\n' + reportUrl;
msg +- '\n\nThanks';
MailApp.sendEmail(EMAILS.join(','), sub, msg);
}
function getAdWordsFormattedDate(d, format){
var date = new Date();
date.setDate(date.getDate() - d);
return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format);
}
Save