N-Gram анализ поисковых запросов и поиск минус-слов

/**
 * Goodlabs Google Ads script: n-gram search term analysis
 * Schedule: Weekly on Mondays
 */
var CONFIG = {
  MIN_IMPRESSIONS: 50,
  MIN_COST: 20,
  SPREADSHEET_URL: "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit",
  DATE_RANGE: "LAST_30_DAYS"
};

function main() {
  Logger.log("Extracting search terms for N-Gram analysis...");
  var report = AdsApp.report(
    "SELECT Query, Impressions, Clicks, Cost, Conversions, ConversionValue " +
    "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
    "WHERE Impressions > " + CONFIG.MIN_IMPRESSIONS + " " +
    "DURING " + CONFIG.DATE_RANGE
  );

  var ngrams = {}; // token -> { imps, clicks, cost, convs, value }

  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var query = row["Query"].toLowerCase().trim();
    var imps = parseInt(row["Impressions"], 10);
    var clicks = parseInt(row["Clicks"], 10);
    var cost = parseFloat(row["Cost"].replace(/,/g, ""));
    var convs = parseFloat(row["Conversions"].replace(/,/g, ""));
    var val = parseFloat(row["ConversionValue"].replace(/,/g, ""));

    var words = query.split(/\s+/);
    
    for (var n = 1; n <= 3; n++) {
      for (var i = 0; i <= words.length - n; i++) {
        var gram = words.slice(i, i + n).join(" ");
        if (!ngrams[gram]) {
          ngrams[gram] = { gram: gram, words: n, imps: 0, clicks: 0, cost: 0, convs: 0, val: 0 };
        }
        ngrams[gram].imps += imps;
        ngrams[gram].clicks += clicks;
        ngrams[gram].cost += cost;
        ngrams[gram].convs += convs;
        ngrams[gram].val += val;
      }
    }
  }

  exportToSheet(ngrams);
}

function exportToSheet(ngrams) {
  var ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
  var sheet = ss.getSheetByName("NGram_Analysis") || ss.insertSheet("NGram_Analysis");
  sheet.clear();
  sheet.appendRow(["N-Gram Token", "Words", "Impressions", "Clicks", "Cost", "Conversions", "CPA", "Conv. Rate", "ROAS", "Status"]);

  var output = [];
  for (var k in ngrams) {
    var item = ngrams[k];
    if (item.cost < CONFIG.MIN_COST) continue;
    var cpa = item.convs > 0 ? (item.cost / item.convs).toFixed(2) : "N/A";
    var cr = item.clicks > 0 ? ((item.convs / item.clicks) * 100).toFixed(2) + "%" : "0%";
    var roas = item.cost > 0 ? (item.val / item.cost).toFixed(2) : "0.00";
    var status = (item.convs === 0 && item.cost >= CONFIG.MIN_COST) ? "🚨 NEGATIVE CANDIDATE" : "OK";

    output.push([
      item.gram, item.words, item.imps, item.clicks,
      item.cost.toFixed(2), item.convs, cpa, cr, roas, status
    ]);
  }

  output.sort(function(a, b) { return parseFloat(b[4]) - parseFloat(a[4]); });

  if (output.length > 0) {
    sheet.getRange(2, 1, output.length, 10).setValues(output);
  }
  sheet.getRange(1, 1, 1, 10).setFontWeight("bold").setBackground("#f3f4f6");
  Logger.log("Exported " + output.length + " n-gram patterns to Google Sheets.");
}