{ "edges": [ { "to": "b2000001-1000-0000-0000-000000000003", "from": "b2000001-1000-0000-0000-000000000002", "toPort": "0", "fromPort": "0" }, { "to": "b2000001-1000-0000-0000-000000000002", "from": "b2000001-1000-0000-0000-000000000001", "toPort": "0", "fromPort": "0" }, { "to": "b2000001-1000-0000-0000-000000000005", "from": "b2000001-1000-0000-0000-000000000004", "toPort": "0", "fromPort": "0" }, { "to": "b2000001-1000-0000-0000-000000000006", "from": "b2000001-1000-0000-0000-000000000005", "toPort": "0", "fromPort": "0" }, { "to": "b2000001-1000-0000-0000-000000000004", "from": "b2000001-1000-0000-0000-000000000003", "toPort": "0", "fromPort": "0" }, { "to": "b2000001-1000-0000-0000-000000000007", "from": "b2000001-1000-0000-0000-000000000006", "toPort": "0", "fromPort": "0" }, { "to": "b2000001-1000-0000-0000-000000000008", "from": "b2000001-1000-0000-0000-000000000006", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "b2000001-1000-0000-0000-000000000001", "name": "Trigger every Monday at 8am", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "field": "weeks", "triggerAtDay": 1, "triggerAtHour": 8 } ] } }, "position": [ 80, 300 ] }, { "id": "b2000001-1000-0000-0000-000000000002", "name": "Set report config variables", "type": "set", "params": { "mode": "manual", "options": {}, "assignments": { "assignments": [ { "id": "cfg-1", "name": "googleSheetId", "type": "string", "value": "YOUR_GOOGLE_SHEET_ID_HERE" }, { "id": "cfg-2", "name": "sheetName", "type": "string", "value": "Weekly Metrics" }, { "id": "cfg-3", "name": "slackChannel", "type": "string", "value": "#business-updates" }, { "id": "cfg-4", "name": "emailRecipients", "type": "string", "value": "user@example.com" }, { "id": "cfg-5", "name": "companyName", "type": "string", "value": "Your Company" } ] }, "duplicateItem": false, "includeOtherFields": true }, "position": [ 300, 300 ] }, { "id": "b2000001-1000-0000-0000-000000000003", "name": "Fetch metrics from Google Sheets", "type": "integration", "params": { "options": {}, "service": "googleSheets", "operation": "read", "sheetName": { "__rl": true, "mode": "name", "value": "={{ $json.sheetName }}" }, "documentId": { "__rl": true, "mode": "id", "value": "={{ $json.googleSheetId }}" } }, "position": [ 540, 300 ] }, { "id": "b2000001-1000-0000-0000-000000000004", "name": "Calculate weekly comparisons", "type": "code", "params": { "jsCode": "// Calculate this week vs last week metrics\nconst items = $input.all().map(i => i.json);\nconst config = $('Set report config variables').first().json;\nconst sorted = items.sort((a, b) => new Date(b.Date || b.date) - new Date(a.Date || a.date));\nconst thisWeek = sorted.slice(0, 7);\nconst lastWeek = sorted.slice(7, 14);\nfunction sumField(arr, field) { return arr.reduce((sum, row) => sum + (parseFloat(row[field]) || 0), 0); }\nfunction pctChange(current, previous) { if (previous === 0) return current > 0 ? 100 : 0; return Math.round(((current - previous) / previous) * 100); }\nconst metrics = { thisWeek: { revenue: sumField(thisWeek, 'Revenue'), leads: sumField(thisWeek, 'Leads'), conversions: sumField(thisWeek, 'Conversions'), adSpend: sumField(thisWeek, 'Ad Spend'), supportTickets: sumField(thisWeek, 'Support Tickets') }, lastWeek: { revenue: sumField(lastWeek, 'Revenue'), leads: sumField(lastWeek, 'Leads'), conversions: sumField(lastWeek, 'Conversions'), adSpend: sumField(lastWeek, 'Ad Spend'), supportTickets: sumField(lastWeek, 'Support Tickets') } };\nmetrics.changes = { revenue: pctChange(metrics.thisWeek.revenue, metrics.lastWeek.revenue), leads: pctChange(metrics.thisWeek.leads, metrics.lastWeek.leads), conversions: pctChange(metrics.thisWeek.conversions, metrics.lastWeek.conversions), adSpend: pctChange(metrics.thisWeek.adSpend, metrics.lastWeek.adSpend), supportTickets: pctChange(metrics.thisWeek.supportTickets, metrics.lastWeek.supportTickets) };\nmetrics.thisWeek.conversionRate = metrics.thisWeek.leads > 0 ? Math.round((metrics.thisWeek.conversions / metrics.thisWeek.leads) * 100) : 0;\nmetrics.thisWeek.roas = metrics.thisWeek.adSpend > 0 ? Math.round((metrics.thisWeek.revenue / metrics.thisWeek.adSpend) * 100) / 100 : 0;\nreturn [{ json: { metrics, rawDataPoints: sorted.length, companyName: config.companyName, slackChannel: config.slackChannel, emailRecipients: config.emailRecipients, reportDate: new Date().toISOString().split('T')[0] } }];" }, "position": [ 780, 300 ] }, { "id": "b2000001-1000-0000-0000-000000000005", "name": "Generate AI performance digest", "type": "llm", "params": { "model": "gpt-4o-mini", "options": { "maxTokens": 1000, "temperature": 0.4 }, "messages": { "values": [ { "content": "=You are a business analyst writing a weekly performance digest for {{ $json.companyName }}. Write a concise, actionable report based on these metrics:\n\nTHIS WEEK:\n- Revenue: ${{ $json.metrics.thisWeek.revenue }} ({{ $json.metrics.changes.revenue }}% vs last week)\n- Leads: {{ $json.metrics.thisWeek.leads }} ({{ $json.metrics.changes.leads }}% vs last week)\n- Conversions: {{ $json.metrics.thisWeek.conversions }} ({{ $json.metrics.changes.conversions }}% vs last week)\n- Ad Spend: ${{ $json.metrics.thisWeek.adSpend }} ({{ $json.metrics.changes.adSpend }}% vs last week)\n- Support Tickets: {{ $json.metrics.thisWeek.supportTickets }} ({{ $json.metrics.changes.supportTickets }}% vs last week)\n- Conversion Rate: {{ $json.metrics.thisWeek.conversionRate }}%\n- ROAS: {{ $json.metrics.thisWeek.roas }}x\n\nFormat your response with these sections:\n1. HEADLINE: One sentence performance summary with emoji\n2. WINS: 2-3 positive highlights\n3. WATCH: 1-2 areas of concern\n4. PRIORITIES: 3 recommended actions for this week\n5. OUTLOOK: Brief 1-sentence forecast\n\nKeep it under 300 words. Be specific with numbers." } ] }, "provider": "openai", "resource": "chat", "maxTokens": 1000, "temperature": 0.4 }, "position": [ 1020, 300 ] }, { "id": "b2000001-1000-0000-0000-000000000006", "name": "Format digest for Slack and email", "type": "code", "params": { "jsCode": "// Format the AI digest for Slack and email\nconst aiResponse = $input.first().json;\nconst prev = $('Calculate weekly comparisons').first().json;\nconst digest = aiResponse.message?.content || aiResponse.text || aiResponse.content || 'Report generation failed.';\nconst slackMessage = ':bar_chart: *Weekly Performance Digest — ' + prev.reportDate + '*\\n\\n' + digest;\nconst emailHtml = '

Weekly Performance Digest — ' + prev.reportDate + '

Key Metrics

Revenue: $' + prev.metrics.thisWeek.revenue + ' (' + (prev.metrics.changes.revenue > 0 ? '+' : '') + prev.metrics.changes.revenue + '%)
Leads: ' + prev.metrics.thisWeek.leads + '
Conversions: ' + prev.metrics.thisWeek.conversions + '
ROAS: ' + prev.metrics.thisWeek.roas + 'x

' + digest + '

';\nreturn [{ json: { slackMessage, emailHtml, emailSubject: 'Weekly Performance Digest — ' + prev.reportDate, slackChannel: prev.slackChannel, emailRecipients: prev.emailRecipients, reportDate: prev.reportDate } }];" }, "position": [ 1260, 300 ] }, { "id": "b2000001-1000-0000-0000-000000000007", "name": "Post digest to Slack channel", "type": "integration", "params": { "text": "={{ $json.slackMessage }}", "channel": { "__rl": true, "mode": "name", "value": "={{ $json.slackChannel }}" }, "service": "slack", "resource": "message", "operation": "read", "otherOptions": {} }, "position": [ 1520, 200 ] }, { "id": "b2000001-1000-0000-0000-000000000008", "name": "Email digest to team", "type": "integration", "params": { "sendTo": "={{ $json.emailRecipients }}", "message": "={{ $json.emailHtml }}", "options": {}, "service": "gmail", "subject": "={{ $json.emailSubject }}", "emailType": "html", "operation": "read" }, "position": [ 1520, 420 ] } ] }

Generate weekly AI business digest from Google Sheets with GPT-4o and send to Slack and email