Skip to content

Instantly share code, notes, and snippets.

@siliconvallaeys
Last active April 24, 2023 00:14
Show Gist options
  • Save siliconvallaeys/b2ec7f081755dacd00aba5c09d84be8d to your computer and use it in GitHub Desktop.
Save siliconvallaeys/b2ec7f081755dacd00aba5c09d84be8d to your computer and use it in GitHub Desktop.
Generate a Google sheet with RSAs from your account and make it easier to find RSAs that don't use the maximum allowable number of headlines and descriptions.
/******************************************
* RSA Report
* @version: 1.0
* @author: Naman Jindal (Optmyzr)
* -------------------------------
* This script can be used to find responsive search ads
* that don't have the desired minimum number of headlines
* and descriptions.
*
* Optmyzr subscribers can use the Ad Text Optimization (RSA) tool
* to find and fix ads without enough asset variations.
*
* Get your free trial at www.optmyzr.com
******************************************/
// If Blank script will create a new Google sheet everytime it runs.
var SS_URL = '';
// Name of the tab in the Google sheet.
var TAB_NAME = 'RSA';
// Flag to decide if the script checks only ads in active campaigns and active ad groups
var INCLUDE_PAUSED = false;
// only include ads with this many or fewer headlines on the output spreadsheet (defaults to 15)
var MAX_HEADLINES = 15;
// only include ads with this many or fewer descriptions on the output spreadsheet (defaults to 4)
var MAX_DESCRIPTIONS = 4;
// Multiple emails can be added sepearated by comma (,)
// Used for access to spreadsheet and for sending email
var EMAIL = '';
// Set to true if you want to recieve the report on Email.
var SEND_EMAIL = true;
//Headline 1 Headline 2 Headline 3 Headline 4 Headline 5 Headline 6 Headline 7 Headline 8 Headline 9 Headline 10 Headline 11 Headline 12 Headline 13 Headline 14 Headline 15 Description Line 1 Description Line 2 Description Line 3 Description Line 4
// Do not edit anything below this line
function main() {
var output = [[
'Account ID', 'Account Name', 'Campaign', 'Ad Group', 'Ad ID', 'Number of Headlines', 'Number of Descriptions', 'Ad Strength',
'Headline 1', 'Headline 2', 'Headline 3', 'Headline 4', 'Headline 5', 'Headline 6', 'Headline 7', 'Headline 8', 'Headline 9',
'Headline 10', 'Headline 11', 'Headline 12', 'Headline 13', 'Headline 14', 'Headline 15',
'Description Line 1', 'Description Line 2', 'Description Line 3', 'Description Line 4'
]];
var accId = AdsApp.currentAccount().getCustomerId(),
accName = AdsApp.currentAccount().getName();
var query = [
'SELECT campaign.name, ad_group.name, ad_group_ad.ad.id, ad_group_ad.ad_strength,',
'ad_group_ad.ad.responsive_search_ad.headlines, ad_group_ad.ad.responsive_search_ad.descriptions',
'FROM ad_group_ad WHERE ad_group_ad.ad.type = RESPONSIVE_SEARCH_AD AND metrics.impressions >= 0',
INCLUDE_PAUSED ? '' : 'AND ad_group_ad.status = ENABLED AND campaign.status = ENABLED and ad_group.status = ENABLED',
'AND segments.date DURING LAST_7_DAYS'
].join(' ');
var rows = AdsApp.report(query).rows();
while(rows.hasNext()) {
var row = rows.next();
var headlines = row['ad_group_ad.ad.responsive_search_ad.headlines'];
var headlineCount = headlines.length;
var descriptions = row['ad_group_ad.ad.responsive_search_ad.descriptions'];
var descriptionCount = descriptions.length;
if(headlineCount > MAX_HEADLINES || descriptionCount > MAX_DESCRIPTIONS) { continue; }
var out = [
accId, accName, row['campaign.name'], row['ad_group.name'], row['ad_group_ad.ad.id'],
headlineCount, descriptionCount, row['ad_group_ad.ad_strength']
];
var headlinesText = [];
for(var z in headlines) {
headlinesText.push(headlines[z].text);
}
while(headlinesText.length < 15) {
headlinesText.push('');
}
var descriptionsText = [];
for(var z in descriptions) {
descriptionsText.push(descriptions[z].text);
}
while(descriptionsText.length < 4) {
descriptionsText.push('');
}
out = out.concat(headlinesText).concat(descriptionsText);
output.push(out);
}
if(!SS_URL) {
var ss = SpreadsheetApp.create(accName + ': RSA Report');
SS_URL = ss.getUrl();
if(EMAIL) {
ss.addEditors(EMAIL.split(','));
}
}
Logger.log('Report URL: ' + SS_URL);
var ss = SpreadsheetApp.openByUrl(SS_URL);
var tab = ss.getSheetByName(TAB_NAME);
if(!tab) {
tab = ss.getSheetByName('Sheet1');
if(!tab) {
tab = ss.insertSheet(TAB_NAME);
} else {
tab.setName(TAB_NAME)
}
}
tab.clearContents();
tab.setFrozenRows(1);
tab.getRange(1,1,output.length,output[0].length).setValues(output);
if(EMAIL && SEND_EMAIL) {
MailApp.sendEmail(EMAIL, accName + ' RSA Report is ready', 'The report is available at the link below:\n'+SS_URL +"\n\nCheers,\nYour friends from Optmyzr and Closed Loop.");
}
}
function getGoogleAdsFormattedDate(d, format){
var date = new Date();
date.setDate(date.getDate() - d);
return Utilities.formatDate(date,AdsApp.currentAccount().getTimeZone(),format);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment