Skrypt Google Ads: Bingalizer

/*
Copyright 2025 Krzysztof Bycina, www.LiveAds.pl
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

// =========================
// PODSTAWOWA KONFIGURACJA
// =========================
// Wypełnij tylko te pola, aby uruchomić skrypt

var SPREADSHEET_URL    = "https://docs.google.com/spreadsheets/d/12345/edit"; // link do arkusza Google Sheets
var DATE_RANGE_DAYS    = 30;    // ile dni wstecz analizować
var TOP_N_PER_LIST     = 70;    // ile top produktów z każdej listy (ROAS / CONV)
var CUSTOM_LABEL_INDEX = 4;     // DO WYBORU: 0, 1, 2, 3 lub 4 (custom label w feedzie)

// =========================
// DODATKOWA KONFIGURACJA
// =========================
// Parametry opcjonalne – możesz dostosować, jeśli chcesz precyzyjniej filtrować dane

var MIN_CLICKS         = 10;    // minimalna liczba kliknięć, by produkt był analizowany
var MIN_IMPRESSIONS    = 100;   // minimalna liczba wyświetleń
var MIN_CONV_FOR_ROAS  = 3;     // min. liczba konwersji, by liczyć ROAS
var MIN_CONV_FOR_CONV  = 1;     // min. liczba konwersji, by trafić do listy TopConv
var LABEL_ROAS         = "TopRoas"; // nazwa etykiety dla najlepszych wg ROAS
var LABEL_CONV         = "TopConv"; // nazwa etykiety dla najlepszych wg konwersji

// =========================
// KONIEC KONFIGURACJI
// =========================
// Poniżej nic nie zmieniaj

function main() {
  if (CUSTOM_LABEL_INDEX < 0 || CUSTOM_LABEL_INDEX > 4) {
    throw new Error("CUSTOM_LABEL_INDEX musi być liczbą 0–4.");
  }

  var currency = AdWordsApp.currentAccount().getCurrencyCode(); 
  var data1 = fetchData(DATE_RANGE_DAYS);
  var filtered = filterByClicksAndImpressions(data1, MIN_CLICKS, MIN_IMPRESSIONS);
  var roasSorted = getSortedROAS(filtered, MIN_CONV_FOR_ROAS);
  var convSorted = getSortedCONV(filtered, MIN_CONV_FOR_CONV);
  var combined = buildUniqueTop(roasSorted, convSorted, TOP_N_PER_LIST, LABEL_ROAS, LABEL_CONV);
  writeToSheet(combined, CUSTOM_LABEL_INDEX, currency);
}

function fetchData(daysBack) {
  var now = new Date();
  var start = new Date(now.getFullYear(), now.getMonth(), now.getDate() - daysBack);
  var tz = AdWordsApp.currentAccount().getTimeZone();
  var dStart = Utilities.formatDate(start, tz, 'yyyyMMdd');
  var dEnd = Utilities.formatDate(now, tz, 'yyyyMMdd');

  var query =
    "SELECT OfferId, Impressions, Clicks, Ctr, Cost, Conversions, ConversionValue " +
    "FROM SHOPPING_PERFORMANCE_REPORT " +
    "DURING " + dStart + "," + dEnd;

  var out = [];
  var rows = AdWordsApp.report(query).rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var id = String(row['OfferId'] || '').toLowerCase();
    var cost = parseFloat(String(row['Cost']).replace(",", "").replace("€", "").trim()) || 0;
    var conv = parseFloat(String(row['Conversions']).replace(",", "")) || 0;
    var convVal = parseFloat(String(row['ConversionValue']).replace(",", "").replace("€", "").trim()) || 0;

    var cpa  = (conv > 0) ? (cost / conv) : 0;
    var roas = (cost > 0) ? (convVal / cost) : 0;

    out.push({
      id: id,
      impressions: parseInt(row['Impressions'], 10) || 0,
      clicks: parseInt(row['Clicks'], 10) || 0,
      cost: cost,
      conversions: conv,
      conversionValue: convVal,
      cpa: cpa,
      roas: roas
    });
  }
  return out;
}

function filterByClicksAndImpressions(data, minClicks, minImpressions) {
  return data.filter(function (x) {
    return x.clicks >= minClicks && x.impressions >= minImpressions;
  });
}

function getSortedROAS(data, minConv) {
  var copy = data.slice().filter(function (x) { return x.conversions >= minConv; });
  copy.sort(function (a, b) { return b.roas - a.roas; });
  return copy;
}

function getSortedCONV(data, minConvForConv) {
  var copy = data.slice().filter(function (x) { return x.conversions >= minConvForConv; });
  copy.sort(function (a, b) {
    if (b.conversions !== a.conversions) return b.conversions - a.conversions;
    if (b.roas !== a.roas) return b.roas - a.roas;
    return b.clicks - a.clicks;
  });
  return copy;
}

function buildUniqueTop(roasSorted, convSorted, topNPerList, labelROAS, labelCONV) {
  var TARGET_UNIQUE = topNPerList * 2;
  var i = 0, j = 0;
  var takenFromRoas = 0, takenFromConv = 0;
  var seen = {};
  var out = [];

  while (out.length < TARGET_UNIQUE && (i < roasSorted.length || j < convSorted.length)) {
    if (takenFromRoas < topNPerList && i < roasSorted.length) {
      var r = roasSorted[i++];
      if (!seen[r.id]) {
        seen[r.id] = true;
        var rr = Object.assign({}, r);
        rr.finalLabel = labelROAS;
        out.push(rr);
        takenFromRoas++;
        if (out.length >= TARGET_UNIQUE) break;
      }
      continue;
    }
    if (takenFromConv < topNPerList && j < convSorted.length) {
      var c = convSorted[j++];
      if (!seen[c.id]) {
        seen[c.id] = true;
        var cc = Object.assign({}, c);
        cc.finalLabel = labelCONV;
        out.push(cc);
        takenFromConv++;
        if (out.length >= TARGET_UNIQUE) break;
      }
      continue;
    }
    if (takenFromRoas >= topNPerList && j < convSorted.length) {
      var c2 = convSorted[j++];
      if (!seen[c2.id]) {
        seen[c2.id] = true;
        var cc2 = Object.assign({}, c2);
        cc2.finalLabel = labelCONV;
        out.push(cc2);
      }
      continue;
    }
    if (takenFromConv >= topNPerList && i < roasSorted.length) {
      var r2 = roasSorted[i++];
      if (!seen[r2.id]) {
        seen[r2.id] = true;
        var rr2 = Object.assign({}, r2);
        rr2.finalLabel = labelROAS;
        out.push(rr2);
      }
      continue;
    }
    break;
  }
  return out;
}

function writeToSheet(data, customLabelIndex, currency) {
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheets()[0]; // pierwszy arkusz
  var customLabelHeader = "custom label " + customLabelIndex;

  var headers = [[
    'id',
    'Impressions',
    'Clicks',
    'Cost (' + currency + ')',
    'Conversions',
    'ConversionValue (' + currency + ')',
    'CPA (' + currency + ')',
    'ROAS',
    customLabelHeader
  ]];

  var output = headers.concat(data.map(function (item) {
    var cost = Number(item.cost);
    var conv = Number(item.conversions);
    var convVal = Number(item.conversionValue);
    var cpa = Number(item.cpa);
    var roas = Number(item.roas);

    return [
      item.id,
      item.impressions,
      item.clicks,
      round2(cost),
      round2(conv),
      round2(convVal),
      round2(cpa),
      round2(roas),
      item.finalLabel || ''
    ];
  }));

  sheet.clear();
  if (output.length > 0) {
    var range = sheet.getRange(1, 1, output.length, output[0].length);
    range.setValues(output);
    if (output.length >= 2) {
      sheet.getRange(2, 4, output.length - 1, 1).setNumberFormat("0.00");
      sheet.getRange(2, 6, output.length - 1, 1).setNumberFormat("0.00");
      sheet.getRange(2, 7, output.length - 1, 1).setNumberFormat("0.00");
      sheet.getRange(2, 8, output.length - 1, 1).setNumberFormat("0.00");
    }
  }
}

function round2(n) {
  return Math.round((n + Number.EPSILON) * 100) / 100;
}