Google Ads - New search terms script

Released: 12.06.2024.

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() {
  try {
    // Edit your settings here. and put your gsheet url
    var sheetUrl = 'YOUR_SPREADHSEET_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)
    
    // no changes after here
    var last30Days = 'LAST_30_DAYS';
    var preceding180Days = getPreceding180DaysRange();

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

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

    // Get unique search terms from the preceding 180 days
    var preceding180DaysQueries = new Set(preceding180DaysData.map(function(row) {
      return row[0]; // 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[0]);
    });

    // Log the filtered data
    //Logger.log('Unique Search Terms in Last 30 Days:');
    //logData(uniqueLast30DaysData);

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

    writeAllDataToSheet(sheetId, allData);
    performNGramAnalysis(sheetId, 'N-Gram Data (new Search terms)', uniqueLast30DaysData, n);
  } catch (error) {
    Logger.log('Error: ' + error.message);
  }
}

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

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

  var data = [];
  while (rows.hasNext()) {
    var row = rows.next();
    data.push([
      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 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 logData(data) {
  var headers = ['Search Term', 'Match Type', 'Cost', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value', 'Cost / Conversion'];
  Logger.log(headers.join(', '));
  for (var i = 0; i < data.length; i++) {
    Logger.log(data[i].join(', '));
  }
}

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 = ['Search Term', 'Match Type', 'Cost', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value', 'Cost / Conversion'];
      var values = [headers].concat(data);
      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 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 performNGramAnalysis(sheetId, sheetName, data, n) {
  var spreadsheet = SpreadsheetApp.openById(sheetId);
  var sheet = getOrCreateSheet(spreadsheet, sheetName);
  sheet.clear();

  // Add header
  sheet.appendRow(['N-Gram', 'Cost', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value', 'Cost / Conv']);

  var nGramData = {};

  for (var i = 0; i < data.length; i++) {
    var query = data[i][0];
    var impressions = data[i][3];
    var clicks = data[i][4];
    var conversions = data[i][5];
    var conversionValue = data[i][6];
    var cost = data[i][2];

    // 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
  for (var nGram in nGramData) {
    var record = nGramData[nGram];
    sheet.appendRow([nGram, record.cost, record.impressions, record.clicks, record.conversions, record.conversionValue,  record.costPerConversion]);
  }
}