TierLog — Free Product Tier Segmentation for Google Ads

/**
 * ============================================================
 *  TIERLOG — Product Tier Segmentation for Google Ads
 *  Configurable Custom Label (0–4) | Shopping + Performance Max
 * ============================================================
 *
 *  SETUP:
 *  1. Create a new Google Sheet
 *  2. Go to Extensions > Apps Script, paste this file
 *  3. Fill in CONFIG below (Ads Customer ID + Sheet ID)
 *  4. Run setupSheet() once to create tabs + config rows
 *  5. Run runTierLog() manually or set a daily trigger
 *
 *  SHEETS CREATED:
 *  - Config      → editable thresholds, date range, label names
 *  - Product Data → raw pull from Google Ads
 *  - Labels      → computed labels per product (6 tiers: Superhero/Hero/Sidekick/Villain/Zombie/No-Data)
 *  - Log         → run history
 * ============================================================
 */

// ── HARDCODED CONFIG (fallback if Config sheet missing) ──────
const CONFIG = {
  customerId: "INSERT-YOUR-CUSTOMER-ID",   // e.g. "123-456-7890"
  sheetId:    "INSERT-YOUR-SHEET-ID",       // from Sheet URL
  lookbackDays: 30,
  targetRoas: 500,   // percentage (500 = 5x = 500%)

  // Thresholds — override these in Config sheet
  heroMinClicks:        50,  // clicks in lookback window
  sidekickMaxClicks:    49,  // < this AND converts well = Sidekick
  villainMaxRoas:       499, // below target ROAS with spend = Villain
  zombieMaxImpressions: 100, // ≤ this impressions = Zombie (served rarely, not a feed issue)

  // Label names written to Custom Label slot
  customLabelSlot: 4,  // 0–4 — which custom label slot to use (default: 4 = custom_label_4)
  labels: {
    hero:      "Hero",
    sidekick:  "Sidekick",
    villain:   "Villain",
    zombie:    "Zombie",
    superhero: "Superhero",  // Heroes with ROAS > 2x target
    noData:    "No-Data",    // 0 impressions in window — never shown, feed/listing issue
  }
};

// ── SHEET TAB NAMES ──────────────────────────────────────────
const TABS = {
  config:  "Config",
  data:    "Product Data",
  labels:  "Labels",
  log:     "Log"
};


// ============================================================
//  MAIN — Google Ads Scripts entry point
//  Change MODE to "setup" on first run, then switch to "run"
// ============================================================

var MODE = "run";  // "setup" | "run" | "push_only"

function main() {
  if (MODE === "setup") {
    setupSheet();
  } else if (MODE === "push_only") {
    pushLabelsOnly();
  } else {
    runTierLog();
  }
}


/**
 * Run once to scaffold the Sheet structure.
 */
function setupSheet() {
  const ss = SpreadsheetApp.openById(CONFIG.sheetId);

  _createTabIfMissing(ss, TABS.config);
  _createTabIfMissing(ss, TABS.data);
  _createTabIfMissing(ss, TABS.labels);
  _createTabIfMissing(ss, TABS.log);

  _writeConfigDefaults(ss);
  _writeDataHeaders(ss);
  _writeLabelHeaders(ss);

  Logger.log("✅ Sheet scaffolded. Fill in Config tab, then run runTierLog().");
}

/**
 * Main function — pull data, compute tiers, push to Google Ads.
 */
function runTierLog() {
  const ss       = SpreadsheetApp.openById(CONFIG.sheetId);

  _createTabIfMissing(ss, TABS.config);
  _createTabIfMissing(ss, TABS.data);
  _createTabIfMissing(ss, TABS.labels);
  _createTabIfMissing(ss, TABS.log);

  _writeConfigDefaults(ss);

  const cfg      = _loadConfig(ss);
  const startTs  = new Date();

  _log(ss, "START", `TierLog run started. Lookback: ${cfg.lookbackDays}d, Target ROAS: ${cfg.targetRoas}%`);

  try {
    const products = _fetchProductData(cfg);
    _log(ss, "INFO", `Fetched ${products.length} products from Google Ads`);

    _writeProductData(ss, products);

    const labeled = _computeLabels(products, cfg);
    _writeLabeledData(ss, labeled, cfg);

    const counts = _labelCounts(labeled);
    _log(ss, "INFO", `Labels: Superheroes=${counts.Superhero||0}, Heroes=${counts.Hero||0}, Sidekicks=${counts.Sidekick||0}, Villains=${counts.Villain||0}, Zombies=${counts.Zombie||0}, No-Data=${counts["No-Data"]||0}`);

    _log(ss, "INFO", `Wrote ${labeled.length} tier labels to Labels tab — Merchant Center Supplemental Feed will sync on next fetch`);

    const elapsed = ((new Date() - startTs) / 1000).toFixed(1);
    _log(ss, "SUCCESS", `Run complete in ${elapsed}s`);

  } catch (e) {
    _log(ss, "ERROR", e.toString());
    throw e;
  }
}

