Google Ads - New search terms script

Update: 06.01.2024. Including dashboards for search, shopping and Pmax.

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
  var sheetUrl = 'YOUR_GSHEET_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';
 
  //check for pmax campaigns
  var campaignIds = getCampaignIds();
  
  // 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 and available
  if (pmaxEnabled && campaignIds.length > 0) {
    // 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[1]; 
    }));

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

    // 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;
    var campaignType = row[10]; // Campaign type is in position 10
    
    return [
      row[0],              // Campaign Name
      row[1],              // Ad Group Name
      row[2],              // Search Term
      row[3],              // Match Type
      row[4],              // Cost
      row[5],              // Impressions
      row[6],              // Clicks
      row[7],              // Conversions
      row[8],              // Conversion Value
      costPerConversion,   // Cost / Conversion
      campaignType,        // Campaign Type
      "",                  // Adgroup Cost / Conv. (formula)
      "",                  // Adgroup Conv. Value / Cost (formula)
      "",                  // CPC bucket (formula)
      conversionValuePerCost  // Conv. Value / Cost
    ];
  });
}

function addPmaxMetrics(data) {
  return data.map(function(row) {
    return [
      row[0],              // Campaign
      row[1],              // Search Category
      row[2],              // Impressions
      row[3],              // Clicks
      row[4],              // Conversions
      row[5]               // Conversion Value
    ];
  });
}

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, campaign.name FROM campaign WHERE campaign.advertising_channel_type = 'PERFORMANCE_MAX' AND campaign.status = 'ENABLED'";
  var results = AdsApp.search(gaqlQuery);
  Logger.log('Starting campaign ID search');
 
  while (results.hasNext()) {
    var result = results.next();
    campaignIds.push(result.campaign.id);
    Logger.log(`Found campaign: ${result.campaign.name} (${result.campaign.id})`);
  }
 
  Logger.log(`Total PMax campaigns found: ${campaignIds.length}`);
  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 fetchPmaxData(period, clickThreshold) {
  const campaignIds = getCampaignIds();
  
    // Check if campaignIds is empty
  if (!campaignIds || campaignIds.length === 0) {
    Logger.log("No PMAX campaign found. Exiting function.");
    return;
  }
 
  const campaignNames = {};
 
  // Batch fetch campaign names
  const nameQuery = "SELECT campaign.id, campaign.name FROM campaign WHERE campaign.id IN (" + campaignIds.join(",") + ")";
  const nameResults = AdsApp.search(nameQuery);
  while (nameResults.hasNext()) {
    const result = nameResults.next();
    campaignNames[result.campaign.id] = result.campaign.name;
  }

  return campaignIds.reduce((allPmaxData, campaignId) => {
    const searchCategoryStats = getSearchCategorieStats(campaignId, period, clickThreshold);
   
    for (const [category, stats] of Object.entries(searchCategoryStats)) {
      allPmaxData.push([
        campaignNames[campaignId],
        category,
        stats.impressions,
        stats.clicks,
        stats.conversions,
        stats.conversionValue
      ]);
    }
    return allPmaxData;
  }, []);
}

