Performance Max Non-Converting Search Term Alerts

Want to know where PMax campaigns are wasting your budget?

* 
* 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 This email address is being protected from spambots. You need JavaScript enabled to view it. 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(
        {
          searchTerm…