ETA vs RSA Query Report
Thu Aug 27 2020 18:23:11 GMT+0000 (Coordinated Universal Time)
Saved by @moakdesigns #javascript
/****************************************** * 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
Comments