Alerts for Non-Converting Search Terms in Performance Max

/**
*
• PMax Non-Converting Search Term Alerts
*
• Happy Scripting
•
• INSTRUCTIONS:
•
• If you are new to scripts -> follow these instructions on how to copy-paste and install Google Ads Scripts:
• https://nilsrooijmans.com/how-to-set-up-and-run-google-ads-scripts/
•
• Next:
• 1. Create a new Google Sheet (tip for chrome users: simply type 'sheets.new' in the address bar)
• 2. Add the complete URL of the spreadsheet to the SPREADSHEET_URL below (line 35)
• 3. Add your email address to the script (line 36)
• 4. Add the name of your Google Ads account to the subject of emails (line 41)
• 5. Set the values for LOOKBACK_WINDOW, MIN_CLICKS and CONVERSION_THRESHOLD variables (lines 43,44,45)
• 6. Authorize and Preview
• 7. Schedule to run weekly (I prefer Mondays at 6AM)
•
• Version 0.9
•
• TODO's:
• - log gaql errors in sheet
• - add ignore list in sheet for search terms to ignore
• - add option to clear sheet after each run (right now all new alerts will be added on top of sheet)
*

*/
/*** [REQUIRED] ADD YOUR SETTINGS HERE ***/
var SPREADSHEET_URL = ""; // insert a new blank spreadsheet url between the quotes, be sure to add the complete url of the spreadsheet
var EMAIL_ADDRESSES = ""; // insert email addresses of people that want to get the alerts between the quotes, seperate multipe email addresses by a comma
/*** [OPTIONAL] YOU MIGHT WANT TO CHANGE SOME CONFIGURATIONS HERE ***/
var EMAIL_SUBJECT = "[GAds Script][account name] - PMax Alert - You've got {nr_of_alerts} Non-Converting Search Terms"; // subject of emails, you might want to include your account name here. Don't replace the {nr_of_alerts} part.
var LOOKBACK_WINDOW = 90; // number of days before today, for which search term data is analysed
var MIN_CLICKS = 100; // ignore search terms with less clicks during the lookback window
var CONVERSION_THRESHOLD = 0.5; // alert when search term has had less conversions than the threshold
/*** DO NOT CHANGE ANYTHING BELOW THIS LINE ***/
function main() {
 console.log(`Let's get rolling...`);
 var sheet = prepareOutputSheet();
  var campaignIds = getCampaignIds();   
 if (campaignIds.length == 0) {
   console.log(`The account currently has zero Performance Max campaigns that are enabled. We're done here.`);
   return;
 }
 console.log(`The account currently has ${campaignIds.length} Performance Max campaigns that are enabled`);
  var startDate = getDate(LOOKBACK_WINDOW);
 var endDate = getDate(1);
 var searchTermAlerts = processCampaigns(campaignIds, startDate, endDate);
 if (searchTermAlerts.length == 0) {
   console.log(`The account has zero PMax search terms alerts. We're done here.`);
   return;
 } 
  var nrOfsearchTermAlerts = searchTermAlerts.length;
 console.log(`The account has ${nrOfsearchTermAlerts} search terms alerts for Performance Max campaigns that are enabled`); 
 addOutputToSheet(searchTermAlerts, sheet);
 sendEmail(nrOfsearchTermAlerts);
  console.log(`\nWe're done. Here's the report: ${SPREADSHEET_URL}`); 
}
function processCampaigns(campaignIds, startDate, endDate) {
  var searchTermAlerts = [];
  for (var i=0; i<campaignIds.length; i++) {
   var campaignId = campaignIds[i];
   var campaignSearchTermAlerts = getCampaignSearchTermAlerts(campaignId, startDate, endDate);
   if (campaignSearchTermAlerts.length == 0) {
     console.log(`Campaign id ${campaignId} has zero search terms alerts.`);
     continue;
   }   
   console.log(`Campaign id ${campaignId} has ${campaignSearchTermAlerts.length} search terms alerts.`);
  
   for (var j=0; j<campaignSearchTermAlerts.length; j++) {
     searchTermAlerts.push(campaignSearchTermAlerts[j]);
   }
 }
  return searchTermAlerts;
}
function getCampaignSearchTermAlerts(campaignId, startDate, endDate) {
 console.log(`\n--- Processing campaign id: ${campaignId} ---`);
  var searchTermAlerts = [];
 var date = new Date();
  var campaignSearchTermInsightCategories = getCampaignSearchTermInsightCategories(campaignId, startDate, endDate);
  for (var i=0; i<campaignSearchTermInsightCategories.length; i++) {
   var campaignSearchTermInsight = campaignSearchTermInsightCategories[i];
   var campaignSearchTermInsightTerms = getCampaignSearchTermInsightTerms(campaignSearchTermInsight, startDate, endDate);
   for (var j=0; j<campaignSearchTermInsightTerms.length; j++) {
     var campaignSearchTermInsightTerm = campaignSearchTermInsightTerms[j];
    
     if(isAlert(campaignSearchTermInsightTerm)) {
       searchTermAlerts.push(
         [
          date,
          campaignSearchTermInsight.campaignName,
          campaignSearchTermInsight.searchTermInsightCategory,
          campaignSearchTermInsightTerm.searchTerm,
          campaignSearchTermInsightTerm.impressions,
          campaignSearchTermInsightTerm.clicks,
          campaignSearchTermInsightTerm.conversions,
          campaignSearchTermInsightTerm.conversionsValue
         ]
       );
     }
   }
   var secondsRemaining = AdsApp.getExecutionInfo().getRemainingTime();
   //console.log(`*** We've got ${secondsRemaining} secs left on this run.`);
   if (secondsRemaining<180) {
     var timeOutWarning =
         `### This Google Ads script ran out of time and only had ${secondsRemaining} sec left to generate the report. We've quit fetching search term data and prepared the report with the alerts if there are any.\n`+
         `--> To process all search terms please consider upgrading to the paid version of this script, or increase MIN_CLICKS and decrease LOOKBACK_WINDOW in the settings of the script.`;
     console.log(timeOutWarning);
     var emailSubject = EMAIL_SUBJECT.replace(`You've got {nr_of_alerts} Non-Converting Search Terms` , `Non-Converting Search Terms Alert script ran out of time.`);
     emailSubject = emailSubject.replace(`[GAds Script]`,`[WARNING]`);
     MailApp.sendEmail(EMAIL_ADDRESSES, emailSubject, timeOutWarning);
     break;
   }
 }
     
 return searchTermAlerts;
  function isAlert(campaignSearchTermInsightTerm) {
   return (campaignSearchTermInsightTerm.clicks>MIN_CLICKS && campaignSearchTermInsightTerm.conversions<CONVERSION_THRESHOLD);
 }
 }