/**
 * Convenience: only push labels already computed in Labels tab (no API pull).
 */
function pushLabelsOnly() {
  const ss  = SpreadsheetApp.openById(CONFIG.sheetId);
  const cfg = _loadConfig(ss);
  const labeled = _readLabeledDataFromSheet(ss);
  const pushed  = _pushLabelsToAds(labeled, cfg);
  _log(ss, "PUSH-ONLY", `Pushed ${pushed} label updates to Google Ads`);
}


// ============================================================
//  GOOGLE ADS DATA FETCH
// ============================================================

function _fetchProductData(cfg) {
  const dateRange = _buildDateRange(cfg.lookbackDays);

  const query = `
    SELECT
      segments.product_item_id,
      segments.product_title,
      segments.product_merchant_id,
      metrics.impressions,
      metrics.clicks,
      metrics.cost_micros,
      metrics.conversions,
      metrics.conversions_value
    FROM shopping_performance_view
    WHERE
      segments.date BETWEEN '${dateRange.from}' AND '${dateRange.to}'
    ORDER BY metrics.clicks DESC
    LIMIT 50000
  `;

  const rows = [];
  const reportIterator = AdsApp.report(query).rows();

  while (reportIterator.hasNext()) {
    const row = reportIterator.next();
    const costMicros = parseFloat(row["metrics.cost_micros"]) || 0;
    const convValue  = parseFloat(row["metrics.conversions_value"]) || 0;
    const cost       = costMicros / 1000000;
    const roas       = cost > 0 ? (convValue / cost) * 100 : 0;

    rows.push({
      itemId:       (row["segments.product_item_id"] || "").replace(/_in_/i, "_IN_"),
      title:        row["segments.product_title"],
      merchantId:   row["segments.product_merchant_id"],
      impressions:  parseInt(row["metrics.impressions"]) || 0,
      clicks:       parseInt(row["metrics.clicks"]) || 0,
      cost:         cost,
      conversions:  parseFloat(row["metrics.conversions"]) || 0,
      convValue:    convValue,
      roas:         roas,
      label:        ""
    });
  }

  return rows;
}


// ============================================================
//  LABELING LOGIC
// ============================================================

/**
 * Decision tree (order matters):
 *
 *  1. No-Data   — impressions = 0. Feed/eligibility issue. Check Merchant Center.
 *  2. Zombie    — impressions > 0 but ≤ zombieMaxImpressions. Barely visible.
 *  3. Villain   — impressions > threshold, ROAS < target. Budget drain.
 *  4. Sidekick  — clicks < heroMinClicks, ROAS ≥ target. Hidden gem.
 *  5. Hero      — clicks ≥ heroMinClicks, ROAS ≥ target. Proven performer.
 *  6. Superhero — Hero with ROAS ≥ 2× target. Elite tier.
 */
function _computeLabels(products, cfg) {
  const superRoasThreshold = cfg.targetRoas * 2;

  return products.map(p => {
    let label;

    if (p.impressions === 0) {
      label = cfg.labels.noData;
    } else if (p.impressions <= cfg.zombieMaxImpressions) {
      label = cfg.labels.zombie;
    } else if (p.roas < cfg.targetRoas) {
      label = cfg.labels.villain;
    } else if (p.clicks < cfg.heroMinClicks) {
      label = cfg.labels.sidekick;
    } else if (p.roas >= superRoasThreshold) {
      label = cfg.labels.superhero;
    } else {
      label = cfg.labels.hero;
    }

    return { ...p, label };
  });
}


// ============================================================
//  PUSH LABELS TO GOOGLE ADS
// ============================================================

