{ "edges": [ { "to": "49684349-9340-4d8b-8447-4bf72b3868c7", "from": "3e556607-b051-41c2-b055-9596380c2093", "toPort": "0", "fromPort": "0" }, { "to": "3e556607-b051-41c2-b055-9596380c2093", "from": "e13b8672-1cf2-4cc6-82b9-474cc5bafa91", "toPort": "0", "fromPort": "0" }, { "to": "e13b8672-1cf2-4cc6-82b9-474cc5bafa91", "from": "5b3d568f-922b-4a31-88fd-79c1ffc77bf4", "toPort": "0", "fromPort": "0" }, { "to": "36a1e222-29ba-4299-89d6-bcf32209c695", "from": "84d19fd3-fb3c-4581-8a23-9f2f04f79df7", "toPort": "0", "fromPort": "0" }, { "to": "bfe10aa6-803b-4541-acef-51354faf1847", "from": "3738983e-3783-4e50-b667-4cbbb413d5fc", "toPort": "0", "fromPort": "0" }, { "to": "605bbc71-d07d-4750-b1a2-7c5162d9222f", "from": "36a1e222-29ba-4299-89d6-bcf32209c695", "toPort": "0", "fromPort": "0" }, { "to": "3738983e-3783-4e50-b667-4cbbb413d5fc", "from": "605bbc71-d07d-4750-b1a2-7c5162d9222f", "toPort": "0", "fromPort": "0" }, { "to": "5b3d568f-922b-4a31-88fd-79c1ffc77bf4", "from": "bfe10aa6-803b-4541-acef-51354faf1847", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "3e556607-b051-41c2-b055-9596380c2093", "name": "Format Email1", "type": "set", "params": { "fields": { "values": [ { "name": "=emailSubject", "stringValue": "=📊 Weekly Sales Report: {{ $node[\"Sales Analytics Engine\"].json.weekRange }}" }, { "name": "emailBody", "stringValue": "=

\n \n

Weekly Performance Report

\n\n

\n \n

🤖 AI Analyst Insights

\"{{ $json.content.parts[0].text }}\"

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

\n This Week\n

₹{{ $('Sales Analytics Engine').item.json.currentTotalFormatted }}

\n Growth\n

\n {{ $('Sales Analytics Engine').item.json.growthStatus }} {{ $('Sales Analytics Engine').item.json.growth }}\n

\n\n

📊 Performance: Week vs Week

\n \n

JSON.parse($('Sales Analytics Engine').item.json.prevCatMap)[cat] || 0)\n },\n {\n label: 'This Week',\n backgroundColor: 'rgba(46,204,113,0.5)',\n data: JSON.parse($('Sales Analytics Engine').item.json.catValues)\n }\n ]\n }\n })) }}\" \n width=\"600\" \n style=\"display: block; width: 100%; max-width: 100%; border-radius: 8px; border: 1px solid #eee;\" \n alt=\"Sales Chart\"\n >\n\n

📦 Detailed Category Breakdown

\n \n \n \n \n \n \n \n \n \n \n {{ $('Sales Analytics Engine').item.json.categoryTableHtml }}\n \n

Category	Prev Week	This Week	Change

\n\n

\n Automated Business Intelligence Report

