N-gram Script til Google Ads

// N-gram Analysis Script for Google Ads
// ========================================
// Identifies patterns in search terms (1, 2 and 3 word combinations)
// and shows cost, clicks, impressions and conversions per n-gram.
//
// Output: Google Sheets (link generated on first run)
// Author: Online Markedsføring (online-markedsføring.com)
// Version: 1.0 | 2025

var CONFIG = {
  MIN_IMPRESSIONS: 10,       // Minimum impressions for an n-gram to appear
  MIN_COST: 5,               // Minimum spend in account currency
  DATE_RANGE: 'LAST_30_DAYS', // LAST_7_DAYS / LAST_30_DAYS / LAST_90_DAYS / THIS_MONTH / LAST_MONTH
  SHEET_NAME: 'N-gram Analysis',
  MAX_NGRAM_SIZE: 3,         // 1 = unigrams, 2 = bigrams, 3 = trigrams
  INCLUDE_SEARCH_PARTNERS: false,
};

function main() {
  var spreadsheet = getOrCreateSpreadsheet();
  var sheet = getOrCreateSheet(spreadsheet, CONFIG.SHEET_NAME);

  sheet.clearContents();
  var headers = [
    'N-gram', 'Type', 'Impressions', 'Clicks', 'Cost', 'Conversions',
    'CTR (%)', 'CPC', 'Conv. Rate (%)', 'Cost/Conv.'
  ];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');

  var ngramData = {};

  var query = 'SELECT search_term_view.search_term, metrics.impressions, ' +
    'metrics.clicks, metrics.cost_micros, metrics.conversions ' +
    'FROM search_term_view ' +
    'WHERE segments.date DURING ' + CONFIG.DATE_RANGE + ' ' +
    'AND metrics.impressions > 0';

  var rows = AdsApp.search(query);

  while (rows.hasNext()) {
    var row = rows.next();
    var searchTerm = row['search_term_view.search_term'].toLowerCase();
    var impressions = parseInt(row['metrics.impressions']) || 0;
    var clicks = parseInt(row['metrics.clicks']) || 0;
    var cost = (parseInt(row['metrics.cost_micros']) || 0) / 1000000;
    var conversions = parseFloat(row['metrics.conversions']) || 0;

    var words = searchTerm.split(/\s+/).filter(function(w) { return w.length > 0; });

    for (var n = 1; n <= CONFIG.MAX_NGRAM_SIZE; n++) {
      for (var i = 0; i <= words.length - n; i++) {
        var ngram = words.slice(i, i + n).join(' ');
        var type = n === 1 ? 'Unigram' : n === 2 ? 'Bigram' : 'Trigram';

        if (!ngramData[ngram]) {
          ngramData[ngram] = {
            type: type,
            impressions: 0, clicks: 0, cost: 0, conversions: 0
          };
        }
        ngramData[ngram].impressions += impressions;
        ngramData[ngram].clicks += clicks;
        ngramData[ngram].cost += cost;
        ngramData[ngram].conversions += conversions;
      }
    }
  }

  var outputRows = [];

  for (var ngram in ngramData) {
    var d = ngramData[ngram];
    if (d.impressions < CONFIG.MIN_IMPRESSIONS || d.cost < CONFIG.MIN_COST) continue;

    var ctr = d.impressions > 0 ? (d.clicks / d.impressions * 100).toFixed(2) : 0;
    var cpc = d.clicks > 0 ? (d.cost / d.clicks).toFixed(2) : 0;
    var convRate = d.clicks > 0 ? (d.conversions / d.clicks * 100).toFixed(2) : 0;
    var costPerConv = d.conversions > 0 ? (d.cost / d.conversions).toFixed(2) : '—';

    outputRows.push([
      ngram, d.type, d.impressions, d.clicks,
      d.cost.toFixed(2), d.conversions.toFixed(1),
      ctr, cpc, convRate, costPerConv
    ]);
  }

  // Sort by cost descending
  outputRows.sort(function(a, b) { return parseFloat(b[4]) - parseFloat(a[4]); });

  if (outputRows.length > 0) {
    sheet.getRange(2, 1, outputRows.length, headers.length).setValues(outputRows);
  }

  // Auto-resize columns
  for (var col = 1; col <= headers.length; col++) {
    sheet.autoResizeColumn(col);
  }

  Logger.log('N-gram analysis complete. ' + outputRows.length + ' n-grams found.');
  Logger.log('Spreadsheet: ' + spreadsheet.getUrl());
}

function getOrCreateSpreadsheet() {
  var files = DriveApp.getFilesByName('Google Ads N-gram Analysis');
  if (files.hasNext()) {
    return SpreadsheetApp.open(files.next());
  }
  return SpreadsheetApp.create('Google Ads N-gram Analysis');
}

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