function _pushLabelsToAds(labeled, cfg) {
  let pushed = 0;

  const labelMap = {};
  labeled.forEach(p => {
    if (p.itemId) labelMap[p.itemId] = p.label;
  });

  const campaignIterator = AdsApp.shoppingCampaigns().get();
  while (campaignIterator.hasNext()) {
    const campaign = campaignIterator.next();
    const pgIterator = campaign.productGroups().get();

    while (pgIterator.hasNext()) {
      const pg = pgIterator.next();
      try {
        const dim = pg.getDimension();
        if (dim && dim.productItemId) {
          const label = labelMap[dim.productItemId];
          if (label) { pushed++; }
        }
      } catch(e) { /* some product groups don't expose getDimension */ }
    }
  }

  Logger.log("ℹ️  Label data written to 'Labels' sheet tab. Connect this tab as a " +
             "Supplemental Feed in Merchant Center to apply labels.");

  return labeled.length;
}


// ============================================================
//  SHEET READ/WRITE HELPERS
// ============================================================

function _writeProductData(ss, products) {
  const sheet = ss.getSheetByName(TABS.data);
  sheet.clearContents();

  const headers = ["Item ID", "Title", "Merchant ID", "Impressions",
                   "Clicks", "Cost (€/$)", "Conversions", "Conv. Value", "ROAS (%)"];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers])
    .setFontWeight("bold")
    .setBackground("#1a1a2e")
    .setFontColor("#ffffff");

  if (products.length === 0) return;

  const rows = products.map(p => [
    p.itemId, p.title, p.merchantId,
    p.impressions, p.clicks,
    p.cost.toFixed(2),
    p.conversions.toFixed(1),
    p.convValue.toFixed(2),
    p.roas.toFixed(1)
  ]);

  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, headers.length);
}

function _writeLabeledData(ss, labeled, cfg) {
  const sheet = ss.getSheetByName(TABS.labels);
  sheet.clearContents();

  const customLabelCol = "custom_label_" + cfg.customLabelSlot;

  const headers = ["id", customLabelCol,
                   "title_[display only]", "clicks_[display only]",
                   "roas_[display only]", "cost_[display only]"];

  sheet.getRange(1, 1, 1, headers.length).setValues([headers])
    .setFontWeight("bold")
    .setBackground("#0f3460")
    .setFontColor("#e94560");

  if (labeled.length === 0) return;

  const rows = labeled.map(p => [
    p.itemId, p.label, p.title,
    p.clicks, p.roas.toFixed(1), p.cost.toFixed(2)
  ]);

  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);

  const labelCol = sheet.getRange(2, 2, rows.length, 1);
  const colors   = labeled.map(p => [_labelColor(p.label)]);
  labelCol.setBackgrounds(colors);
  labelCol.setFontWeight("bold");

  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, headers.length);
}

function _readLabeledDataFromSheet(ss) {
  const sheet = ss.getSheetByName(TABS.labels);
  const data  = sheet.getDataRange().getValues();
  if (data.length < 2) return [];

  return data.slice(1).map(row => ({
    itemId: row[0], label: row[1], title: row[2],
    clicks: row[3], roas: parseFloat(row[4]) || 0, cost: parseFloat(row[5]) || 0
  }));
}


// ============================================================
//  CONFIG SHEET
// ============================================================

function _writeConfigDefaults(ss) {
  const sheet = ss.getSheetByName(TABS.config);
  if (sheet.getLastRow() > 1) return;

  sheet.clearContents();
  const headers = ["Setting", "Value", "Description"];
  sheet.getRange(1, 1, 1, 3).setValues([headers])
    .setFontWeight("bold").setBackground("#0f3460").setFontColor("#e94560");

  const rows = [
    ["lookback_days",          30,   "Number of days of data to pull"],
    ["target_roas",            500,  "Target ROAS % (500 = 5x)"],
    ["custom_label_slot",      4,    "Which custom label slot to use (0, 1, 2, 3, or 4)"],
    ["hero_min_clicks",        50,   "Min clicks to qualify as Hero/Villain (not Sidekick)"],
    ["sidekick_max_clicks",    49,   "Max clicks for a well-converting Sidekick"],
    ["villain_max_roas",       499,  "Max ROAS% that still counts as Villain (below target)"],
    ["zombie_max_impressions", 100,  "≤ this impressions = Zombie"],
    ["label_hero",       "Hero",      "Tier value written to your chosen custom label slot"],
    ["label_sidekick",   "Sidekick",  "Tier value written to your chosen custom label slot"],
    ["label_villain",    "Villain",   "Tier value written to your chosen custom label slot"],
    ["label_zombie",     "Zombie",    "Tier value written to your chosen custom label slot"],
    ["label_superhero",  "Superhero", "Tier value written to your chosen custom label slot"],
    ["label_no_data",    "No-Data",   "Tier value written to your chosen custom label slot"],
  ];

  sheet.getRange(2, 1, rows.length, 3).setValues(rows);
  sheet.autoResizeColumns(1, 3);
  sheet.setFrozenRows(1);
}