" } ] }, "options": {} }, "position": [ 15152, 5824 ] }, { "id": "49684349-9340-4d8b-8447-4bf72b3868c7", "name": "Send Email1", "type": "integration", "params": { "sendTo": "YOUR_GMAIL_RECIPIENT_EMAIL", "message": "={{ $json.emailBody }}", "options": {}, "service": "gmail", "subject": "={{ $json.emailSubject }}", "operation": "read" }, "position": [ 15376, 5824 ] }, { "id": "36a1e222-29ba-4299-89d6-bcf32209c695", "name": "Get row(s) in sheet1", "type": "integration", "params": { "options": {}, "service": "googleSheets", "operation": "read", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list", "value": "YOUR_GOOGLE_SHEETS_DOCUMENT_ID", "cachedResultUrl": "", "cachedResultName": "Sales Weekly Data" } }, "position": [ 13680, 5824 ] }, { "id": "605bbc71-d07d-4750-b1a2-7c5162d9222f", "name": "Removing Empty Rows1", "type": "code", "params": { "jsCode": "// 1. Get all items from the Google Sheets node\nconst items = $input.all();\n\n// 2. Define the cleaning logic\nconst cleanedData = items\n .filter(item => {\n // REMOVE EMPTY ROWS: Only keep rows that have a Date and a Product Type\n const data = item.json;\n return data[\"Date\"] && data[\"Product type\"] && data[\"Date\"].toString().trim() !== \"\";\n })\n .map(item => {\n const data = item.json;\n\n // CLEAN COMMAS & CURRENCY: Convert \"₹28,000\" -> 28000 (Number)\n let rawSales = data[\"Total sales (₹)\"] ? data[\"Total sales (₹)\"].toString() : \"0\";\n let cleanSales = Number(rawSales.replace(/[₹,]/g, ''));\n\n // CLEAN QUANTITY: Ensure \"8\" is a Number, not a string\n let cleanQty = Number(data[\"Net quantity\"]) || 0;\n\n // RETURN CLEANED OBJECT: This creates the \"Table\" format n8n likes\n return {\n json: {\n ...data, // Keeps original columns (Date, Day, etc.)\n \"Total sales (₹)\": cleanSales, // Overwrites with pure number\n \"Net quantity\": cleanQty, // Overwrites with pure number\n \"IsCleaned\": true // Flag for your own tracking\n }\n };\n });\n\nreturn cleanedData;" }, "position": [ 13904, 5824 ] }, { "id": "3738983e-3783-4e50-b667-4cbbb413d5fc", "name": "Filter Latest Week1", "type": "code", "params": { "jsCode": "// Define the full range: 19-Mar to 1-Apr\nconst rangeStart = new Date(2026, 2, 19); \nconst rangeEnd = new Date(2026, 3, 1);\nrangeEnd.setHours(23, 59, 59);\n\nfunction parseSheetDate(dateStr) {\n const months = { \"Jan\":0, \"Feb\":1, \"Mar\":2, \"Apr\":3, \"May\":4, \"Jun\":5, \"Jul\":6, \"Aug\":7, \"Sep\":8, \"Oct\":9, \"Nov\":10, \"Dec\":11 };\n const parts = dateStr.split('-');\n return new Date(parts[2], months[parts[1]], parts[0]);\n}\n\nreturn items.filter(item => {\n const rowDate = parseSheetDate(item.json[\"Date\"]);\n return rowDate >= rangeStart && rowDate <= rangeEnd;\n});" }, "position": [ 14128, 5824 ] }, { "id": "e13b8672-1cf2-4cc6-82b9-474cc5bafa91", "name": "HTTP Request1", "type": "http", "params": { "url": "https://quickchart.io/chart", "options": { "response": { "response": { "responseFormat": "file" } } }, "sendQuery": true, "queryParameters": { "parameters": [ { "name": "c", "value": "={\n \"type\": \"bar\",\n \"data\": {\n \"labels\": {{ $('Sales Analytics Engine').item.json.catLabels }},\n \"datasets\": [\n {\n \"label\": \"Last Week (₹)\",\n \"backgroundColor\": \"rgba(231, 76, 60, 0.5)\",\n \"data\": {{ JSON.stringify(JSON.parse($('Sales Analytics Engine').item.json.catLabels).map(cat => JSON.parse($('Sales Analytics Engine').item.json.prevCatMap)[cat] || 0)) }}\n },\n {\n \"label\": \"This Week (₹)\",\n \"backgroundColor\": \"rgba(46, 204, 113, 0.5)\",\n \"data\": {{ $('Sales Analytics Engine').item.json.catValues }}\n }\n ]\n },\n \"options\": {\n \"title\": { \"display\": true, \"text\": \"Category Performance: Week vs Week\" }\n }\n}" } ] } }, "position": [ 14928, 5824 ] }, { "id": "84d19fd3-fb3c-4581-8a23-9f2f04f79df7", "name": "Schedule Trigger1", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "field": "weeks", "triggerAtDay": [ 1 ], "triggerAtHour": 21 } ] } }, "position": [ 13456, 5824 ] }, { "id": "5b3d568f-922b-4a31-88fd-79c1ffc77bf4", "name": "Message a model", "type": "llm", "params": { "modelId": { "__rl": true, "mode": "list", "value": "models/gemini-2.5-flash", "cachedResultName": "models/gemini-2.5-flash" }, "options": {}, "messages": { "values": [ { "content": "=You are an expert Retail Analyst. Based on the following data, write a detailed 3-sentence summary.\n\nThe Data: {{ $json.aiContext }}\n\nInstructions: > 1. Start with the overall performance ({{ $json.growthStatus }} by {{ $json.growth }}).\n2. Specifically identify which category performed significantly better and which one performed lower than last week.\n3. Mention the total volume of {{ $json.totalQty }} units sold.\n4. End with a focused business recommendation for the coming week." } ] }, "provider": "google" }, "position": [ 14576, 5824 ] }, { "id": "bfe10aa6-803b-4541-acef-51354faf1847", "name": "Sales Analytics Engine", "type": "code", "params": { "jsCode": "let currentTotal = 0;\nlet previousTotal = 0;\nlet totalQty = 0;\n\n// Track categories for both weeks\nconst currentCatMap = {}; \nconst prevCatMap = {}; \nconst dailyTrend = { \"Monday\":0, \"Tuesday\":0, \"Wednesday\":0, \"Thursday\":0, \"Friday\":0, \"Saturday\":0, \"Sunday\":0 };\n\n// 1. Define the split date\nconst splitDate = new Date(2026, 2, 26); \n\nfor (const item of items) {\n const months = { \"Jan\":0, \"Feb\":1, \"Mar\":2, \"Apr\":3, \"May\":4, \"Jun\":5, \"Jul\":6, \"Aug\":7, \"Sep\":8, \"Oct\":9, \"Nov\":10, \"Dec\":11 };\n const parts = item.json[\"Date\"].split('-');\n const rowDate = new Date(parts[2], months[parts[1]], parts[0]);\n const sales = Number(item.json[\"Total sales (₹)\"]) || 0;\n const qty = Number(item.json[\"Net quantity\"]) || 0;\n const cat = item.json[\"Product type\"];\n\n if (rowDate >= splitDate) {\n // --- CURRENT WEEK ---\n currentTotal += sales;\n totalQty += qty;\n currentCatMap[cat] = (currentCatMap[cat] || 0) + sales;\n const dayName = item.json[\"Day\"];\n if (dailyTrend.hasOwnProperty(dayName)) dailyTrend[dayName] += sales;\n } else {\n // --- PREVIOUS WEEK ---\n previousTotal += sales;\n prevCatMap[cat] = (prevCatMap[cat] || 0) + sales;\n }\n}\n\n// 2. Performance Metrics & Color Logic\nconst isGrowthPositive = currentTotal >= previousTotal;\nconst growthValue = previousTotal === 0 ? 100 : ((currentTotal - previousTotal) / previousTotal * 100).toFixed(1);\n\nconst growthStatus = isGrowthPositive ? \"📈 Up\" : \"📉 Down\";\nconst growthColor = isGrowthPositive ? \"#27ae60\" : \"#e74c3c\"; // Green if up, Red if down\n\n// 3. Build Category Comparison Table\nlet categoryRows = \"\";\nconst allCategories = Array.from(new Set([...Object.keys(currentCatMap), ...Object.keys(prevCatMap)]));\nallCategories.sort((a, b) => (currentCatMap[b] || 0) - (currentCatMap[a] || 0));\n\nconst aiCategorySummary = [];\n\nfor (const cat of allCategories) {\n const curVal = currentCatMap[cat] || 0;\n const prevVal = prevCatMap[cat] || 0;\n \n let catChange = \"New\";\n if (prevVal > 0) {\n catChange = (((curVal - prevVal) / prevVal) * 100).toFixed(0) + \"%\";\n }\n \n const statusIcon = curVal >= prevVal ? \"▲\" : \"▼\";\n const statusColor = curVal >= prevVal ? \"#27ae60\" : \"#e74c3c\";\n\n categoryRows += `\n \n ${cat}\n ₹${prevVal.toLocaleString('en-IN')}\n ₹${curVal.toLocaleString('en-IN')}\n \n ${statusIcon} ${catChange}\n \n `;\n\n aiCategorySummary.push(`${cat}: ₹${curVal} (vs ₹${prevVal} last week)`);\n}\n\n// 4. Final Output\nreturn [{\n json: {\n currentTotalFormatted: currentTotal.toLocaleString('en-IN'),\n previousTotalFormatted: previousTotal.toLocaleString('en-IN'),\n totalQty,\n growth: (growthValue > 0 ? \"+\" : \"\") + growthValue + \"%\",\n growthStatus,\n growthColor, // <--- EXPLICIT COLOR PASSED TO HTML\n isGrowthPositive,\n prevCatMap: JSON.stringify(prevCatMap),\n \n aiContext: `This week: ₹${currentTotal}. Last week: ₹${previousTotal}. Growth: ${growthValue}%. Details per category: ${aiCategorySummary.join(', ')}.`,\n \n chartData: [\"Monday\", \"Tuesday\", \"Wednesday\", \"Thursday\", \"Friday\", \"Saturday\", \"Sunday\"].map(d => dailyTrend[d]).join(','),\n catLabels: JSON.stringify(allCategories),\n catValues: JSON.stringify(allCategories.map(c => currentCatMap[c] || 0)),\n \n categoryTableHtml: categoryRows,\n weekRange: \"26-Mar-2026 to 01-Apr-2026\"\n }\n}];" }, "position": [ 14352, 5824 ] } ] }

Generate weekly sales insights with Google Sheets, Gemini AI and Gmail