Labelizer

// Indexer 1.5
// Enter your data here --------------------------------------------------------
var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/xxx/";
var DAYS_AGO = 180;
var SHOPIFY_SHOP = "xxx";
var SHOPIFY_ACCESS_TOKEN = "xxx";
var HERO_ROI = 8;
var SIDEKICK_ROI = 5;
var VILLAIN_CLICKS = 180;
// -----------------------------------------------------------------------------

function main() {
  var products = getFilteredShoppingProducts(DAYS_AGO);
  products.sort(function (a, b) { return a[0] > b[0]; });
  products = products.slice(0, 999999);
  pushToSpreadsheet(products);
  console.log("Spreadsheet updated successfully.");
  updateTags(products);
  console.log("Tags updated successfully.");
}

function getFilteredShoppingProducts(DAYS_AGO) {
  var today = new Date();
  var timeframe = new Date(today.getFullYear(), today.getMonth(), today.getDate() - DAYS_AGO);
  var dateFrom = Utilities.formatDate(timeframe, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  var dateTo = Utilities.formatDate(today, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  var query =
    "SELECT OfferId, Impressions, Clicks, Ctr, Cost, Conversions, ConversionValue " +
    "FROM SHOPPING_PERFORMANCE_REPORT " +
    "DURING " + dateFrom + "," + dateTo;
  var products = [];
  var count = 0;
  var report = AdWordsApp.report(query);
  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var offer_id = row['OfferId'].toUpperCase();
    var impressions = row['Impressions'].toString();
    var clicks = row['Clicks'].toString();
    var cost = row['Cost'].toString();
    var conversions = row['Conversions'].toString();
    var conversionValue = row['ConversionValue'].toString();
    var convValuePerCost = (conversionValue.replace(",", "") / cost.replace(",", "")).toString();
    if (isNaN(convValuePerCost)) {
      convValuePerCost = 0;
    }
    // Bucketing logic
    var isProductType = '';
    var minConversions = DAYS_AGO / 30;
    if (convValuePerCost >= HERO_ROI && conversions > minConversions) {
      isProductType = 'hero';
    } else if (convValuePerCost >= SIDEKICK_ROI) {
      isProductType = 'sidekick';
    } else if (clicks > VILLAIN_CLICKS && convValuePerCost < SIDEKICK_ROI) {
      isProductType = 'villain';
    } else {
      isProductType = 'zombie';
    }
    products.push([offer_id, impressions, clicks, cost, conversions, conversionValue, convValuePerCost, isProductType]);
    count += 1;
  }
  Logger.log(count);
  return products;
}

function pushToSpreadsheet(data) {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName('Indexer');
  var lastRow = sheet.getMaxRows();
  sheet.getRange('A2:H' + lastRow).clearContent();
  var start_row = 2;
  var endRow = start_row + data.length - 1;
  var range = sheet.getRange('A' + start_row + ':' + 'H' + endRow);
  if (data.length > 0) { range.setValues(data); }
  return;
}

function updateTags(products) {
  var tagsToRemove = ["hero", "sidekick", "villain", "zombie"];

  for (var i = 0; i < products.length; i++) {
    var sku = products[i][0];

    // Skip SKUs that start with SHOPIFY_GB_
    if (sku.startsWith("SHOPIFY_GB_")) {
      console.log("Skipping GB SKU: " + sku);
      continue;
    }

    var isProductType = products[i][7];

    if (tagsToRemove.indexOf(isProductType) !== -1 || isProductType !== '') {
      var productId = getProductIdBySku(sku);
      if (productId) {
        console.log("Product ID: " + productId + " SKU: " + sku + " isProductType: " + isProductType);
        updateShopifyProduct(productId, tagsToRemove, isProductType);
      } else {
        console.log("Product ID not found for SKU: " + sku);
      }
    }
  }
}

function getProductIdBySku(sku) {
  var query = `
    query {
      productVariants(first: 1, query: "sku:${sku}") {
        edges {
          node {
            product {
              id
            }
          }
        }
      }
    }
  `;

  var url = "https://" + SHOPIFY_SHOP + ".myshopify.com/admin/api/2024-04/graphql.json";
  var options = {
    "method": "POST",
    "headers": {
      "Content-Type": "application/json",
      "X-Shopify-Access-Token": SHOPIFY_ACCESS_TOKEN
    },
    "payload": JSON.stringify({ query: query })
  };

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  if (data.data.productVariants.edges.length > 0) {
    return data.data.productVariants.edges[0].node.product.id;
  }

  return null;
}

function updateShopifyProduct(productId, tagsToRemove, isProductType) {
  var url = "https://" + SHOPIFY_SHOP + ".myshopify.com/admin/api/2024-04/graphql.json";

  // Remove tags
  var removeTagsMutation = `
    mutation removeTags($id: ID!, $tags: [String!]!) {
      tagsRemove(id: $id, tags: $tags) {
        node {
          id
        }
        userErrors {
          message
        }
      }
    }
  `;
  var removeTagsOptions = {
    "method": "POST",
    "headers": {
      "Content-Type": "application/json",
      "X-Shopify-Access-Token": SHOPIFY_ACCESS_TOKEN
    },
    "payload": JSON.stringify({
      query: removeTagsMutation,
      variables: {
        id: productId,
        tags: tagsToRemove
      }
    })
  };
  var removeTagsResponse = UrlFetchApp.fetch(url, removeTagsOptions);
  var removeTagsData = JSON.parse(removeTagsResponse.getContentText());
  if (removeTagsData.data.tagsRemove.userErrors.length != 0) {
    console.log("Failed to remove tags " + productId);
    console.log("Error: " + removeTagsData.data.tagsRemove.userErrors[0].message);
  }

  // Add new tag
  if (isProductType !== '') {
    var addTagsMutation = `
      mutation addTags($id: ID!, $tags: [String!]!) {
        tagsAdd(id: $id, tags: $tags) {
          node {
            id
          }
          userErrors {
            message
          }
        }
      }
    `;
    var addTagsOptions = {
      "method": "POST",
      "headers": {
        "Content-Type": "application/json",
        "X-Shopify-Access-Token": SHOPIFY_ACCESS_TOKEN
      },
      "payload": JSON.stringify({
        query: addTagsMutation,
        variables: {
          id: productId,
          tags: [isProductType]
        }
      })
    };
    var addTagsResponse = UrlFetchApp.fetch(url, addTagsOptions);
    var addTagsData = JSON.parse(addTagsResponse.getContentText());
    if (addTagsData.data.tagsAdd.userErrors.length != 0) {
      console.log("Failed to add new tag " + productId);
      console.log("Error: " + addTagsData.data.tagsAdd.userErrors[0].message);
    }
  }

  // Update metafield
  if (isProductType !== '') {
    var updateMetafieldsMutation = `
      mutation metafieldsSet($metafields: [MetafieldsSetInput!]!) {
        metafieldsSet(metafields: $metafields) {
          metafields {
            key
            namespace
            value
          }
          userErrors {
            field
            message
            code
          }
        }
      }
    `;
    var updateMetafieldsOptions = {
      "method": "POST",
      "headers": {
        "Content-Type": "application/json",
        "X-Shopify-Access-Token": SHOPIFY_ACCESS_TOKEN
      },
      "payload": JSON.stringify({
        query: updateMetafieldsMutation,
        variables: {
          metafields: [
            {
              ownerId: productId,
              namespace: "pmp",
              key: "performanceLabel",
              value: JSON.stringify([isProductType]),
              type: "list.single_line_text_field"
            }
          ]
        }
      })
    };
    var updateMetafieldsResponse = UrlFetchApp.fetch(url, updateMetafieldsOptions);
    var updateMetafieldsData = JSON.parse(updateMetafieldsResponse.getContentText());
    if (updateMetafieldsData.data.metafieldsSet.userErrors.length > 0) {
      console.log("Failed to update metafield for product " + productId);
      console.log("Error: " + updateMetafieldsData.data.metafieldsSet.userErrors[0].message);
    }
  }
}