script

const KLAVIYO_API_KEY = "YOUR KLAVIYO API KEY";
const KLAVIYO_API_REVISION = "2025-07-15";
const KLAVIYO_BASE = "https://a.klaviyo.com/api/";

/** Low-level request wrapper */
function _klaviyoRequest_(endpoint, method = "GET", bodyObj = null, extraParams = {}) {
  let url = KLAVIYO_BASE + endpoint;
  const qs = [];
  for (let k in extraParams) {
    qs.push(encodeURIComponent(k) + "=" + encodeURIComponent(extraParams[k]));
  }
  if (qs.length > 0) {
    url += "?" + qs.join("&");
  }
  const options = {
    method: method,
    headers: {
      "Authorization": "Klaviyo-API-Key " + KLAVIYO_API_KEY,
      "Accept": "application/vnd.api+json",
      "Content-Type": "application/json",
      "revision": KLAVIYO_API_REVISION
    },
    muteHttpExceptions: true
  };
  if (bodyObj !== null) {
    options.payload = JSON.stringify(bodyObj);
  }
  const resp = UrlFetchApp.fetch(url, options);
  const status = resp.getResponseCode();
  const text = resp.getContentText();
  Logger.log("Request URL: %s", url);
  Logger.log("Status: %s, Body: %s", status, text);
  let obj;
  try {
    obj = JSON.parse(text);
  } catch (e) {
    throw new Error("Invalid JSON response: " + text);
  }
  if (status >= 200 && status < 300) {
    return obj;
  } else {
    throw new Error("Klaviyo API error " + status + ": " + text);
  }
}

/** Fetch campaign names (ID → name) using cursor pagination and required filter */
function fetchCampaignNames() {
  const map = {};
  let nextUrl = "campaigns?filter=equals(messages.channel,'email')";
  while (nextUrl) {
    const resp = _klaviyoRequest_(nextUrl, "GET");
    const data = resp.data || [];
    data.forEach(c => {
      if (c.id && c.attributes && c.attributes.name) {
        map[c.id] = c.attributes.name;
      }
    });
    if (resp.links && resp.links.next) {
      const fullNext = resp.links.next;
      // Convert fullNext (absolute URL) into relative path for our requests
      const base = KLAVIYO_BASE;
      if (fullNext.startsWith(base)) {
        nextUrl = fullNext.slice(base.length);
      } else {
        // Fallback: use fullNext directly
        nextUrl = fullNext;
      }
    } else {
      nextUrl = null;
    }
  }
  return map;
}

/** Fetch flow names (ID → name) using cursor pagination */
function fetchFlowNames() {
  const map = {};
  let nextUrl = "flows";
  while (nextUrl) {
    const resp = _klaviyoRequest_(nextUrl, "GET");
    const data = resp.data || [];
    data.forEach(f => {
      if (f.id && f.attributes && f.attributes.name) {
        map[f.id] = f.attributes.name;
      }
    });
    if (resp.links && resp.links.next) {
      const fullNext = resp.links.next;
      const base = KLAVIYO_BASE;
      if (fullNext.startsWith(base)) {
        nextUrl = fullNext.slice(base.length);
      } else {
        nextUrl = fullNext;
      }
    } else {
      nextUrl = null;
    }
  }
  return map;
}

/** Fetch campaign performance stats for last 30 days */
function getCampaignStats(conversionMetricId) {
  const payload = {
    data: {
      type: "campaign-values-report",
      attributes: {
        timeframe: { key: "last_30_days" },
        conversion_metric_id: conversionMetricId,
        statistics: [
          "opens",
          "open_rate",
          "clicks",
          "clicks_unique",
          "conversion_rate",
          "conversion_uniques",
          "conversion_value",
          "conversions",
          "delivered",
          "delivery_rate"
        ]
      }
    }
  };
  const resp = _klaviyoRequest_("campaign-values-reports", "POST", payload);
  const results = resp.data?.attributes?.results;
  return Array.isArray(results) ? results : [];
}

/** Fetch flow performance stats for last 30 days */
function getFlowStats(conversionMetricId) {
  const payload = {
    data: {
      type: "flow-values-report",
      attributes: {
        timeframe: { key: "last_30_days" },
        conversion_metric_id: conversionMetricId,
        statistics: [
          "clicks",
          "clicks_unique",
          "conversion_rate",
          "conversion_uniques",
          "conversion_value",
          "conversions",
          "delivered",
          "delivery_rate"
        ]
      }
    }
  };
  const resp = _klaviyoRequest_("flow-values-reports", "POST", payload);
  const results = resp.data?.attributes?.results;
  return Array.isArray(results) ? results : [];
}