function getSearchCategorieStats(campaignId, period, clickThreshold) {
 Logger.log('Starting stats retrieval for campaign ' + campaignId);
 const searchCategoryObject = {};
 const periodString = period === "last_30_days"
   ? ` BETWEEN '${dates(30)}' AND '${dates(1)}'`
   : ` BETWEEN '${dates(210)}' AND '${dates(31)}'`;

 // Get only category data
 const query = `
   SELECT
     campaign_search_term_insight.category_label,
     metrics.impressions,
     metrics.clicks,
     metrics.conversions,
     metrics.conversions_value
   FROM campaign_search_term_insight
   WHERE campaign_search_term_insight.campaign_id = ${campaignId}
   AND segments.date ${periodString}`;

 Logger.log('Executing query...');
 const report = AdsApp.search(query);
 Logger.log('Processing query results...');
 let rowCount = 0;
 
 while (report.hasNext()) {
   rowCount++;
   const result = report.next();
   const category = result.campaignSearchTermInsight.categoryLabel;
   const clicks = parseInt(result.metrics.clicks);

   if (clicks >= clickThreshold) {
     if (!searchCategoryObject[category]) {
       searchCategoryObject[category] = {
         impressions: 0,
         clicks: 0,
         conversions: 0,
         conversionValue: 0
       };
     }

     searchCategoryObject[category].impressions += parseInt(result.metrics.impressions);
     searchCategoryObject[category].clicks += clicks;
     searchCategoryObject[category].conversions += parseFloat(result.metrics.conversions);
     searchCategoryObject[category].conversionValue += parseFloat(result.metrics.conversionsValue);
   }
 }
 
 Logger.log(`Completed processing ${rowCount} rows`);
 return searchCategoryObject;
}

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}
  `;

  // Create campaign type mapping
  var campaignTypeMap = {};
  var campaignQuery = "SELECT campaign.name, campaign.advertising_channel_type FROM campaign WHERE campaign.status != 'REMOVED'";
  var campaignResults = AdsApp.search(campaignQuery);
  
  while (campaignResults.hasNext()) {
    var result = campaignResults.next();
    var campaignName = result.campaign.name;
    var channelType = result.campaign.advertisingChannelType;
    campaignTypeMap[campaignName] = channelType;
  }

  var report = AdsApp.report(query);
  var rows = report.rows();
  var data = [];

  while (rows.hasNext()) {
    var row = rows.next();
   
    // Special handling for cost
    var cost = row['Cost'];
    cost = cost.toString().replace(/,/g, '');
    cost = parseFloat(cost);
    
    // Get campaign name and determine type
    var campaignName = row['CampaignName'];
    var channelType = campaignTypeMap[campaignName];
    var campaignType = 'Unknown';
    
    // Map the channel type to a readable format
    if (channelType === 'SHOPPING') {
      campaignType = 'Shopping';
    } else if (channelType === 'SEARCH') {
      campaignType = 'Search';
    } else if (channelType === 'PERFORMANCE_MAX') {
      campaignType = 'PMax';
    }
   
    data.push([
      campaignName,
      row['AdGroupName'],
      row['Query'],
      row['QueryMatchTypeWithVariant'],
      cost,
      parseInt(row['Impressions']),
      parseInt(row['Clicks']),
      parseFloat(row['Conversions']),
      parseFloat(row['ConversionValue']),
      parseFloat(row['CostPerConversion']),
      campaignType
    ]);
  }

  Logger.log('Total campaigns processed: ' + Object.keys(campaignTypeMap).length);
  Logger.log('Total rows processed: ' + data.length);

  return data;
}

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 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;
      var formulasNeeded = false;
     
      if (sheetName.includes('Pmax')) {
        headers = [
          'Campaign',              // A
          'Search Category',       // B
          'Impressions',          // C
          'Clicks',               // D
          'Conversions',          // E
          'Conversion Value'       // F
        ];
      } else {
        headers = [
          'Campaign Name',                // A
          'Ad Group Name',               // B
          'Search Term',                 // C
          'Match Type',                  // D
          'Cost',                        // E
          'Impressions',                 // F
          'Clicks',                      // G
          'Conversions',                 // H
          'Conversion Value',            // I
          'Cost / Conversion',           // J
          'Campaign Type',               // K
          'Adgroup Cost / Conv.',        // L
          'Adgroup Conv. Value / Cost',  // M
          'CPC bucket',                  // N
          'Conv. Value / Cost'           // O
        ];
        formulasNeeded = true;
      }

      var values = [headers];
     
      // Only add non-empty rows to values array
      for (var i = 0; i < data.length; i++) {
        var row = data[i];
        if (row.some(cell => cell !== "")) {
          values.push(row);
        }
      }

      // Write only non-empty rows
      if (values.length > 1) {
        sheet.getRange(1, 1, values.length, headers.length).setValues(values);

        if (formulasNeeded) {
          // Set formula for Adgroup Cost / Conv. (column L)
          var adGroupCostPerConvFormula = '=IF(B2<>"", SUMIF(B:B, B2, E:E) / SUMIF(B:B, B2, H:H), 0)';
          var costPerConvRange = sheet.getRange(2, 12, values.length - 1, 1);
          costPerConvRange.setFormula(adGroupCostPerConvFormula);

          // Set formula for Adgroup Conv. Value / Cost (column M)
          var adGroupValuePerCostFormula = '=IFERROR(ARRAYFORMULA(IF(B2:B<>"", SUMIF(B2:B, B2:B, H2:H) / SUMIF(B2:B, B2:B, E2:E), )), 0)';
          sheet.getRange(2, 13).setFormula(adGroupValuePerCostFormula);

          // Add CPC bucket formula (column N)
          var cpcBucketFormula = '=ARRAYFORMULA(IF(G2:G = 0, "Division by 0", IF(E2:E / G2:G <= 1, "0 <= CPC <= 1", IF(E2:E / G2:G <= 2, "1 < CPC <= 2", IF(E2:E / G2:G <= 3, "2 < CPC <= 3", IF(E2:E / G2:G <= 4, "3 < CPC <= 4", IF(E2:E / G2:G <= 5, "4 < CPC <= 5", IF(E2:E / G2:G <= 6, "5 < CPC <= 6", IF(E2:E / G2:G <= 7, "6 < CPC <= 7", IF(E2:E / G2:G <= 8, "7 < CPC <= 8", IF(E2:E / G2:G <= 9, "8 < CPC <= 9", IF(E2:E / G2:G <= 10, "9 < CPC <= 10", "CPC > 10"))))))))))))';
          sheet.getRange(2, 14).setFormula(cpcBucketFormula);
        }
      }
    }
  } catch (error) {
    Logger.log('Error writing to sheet: ' + error.message);
  }
}

function performRegularNGramAnalysis(sheet, data, n) {
  if (!data || !Array.isArray(data) || data.length === 0 || typeof data.length === 'undefined' ) {
    Logger.log('Error: Invalid or empty data passed to performregularNGramAnalysis');
    return;
  }
  // 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 checkPermissions() {
  try {
    const account = AdsApp.currentAccount();
    Logger.log(`Account: ${account.getName()} (${account.getCustomerId()})`);
    return true;
  } catch (e) {
    Logger.log(`Permission Error: ${e.message}`);
    return false;
  }
}

function performPmaxNGramAnalysis(sheet, data, n) {
  // Add error checking
  if (!data || !Array.isArray(data) || data.length === 0 || typeof data.length === 'undefined' ) {
    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][1];
    var impressions = data[i][2];
    var clicks = data[i][3];
    var conversions = data[i][4];
    var conversionValue = data[i][5];

    // 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');
  }
}