Google Ads - New search terms script

Update: 24.07.2024. Now also including a dashboard for PMAX data.

What the Script Does:

Pulls search term data from the past 30 days that had not cost induced in the preceding 180 days.

So you always stay on top of new search terms in your account.

How to Set Up the Script:

  1. Copy this gsheet:
  1. Configure the Script in Google Ads:
    • Log in to your Google Ads account.
    • 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 created.
    • Preview the script for it to pull account data into the reporting sheet
    • Schedule the script to run as often as you need it
function main() {
  // Edit your settings here. and put your gsheet url
  // copy this sheet: https://docs.google.com/spreadsheets/d/1Fi8_4bLm1AJPtijdXaIfh_WY5abfGgCHME8JpyFUUkQ/copy
  var sheetUrl = 'your_sheet_url_here'; // Replace with your Google Sheet URL
  var sheetId = extractSheetIdFromUrl(sheetUrl);
  var n = 1; // Set your desired N-gram value (e.g., 2 for bi-grams)
  
  // Fetch settings from the "Settings" tab
  var settingsSheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName("Settings");
  var clickThreshold = settingsSheet.getRange("C3").getValue();
  var pmaxEnabled = settingsSheet.getRange("C5").getValue().toString().toLowerCase() === 'true';
  
  // no changes after here
  var last30Days = 'LAST_30_DAYS';
  var preceding180Days = getPreceding180DaysRange();

  // Fetch data for the last 30 days
  var last30DaysData = fetchSearchTermsData(last30Days, clickThreshold);

  // Fetch data for the preceding 180 days
  var preceding180DaysData = fetchSearchTermsData(preceding180Days, clickThreshold);

  // Get unique search terms from the preceding 180 days
  var preceding180DaysQueries = new Set(preceding180DaysData.map(function(row) {
    return row[2]; // Query is the first element in each row
  }));

  // Filter out the search terms that occurred in the preceding 180 days
  var uniqueLast30DaysData = last30DaysData.filter(function(row) {
    return !preceding180DaysQueries.has(row[2]);
  });

  // Collect all data for writing
  var allData = {
    'Last 30 Days Data': addMetrics(last30DaysData),
    'Preceding 180 Days Data': addMetrics(preceding180DaysData),
    '30 Days Data (new Search terms)': addMetrics(uniqueLast30DaysData)
  };

  // Only fetch and process PMAX data if enabled
  if (pmaxEnabled) {
    // Fetch PMax data for the last 30 days and preceding 180 days
    var last30DaysPmaxData = fetchPmaxData("last_30_days", clickThreshold);
    var preceding180DaysPmaxData = fetchPmaxData("preceding_180_days", clickThreshold);

    // Get unique search terms from the preceding 180 days
    var preceding180DaysPmaxQueries = new Set(preceding180DaysPmaxData.map(function(row) {
      return row[3]; // Query is the first element in each row
    }));

    // Filter out the search terms that occurred in the preceding 180 days
    var uniqueLast30DaysPmaxData = last30DaysPmaxData.filter(function(row) {
      return !preceding180DaysPmaxQueries.has(row[3]);
    });

    // Add PMAX data to allData
    allData['Last 30 Days Data (Pmax)'] = addPmaxMetrics(last30DaysPmaxData);
    allData['Preceding 180 days data (Pmax)'] = addPmaxMetrics(preceding180DaysPmaxData);
    allData['30 Days Data (new Search terms - Pmax)'] = addPmaxMetrics(uniqueLast30DaysPmaxData);
  }

  writeAllDataToSheet(sheetId, allData);
  
  performNGramAnalysis(sheetId, 'N-Gram Data (new Search terms)', uniqueLast30DaysData, n);
  
  if (pmaxEnabled) {
    performNGramAnalysis(sheetId, 'N-Gram Data (new Search terms - Pmax)', uniqueLast30DaysPmaxData, n);
  }
}

function addMetrics(data) {
  return data.map(function(row) {
    var cost = row[4];
    var conversions = row[7];
    var conversionValue = row[8];
    var costPerConversion = conversions > 0 ? cost / conversions : 0;
    var conversionValuePerCost = cost > 0 ? conversionValue / cost : 0;
    return row.concat([costPerConversion, conversionValuePerCost]);
  });
}