/** Aggregate / roll up multiple message-level stats into one per ID */
function rollupById(results, idKey) {
  const agg = {};
  results.forEach(r => {
    const grp = r.groupings || {};
    const id = grp[idKey];
    if (!id) return;
    if (!(id in agg)) {
      agg[id] = {
        id: id,
        sum_conversion_value: 0,
        sum_conversions: 0,
        sum_delivered: 0,
        sum_clicks: 0,
        sum_clicks_unique: 0,
        sum_opens: 0,
        sum_open_numer: 0,
        count: 0
      };
    }
    const st = r.statistics || {};
    const rec = agg[id];
    rec.sum_conversion_value += (st.conversion_value || 0);
    rec.sum_conversions += (st.conversions || 0);
    rec.sum_delivered += (st.delivered || 0);
    rec.sum_clicks += (st.clicks || 0);
    rec.sum_clicks_unique += (st.clicks_unique || 0);
    rec.sum_opens += (st.opens || 0);
    if (!isNaN(st.open_rate) && typeof st.open_rate === "number") {
      rec.sum_open_numer += st.open_rate * (st.delivered || 0);
    }
    rec.count++;
  });
  return Object.values(agg).map(rec => {
    const avg_open_rate = rec.sum_delivered > 0 ? rec.sum_open_numer / rec.sum_delivered : 0;
    const conv_rate = rec.sum_delivered > 0 ? rec.sum_conversions / rec.sum_delivered : 0;
    return {
      id: rec.id,
      conversion_value: rec.sum_conversion_value,
      conversions: rec.sum_conversions,
      delivered: rec.sum_delivered,
      clicks: rec.sum_clicks,
      clicks_unique: rec.sum_clicks_unique,
      opens: rec.sum_opens,
      open_rate: avg_open_rate,
      conversion_rate: conv_rate
    };
  });
}

/** Main function: merge, update, append, prune older than 2 years */
function refreshKlaviyoMerged() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  let sheetC = ss.getSheetByName("Campaign Summary");
  if (!sheetC) sheetC = ss.insertSheet("Campaign Summary");
  let sheetF = ss.getSheetByName("Flow Summary");
  if (!sheetF) sheetF = ss.insertSheet("Flow Summary");
  
  const campHeaders = ["Campaign ID","Campaign Name","Revenue","Conversions","Delivered","Opens","Open Rate","Clicks","Clicks Unique","Conversion Rate","Last Updated"];
  const flowHeaders = ["Flow ID","Flow Name","Revenue","Conversions","Delivered","Clicks","Clicks Unique","Conversion Rate","Last Updated"];
  
  if (sheetC.getLastRow() === 0) {
    sheetC.getRange(1, 1, 1, campHeaders.length).setValues([campHeaders]);
  }
  if (sheetF.getLastRow() === 0) {
    sheetF.getRange(1, 1, 1, flowHeaders.length).setValues([flowHeaders]);
  }
  
  // Build ID → row maps
  const campData = sheetC.getDataRange().getValues();
  const campIdToRow = {};
  for (let i = 1; i < campData.length; i++) {
    const id = campData[i][0];
    if (id) {
      campIdToRow[id] = i + 1;
    }
  }
  const flowData = sheetF.getDataRange().getValues();
  const flowIdToRow = {};
  for (let i = 1; i < flowData.length; i++) {
    const id = flowData[i][0];
    if (id) {
      flowIdToRow[id] = i + 1;
    }
  }
  
  // Get conversion metric ID (e.g. “Placed Order”)
  const metricsResp = _klaviyoRequest_("metrics", "GET");
  const metricsList = metricsResp.data;
  const placed = metricsList.find(m => m.attributes && m.attributes.name === "Placed Order");
  if (!placed) {
    throw new Error("Conversion metric 'Placed Order' not found");
  }
  const conversionMetricId = placed.id;
  
  // Fetch stats
  const campStatsRaw = getCampaignStats(conversionMetricId);
  const flowStatsRaw = getFlowStats(conversionMetricId);
  
  const campRoll = rollupById(campStatsRaw, "campaign_id");
  const flowRoll = rollupById(flowStatsRaw, "flow_id");
  
  const campNames = fetchCampaignNames();
  const flowNames = fetchFlowNames();
  
  const now = new Date();
  
  // Update / insert campaign
  campRoll.forEach(r => {
    const name = campNames[r.id] || "";
    const vals = [
      r.id,
      name,
      r.conversion_value,
      r.conversions,
      r.delivered,
      r.opens,
      r.open_rate,
      r.clicks,
      r.clicks_unique,
      r.conversion_rate,
      now
    ];
    const existingRow = campIdToRow[r.id];
    if (existingRow) {
      sheetC.getRange(existingRow, 1, 1, vals.length).setValues([vals]);
    } else {
      const lr = sheetC.getLastRow();
      sheetC.getRange(lr + 1, 1, 1, vals.length).setValues([vals]);
    }
  });
  
  // Update / insert flows
  flowRoll.forEach(r => {
    const name = flowNames[r.id] || "";
    const vals = [
      r.id,
      name,
      r.conversion_value,
      r.conversions,
      r.delivered,
      r.clicks,
      r.clicks_unique,
      r.conversion_rate,
      now
    ];
    const existingRow = flowIdToRow[r.id];
    if (existingRow) {
      sheetF.getRange(existingRow, 1, 1, vals.length).setValues([vals]);
    } else {
      const lf = sheetF.getLastRow();
      sheetF.getRange(lf + 1, 1, 1, vals.length).setValues([vals]);
    }
  });
  
  // Prune old rows older than 2 years
  const TWO_YEARS_MS = 2 * 365 * 24 * 3600 * 1000;
  pruneOldRows(sheetC, campHeaders.length, TWO_YEARS_MS);
  pruneOldRows(sheetF, flowHeaders.length, TWO_YEARS_MS);
}

/** Delete rows whose “Last Updated” date is older than threshold */
function pruneOldRows(sheet, lastColIndex, thresholdMs) {
  const data = sheet.getDataRange().getValues();
  const now = Date.now();
  const rowsToDelete = [];
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const lastUp = row[lastColIndex - 1];
    if (lastUp instanceof Date) {
      if ((now - lastUp.getTime()) > thresholdMs) {
        rowsToDelete.push(i + 1);
      }
    }
  }
  rowsToDelete.reverse().forEach(r => {
    sheet.deleteRow(r);
  });
}