The Close Variants Suppressor

What the Script Does:

1. Date Range: The script pulls search term data for the past 7, 14, 30, 90 or 180 days (change this in the settings tab)
2. Search term exclusion: if there are exact match (close variants) in your adgroups, the script will exclude them. This way you make sure to keep your exact match targeting tight. No other match types are touched.
3. Changes are only made if "Let Script exclude search terms?" is set to "Yes". By deafault this value is set to "No".

Once the script ran you will be able to see your spend and conversions per match type. Also it shows you the spend and conversions of the excluded search terms.

How to Set Up the Script:

1. Prepare the Google Sheet:
   • Copy this gsheet

1. Configure the Script in Google Ads:
   • Log in to your Google Ads account.
   • Navigate to Tools & Settings > Bulk actions > Scripts.
   • Click the "+" button to create a new script.
   • Copy and paste the script below into the script editor.
   • Replace "YOUR_SPREADSHEET_URL_HERE" in the script with the URL of the Google Sheet you prepared.
   • Save the script.
2. Authorize and Test the Script:
   • The first time you run the script, you will need to authorize it to access your Google Ads data and the Google Sheet.
   • Click the "Preview" button to run the script in test mode. This allows you to verify that the script works as expected without making any changes to your live data.
   • Check out the graphs to understand your match type performance better.
   • Have fun!

‍

var dataToWrite = [];
var spreadsheetURL = "YOUR_SPREADSHEET_URL";
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetURL);
var sheet = spreadsheet.getSheetByName("Search Term Data");
var settingsSheet = spreadsheet.getSheetByName("Script Settings");

function main() {
  clearSheetExceptHeadline(spreadsheetURL, "Search Term Data");

  if (spreadsheetURL.indexOf('docs.google.com') == -1) {
    Logger.log("Please make sure to provide a valid SpreadsheetURL");
    return;
  }

  var settingsRange = settingsSheet.getRange("D3:J11").getValues();
  var reportOnlyLabeledAdgroups = settingsRange[0][0].replace(/\s+/g, ''); // Value at D3
  var automateNegatives = settingsRange[5][0]; // Value at D8 (6 rows down from D3)

  var timeframe = settingsRange[1][0]; // Value at D4
  var reportingTimeframe = getDateRangeForTimeframe(timeframe);

  var matchTypeFilter = settingsRange.slice(4, 9).map(function(row) {
    return row[5] === true ? row[6] : null;
  }).filter(function(value) { return value !== null; });

  Logger.log('will exclude search terms for these match types: ');

  if (reportOnlyLabeledAdgroups == 'No') {
    Logger.log('The script is iterating over all adgroups. If the script takes too long to execute: ' +
               'Please consider applying the "Automate negatives" Label to Adgroups and change the script setting "Report / change only labeled Adgroups" in the Gsheet to "Yes" ');
  }

  var adGroupsIterator = AdsApp.adGroups().get();
  while (adGroupsIterator.hasNext()) {
    var adGroup = adGroupsIterator.next();
    if (hasLabel(adGroup, "Automate negatives") || reportOnlyLabeledAdgroups == 'No') {
      var adGroupId = adGroup.getId();
      var adGroupCostPerConversion = calculateCostPerConversion(adGroupId, reportingTimeframe);
      processAdGroup(adGroup, automateNegatives, sheet, reportingTimeframe, adGroupCostPerConversion, matchTypeFilter);
    }
  }

  if (dataToWrite.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, dataToWrite.length, dataToWrite[0].length).setValues(dataToWrite);
  } else {
    Logger.log("No data to log. Please make sure at least one adgroup contains the label 'Automate negatives' Or you change the script setting to all adgroups (Gsheet settings tabs)");
  }
  
  //copy and paste search term data
  copySearchTermData(spreadsheetURL);
}