function addPmaxMetrics(data) {
  return data.map(function(row) {
    var impressions = row[2];
    var clicks = row[3];
    var conversions = row[4];
    var conversionValue = row[5];
    var costPerConversion = conversions > 0 ? conversionValue / conversions : 0;
    var conversionValuePerCost = impressions > 0 ? conversionValue / impressions : 0;
    return row.concat([costPerConversion, conversionValuePerCost]);
  });
}

function extractSheetIdFromUrl(url) {
  var matches = url.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/);
  if (matches && matches[1]) {
    return matches[1];
  } else {
    throw new Error('Invalid Google Sheet URL');
  }
}

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();
    campaignIds.push(result.campaign.id);
  }  

  return campaignIds;
}

function getCampaignNameById(campaignId) {
  var gaqlQuery = "SELECT campaign.name FROM campaign WHERE campaign.id = '" + campaignId + "'";
  var results = AdsApp.search(gaqlQuery);
  
  if (results.hasNext()) {
    var result = results.next();
    return result.campaign.name;
  } else {
    console.log("No campaign with id: " + campaignId);
    return "";
  }
}

function fetchSearchTermsData(dateRange, clickThreshold) {
  var query = `
    SELECT 
      CampaignName,
      AdGroupName,
      Query, 
      QueryMatchTypeWithVariant, 
      Cost, 
      Impressions, 
      Clicks, 
      Conversions, 
      ConversionValue, 
      CostPerConversion 
    FROM 
      SEARCH_QUERY_PERFORMANCE_REPORT 
    WHERE 
      Impressions > 0 AND Clicks >= ${clickThreshold}
    DURING 
      ${dateRange}
  `;

  var report = AdsApp.report(query);
  var rows = report.rows();

  var data = [];
  while (rows.hasNext()) {
    var row = rows.next();
    data.push([
      row['CampaignName'],
      row['AdGroupName'],
      row['Query'],
      row['QueryMatchTypeWithVariant'],
      parseFloat(row['Cost']),
      parseInt(row['Impressions']),
      parseInt(row['Clicks']),
      parseFloat(row['Conversions']),
      parseFloat(row['ConversionValue']),
      parseFloat(row['CostPerConversion'])
    ]);
  }

  return data;
}

function fetchPmaxData(period, clickThreshold) {
  var campaignIds = getCampaignIds();
  var allPmaxData = [];

  for (var i = 0; i < campaignIds.length; i++) {
    var campaignId = campaignIds[i];
    var campaignName = getCampaignNameById(campaignId);
    var searchCategoryStats = getSearchCategorieStats(campaignId, period, clickThreshold);

    for (var category in searchCategoryStats) {
      for (var subCategory in searchCategoryStats[category]) {
        for (var searchTerm in searchCategoryStats[category][subCategory]) {
          var stats = searchCategoryStats[category][subCategory][searchTerm];
          allPmaxData.push([
            campaignName,
            category,
            subCategory,
            searchTerm,
            stats.impressions,
            stats.clicks,
            stats.conversions,
            stats.conversionValue
          ]);
        }
      }
    }
  }

  return allPmaxData;
}

