{ "edges": [ { "to": "5bcf9c46-80b9-4c46-999e-ab02c2743f9b", "from": "08c06ca5-dfaf-4b10-ae33-df0fb3e2cc6d", "toPort": "0", "fromPort": "0" }, { "to": "fe6d16bc-d256-4c3f-9945-7be037564dda", "from": "9686ae09-74f9-4f99-b92a-457b83f1cfaa", "toPort": "0", "fromPort": "0" }, { "to": "4da571ca-aae7-4c74-b2a4-b19bf50da20a", "from": "8ac7bc5d-c409-44b2-8176-24ecbd2ae969", "toPort": "0", "fromPort": "ai_languageModel" }, { "to": "9686ae09-74f9-4f99-b92a-457b83f1cfaa", "from": "ad3b35be-95a2-4fa5-813a-62910b68b002", "toPort": "0", "fromPort": "0" }, { "to": "4da571ca-aae7-4c74-b2a4-b19bf50da20a", "from": "5bcf9c46-80b9-4c46-999e-ab02c2743f9b", "toPort": "0", "fromPort": "1" }, { "to": "08c06ca5-dfaf-4b10-ae33-df0fb3e2cc6d", "from": "75c2b1e5-d2c2-4867-93e7-41d81f3db16a", "toPort": "0", "fromPort": "0" }, { "to": "f6b4adc7-0709-4058-8ff6-9107ccfbef6b", "from": "ed54b5d9-678f-4a17-8b56-d96cc7dda6f8", "toPort": "0", "fromPort": "0" }, { "to": "75c2b1e5-d2c2-4867-93e7-41d81f3db16a", "from": "ed54b5d9-678f-4a17-8b56-d96cc7dda6f8", "toPort": "0", "fromPort": "0" }, { "to": "911581aa-7453-44ca-b5c6-db1a6ce8efd7", "from": "ed54b5d9-678f-4a17-8b56-d96cc7dda6f8", "toPort": "0", "fromPort": "0" }, { "to": "5bcf9c46-80b9-4c46-999e-ab02c2743f9b", "from": "fe6d16bc-d256-4c3f-9945-7be037564dda", "toPort": "0", "fromPort": "0" }, { "to": "ed54b5d9-678f-4a17-8b56-d96cc7dda6f8", "from": "4da571ca-aae7-4c74-b2a4-b19bf50da20a", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "8ac7bc5d-c409-44b2-8176-24ecbd2ae969", "name": "Groq Chat Model1", "type": "llm", "params": { "model": "llama-3.3-70b-versatile", "options": {}, "provider": "openai" }, "position": [ 18496, 6112 ] }, { "id": "f6b4adc7-0709-4058-8ff6-9107ccfbef6b", "name": "Send a message2", "type": "integration", "params": { "sendTo": "your-email", "message": "={{ $json.email_data.full_report }}", "options": {}, "service": "gmail", "subject": "Revenue Forecast Summary Data", "operation": "read" }, "position": [ 19088, 5856 ] }, { "id": "911581aa-7453-44ca-b5c6-db1a6ce8efd7", "name": "Send a message3", "type": "integration", "params": { "text": "={{ $json.email_data.full_report }}", "select": "channel", "service": "slack", "channelId": { "__rl": true, "mode": "list", "value": "gmail-id", "cachedResultName": "all-n8ntest" }, "operation": "read", "otherOptions": {}, "authentication": "oAuth2" }, "position": [ 19088, 6064 ] }, { "id": "5bcf9c46-80b9-4c46-999e-ab02c2743f9b", "name": "Loop Over Items1", "type": "passthrough", "params": { "mode": "noOp", "options": {} }, "position": [ 18352, 5936 ] }, { "id": "08c06ca5-dfaf-4b10-ae33-df0fb3e2cc6d", "name": "Wait1", "type": "passthrough", "params": { "mode": "noOp" }, "position": [ 19472, 6048 ] }, { "id": "ed54b5d9-678f-4a17-8b56-d96cc7dda6f8", "name": "Format AI response1", "type": "code", "params": { "jsCode": "// Extract AI response text\nconst aiOutput = $input.first().json.output;\n\n// Helper function to extract section content\nfunction extractSection(text, sectionTitle) {\n const regex = new RegExp(`## ${sectionTitle}([\\\\s\\\\S]*?)(?=##|$)`, 'i');\n const match = text.match(regex);\n return match ? match[1].trim() : 'Not available';\n}\n\n// Helper function to extract bullet points\nfunction extractBullets(text) {\n const bullets = text.match(/[\\*\\+\\-]\\s+\\*\\*(.+?)\\*\\*/g) || [];\n return bullets.map(b => b.replace(/[\\*\\+\\-]\\s+\\*\\*(.+?)\\*\\*/, '$1')).join(', ');\n}\n\n// Helper function to clean markdown formatting\nfunction cleanText(text) {\n return text\n .replace(/\\*\\*/g, '') // Remove bold markers\n .replace(/\\*/g, '') // Remove italic markers\n .replace(/\\t/g, '') // Remove tabs\n .replace(/\\n\\n+/g, '\\n') // Remove multiple newlines\n .trim();\n}\n\n// Extract revenue figures\nfunction extractRevenue(text) {\n const bestCase = text.match(/Best Case:\\s*₹?([\\d,]+)/i);\n const likelyCase = text.match(/Likely Case:\\s*₹?([\\d,]+)/i);\n const worstCase = text.match(/Worst Case:\\s*₹?([\\d,]+)/i);\n const weighted = text.match(/Weighted Pipeline Value:\\s*₹?([\\d,]+)/i);\n \n return {\n best_case: bestCase ? `₹${bestCase[1]}` : '₹0',\n likely_case: likelyCase ? `₹${likelyCase[1]}` : '₹0',\n worst_case: worstCase ? `₹${worstCase[1]}` : '₹0',\n weighted_value: weighted ? `₹${weighted[1]}` : '₹0'\n };\n}\n\n// Extract high-risk deals\nfunction extractHighRiskDeals(text) {\n const dealName = text.match(/\\*\\*Deal Name:\\*\\*\\s*(.+)/);\n const dealAmount = text.match(/\\*\\*Deal Amount:\\*\\*\\s*₹?([\\d,]+)/);\n const riskFactors = text.match(/\\*\\*Risk Factors:\\*\\*([\\s\\S]*?)(?=\\*\\*Recommended|$)/);\n \n let riskText = '';\n if (dealName && dealAmount) {\n riskText = `${dealName[1]} (₹${dealAmount[1]})`;\n if (riskFactors) {\n const factors = riskFactors[1].match(/\\+\\s+(.+)/g);\n if (factors) {\n riskText += ' - ' + factors.map(f => f.replace(/\\+\\s+/, '')).join('; ');\n }\n }\n }\n return riskText || 'No high-risk deals identified';\n}\n\n// Extract top recommendations\nfunction extractRecommendations(text) {\n const recommendations = text.match(/\\*\\*Top 3 Actions.*?:\\*\\*([\\s\\S]*?)(?=\\*\\*Deals Requiring|$)/);\n if (recommendations) {\n const actions = recommendations[1].match(/\\d+\\.\\s+\\*\\*(.+?)\\*\\*/g);\n if (actions) {\n return actions.map((a, i) => {\n const title = a.match(/\\*\\*(.+?)\\*\\*/)[1];\n const desc = recommendations[1].match(new RegExp(`${i + 1}\\\\.\\\\s+\\\\*\\\\*${title}\\\\*\\\\*:?\\\\s*(.+?)(?=\\\\n\\\\s*\\\\d+\\\\.|$)`, 's'));\n return `${i + 1}. ${title}${desc ? ': ' + cleanText(desc[1]) : ''}`;\n }).join('\\n');\n }\n }\n return 'No specific recommendations';\n}\n\n// Extract revenue drivers\nfunction extractRevenueDrivers(text) {\n const section = extractSection(aiOutput, 'Revenue Drivers Analysis');\n const keyDeals = section.match(/\\*\\*Key Deals:\\*\\*\\s*(.+)/);\n const trends = section.match(/\\*\\*Revenue Trends:\\*\\*\\s*(.+)/);\n \n let drivers = '';\n if (trends) drivers += cleanText(trends[1]) + '. ';\n if (keyDeals) drivers += 'Key deals: ' + cleanText(keyDeals[1]);\n \n return drivers || 'Insufficient data for analysis';\n}\n\n// Parse all sections\nconst revenue = extractRevenue(aiOutput);\nconst highRiskDeals = extractHighRiskDeals(extractSection(aiOutput, 'High-Risk Deals'));\nconst revenueDrivers = extractRevenueDrivers(aiOutput);\nconst recommendations = extractRecommendations(aiOutput);\n\n// Format for Google Sheets\nconst formattedData = {\n timestamp: new Date().toLocaleString('en-IN', { timeZone: 'Asia/Kolkata' }),\n forecast_period: new Date().toLocaleString('en-IN', { month: 'long', year: 'numeric' }),\n expected_revenue: revenue.likely_case,\n best_case: revenue.best_case,\n worst_case: revenue.worst_case,\n weighted_pipeline: revenue.weighted_value,\n high_risk_deals: highRiskDeals,\n revenue_drivers: revenueDrivers,\n recommendations: recommendations,\n full_ai_response: cleanText(aiOutput),\n report_sent_to: 'user@example.com, user@example.com'\n};\n\n// Format for Email HTML (clean sections)\nconst emailData = {\n timestamp: formattedData.timestamp,\n forecast_summary: `\n

Expected Revenue: ${revenue.likely_case}

Best Case: ${revenue.best_case}

Worst Case: ${revenue.worst_case}

Weighted Pipeline: ${revenue.weighted_value}

\n `,\n high_risk_section: `\n

${highRiskDeals}

\n `,\n revenue_drivers_section: `\n

${revenueDrivers}

\n `,\n recommendations_section: `\n

${recommendations}

\n `,\n full_report: aiOutput.replace(/\\n/g, '
').replace(/\\*\\*(.+?)\\*\\*/g, '$1')\n};\n\n// Return both formats\nreturn [\n {\n json: {\n // For Google Sheets\n sheets_data: formattedData,\n \n // For Email\n email_data: emailData,\n \n // Raw data for debugging\n raw_output: aiOutput\n }\n }\n];" }, "position": [ 18864, 5952 ] }, { "id": "75c2b1e5-d2c2-4867-93e7-41d81f3db16a", "name": "Append row in sheet", "type": "integration", "params": { "columns": { "value": { "Status": "Email Sent", "Best_Case": "={{ $json.sheets_data.best_case }}", "Time Stamp": "={{ $json.sheets_data.timestamp }}", "Worst_Case": "={{ $json.sheets_data.worst_case }}", "Report_Sent_To": "={{ $json.sheets_data.report_sent_to }}", "Forecast_Period": "={{ $json.sheets_data.forecast_period }}", "High_Risk_Deals": "={{ $json.sheets_data.high_risk_deals }}", "Recommendations": "={{ $json.sheets_data.recommendations }}", "Revenue_Drivers": "={{ $json.sheets_data.revenue_drivers }}", "Expected_Revenue": "={{ $json.sheets_data.expected_revenue }}", "Full_AI_Response": "={{ $json.sheets_data.full_ai_response }}", "Weighted_Pipeline": "={{ $json.sheets_data.weighted_pipeline }}" }, "schema": [ { "id": "Time Stamp", "type": "string", "display": true, "required": false, "displayName": "Time Stamp", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Forecast_Period", "type": "string", "display": true, "required": false, "displayName": "Forecast_Period", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Expected_Revenue", "type": "string", "display": true, "required": false, "displayName": "Expected_Revenue", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Best_Case", "type": "string", "display": true, "required": false, "displayName": "Best_Case", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Worst_Case", "type": "string", "display": true, "required": false, "displayName": "Worst_Case", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Weighted_Pipeline", "type": "string", "display": true, "required": false, "displayName": "Weighted_Pipeline", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "High_Risk_Deals", "type": "string", "display": true, "required": false, "displayName": "High_Risk_Deals", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Revenue_Drivers", "type": "string", "display": true, "required": false, "displayName": "Revenue_Drivers", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Recommendations", "type": "string", "display": true, "required": false, "displayName": "Recommendations", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Full_AI_Response", "type": "string", "display": true, "required": false, "displayName": "Full_AI_Response", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Report_Sent_To", "type": "string", "display": true, "required": false, "displayName": "Report_Sent_To", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Status", "type": "string", "display": true, "required": false, "displayName": "Status", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "googleSheets", "operation": "append", "sheetName": { "__rl": true, "mode": "list", "value": 1485545756, "cachedResultUrl": "sheet-url", "cachedResultName": "sheet-name" }, "documentId": { "__rl": true, "mode": "list", "value": "sheet-value", "cachedResultUrl": "sheet-url", "cachedResultName": "sheet-name" } }, "position": [ 19248, 5952 ] }, { "id": "ad3b35be-95a2-4fa5-813a-62910b68b002", "name": "HubSpot Trigger1", "type": "integration", "params": { "service": "hubspottrigger", "eventsUi": { "eventValues": [ { "name": "deal.creation" } ] }, "operation": "run", "__originalType": "n8n-nodes-base.hubspotTrigger", "additionalFields": {} }, "position": [ 17808, 5936 ] }, { "id": "9686ae09-74f9-4f99-b92a-457b83f1cfaa", "name": "Get many deals1", "type": "integration", "params": { "filters": {}, "service": "hubspot", "resource": "deal", "operation": "getAll" }, "position": [ 17984, 5936 ] }, { "id": "fe6d16bc-d256-4c3f-9945-7be037564dda", "name": "Format Hubspot Data1", "type": "code", "params": { "jsCode": "// Helper: map region to currency\nfunction getCurrency(region) {\n const map = {\n \"India\": \"INR\",\n \"USA\": \"USD\",\n \"United States\": \"USD\",\n \"UK\": \"GBP\",\n \"United Kingdom\": \"GBP\",\n \"UAE\": \"AED\"\n };\n return map[region] || \"USD\"; // default\n}\n\nreturn items.map(item => {\n const deal = item.json.properties || {};\n\n // Extract values safely\n const dealName = deal.dealname || \"Unknown Deal\";\n const dealAmount = Number(deal.amount || 0);\n\n const stage = deal.dealstage || \"Unknown\";\n const closeDate = deal.closedate\n ? new Date(Number(deal.closedate)).toISOString().split(\"T\")[0]\n : null;\n\n const probability = deal.hs_probability\n ? Math.round(Number(deal.hs_probability) * 100)\n : 0;\n\n const owner = deal.hubspot_owner_id || \"Unassigned\";\n\n const region =\n deal.country ||\n deal.region ||\n deal.hs_country_region ||\n \"Unknown\";\n\n const currency = getCurrency(region);\n\n return {\n json: {\n \"Deal Name\": dealName,\n \"Customer Name\": deal.associatedcompanyname || \"N/A\",\n \"Deal Amount\": dealAmount,\n \"Currency\": currency,\n \"Stage\": stage,\n \"Close Date\": closeDate,\n \"Probability (%)\": probability,\n \"Sales Owner\": owner,\n \"Region\": region\n }\n };\n});\n" }, "position": [ 18160, 5936 ] }, { "id": "4da571ca-aae7-4c74-b2a4-b19bf50da20a", "name": "AI Revenue Forecast & Risk Analysis1", "type": "llm", "params": { "text": "=Analyze the following sales pipeline data and generate a revenue forecast report:\n\n**Current Date:** {{$now.format('YYYY-MM-DD')}}\n**Forecast Period:** Next 30 days\n\n**Sales Pipeline Data:**\n{{JSON.stringify($json)}}\n\nPlease provide:\n\n1. **Revenue Forecast Summary**\n - Expected revenue (best case, likely case, worst case)\n - Weighted pipeline value\n - Conversion rate trends\n\n2. **High-Risk Deals** (identify deals that may slip or not close)\n - Deal name, amount, and risk factors\n - Recommended actions to mitigate risks\n\n3. **Revenue Drivers Analysis**\n - Why is revenue increasing or decreasing compared to last period?\n - Which deals are moving the needle?\n - Pipeline health assessment\n\n4. **Key Recommendations**\n - Top 3 actions for sales leadership\n - Deals requiring immediate attention\n\nFormat your response in clear sections with bullet points for easy reading.", "prompt": "=Analyze the following sales pipeline data and generate a revenue forecast report:\n\n**Current Date:** {{$now.format('YYYY-MM-DD')}}\n**Forecast Period:** Next 30 days\n\n**Sales Pipeline Data:**\n{{JSON.stringify($json)}}\n\nPlease provide:\n\n1. **Revenue Forecast Summary**\n - Expected revenue (best case, likely case, worst case)\n - Weighted pipeline value\n - Conversion rate trends\n\n2. **High-Risk Deals** (identify deals that may slip or not close)\n - Deal name, amount, and risk factors\n - Recommended actions to mitigate risks\n\n3. **Revenue Drivers Analysis**\n - Why is revenue increasing or decreasing compared to last period?\n - Which deals are moving the needle?\n - Pipeline health assessment\n\n4. **Key Recommendations**\n - Top 3 actions for sales leadership\n - Deals requiring immediate attention\n\nFormat your response in clear sections with bullet points for easy reading.", "options": { "systemMessage": "=You are an expert Revenue Forecasting AI specialized in sales pipeline analysis. Your role is to:\n\n1. Analyze sales deal data (amount, stage, close date, probability)\n2. Generate accurate revenue forecasts for the next month/quarter\n3. Identify high-risk deals that may not close\n4. Explain key revenue drivers (why revenue is increasing or decreasing)\n5. Provide actionable insights for sales leadership\n\n**Output Requirements:**\n- Be concise and executive-ready\n- Use clear, business-friendly language\n- Highlight critical risks and opportunities\n- Provide specific deal-level recommendations\n- Format responses in structured sections\n\n**Tone:** Professional, data-driven, and action-oriented" }, "provider": "openai", "promptType": "define", "approximated": true, "systemMessage": "=You are an expert Revenue Forecasting AI specialized in sales pipeline analysis. Your role is to:\n\n1. Analyze sales deal data (amount, stage, close date, probability)\n2. Generate accurate revenue forecasts for the next month/quarter\n3. Identify high-risk deals that may not close\n4. Explain key revenue drivers (why revenue is increasing or decreasing)\n5. Provide actionable insights for sales leadership\n\n**Output Requirements:**\n- Be concise and executive-ready\n- Use clear, business-friendly language\n- Highlight critical risks and opportunities\n- Provide specific deal-level recommendations\n- Format responses in structured sections\n\n**Tone:** Professional, data-driven, and action-oriented" }, "position": [ 18560, 5952 ] } ] }

Send AI revenue forecast alerts from HubSpot via Gmail, Slack and Sheets