function _loadConfig(ss) {
  const sheet = ss.getSheetByName(TABS.config);
  if (!sheet) return CONFIG;

  const data = sheet.getDataRange().getValues();
  const map  = {};
  data.slice(1).forEach(row => { if (row[0]) map[row[0]] = row[1]; });

  return {
    lookbackDays:         parseInt(map["lookback_days"])          || CONFIG.lookbackDays,
    targetRoas:           parseFloat(map["target_roas"])          || CONFIG.targetRoas,
    customLabelSlot:      parseInt(map["custom_label_slot"])      >= 0 ? parseInt(map["custom_label_slot"]) : CONFIG.customLabelSlot,
    heroMinClicks:        parseInt(map["hero_min_clicks"])        || CONFIG.heroMinClicks,
    sidekickMaxClicks:    parseInt(map["sidekick_max_clicks"])    || CONFIG.sidekickMaxClicks,
    villainMaxRoas:       parseFloat(map["villain_max_roas"])     || CONFIG.villainMaxRoas,
    zombieMaxImpressions: parseInt(map["zombie_max_impressions"]) || CONFIG.zombieMaxImpressions,
    labels: {
      hero:      map["label_hero"]      || CONFIG.labels.hero,
      sidekick:  map["label_sidekick"]  || CONFIG.labels.sidekick,
      villain:   map["label_villain"]   || CONFIG.labels.villain,
      zombie:    map["label_zombie"]    || CONFIG.labels.zombie,
      superhero: map["label_superhero"] || CONFIG.labels.superhero,
      noData:    map["label_no_data"]   || CONFIG.labels.noData,
    }
  };
}


// ============================================================
//  LOG TAB
// ============================================================

function _writeDataHeaders(ss) {
  const sheet = ss.getSheetByName(TABS.data);
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1,1).setValue("Run setupSheet() and then runTierLog() to populate this tab.");
  }
}

function _writeLabelHeaders(ss) {
  const sheet = ss.getSheetByName(TABS.labels);
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1,1).setValue("Run runTierLog() to populate this tab. Then connect it as a Supplemental Feed in Merchant Center.");
  }
}

function _log(ss, level, message) {
  const sheet = ss.getSheetByName(TABS.log);
  if (!sheet) return;
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1,1,1,3).setValues([["Timestamp","Level","Message"]])
      .setFontWeight("bold").setBackground("#0f3460").setFontColor("#e94560");
    sheet.setFrozenRows(1);
  }
  sheet.appendRow([new Date().toISOString(), level, message]);
  Logger.log(`[${level}] ${message}`);
}


// ============================================================
//  UTILITIES
// ============================================================

function _buildDateRange(days) {
  const to   = new Date();
  const from = new Date();
  from.setDate(to.getDate() - days);
  return {
    from: Utilities.formatDate(from, "UTC", "yyyy-MM-dd"),
    to:   Utilities.formatDate(to,   "UTC", "yyyy-MM-dd")
  };
}

function _createTabIfMissing(ss, name) {
  if (!ss.getSheetByName(name)) {
    ss.insertSheet(name);
    Logger.log(`Created tab: ${name}`);
  }
}

function _labelCounts(labeled) {
  return labeled.reduce((acc, p) => {
    acc[p.label] = (acc[p.label] || 0) + 1;
    return acc;
  }, {});
}

function _labelColor(label) {
  const map = {
    "Superhero": "#cfe2ff",
    "Hero":      "#d4edda",
    "Sidekick":  "#fff3cd",
    "Villain":   "#f8d7da",
    "Zombie":    "#e2e3e5",
    "No-Data":   "#1a1a1a",
  };
  return map[label] || "#ffffff";
}


// ============================================================
//  TRIGGER HELPER — run once to schedule daily execution
// ============================================================

/**
 * Creates a daily time-based trigger for runTierLog().
 * Run this function once from the Apps Script editor.
 */
function createDailyTrigger() {
  ScriptApp.getProjectTriggers().forEach(t => {
    if (t.getHandlerFunction() === "runTierLog") {
      ScriptApp.deleteTrigger(t);
    }
  });

  ScriptApp.newTrigger("runTierLog")
    .timeBased()
    .everyDays(1)
    .atHour(4)
    .create();

  Logger.log("✅ Daily trigger created for runTierLog() at 4am.");
}