function getSearchCategorieStats(campaignId, period, clickThreshold) {
  var searchCategoryObject = {};
  var periodString;
  
  switch(period) {
    case "last_30_days":
      periodString = " BETWEEN '" + dates(30) + "' AND '" + dates(1) + "'";
      break;
    case "preceding_180_days":
      periodString = " BETWEEN '" + dates(210) + "' AND '" + dates(31) + "'";
      break;
    default:
      Logger.log("### ERROR: Could not recognize the period");
      return searchCategoryObject;
  }

  // First, get all campaign_search_term_insight.id for this campaign
  var idQuery = "SELECT campaign_search_term_insight.id " +
                "FROM campaign_search_term_insight " +
                "WHERE campaign_search_term_insight.campaign_id = " + campaignId +
                " AND segments.date " + periodString;

  var idReport = AdsApp.search(idQuery);
  
  while (idReport.hasNext()) {
    var idResult = idReport.next();
    var insightId = idResult.campaignSearchTermInsight.id;

    // For each insight ID, get the search terms and metrics
    var termQuery = "SELECT campaign_search_term_insight.category_label, " +
                    "segments.search_subcategory, " +
                    "segments.search_term, " +
                    "metrics.impressions, metrics.clicks, metrics.conversions, metrics.conversions_value " +
                    "FROM campaign_search_term_insight " +
                    "WHERE campaign_search_term_insight.id = '" + insightId + "'" +
                    " AND campaign_search_term_insight.campaign_id = '" + campaignId + "'" +
                    " AND segments.date " + periodString;

    var termReport = AdsApp.search(termQuery);
    
    while (termReport.hasNext()) {
      var termResult = termReport.next();
      var category = termResult.campaignSearchTermInsight.categoryLabel;
      var subCategory = termResult.segments.search_subcategory;
      var searchTerm = termResult.segments.searchTerm;
      var impressions = parseInt(termResult.metrics.impressions);
      var clicks = parseInt(termResult.metrics.clicks);
      var conversions = parseFloat(termResult.metrics.conversions);
      var conversionValue = parseFloat(termResult.metrics.conversionsValue);
      
      // Apply click threshold filter in memory
      if (clicks >= clickThreshold) {
        if (!searchCategoryObject[category]) {
          searchCategoryObject[category] = {};
        }
        if (!searchCategoryObject[category][subCategory]) {
          searchCategoryObject[category][subCategory] = {};
        }
        if (!searchCategoryObject[category][subCategory][searchTerm]) {
          searchCategoryObject[category][subCategory][searchTerm] = {
            impressions: 0,
            clicks: 0,
            conversions: 0,
            conversionValue: 0
          };
        }

        searchCategoryObject[category][subCategory][searchTerm].impressions += impressions;
        searchCategoryObject[category][subCategory][searchTerm].clicks += clicks;
        searchCategoryObject[category][subCategory][searchTerm].conversions += conversions;
        searchCategoryObject[category][subCategory][searchTerm].conversionValue += conversionValue;
      }
    }
  }
  
  return searchCategoryObject;
}

