Performance Max Non-Converting Search Term Alerts

/**
*
* PMax Non-Converting Search Term Alerts
*
* @author: Nils Rooijmans (c)
* 
* 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)
*
* contact nils@nilsrooijmans.com for questions and the scalable paid version of the script with (semi)automatic negative keyword management

*/


/*** [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");  
}