function getCampaignSearchTermInsightTerms(campaignSearchTermInsight, startDate, endDate) {
  var campaignSearchTermInsightTerms = [];
  
 var searchTermInsightCategoryId = campaignSearchTermInsight.searchTermInsightCategoryId;
 var campaignId = campaignSearchTermInsight.campaignId;
  var startTime = new Date().getTime();
  console.log(`  Fetching search term data for search term insight category : ${campaignSearchTermInsight.searchTermInsightCategory} at precisely ${startTime} `);     
 try {
   var gaqlQuery= `
     SELECT
       segments.search_subcategory,
       segments.search_term,
       campaign_search_term_insight.id,
       metrics.impressions,
       metrics.clicks, 
       metrics.conversions,
       metrics.conversions_value
     FROM
       campaign_search_term_insight
     WHERE
       segments.date BETWEEN ${startDate} AND ${endDate}
       AND campaign_search_term_insight.campaign_id = ${campaignId}
       AND campaign_search_term_insight.id = "${searchTermInsightCategoryId}"
   `;
   //console.log("gaqlQuery: "+gaqlQuery);
   var results = AdsApp.search(gaqlQuery);
   while (results.hasNext()) {
     var result = results.next();
     var searchSubcat = result.segments.searchSubcategory;
     var searchTerm = result.segments.searchTerm;
     var searchTermInsightCategoryId = result.campaignSearchTermInsight.id;
     var impressions = result.metrics.impressions;
     var clicks = result.metrics.clicks;
     var conversions = result.metrics.conversions;
     var conversionsValue = result.metrics.conversionsValue;
     campaignSearchTermInsightTerms.push(
       {
         searchTermInsightCategoryId: searchTermInsightCategoryId,
         searchTerm: searchTerm,
         impressions: impressions,
         clicks: clicks,
         conversions: conversions,
         conversionsValue: conversionsValue
       }
     );
   }
 } catch(e) {
   console.log(`### ERROR fetching search term data for campaign_search_term_insight ${campaignSearchTermInsight.searchTermInsightCategory} with id: ${searchTermInsightCategoryId}, error code = ${e}`);
 }
  var endTime = new Date().getTime();
 var duration = (endTime - startTime) / 1000;
 console.log(`  Finished fetching search term data for search term insight category : ${campaignSearchTermInsight.searchTermInsightCategory} at precisely ${endTime} --> it took ${duration} secs`);
 if(duration>60) {
   console.log(`### GODDAMN that last query took forever! ${duration} seconds !!! Let's hope the next one will go quicker.`);
   // TODO: log slow queries in sheets
 }
 return removeDuplicates(campaignSearchTermInsightTerms, searchTermInsightCategoryId, searchTerm);
}
function getCampaignSearchTermInsightCategories(campaignId, startDate, endDate) {
  var campaignSearchTermInsightCategories = [];
 console.log(`Fetching search term insight category data for campaign : ${campaignId}`);
  try {
   var gaqlQuery= `
     SELECT
       campaign.name,
       campaign.id,
       campaign_search_term_insight.category_label,
       campaign_search_term_insight.id,
       metrics.clicks
     FROM
       campaign_search_term_insight  
     WHERE
       segments.date BETWEEN ${startDate} AND ${endDate}
       AND campaign_search_term_insight.campaign_id = ${campaignId}
       AND metrics.clicks >= ${MIN_CLICKS}
   `;
   //console.log("gaqlQuery: "+gaqlQuery);
   var results = AdsApp.search(gaqlQuery);
   while (results.hasNext()) {
     var result = results.next();
     var campaignName = result.campaign.name;
     var searchTermInsightCategory = result.campaignSearchTermInsight.categoryLabel;
     var searchTermInsightCategoryId = result.campaignSearchTermInsight.id;
     var clicks = result.metrics.clicks;
     campaignSearchTermInsightCategories.push(
       {
         searchTermInsightCategoryId: searchTermInsightCategoryId,
         campaignName: campaignName,
         campaignId: campaignId,
         searchTermInsightCategory: searchTermInsightCategory,
         clicks: clicks
       }
     );
   }
 } catch(e) {
   console.log(`### ERROR fetching search term insight category data for campaign : ${campaignId}, error code = ${e}`);
 }
  return campaignSearchTermInsightCategories;
}
// returns an array with campaign ids of all enabled performance max campaigns
function getCampaignIds() {
  var campaignIds = [];
 var gaqlQuery = "SELECT campaign.id FROM campaign WHERE campaign.advertising_channel_type = 'PERFORMANCE_MAX' AND campaign.status = 'ENABLED'";
  var results = AdsApp.search(gaqlQuery);
  while (results.hasNext()) {
   var result = results.next();
   var campaignId = result.campaign.id;
   campaignIds.push(campaignId);
 } 
 return campaignIds;
}
function getDate(days) {
   return Utilities.formatDate(new Date(Date.now() - days * 86400000), AdsApp.currentAccount().getTimeZone(), "yyyyMMdd");
}
function removeDuplicates(arr, prop1, prop2) {
   const unique = new Map();
   arr.forEach((item) => {
       const uniqueKey = `${item[prop1]}_${item[prop2]}`; // Creating a unique key
       if (!unique.has(uniqueKey)) {
           unique.set(uniqueKey, item); // Store item if unique
       }
   });
   return Array.from(unique.values()); // Convert back to array
}
// prepare the report sheet
function prepareOutputSheet() {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
 if (!spreadsheet) {
   Logger.log("Cannot open new reporting spreadsheet") ;
   return ;
 }
 var sheet = spreadsheet.getActiveSheet();
 if (!sheet) {
   Logger.log("Cannot open new reporting sheet") ;
   return ;
 } 
 // set width of columns
 sheet.setColumnWidth(1, 100);
 sheet.setColumnWidth(2, 300);
 sheet.setColumnWidth(3, 300);
 sheet.setColumnWidth(4, 300);
 sheet.setColumnWidth(5, 100);
 sheet.setColumnWidth(6, 100);
 sheet.setColumnWidth(7, 100);
 sheet.setColumnWidth(8, 100);
  addHeaderToOutputSheet(sheet);
  return sheet;
}
// add header to sheet
function addHeaderToOutputSheet(sheet) {
  try {
   var headerSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1RGsjiIm2__JExPGbraDNshForuk-vv8RZW1xoC2mGVA/").getSheetByName('header_sheet');
 } catch(e) {
   console.log(`### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com\n${e}`);
   throw `### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com\n${e}`;
 }
  var headerRange = headerSheet.getRange(1, 1, 2, headerSheet.getLastColumn());
 var headerData = headerRange.getValues();
  console.log("Adding header to the output sheet");
 headerData[0][4] = `Current lookback window: ${LOOKBACK_WINDOW} DAYS`;
  var range=sheet.getRange(1,1,2,headerData[1].length);
 range.clear();
 range.clearFormat();
 range.setValues(headerData)
 range.setFontWeight("bold");
 range = sheet.getRange(1,1,1,1);
 range.setFontColor('#007BFF')
 sheet.setFrozenRows(2);
}
// add alerts to report sheet
function addOutputToSheet(output, sheet) {
  if (!(output.length > 0)) return; // nothing to add to sheet
 var numberOfRows=sheet.getLastRow() ;
  sheet.insertRowsBefore(3, output.length); // add empty rows below header row
 var startRow = 3;
  var range=sheet.getRange(startRow, 1, output.length, output[0].length) ;
 range.setValues(output) ;
  console.log("Number of rows added to output sheet: "+output.length+"\n\n");
 }
// sends the alert email
function sendEmail(number) {
 var emailBody =
     "\nNumber of non-converting search terms in your PMax campaigns: " + number + "\n" +
     "See details: "+ SPREADSHEET_URL+ "\n---\n\n"+
     "For more FREE Google Ads Scripts to improve your results and make your working day feel like a breeze, visit https://nilsrooijmans.com \n" +
     "---\n" +
     "This email is generated by a copy of the free Google Ads Script - PMax Non-Converting Search Term Alerts, (C) Nils Rooijmans \n" +
     "---\n";
    var emailSubject = EMAIL_SUBJECT.replace('{nr_of_alerts}' , number);
    MailApp.sendEmail(EMAIL_ADDRESSES, emailSubject, emailBody);
   Logger.log("Sending mail"); 
}