function getPreceding180DaysRange() {
  var today = new Date();
  var startDate = new Date(today.getFullYear(), today.getMonth(), today.getDate() - 210);
  var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate() - 31);

  var start = Utilities.formatDate(startDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  var end = Utilities.formatDate(endDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

  return `${start},${end}`;
}

function dates(x){ 
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var now = new Date();
  var date = new Date(now.getTime() - x * MILLIS_PER_DAY);
  var timeZone = AdsApp.currentAccount().getTimeZone();
  var output = Utilities.formatDate(date, timeZone, 'yyyy-MM-dd');
  return output;
}

function writeAllDataToSheet(sheetId, allData) {
  try {
    var spreadsheet = SpreadsheetApp.openById(sheetId);

    for (var sheetName in allData) {
      var data = allData[sheetName];
      var sheet = getOrCreateSheet(spreadsheet, sheetName);
      sheet.clear();

      var headers;
      if (sheetName.includes('Pmax')) {
        headers = ['Campaign', 'Search Category', 'Search Subcategory', 'Search term', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value'];
      } else {
        headers = ['Campaign Name', 'Ad Group Name', 'Search Term', 'Match Type', 'Cost', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value', 'Cost / Conversion', 'Conv. Value / Cost'];
      }

      var values = [headers];
      
      for (var i = 0; i < data.length; i++) {
        var row = data[i];
        if (row.length < headers.length) {
          // If the row has fewer columns than headers, pad with empty strings
          while (row.length < headers.length) {
            row.push("");
          }
        } else if (row.length > headers.length) {
          // If the row has more columns than headers, truncate
          row = row.slice(0, headers.length);
        }
        values.push(row);
        
        // Add logging for Pmax data
        if (sheetName.includes('Pmax')) {
          //Logger.log('Pmax row ' + i + ': ' + JSON.stringify(row));
        }
      }

      sheet.getRange(1, 1, values.length, headers.length).setValues(values);
    }
  } catch (error) {
    Logger.log('Error writing to sheet: ' + error.message);
  }
}

function getOrCreateSheet(spreadsheet, sheetName) {
  var sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }
  return sheet;
}

function performNGramAnalysis(sheetId, sheetName, data, n) {
  Logger.log('Performing N-Gram analysis for sheet: ' + sheetName);
  Logger.log('Data length: ' + (data ? data.length : 'undefined'));
  
  var spreadsheet = SpreadsheetApp.openById(sheetId);
  var sheet = getOrCreateSheet(spreadsheet, sheetName);
  sheet.clear();

  // Determine the header based on the sheet name
  var headers;
  if (sheetName.includes('Pmax')) {
    headers = ['N-Gram', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value'];
  } else {
    headers = ['N-Gram', 'Cost', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value', 'Cost / Conv'];
  }

  // Write the header row
  sheet.appendRow(headers);

  // Perform the N-Gram analysis
  if (sheetName.includes('Pmax')) {
    performPmaxNGramAnalysis(sheet, data, n);
  } else {
    performRegularNGramAnalysis(sheet, data, n);
  }
}

function performRegularNGramAnalysis(sheet, data, n) {
  // Build a dictionary of N-Grams and their associated metrics
  var nGramData = {};
  for (var i = 0; i < data.length; i++) {
    var query = data[i][2];
    var impressions = data[i][5];
    var clicks = data[i][6];
    var conversions = data[i][7];
    var conversionValue = data[i][8];
    var cost = data[i][4];

    // Split query into words
    var words = query.split(/\s+/);

    // Generate N-grams
    for (var j = 0; j <= words.length - n; j++) {
      var nGram = words.slice(j, j + n).join(' ');

      if (!nGramData[nGram]) {
        nGramData[nGram] = { impressions: 0, clicks: 0, conversions: 0, conversionValue: 0, cost: 0, costPerConversion: 0 };
      }

      nGramData[nGram].impressions += impressions;
      nGramData[nGram].clicks += clicks;
      nGramData[nGram].conversions += conversions;
      nGramData[nGram].conversionValue += conversionValue;
      nGramData[nGram].cost += cost;
      nGramData[nGram].costPerConversion = nGramData[nGram].conversions ? (nGramData[nGram].cost / nGramData[nGram].conversions) : 0;
    }
  }

  // Write N-gram data to sheet
  var values = [];
  for (var nGram in nGramData) {
    var record = nGramData[nGram];
    values.push([nGram, record.cost, record.impressions, record.clicks, record.conversions, record.conversionValue, record.costPerConversion]);
  }
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

function performPmaxNGramAnalysis(sheet, data, n) {
  // Add error checking
  if (!data || !Array.isArray(data) || data.length === 0) {
    Logger.log('Error: Invalid or empty data passed to performPmaxNGramAnalysis');
    return;
  }

  // Build a dictionary of N-Grams and their associated metrics
  var nGramData = {};
  for (var i = 0; i < data.length; i++) {
    // Add error checking for each row
    if (!data[i] || !Array.isArray(data[i]) || data[i].length < 6) {
      Logger.log('Error: Invalid data row at index ' + i + ' in performPmaxNGramAnalysis');
      continue;
    }

    var query = data[i][3];
    var impressions = data[i][4];
    var clicks = data[i][5];
    var conversions = data[i][6];
    var conversionValue = data[i][7];

    // Check if query is a string
    if (typeof query !== 'string') {
      Logger.log('Error: Invalid query at index ' + i + ' in performPmaxNGramAnalysis');
      continue;
    }

    // Split query into words
    var words = query.split(/\s+/);

    // Generate N-grams
    for (var j = 0; j <= words.length - n; j++) {
      var nGram = words.slice(j, j + n).join(' ');

      if (!nGramData[nGram]) {
        nGramData[nGram] = { impressions: 0, clicks: 0, conversions: 0, conversionValue: 0 };
      }

      nGramData[nGram].impressions += impressions;
      nGramData[nGram].clicks += clicks;
      nGramData[nGram].conversions += conversions;
      nGramData[nGram].conversionValue += conversionValue;
    }
  }

  // Write N-gram data to sheet
  var values = [];
  for (var nGram in nGramData) {
    var record = nGramData[nGram];
    values.push([nGram, record.impressions, record.clicks, record.conversions, record.conversionValue]);
  }

  // Add error checking before writing to sheet
  if (values.length > 0) {
    sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
  } else {
    Logger.log('Warning: No N-gram data to write to sheet in performPmaxNGramAnalysis');
  }
}