function processAdGroup(adGroup, automateNegatives, sheet, reportingTimeframe, adGroupCostPerConversion, matchTypeFilter) {
  var adGroupId = adGroup.getId();
  var adGroupName = adGroup.getName();
  var campaignName = adGroup.getBaseCampaign().getName();
  var adGroupKeywords = [];
  var keywordsIterator = adGroup.keywords().get();
  while (keywordsIterator.hasNext()) {
    var keyword = keywordsIterator.next();
    adGroupKeywords.push(keyword.getText());
  }

  var searchTermsQuery = "SELECT Query, KeywordTextMatchingQuery, Cost, Clicks, Conversions, ConversionValue, QueryMatchTypeWithVariant " +
  "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
  "WHERE AdGroupId = " + adGroupId +
  " AND Clicks > 0 " +
  "DURING " + reportingTimeframe +
  " ORDER BY Clicks DESC";

  var searchTermsIterator = AdsApp.report(searchTermsQuery).rows();
  while (searchTermsIterator.hasNext()) {
    var searchTerm = searchTermsIterator.next();
    logSearchTerm(searchTerm, campaignName, adGroupName, adGroupKeywords, automateNegatives, sheet, adGroupCostPerConversion, matchTypeFilter, adGroup);
  }
}

function logSearchTerm(searchTerm, campaignName, adGroupName, adGroupKeywords, automateNegatives, sheet, adGroupCostPerConversion, matchTypeFilter, adGroup) {
  var searchTermText = searchTerm["Query"].trim();
  var matchTypeVariant = searchTerm["QueryMatchTypeWithVariant"];
  var matchTypeCostPerConversion = 0;
  if (searchTerm["Conversions"] > 0) {
    matchTypeCostPerConversion = searchTerm["Cost"] / searchTerm["Conversions"];
    matchTypeCostPerConversion = matchTypeCostPerConversion.toFixed(2);
  }
  var matchTypeROAS = 0;
  if (searchTerm["Conversions"] > 0) {
    matchTypeROAS = searchTerm["ConversionValue"] / searchTerm["Cost"];
    matchTypeROAS = matchTypeROAS.toFixed(2);
  }

  var account = AdsApp.currentAccount();
  var accountId = account.getCustomerId();
  var accountName = account.getName();

  if (matchTypeFilter.includes(matchTypeVariant)) {
    if (automateNegatives == "Yes") {
      adGroup.createNegativeKeyword("[" + searchTermText + "]");
      Logger.log("Excluded search term in ad group '" + adGroupName + "': " + searchTermText);
    }

    var rowData = [new Date(), accountId, accountName, campaignName, adGroupName, adGroupCostPerConversion, searchTerm["KeywordTextMatchingQuery"], "[" + searchTermText + "]", searchTerm["Cost"], searchTerm["Clicks"], searchTerm["Conversions"], searchTerm["ConversionValue"], matchTypeVariant, "Search Term Excluded", matchTypeCostPerConversion, matchTypeROAS];
    dataToWrite.push(rowData);

  } else {
    var rowData = [new Date(), accountId, accountName, campaignName, adGroupName, adGroupCostPerConversion, searchTerm["KeywordTextMatchingQuery"], "[" + searchTermText + "]", searchTerm["Cost"], searchTerm["Clicks"], searchTerm["Conversions"], searchTerm["ConversionValue"], matchTypeVariant, "Search Term Not Excluded", matchTypeCostPerConversion, matchTypeROAS];
    dataToWrite.push(rowData);
  }
}

function hasLabel(adGroup, labelName) {
  var labels = adGroup.labels().get();
  var lowerCaseLabelName = labelName.toLowerCase();
  while (labels.hasNext()) {
    var label = labels.next();
    if (label.getName().toLowerCase() === lowerCaseLabelName) {
      return true;
    }
  }
  return false;
}

function clearSheetExceptHeadline(spreadsheetURL, sheetName) {
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetURL);
  var sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    Logger.log("Sheet not found: " + sheetName);
    return;
  }

  var filter = sheet.getFilter();
  if (filter) {
    filter.remove();
  }

  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  if (lastRow > 1) {
    sheet.getRange(2, 1, lastRow - 1, lastColumn).clearContent();
    var rowsAfterClear = sheet.getLastRow();
    if (rowsAfterClear > 1) {
      sheet.deleteRows(2, rowsAfterClear - 1);
    }
  } else {
    Logger.log("No data to clear below the headline row in sheet: " + sheetName);
  }
}

