Generate Daily Product Rankings Based On Sales Data

This scheduled trigger runs once per day at midnight. The timing provides complete coverage of the previous day's sales and processes rankings before peak shopping hours.

Customization: You can adjust the schedule to run more frequently (like "hourly" for real-time rankings) or at different times (like early morning before staff arrives).

This transform step sets up configuration variables using custom JavaScript (from transform.js). The mapping creates three variables:

1. Days Back: Number of days to analyze for sales (e.g., 7 for weekly rankings, 30 for monthly)
2. Limit: Maximum number of products to rank (e.g., 10 for top 10, 50 for top 50)
3. Collection ID: The Shopify collection ID where best-sellers are featured

These variables are used throughout the workflow in SQL queries, loops, and API calls, enabling easy configuration changes in one central location.

Setup requirement: You need to configure these values in the transform mapping to match your business needs and collection ID.

This step runs a SQL query against the "Shopify Orders" MESA Data table.

Query breakdown:

SELECT 
    ROW_NUMBER() OVER (ORDER BY sum("Product Quantity") DESC) AS "Best Sellers Index",
    "Product ID",
    "Product Title",
    sum("Product Quantity") AS "Quantity"
FROM "Shopify Orders" 
WHERE "Order Created At" > CURRENT_DATE - INTERVAL '{{Days Back}} days' 
GROUP BY "Product ID", "Product Title"
ORDER BY "Quantity" DESC
LIMIT {{Limit}}

Query components:

1. ROW_NUMBER(): Assigns ranking position (1, 2, 3, etc.)
2. sum("Product Quantity"): Calculates total units sold per product
3. WHERE clause: Filters to recent orders within configured time period
4. GROUP BY: Aggregates sales by product
5. ORDER BY: Sorts by quantity descending (highest sales first)
6. LIMIT: Returns only top N products

The result is a ranked list of products with their sales quantities and ranking positions.

Data requirement: This assumes you have a MESA Data table called "Shopify Orders" populated with order data including Product ID, Product Title, Product Quantity, and Order Created At fields.

This step fetches products from the Shopify collection specified in the Collection ID variable. It returns up to 100 products including product IDs, titles, and existing metafields. This list is used to reset all rankings to zero before applying new rankings.

Why reset existing rankings: Products that were previously in the top sellers but have dropped out need their rankings cleared. This step ensures clean slate before new rankings apply.

This loop iterates through all products retrieved from the collection. For every product, the loop executes a metafield update that sets the ranking to zero. This clears old rankings before new rankings apply.

This step sets a product metafield to zero for each product in the collection.

Metafield structure:

1. namespace: "custom"
2. key: "best_sellers_index"
3. type: "number_integer"
4. value: "0"

This metafield can be displayed on product pages, collection pages, or used for sorting/filtering. Resetting to zero ensures products that fall out of top sellers don't retain old rankings.

This loop iterates through the ranked products from the SQL query. For every best-seller, the loop executes product retrieval, metafield update, and collection reordering. This applies the new rankings.

This step retrieves the product record using the Product ID from the SQL results. It returns complete product data including product ID (in both REST and GraphQL formats), title, variants, and existing metafields.

This step sets the product metafield to the ranking position from the SQL query.

Metafield structure:

1. namespace: "custom"
2. key: "best_sellers_index"
3. type: "number_integer"
4. value: The "Best Sellers Index" from SQL (1, 2, 3, etc.)

This metafield can be referenced in Shopify themes to display "#1 Best Seller" badges, sort products, or create filtered views.

This custom JavaScript step uses Shopify's GraphQL API to reorder products in the collection.

// GraphQL mutation for collection reordering
let query = `mutation collectionReorderProducts($id: ID!, $moves: [MoveInput!]!) {
  collectionReorderProducts(id: $id, moves: $moves) {
    job { id }
    userErrors { field message }
  }
}`;

// Execute mutation with collection ID and new position
const response = ShopifyGraphql.send(query, {
  "id": "gid://shopify/Collection/" + Collection ID,
  "moves": [{
    "id": "gid://shopify/Product/" + product ID,
    "newPosition": ranking position
  }]
});

The code constructs a GraphQL mutation that moves the product to the position matching its ranking (product ranked #1 moves to position 1, #2 to position 2, etc.).

GraphQL requirement: This uses Shopify's collectionReorderProducts mutation which is more reliable than REST API for collection ordering.