siliconvallaeys · November 13, 2024 17:13 · CMduPreez · Apr 14, 2023 · Enri92c · Apr 17, 2023
diff --git a/GPT Suggestions for RSAs with Google Ads Scripts b/GPT Suggestions for RSAs with Google Ads Scripts
 /******************************************
 * RSA Report
 * @version: 3.0
 * @authors: Naman Jindal (Optmyzr), Frederick Vallaeys (Optmyzr)
 * -------------------------------
 * Install this script in your Google Ads account (not an MCC account)
 * to generate a Google Sheet with a list of all your responsive search ads
 * and their headlines and descriptions.
 * For RSAs that are not using the maximum number of allowed variations,
 * this script will suggest new variations for headlines and descriptions 
 * using the OpenAI GPT API.
 * The resulting sheet can be bulk uploaded back into Google Ads.
 * --------------------------------
 * For more PPC tools, visit 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 = '';

 // Flag to decide if the script checks only ads in active campaigns and active ad groups
 var INCLUDE_PAUSED = true;

 // Script will only process this many or fewer ads to run within ChatGPT API limitations
 var MAX_ADS = 100;

 // Language code for output. Example US-EN or UK-EN, or FR-FR or CA-FR
 var LANGUAGE_CODE = 'US-EN';

 // Use this to mention the type of industry your campaigns belongs to, to get more relevant results from ChatGPT API
 var INDUSTRY_TYPE = '';

 // Use this to filter on specific campaigns by label. Case Sensitive.
 var CAMPAIGN_LABEL_IN = [''];

 // Use this to filter on specific adgroups by label. Case Sensitive
 var ADGROUP_LABEL_IN = [''];

 // Use this to filter on specific campaigns only. Case insensitive
 var CAMPAIGN_NAME_CONTAINS = '';

 // 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 = false;

 var OPEN_AI_API_KEY = ''

 var GPT_MODEL = 'gpt-3.5-turbo';


 // Do not edit anything below this line
 function main() {
  
  var output = [[
    'Account ID', 'Account Name', 'Campaign', 'Ad Group', 'Ad ID', '# Headlines', '# 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 columCount = output[0].length;
  var backgroupHeader = [];
  while(backgroupHeader.length < columCount) {
    backgroupHeader.push('#ffffff');
  }
  
  var backgrounds = [backgroupHeader];
  
  var accId = AdsApp.currentAccount().getCustomerId(),
      customerId = AdsApp.currentAccount().getCustomerId().replace(/-/g, ''),
      accName = AdsApp.currentAccount().getName();
  
  //var CAMPAIGN_IDS = [];
  var CAMPAIGN_LABELS_IN = [];
  if(CAMPAIGN_LABEL_IN.length) {
    var iter = AdsApp.labels().withCondition('label.name IN ("' + CAMPAIGN_LABEL_IN.join('","') + '")').get();
    while(iter.hasNext()) {
      var label = iter.next();
      CAMPAIGN_LABELS_IN.push('/customers/'+customerId+'/labels/'+label.getId());
    }
  }
  
  var ADGROUP_LABELS_IN = [];
  if(ADGROUP_LABEL_IN.length) {
    var iter = AdsApp.labels().withCondition('label.name IN ("' + ADGROUP_LABEL_IN.join('","') + '")').get();
    while(iter.hasNext()) {
      var label = iter.next();
      ADGROUP_LABELS_IN.push('/customers/'+customerId+'/labels/'+label.getId());
    }
  }
  
  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',
    CAMPAIGN_NAME_CONTAINS ? 'AND campaign.name REGEXP_MATCH "(?i).*'+CAMPAIGN_NAME_CONTAINS+'.*"' : '',
    INCLUDE_PAUSED ? '' : 'AND ad_group_ad.status = ENABLED AND campaign.status = ENABLED and ad_group.status = ENABLED',
    CAMPAIGN_LABELS_IN.length ? 'AND campaign.labels CONTAINS ANY ("'+CAMPAIGN_LABELS_IN.join('","')+'")' : '',
    ADGROUP_LABELS_IN.length ? 'AND ad_group.labels CONTAINS ANY ("'+ADGROUP_LABELS_IN.join('","')+'")' : '',
    '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 bgRow = ['#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff'];
    
    var headlinesText = [];
    for(var z in headlines) {
      headlinesText.push(headlines[z].text);
      bgRow.push('#ffffff');
    }
    
    var diff = 15 - headlinesText.length;
    
    var autoHeadlines = [];
    if(diff > 0) {
      var prompt = 'Find '+diff+' more ad headlines under 30 characters that are similar to these:\n';
      if(LANGUAGE_CODE && INDUSTRY_TYPE){ 
        prompt = 'Find '+diff+' more ad headlines, in ' + LANGUAGE_CODE +' language code, for a '+INDUSTRY_TYPE+' industry campaign under 30 characters that are similar to these:\n';
      } else if(LANGUAGE_CODE){ 
        prompt = 'Find '+diff+' more ad headlines, in ' + LANGUAGE_CODE +' language code, under 30 characters that are similar to these:\n';
      } else if(INDUSTRY_TYPE){ 
        prompt = 'Find '+diff+' more ad headlines for a '+INDUSTRY_TYPE+' industry campaign under 30 characters that are similar to these:\n';
      }
      
      autoHeadlines = generateTextOpenAI(prompt, headlinesText);
    }
    
    if(autoHeadlines.length) {
      headlinesText = headlinesText.concat(autoHeadlines);
      for(var i = 0; i < autoHeadlines.length; i++) {
        bgRow.push('#d9ead3');
      }
    }
    
    while(headlinesText.length < 15) {
      headlinesText.push('');
      bgRow.push('#fffff')
    }
    
    while(headlinesText.length > 15) {
      headlinesText.pop();
      bgRow.pop();
    }
    
    var descriptionsText = [];
    for(var z in descriptions) {
      descriptionsText.push(descriptions[z].text);
      bgRow.push('#ffffff');
    }
    
    var diff = 4 - descriptions.length;
    var autoDescriptions = [];
    if(diff > 0) {
      var prompt = 'Find '+diff+' more ad descriptions under 90 characters that are similar to these:\n';
      
      if(LANGUAGE_CODE && INDUSTRY_TYPE){ 
        prompt = 'Find '+diff+' more ad descriptions, in ' + LANGUAGE_CODE +' language code, for a '+INDUSTRY_TYPE+' industry campaign under 90 characters that are similar to these:\n';
      } else if(LANGUAGE_CODE){ 
        prompt = 'Find '+diff+' more ad descriptions, in ' + LANGUAGE_CODE +' language code, under 90 characters that are similar to these:\n';
      } else if(INDUSTRY_TYPE){ 
        prompt = 'Find '+diff+' more ad descriptions for a '+INDUSTRY_TYPE+' industry campaign under 90 characters that are similar to these:\n';
      }
      
      autoDescriptions = generateTextOpenAI(prompt, descriptionsText);
    }
    
    if(autoDescriptions.length) {
      descriptionsText = descriptionsText.concat(autoDescriptions);
      for(var i = 0; i < autoDescriptions.length; i++) {
        bgRow.push('#d9ead3');
      }
    }
    
    while(descriptionsText.length < 4) {
      descriptionsText.push('');
      bgRow.push('#ffffff');
    }
    
    while(descriptionsText.length > 4) {
      descriptionsText.pop();
      bgRow.pop();
    }
    
    out = out.concat(headlinesText).concat(descriptionsText);
    
    backgrounds.push(bgRow);
    output.push(out);
    
    if(output.length > MAX_ADS) {
      break;
    }
  }
  
  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).setBackgrounds(backgrounds).setFontFamily('Calibri');
  
  if(EMAIL && SEND_EMAIL) {
    MailApp.sendEmail(EMAIL, accName + ' RSA Report is ready', 'Report is available at below link:\n'+SS_URL);
  }
 }

 function getGoogleAdsFormattedDate(d, format){
  var date = new Date();
  date.setDate(date.getDate() - d);
  return Utilities.formatDate(date,AdsApp.currentAccount().getTimeZone(),format);
 }

 function generateTextOpenAI(question, texts) {
  
  //texts.pop();
  var prompt = question + texts.join('\n');
  
  var messages= [
    {"role": "user", "content": prompt}
  ];
  
  var payload = {
    "model": GPT_MODEL,
    "messages": messages
  };
  
  var httpOptions = {
    "method" : "POST",
    "muteHttpExceptions": true,
    "contentType": "application/json",
    "headers" : {
      "Authorization" : 'Bearer ' + OPEN_AI_API_KEY  
    },
    'payload': JSON.stringify(payload)
  };
  
  //Logger.log(JSON.stringify(payload));
  var response = JSON.parse(UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', httpOptions));
  var choices = response['choices'];
  
  var texts = [];
  if(choices && choices[0] && choices[0]['message']) {
    var output = choices[0]['message']['content'].split('\n');
    for(var z in output) {
      if(!output[z].trim()) { continue; }
      var parts = output[z].split('. ');
      if(parts.length > 1) {
        parts.shift();
      }
      texts.push(parts.join('. '));
    }
  } else {
    //Logger.log('No results found!')
    Logger.log(response);
  }
  
  return texts;
 }
	/******************************************
	* RSA Report
	* @version: 3.0
	* @authors: Naman Jindal (Optmyzr), Frederick Vallaeys (Optmyzr)
	* -------------------------------
	* Install this script in your Google Ads account (not an MCC account)
	* to generate a Google Sheet with a list of all your responsive search ads
	* and their headlines and descriptions.
	* For RSAs that are not using the maximum number of allowed variations,
	* this script will suggest new variations for headlines and descriptions
	* using the OpenAI GPT API.
	* The resulting sheet can be bulk uploaded back into Google Ads.
	* --------------------------------
	* For more PPC tools, visit 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 = '';

	// Flag to decide if the script checks only ads in active campaigns and active ad groups
	var INCLUDE_PAUSED = true;

	// Script will only process this many or fewer ads to run within ChatGPT API limitations
	var MAX_ADS = 100;

	// Language code for output. Example US-EN or UK-EN, or FR-FR or CA-FR
	var LANGUAGE_CODE = 'US-EN';

	// Use this to mention the type of industry your campaigns belongs to, to get more relevant results from ChatGPT API
	var INDUSTRY_TYPE = '';

	// Use this to filter on specific campaigns by label. Case Sensitive.
	var CAMPAIGN_LABEL_IN = [''];

	// Use this to filter on specific adgroups by label. Case Sensitive
	var ADGROUP_LABEL_IN = [''];

	// Use this to filter on specific campaigns only. Case insensitive
	var CAMPAIGN_NAME_CONTAINS = '';

	// 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 = false;

	var OPEN_AI_API_KEY = ''

	var GPT_MODEL = 'gpt-3.5-turbo';


	// Do not edit anything below this line
	function main() {

	var output = [[
	'Account ID', 'Account Name', 'Campaign', 'Ad Group', 'Ad ID', '# Headlines', '# 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 columCount = output[0].length;
	var backgroupHeader = [];
	while(backgroupHeader.length < columCount) {
	backgroupHeader.push('#ffffff');
	}

	var backgrounds = [backgroupHeader];

	var accId = AdsApp.currentAccount().getCustomerId(),
	customerId = AdsApp.currentAccount().getCustomerId().replace(/-/g, ''),
	accName = AdsApp.currentAccount().getName();

	//var CAMPAIGN_IDS = [];
	var CAMPAIGN_LABELS_IN = [];
	if(CAMPAIGN_LABEL_IN.length) {
	var iter = AdsApp.labels().withCondition('label.name IN ("' + CAMPAIGN_LABEL_IN.join('","') + '")').get();
	while(iter.hasNext()) {
	var label = iter.next();
	CAMPAIGN_LABELS_IN.push('/customers/'+customerId+'/labels/'+label.getId());
	}
	}

	var ADGROUP_LABELS_IN = [];
	if(ADGROUP_LABEL_IN.length) {
	var iter = AdsApp.labels().withCondition('label.name IN ("' + ADGROUP_LABEL_IN.join('","') + '")').get();
	while(iter.hasNext()) {
	var label = iter.next();
	ADGROUP_LABELS_IN.push('/customers/'+customerId+'/labels/'+label.getId());
	}
	}

	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',
	CAMPAIGN_NAME_CONTAINS ? 'AND campaign.name REGEXP_MATCH "(?i).'+CAMPAIGN_NAME_CONTAINS+'."' : '',
	INCLUDE_PAUSED ? '' : 'AND ad_group_ad.status = ENABLED AND campaign.status = ENABLED and ad_group.status = ENABLED',
	CAMPAIGN_LABELS_IN.length ? 'AND campaign.labels CONTAINS ANY ("'+CAMPAIGN_LABELS_IN.join('","')+'")' : '',
	ADGROUP_LABELS_IN.length ? 'AND ad_group.labels CONTAINS ANY ("'+ADGROUP_LABELS_IN.join('","')+'")' : '',
	'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 bgRow = ['#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff'];

	var headlinesText = [];
	for(var z in headlines) {
	headlinesText.push(headlines[z].text);
	bgRow.push('#ffffff');
	}

	var diff = 15 - headlinesText.length;

	var autoHeadlines = [];
	if(diff > 0) {
	var prompt = 'Find '+diff+' more ad headlines under 30 characters that are similar to these:\n';
	if(LANGUAGE_CODE && INDUSTRY_TYPE){
	prompt = 'Find '+diff+' more ad headlines, in ' + LANGUAGE_CODE +' language code, for a '+INDUSTRY_TYPE+' industry campaign under 30 characters that are similar to these:\n';
	} else if(LANGUAGE_CODE){
	prompt = 'Find '+diff+' more ad headlines, in ' + LANGUAGE_CODE +' language code, under 30 characters that are similar to these:\n';
	} else if(INDUSTRY_TYPE){
	prompt = 'Find '+diff+' more ad headlines for a '+INDUSTRY_TYPE+' industry campaign under 30 characters that are similar to these:\n';
	}

	autoHeadlines = generateTextOpenAI(prompt, headlinesText);
	}

	if(autoHeadlines.length) {
	headlinesText = headlinesText.concat(autoHeadlines);
	for(var i = 0; i < autoHeadlines.length; i++) {
	bgRow.push('#d9ead3');
	}
	}

	while(headlinesText.length < 15) {
	headlinesText.push('');
	bgRow.push('#fffff')
	}

	while(headlinesText.length > 15) {
	headlinesText.pop();
	bgRow.pop();
	}

	var descriptionsText = [];
	for(var z in descriptions) {
	descriptionsText.push(descriptions[z].text);
	bgRow.push('#ffffff');
	}

	var diff = 4 - descriptions.length;
	var autoDescriptions = [];
	if(diff > 0) {
	var prompt = 'Find '+diff+' more ad descriptions under 90 characters that are similar to these:\n';

	if(LANGUAGE_CODE && INDUSTRY_TYPE){
	prompt = 'Find '+diff+' more ad descriptions, in ' + LANGUAGE_CODE +' language code, for a '+INDUSTRY_TYPE+' industry campaign under 90 characters that are similar to these:\n';
	} else if(LANGUAGE_CODE){
	prompt = 'Find '+diff+' more ad descriptions, in ' + LANGUAGE_CODE +' language code, under 90 characters that are similar to these:\n';
	} else if(INDUSTRY_TYPE){
	prompt = 'Find '+diff+' more ad descriptions for a '+INDUSTRY_TYPE+' industry campaign under 90 characters that are similar to these:\n';
	}

	autoDescriptions = generateTextOpenAI(prompt, descriptionsText);
	}

	if(autoDescriptions.length) {
	descriptionsText = descriptionsText.concat(autoDescriptions);
	for(var i = 0; i < autoDescriptions.length; i++) {
	bgRow.push('#d9ead3');
	}
	}

	while(descriptionsText.length < 4) {
	descriptionsText.push('');
	bgRow.push('#ffffff');
	}

	while(descriptionsText.length > 4) {
	descriptionsText.pop();
	bgRow.pop();
	}

	out = out.concat(headlinesText).concat(descriptionsText);

	backgrounds.push(bgRow);
	output.push(out);

	if(output.length > MAX_ADS) {
	break;
	}
	}

	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).setBackgrounds(backgrounds).setFontFamily('Calibri');

	if(EMAIL && SEND_EMAIL) {
	MailApp.sendEmail(EMAIL, accName + ' RSA Report is ready', 'Report is available at below link:\n'+SS_URL);
	}
	}

	function getGoogleAdsFormattedDate(d, format){
	var date = new Date();
	date.setDate(date.getDate() - d);
	return Utilities.formatDate(date,AdsApp.currentAccount().getTimeZone(),format);
	}

	function generateTextOpenAI(question, texts) {

	//texts.pop();
	var prompt = question + texts.join('\n');

	var messages= [
	{"role": "user", "content": prompt}
	];

	var payload = {
	"model": GPT_MODEL,
	"messages": messages
	};

	var httpOptions = {
	"method" : "POST",
	"muteHttpExceptions": true,
	"contentType": "application/json",
	"headers" : {
	"Authorization" : 'Bearer ' + OPEN_AI_API_KEY
	},
	'payload': JSON.stringify(payload)
	};

	//Logger.log(JSON.stringify(payload));
	var response = JSON.parse(UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', httpOptions));
	var choices = response['choices'];

	var texts = [];
	if(choices && choices[0] && choices[0]['message']) {
	var output = choices[0]['message']['content'].split('\n');
	for(var z in output) {
	if(!output[z].trim()) { continue; }
	var parts = output[z].split('. ');
	if(parts.length > 1) {
	parts.shift();
	}
	texts.push(parts.join('. '));
	}
	} else {
	//Logger.log('No results found!')
	Logger.log(response);
	}

	return texts;
	}
No results found