function getDateRangeForTimeframe(timeframe) {
  var today = new Date();
  var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate() - 1);
  var startDate = new Date();
  switch (timeframe) {
    case 'LAST_7_DAYS':
      startDate.setDate(endDate.getDate() - 7);
      break;
    case 'LAST_14_DAYS':
      startDate.setDate(endDate.getDate() - 14);
      break;
    case 'LAST_30_DAYS':
      startDate.setDate(endDate.getDate() - 30);
      break;
    case 'LAST_90_DAYS':
      startDate.setDate(endDate.getDate() - 90);
      break;
    case 'LAST_180_DAYS':
      startDate.setDate(endDate.getDate() - 180);
      break;
    default:
      throw new Error('Invalid timeframe specified');
  }

  function formatAsYYYYMMDD(date) {
    var dd = date.getDate();
    var mm = date.getMonth() + 1;
    var yyyy = date.getFullYear();
    if (dd < 10) dd = '0' + dd;
    if (mm < 10) mm = '0' + mm;
    return '' + yyyy + mm + dd;
  }

  var startDateStr = formatAsYYYYMMDD(startDate);
  var endDateStr = formatAsYYYYMMDD(endDate);
  return startDateStr + ',' + endDateStr;
}

function calculateCostPerConversion(adGroupId, reportingTimeframe) {
  var dates = reportingTimeframe.split(',');
  var startDate = dates[0];
  var endDate = dates[1];

  var query = `SELECT ad_group.id, metrics.cost_micros, metrics.conversions, metrics.cost_per_conversion 
               FROM ad_group
               WHERE ad_group.id = ${adGroupId}
               AND segments.date BETWEEN '${startDate}' AND '${endDate}'`;

  var rows = AdsApp.search(query);
  var totalCostMicros = 0;
  var totalConversions = 0;
  var rowCount = 0;

  if (!rows.hasNext()) {
    Logger.log('No data found for the specified AdGroup ID and timeframe.');
    return;
  }

  while (rows.hasNext()) {
    var row = rows.next();
    rowCount++;
    var costMicros = parseFloat(row.metrics.costMicros);
    var conversions = parseFloat(row.metrics.conversions);

    if (!isNaN(costMicros) && !isNaN(conversions)) {
      totalCostMicros += costMicros;
      totalConversions += conversions;
    }
  }

  if (totalConversions > 0) {
    var totalCost = totalCostMicros / 1000000;
    var costPerConversion = totalCost / totalConversions;
    return costPerConversion.toFixed(2);
  } else {
    return 0;
  }
}

function copySearchTermData(spreadsheetUrl) {
  // Open the spreadsheet using the URL
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  
  // Get the "Search Term Data" sheet
  var searchTermDataSheet = spreadsheet.getSheetByName('Search Term Data');
  
  // Get the "Negative Keyword Deepdive" sheet
  var negativeKeywordDeepdiveSheet = spreadsheet.getSheetByName('Negative Keywords Deepdive');
  
  if (!searchTermDataSheet || !negativeKeywordDeepdiveSheet) {
    throw new Error('One or both of the sheets not found.');
  }
  
  // Get the range of data in "Search Term Data" sheet
  var dataRange = searchTermDataSheet.getDataRange();
  var data = dataRange.getValues();
  
  // Determine the starting row for pasting data in "Negative Keyword Deepdive" sheet
  var startRow = 6;
  
  // Clear the existing content in the "Negative Keyword Deepdive" sheet
  var lastRow = sheet.getMaxRows();
  var lastColumn = sheet.getMaxColumns();
  var range = negativeKeywordDeepdiveSheet.getRange(7, 1, lastRow - 6, lastColumn);
  range.clearContent();
  
  // Set the range for pasting the data
  var pasteRange = negativeKeywordDeepdiveSheet.getRange(startRow, 1, data.length, data[0].length);
  
  // Paste the data into the specified range
  pasteRange.setValues(data);
  
  var range = sheet.getDataRange();
}