{ "edges": [ { "to": "f891bcde-0132-4d14-89d1-353a2eb4fe67", "from": "5c8d2cac-9352-41f8-a7b2-0306d5c48b35", "toPort": "0", "fromPort": "0" }, { "to": "f8b6b7f8-84f5-4389-97de-a2d10a453ca4", "from": "0e95dde7-7114-4cab-bd5e-4b7e48780b14", "toPort": "0", "fromPort": "0" }, { "to": "1c3e7681-f5fe-45b8-86d9-f0396514a5fc", "from": "079b7afa-9a6a-40a5-8958-aab8e56f6776", "toPort": "0", "fromPort": "0" }, { "to": "b9baded1-30df-429d-8dc7-c380f7bfd9d2", "from": "60b17335-68ae-40d0-a48f-e4565e3e369f", "toPort": "0", "fromPort": "0" }, { "to": "4b410699-974f-4cd6-ad6c-03fa40bb51f1", "from": "613e0200-dbda-4203-914f-9c4441835a69", "toPort": "0", "fromPort": "0" }, { "to": "079b7afa-9a6a-40a5-8958-aab8e56f6776", "from": "613e0200-dbda-4203-914f-9c4441835a69", "toPort": "0", "fromPort": "0" }, { "to": "a3d97281-207d-4a6a-a103-b24e94cf4cd6", "from": "f8b6b7f8-84f5-4389-97de-a2d10a453ca4", "toPort": "0", "fromPort": "0" }, { "to": "e3e9f530-2e87-4cd2-9069-a30a34edafed", "from": "b9baded1-30df-429d-8dc7-c380f7bfd9d2", "toPort": "0", "fromPort": "0" }, { "to": "613e0200-dbda-4203-914f-9c4441835a69", "from": "e3e9f530-2e87-4cd2-9069-a30a34edafed", "toPort": "0", "fromPort": "0" }, { "to": "60b17335-68ae-40d0-a48f-e4565e3e369f", "from": "a3d97281-207d-4a6a-a103-b24e94cf4cd6", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "0e95dde7-7114-4cab-bd5e-4b7e48780b14", "name": "When Daily at 7am", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "field": "hours", "triggerAtHour": 7, "triggerAtMinute": 0 } ] } }, "position": [ 12576, 4672 ] }, { "id": "f8b6b7f8-84f5-4389-97de-a2d10a453ca4", "name": "Set Inventory Settings", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "d1", "name": "daysToCalculateVelocity", "type": "number", "value": 7 }, { "id": "d3", "name": "recipientMail", "type": "string", "value": "PASTE_RECIPIENT_MAIL_HERE" }, { "id": "d4", "name": "googleSheetUrl", "type": "string", "value": "PASTE_GOOGLE_SHEET_URL_HERE" }, { "id": "d5", "name": "slackEscalationChannel", "type": "string", "value": "PASTE_SLACK_CHANNEL_ID_HERE" } ] } }, "position": [ 12800, 4672 ] }, { "id": "60b17335-68ae-40d0-a48f-e4565e3e369f", "name": "Fetch Shopify Orders", "type": "integration", "params": { "options": { "createdAtMin": "={{ $now.minus({ days: $('Set Inventory Settings').item.json.daysToCalculateVelocity }).toISO() }}" }, "service": "shopify", "operation": "getAll", "returnAll": true, "__originalType": "n8n-nodes-base.shopify", "authentication": "oAuth2" }, "position": [ 13360, 4672 ] }, { "id": "b9baded1-30df-429d-8dc7-c380f7bfd9d2", "name": "Fetch Shopify Inventory", "type": "integration", "params": { "service": "shopify", "resource": "product", "operation": "getAll", "returnAll": true, "__originalType": "n8n-nodes-base.shopify", "authentication": "oAuth2", "additionalFields": { "status": "active" } }, "position": [ 13584, 4672 ] }, { "id": "a3d97281-207d-4a6a-a103-b24e94cf4cd6", "name": "Read Inventory Logs from Sheets", "type": "integration", "params": { "options": {}, "service": "googleSheets", "operation": "read", "returnAll": true, "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "url", "value": "={{ $('Set Inventory Settings').item.json.googleSheetUrl }}" } }, "position": [ 13104, 4672 ] }, { "id": "e3e9f530-2e87-4cd2-9069-a30a34edafed", "name": "Calculate Inventory Velocity", "type": "code", "params": { "jsCode": "const config = $('Set Inventory Settings').first().json;\n\nconst orders = $('Fetch Shopify Orders')\n .all()\n .map(i => i.json);\n\nconst products = $('Fetch Shopify Inventory')\n .all()\n .map(i => i.json);\n\nconst sheetLogs = $('Read Inventory Logs from Sheets')\n .all()\n .map(i => i.json);\n\nconst velocityDays = config.daysToCalculateVelocity || 7;\nconst now = new Date();\n\n// ─────────────────────────────────────────────\n// Category Thresholds\n// ─────────────────────────────────────────────\nconst THRESHOLDS = {\n critical: 2,\n warning: 5,\n caution: 7,\n monitor: 14\n};\n\nfunction getCategory(daysLeft) {\n if (daysLeft <= THRESHOLDS.critical) return 'critical';\n if (daysLeft <= THRESHOLDS.warning) return 'warning';\n if (daysLeft <= THRESHOLDS.caution) return 'caution';\n if (daysLeft <= THRESHOLDS.monitor) return 'monitor';\n return 'healthy';\n}\n\nconst CATEGORY_EMOJI = {\n critical: '🔴',\n warning: '🟡',\n caution: '🔵',\n monitor: '🟣'\n};\n\nconst CATEGORY_ORDER = {\n critical: 0,\n warning: 1,\n caution: 2,\n monitor: 3\n};\n\n// ─────────────────────────────────────────────\n// 1. Count Sold Quantity Per Variant\n// ─────────────────────────────────────────────\nconst salesCount = {};\n\norders.forEach(order => {\n if (order.cancelled_at) return;\n if (!['paid', 'partially_paid'].includes(order.financial_status)) return;\n if (!order.line_items) return;\n\n order.line_items.forEach(item => {\n const variantId = String(item.variant_id);\n salesCount[variantId] = (salesCount[variantId] || 0) + item.quantity;\n });\n});\n\nconst orderedVariantIds = new Set(Object.keys(salesCount));\n\n// ─────────────────────────────────────────────\n// 2. Deduplicate Products\n// Shopify returnAll can return duplicate pages\n// ─────────────────────────────────────────────\nconst seenProductIds = new Set();\nconst uniqueProducts = products.filter(product => {\n if (seenProductIds.has(product.id)) return false;\n seenProductIds.add(product.id);\n return true;\n});\n\n// ─────────────────────────────────────────────\n// 3. Process Inventory & Velocity\n// ─────────────────────────────────────────────\nconst lowStockItems = [];\n\nuniqueProducts.forEach(product => {\n\n if (!product.variants) return;\n if (product.status !== 'active') return;\n\n product.variants.forEach(variant => {\n\n if (!variant.inventory_management) return;\n\n const variantId = String(variant.id);\n\n // Zero-velocity variants: flag if stock is critically low regardless of sales\n const hasRecentSales = orderedVariantIds.has(variantId);\n\n const title = `${product.title}${variant.title !== 'Default Title' ? ' - ' + variant.title : ''}`.trim();\n\n const stock = variant.inventory_quantity ?? 0;\n\n // Show variant ID suffix if no SKU set\n const sku = variant.sku && variant.sku.trim() !== ''\n ? variant.sku\n : `VAR-${variantId.slice(-6)}`;\n\n const soldInWindow = salesCount[variantId] || 0;\n const dailyVelocity = soldInWindow / velocityDays;\n\n // For zero-velocity items: only flag if stock is below minimumStockFloor\n const minimumStockFloor = 5;\n if (!hasRecentSales) {\n if (stock > minimumStockFloor) return;\n }\n\n const daysLeft = dailyVelocity > 0\n ? Math.max(0, stock / dailyVelocity)\n : 999;\n\n const category = getCategory(daysLeft);\n\n if (category === 'healthy') return;\n\n // Skip if already alerted today (UTC)\n const todayUTC = now.toISOString().split('T')[0];\n\n const alreadyAlertedToday = sheetLogs.some(row => {\n const rowDateUTC = String(row.runTimestamp || '').split('T')[0];\n if (rowDateUTC !== todayUTC) return false;\n const variantIdList = String(row.variantIds || '')\n .split(',')\n .map(id => id.trim());\n return variantIdList.includes(variantId);\n });\n\n if (alreadyAlertedToday) return;\n\n lowStockItems.push({\n variantId,\n sku,\n title,\n stock,\n dailyVelocity: parseFloat(dailyVelocity.toFixed(2)),\n daysLeft: parseFloat(daysLeft.toFixed(1)),\n category\n });\n });\n});\n\n// ─────────────────────────────────────────────\n// Stop Workflow If Nothing Found\n// ─────────────────────────────────────────────\nif (lowStockItems.length === 0) return [];\n\n// ─────────────────────────────────────────────\n// 4. Sort By Urgency\n// ─────────────────────────────────────────────\nlowStockItems.sort((a, b) => {\n const categoryCompare =\n CATEGORY_ORDER[a.category] - CATEGORY_ORDER[b.category];\n if (categoryCompare !== 0) return categoryCompare;\n return a.daysLeft - b.daysLeft;\n});\n\n// ─────────────────────────────────────────────\n// 5. Build Timestamp\n// ─────────────────────────────────────────────\nconst pad = n => String(n).padStart(2, '0');\nconst runTimestamp = `${now.getUTCFullYear()}-${pad(now.getUTCMonth() + 1)}-${pad(now.getUTCDate())}`;\n\n// ─────────────────────────────────────────────\n// 6. Alert Counts\n// ─────────────────────────────────────────────\nconst itemCount = lowStockItems.length;\nconst criticalCount = lowStockItems.filter(i => i.category === 'critical').length;\nconst warningCount = lowStockItems.filter(i => i.category === 'warning').length;\nconst cautionCount = lowStockItems.filter(i => i.category === 'caution').length;\nconst monitorCount = lowStockItems.filter(i => i.category === 'monitor').length;\n\n// ─────────────────────────────────────────────\n// 7. Generate Slack Message\n// ─────────────────────────────────────────────\nconst slackList = lowStockItems.map(item => {\n const emoji = CATEGORY_EMOJI[item.category];\n const depletionText = item.daysLeft === 0\n ? 'is already out of stock.'\n : `may go out of stock in ${item.daysLeft} days.`;\n return `${emoji} *${item.title}* (SKU: ${item.sku}) ${depletionText}`;\n}).join('\\n\\n');\n\nconst slackSummary = [\n `📦 *Inventory Alert — ${itemCount} product${itemCount > 1 ? 's' : ''} need attention*`,\n `🔴 Critical: ${criticalCount} 🟡 Warning: ${warningCount} 🔵 Caution: ${cautionCount} 🟣 Monitor: ${monitorCount}`,\n ``,\n `Based on the last ${velocityDays} days of sales:`,\n ``,\n slackList,\n ``,\n `🕐 _Predicted at ${runTimestamp} · Inventory Monitor_`\n].join('\\n');\n\n// ─────────────────────────────────────────────\n// 8. Generate Email Subject\n// ─────────────────────────────────────────────\nconst emailSubject = criticalCount > 0\n ? `🔴 CRITICAL: ${criticalCount} item${criticalCount > 1 ? 's' : ''} may stock out soon`\n : `⚠️ Inventory Alert: ${itemCount} item${itemCount > 1 ? 's' : ''} need attention`;\n\n// ─────────────────────────────────────────────\n// 9. Return Final Output\n// ─────────────────────────────────────────────\nreturn [{\n json: {\n runTimestamp,\n variantIds: lowStockItems.map(i => i.variantId).join(', '),\n totalVariantsAlerted: itemCount,\n criticalCount,\n warningCount,\n cautionCount,\n monitorCount,\n slackSummary,\n emailSubject,\n lowStockItems,\n velocityDays\n }\n}];" }, "position": [ 13824, 4672 ] }, { "id": "613e0200-dbda-4203-914f-9c4441835a69", "name": "Append Alert to Sheets", "type": "integration", "params": { "columns": { "value": { "variantIds": "={{ $json.variantIds }}", "runTimestamp": "={{ $json.runTimestamp }}", "totalVariantsAlerted": "={{ $json.totalVariantsAlerted }}" }, "schema": [ { "id": "runTimestamp", "type": "string", "display": true, "required": false, "displayName": "runTimestamp", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "variantIds", "type": "string", "display": true, "required": false, "displayName": "variantIds", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "totalVariantsAlerted", "type": "string", "display": true, "required": false, "displayName": "totalVariantsAlerted", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "googleSheets", "operation": "append", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "url", "value": "={{ $('Set Inventory Settings').item.json.googleSheetUrl }}" } }, "position": [ 14160, 4672 ] }, { "id": "4b410699-974f-4cd6-ad6c-03fa40bb51f1", "name": "Post Inventory Alert to Slack", "type": "integration", "params": { "text": "={{ $('Calculate Inventory Velocity').item.json.slackSummary }}", "select": "channel", "service": "slack", "channelId": { "__rl": true, "mode": "id", "value": "={{ $('Set Inventory Settings').item.json.slackEscalationChannel }}" }, "operation": "read", "otherOptions": { "mrkdwn": true } }, "position": [ 14544, 4672 ] }, { "id": "079b7afa-9a6a-40a5-8958-aab8e56f6776", "name": "Build Email Content", "type": "code", "params": { "jsCode": "const d = $('Calculate Inventory Velocity').first().json;\n\nconst itemRows = d.lowStockItems.map(item => {\n\n const depletionText = item.daysLeft === 0\n ? 'Already out of stock'\n : `May go out of stock in ${item.daysLeft} days`;\n\n const rowBg = {\n critical: '#fff5f5',\n warning: '#fffbeb',\n caution: '#eff6ff',\n monitor: '#faf5ff'\n };\n\n const textColor = {\n critical: '#dc2626',\n warning: '#d97706',\n caution: '#2563eb',\n monitor: '#7c3aed'\n };\n\n return `\n \n \n ${item.title}
\n SKU: ${item.sku}\n \n ${item.stock}\n ${item.dailyVelocity}/day\n ${depletionText}\n `;\n}).join('');\n\nconst emailBody = `\n\n\n\n \n \n \n \n

\n \n \n \n \n \n \n \n \n \n \n \n \n \n

Inventory Intelligence

📉 ${d.totalVariantsAlerted} Item${d.totalVariantsAlerted > 1 ? 's' : ''} Need Attention

\n Based on sales velocity over the last ${d.velocityDays} days. Items are categorised as:\n

\n 🔴 Critical — stockout in ≤ 2 days | \n 🟡 Warning — ≤ 5 days | \n 🔵 Caution — ≤ 7 days | \n 🟣 Monitor — ≤ 14 days\n

\n \n \n \n \n \n \n \n ${itemRows}\n

\n Product\n

\n Stock\n

\n Burn Rate\n

\n Status\n

\n 🕐 Monitored at ${d.runTimestamp} · Reorder Alert Monitor\n

\n\n`;\n\nreturn [{\n json: {\n ...d,\n emailBody\n }\n}];" }, "position": [ 14336, 4848 ] }, { "id": "1c3e7681-f5fe-45b8-86d9-f0396514a5fc", "name": "Send Stock Alert Email", "type": "integration", "params": { "sendTo": "={{ $('Set Inventory Settings').item.json.recipientMail }}", "message": "={{ $json.emailBody }}", "options": { "emailType": "html" }, "service": "gmail", "subject": "={{ $json.emailSubject }}", "operation": "read" }, "position": [ 14544, 4848 ] }, { "id": "5c8d2cac-9352-41f8-a7b2-0306d5c48b35", "name": "On Global Error", "type": "trigger", "params": { "kind": "n8n-nodes-base.errorTrigger" }, "position": [ 12576, 5472 ] }, { "id": "f891bcde-0132-4d14-89d1-353a2eb4fe67", "name": "Post Error to Slack", "type": "integration", "params": { "text": "={{ '🚨 *Workflow Error — Inventory Monitor*\\n\\n*Failed Node:* ' + $json.execution.error.node.name + '\\n*Error:* ' + $json.execution.error.message }}", "select": "channel", "service": "slack", "channelId": { "__rl": true, "mode": "id", "value": "" }, "operation": "read", "otherOptions": { "mrkdwn": true } }, "position": [ 12832, 5472 ] } ] }

Send predictive Shopify low-stock alerts with Google Sheets, Slack, and Gmail