Söktermsgrävning — hitta nya sökord och negativa

/**
 * Search Query Mining
 * Exporterar söktermer till Google Sheets för analys.
 * Markerar potentiella nya sökord och negativa.
 *
 * Konfigurera SHEET_URL nedan.
 */

var SHEET_URL = "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/";
var MIN_CLICKS = 3;
var LOOKBACK = "LAST_30_DAYS";
var CPA_THRESHOLD = 200; // SEK — söktermer med högre CPA flaggas som negativa

function main() {
  var ss = SpreadsheetApp.openByUrl(SHEET_URL);
  var sheet = ss.getSheetByName("QueryMining") || ss.insertSheet("QueryMining");
  sheet.clear();

  // Header
  sheet.appendRow([
    "Sökterm", "Kampanj", "Klick", "Impressions",
    "CTR", "Kostnad", "Konverteringar", "CPA", "Rekommendation"
  ]);

  var report = AdsApp.report(
    "SELECT Query, CampaignName, Clicks, Impressions, Ctr, Cost, Conversions " +
    "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
    "WHERE Clicks >= " + MIN_CLICKS + " " +
    "DURING " + LOOKBACK
  );

  var rows = report.rows();
  var added = 0;

  while (rows.hasNext()) {
    var row = rows.next();
    var clicks = parseInt(row["Clicks"]);
    var cost = parseFloat(row["Cost"]);
    var conversions = parseFloat(row["Conversions"]);
    var cpa = conversions > 0 ? cost / conversions : 9999;

    var recommendation = "";
    if (conversions > 0 && cpa <= CPA_THRESHOLD) {
      recommendation = "✅ Lägg till som sökord";
    } else if (clicks >= 10 && conversions === 0) {
      recommendation = "🚫 Potentiellt negativt";
    } else if (cpa > CPA_THRESHOLD && conversions > 0) {
      recommendation = "⚠️ Hög CPA — granska";
    }

    sheet.appendRow([
      row["Query"], row["CampaignName"], clicks,
      parseInt(row["Impressions"]), row["Ctr"],
      cost, conversions, cpa === 9999 ? "—" : cpa.toFixed(0),
      recommendation
    ]);
    added++;
  }

  Logger.log("Exporterade " + added + " söktermer till Sheets.");
}