{ "edges": [ { "to": "74d41cf2-0720-41f1-8812-489aa40c4e31", "from": "529e2213-0c10-4474-aebb-0bcc8a84be04", "toPort": "0", "fromPort": "0" }, { "to": "8839952d-ad31-4913-a4f6-2dd6274c0232", "from": "32971d84-6da7-4a23-a8ac-b3f199fed010", "toPort": "0", "fromPort": "0" }, { "to": "d9ab4d26-70d1-46f7-8699-517291e6d2dc", "from": "74d41cf2-0720-41f1-8812-489aa40c4e31", "toPort": "0", "fromPort": "0" }, { "to": "7a362464-dac7-48e7-a0c3-169c51456125", "from": "3544a224-7ab2-4c3e-8cfe-7a4311dd8e11", "toPort": "0", "fromPort": "0" }, { "to": "4057508b-9721-4988-9c84-1c43c432842c", "from": "7a362464-dac7-48e7-a0c3-169c51456125", "toPort": "0", "fromPort": "0" }, { "to": "529e2213-0c10-4474-aebb-0bcc8a84be04", "from": "0003e316-2e42-4244-b721-415e51006de1", "toPort": "0", "fromPort": "ai_languageModel" }, { "to": "3544a224-7ab2-4c3e-8cfe-7a4311dd8e11", "from": "075b271c-3fbc-40da-9605-6c522f545531", "toPort": "0", "fromPort": "0" }, { "to": "32971d84-6da7-4a23-a8ac-b3f199fed010", "from": "4057508b-9721-4988-9c84-1c43c432842c", "toPort": "0", "fromPort": "0" }, { "to": "92dbdec2-6a0b-42a1-941e-0d5b9d01dcad", "from": "8839952d-ad31-4913-a4f6-2dd6274c0232", "toPort": "0", "fromPort": "0" }, { "to": "7182d6fa-864d-4c30-8b0e-c3576f3763dd", "from": "92dbdec2-6a0b-42a1-941e-0d5b9d01dcad", "toPort": "0", "fromPort": "0" }, { "to": "529e2213-0c10-4474-aebb-0bcc8a84be04", "from": "7182d6fa-864d-4c30-8b0e-c3576f3763dd", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "3544a224-7ab2-4c3e-8cfe-7a4311dd8e11", "name": "HTTP Request", "type": "http", "params": { "url": "https://api.brevo.com/v3/emailCampaigns", "options": {}, "sendQuery": true, "authentication": "predefinedCredentialType", "queryParameters": { "parameters": [ { "name": "status", "value": "sent" }, { "name": "limit", "value": "10" } ] }, "nodeCredentialType": "sendInBlueApi" }, "position": [ 224, 0 ] }, { "id": "4057508b-9721-4988-9c84-1c43c432842c", "name": "Append row in sheet", "type": "integration", "params": { "columns": { "value": { "Date": "={{ $json.Date }}", "Sent": "={{ $json.Sent }}", "Delivered": "={{ $json.Delivered }}", "Open Rate": "={{ $json[\"Open Rate\"] }}", "Click Rate": "={{ $json[\"Click Rate\"] }}", "Bounce Rate": "={{ $json[\"Bounce Rate\"] }}", "Campaign Name": "={{ $json[\"Campaign Name\"] }}" }, "schema": [ { "id": "Date", "type": "string", "display": true, "required": false, "displayName": "Date", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Campaign Name", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Campaign Name", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Sent", "type": "string", "display": true, "required": false, "displayName": "Sent", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Delivered", "type": "string", "display": true, "required": false, "displayName": "Delivered", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Open Rate", "type": "string", "display": true, "required": false, "displayName": "Open Rate", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Click Rate", "type": "string", "display": true, "required": false, "displayName": "Click Rate", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Bounce Rate", "type": "string", "display": true, "required": false, "displayName": "Bounce Rate", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "googleSheets", "operation": "append", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list", "value": "YOUR_GOOGLE_SHEET_ID", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit", "cachedResultName": "Brevo Data" } }, "position": [ 704, 0 ] }, { "id": "529e2213-0c10-4474-aebb-0bcc8a84be04", "name": "AI Agent", "type": "llm", "params": { "text": "=You are a Senior Marketing Analyst. Review this comprehensive campaign data for the last 7 days:\n\n| Campaign | Sent | Delivered | Open Rate | Click Rate | Bounce Rate |\n| :--- | :--- | :--- | :--- | :--- | :--- |\n{{ $json[\"Campaign Name\"].map((name, i) => `| ${name} | ${$json[\"Sent\"][i]} | ${$json[\"Delivered\"][i]} | ${($json[\"Open Rate\"][i] * 100).toFixed(1)}% | ${($json[\"Click Rate\"][i] * 100).toFixed(1)}% | ${($json[\"Bounce Rate\"][i] * 100).toFixed(1)}% |`).join('\\n') }}\n\nWrite a 3-sentence executive summary for the Marketing Lead:\n1. Identify the campaign with the best \"Engagement Depth\" (high Click-to-Open ratio).\n2. Alert the lead if any \"Bounce Rate\" is above or equal to 2% (indicating list health issues).\n3. Suggest a specific strategy for next week based on these conversion trends.", "prompt": "=You are a Senior Marketing Analyst. Review this comprehensive campaign data for the last 7 days:\n\n| Campaign | Sent | Delivered | Open Rate | Click Rate | Bounce Rate |\n| :--- | :--- | :--- | :--- | :--- | :--- |\n{{ $json[\"Campaign Name\"].map((name, i) => `| ${name} | ${$json[\"Sent\"][i]} | ${$json[\"Delivered\"][i]} | ${($json[\"Open Rate\"][i] * 100).toFixed(1)}% | ${($json[\"Click Rate\"][i] * 100).toFixed(1)}% | ${($json[\"Bounce Rate\"][i] * 100).toFixed(1)}% |`).join('\\n') }}\n\nWrite a 3-sentence executive summary for the Marketing Lead:\n1. Identify the campaign with the best \"Engagement Depth\" (high Click-to-Open ratio).\n2. Alert the lead if any \"Bounce Rate\" is above or equal to 2% (indicating list health issues).\n3. Suggest a specific strategy for next week based on these conversion trends.", "options": {}, "provider": "openai", "promptType": "define", "approximated": true }, "position": [ 1792, 0 ] }, { "id": "0003e316-2e42-4244-b721-415e51006de1", "name": "Groq Chat Model", "type": "llm", "params": { "model": "llama-3.3-70b-versatile", "options": {}, "provider": "openai" }, "position": [ 1864, 224 ] }, { "id": "74d41cf2-0720-41f1-8812-489aa40c4e31", "name": "Edit Fields", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "40b7af5a-c109-4205-aff1-6a98df5b1be8", "name": "emailBoady", "type": "string", "value": "=

\n \n

Weekly Marketing Intelligence

Reporting Period: {{ $today.minus({days: 7}).toFormat('dd LLL') }} - {{ $today.toFormat('dd LLL yyyy') }}

\n\n

\n 🤖 AI Executive Summary\n

\n \"{{ $json.output }}\"\n

\n\n

Campaign Metrics Breakdown

\n \n \n \n \n \n \n \n \n \n \n \n {{ $node[\"Aggregate weekly data\"].json[\"Campaign Name\"].map((name, i) => `\n \n \n \n \n \n \n \n `).join('') }}\n \n

Campaign	Delivered	Open Rate	Click Rate	Bounce
${name}	${$node[\"Aggregate weekly data\"].json[\"Delivered\"][i]}	${($node[\"Aggregate weekly data\"].json[\"Open Rate\"][i] * 100).toFixed(1)}%	${($node[\"Aggregate weekly data\"].json[\"Click Rate\"][i] * 100).toFixed(1)}%	= 0.02 ? '#d93025' : '#5f6368' };\">\n ${($node[\"Aggregate weekly data\"].json[\"Bounce Rate\"][i] * 100).toFixed(1)}%\n

\n\n

\n This automated report was generated by n8n Intelligence. Data sourced from Brevo & Google Sheets.\n

" } ] } }, "position": [ 2144, 0 ] }, { "id": "d9ab4d26-70d1-46f7-8699-517291e6d2dc", "name": "Send a message", "type": "integration", "params": { "sendTo": "user@example.com", "message": "={{ $json.emailBoady }}", "options": {}, "service": "gmail", "subject": "=🚀 Weekly Marketing Performance Brief: {{ $today.toFormat('dd/MM/yyyy') }}", "operation": "read" }, "position": [ 2368, 0 ] }, { "id": "8839952d-ad31-4913-a4f6-2dd6274c0232", "name": "Get row(s) in sheet", "type": "integration", "params": { "options": {}, "service": "googleSheets", "operation": "read", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit#gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list", "value": "YOUR_GOOGLE_SHEET_ID", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit", "cachedResultName": "Brevo Data" } }, "position": [ 1152, 0 ] }, { "id": "32971d84-6da7-4a23-a8ac-b3f199fed010", "name": "Aggregate", "type": "passthrough", "params": { "mode": "aggregate", "options": {}, "aggregate": "aggregateAllItemData" }, "position": [ 960, 0 ] }, { "id": "92dbdec2-6a0b-42a1-941e-0d5b9d01dcad", "name": "Filtered 7 days data", "type": "code", "params": { "jsCode": "// 1. Get the date for exactly 7 days ago\nconst sevenDaysAgo = new Date();\nsevenDaysAgo.setDate(sevenDaysAgo.getDate() - 7);\nsevenDaysAgo.setHours(0, 0, 0, 0);\n\nconst weeklyData = [];\n\nfor (const item of items) {\n const rowDate = new Date(item.json.Date);\n\n if (rowDate >= sevenDaysAgo) {\n weeklyData.push({\n json: {\n ...item.json,\n aiContext: `${item.json['Campaign Name']} sent on ${item.json.Date} had a ${item.json['Open Rate']} open rate.`\n }\n });\n }\n}\n\nreturn weeklyData;" }, "position": [ 1344, 0 ] }, { "id": "7182d6fa-864d-4c30-8b0e-c3576f3763dd", "name": "Aggregate weekly data", "type": "passthrough", "params": { "mode": "aggregate", "options": {}, "fieldsToAggregate": { "fieldToAggregate": [ { "fieldToAggregate": "Campaign Name" }, { "fieldToAggregate": "Sent" }, { "fieldToAggregate": "Delivered" }, { "fieldToAggregate": "Open Rate" }, { "fieldToAggregate": "Click Rate" }, { "fieldToAggregate": "Bounce Rate" } ] } }, "position": [ 1568, 0 ] }, { "id": "7a362464-dac7-48e7-a0c3-169c51456125", "name": "Data Cleaning1", "type": "code", "params": { "jsCode": "const campaigns = items[0].json.campaigns || [];\n\nreturn campaigns.map(campaign => {\n const stats = campaign.statistics.campaignStats[0] || {};\n \n const sent = stats.sent || 0;\n const delivered = stats.delivered || 0;\n const opens = stats.uniqueViews || 0;\n const clicks = stats.uniqueClicks || 0;\n const bounces = (stats.softBounces || 0) + (stats.hardBounces || 0);\n\n const openRate = delivered > 0 ? (opens / delivered) : 0;\n const clickRate = delivered > 0 ? (clicks / delivered) : 0;\n const bounceRate = delivered > 0 ? (bounces / delivered) : 0;\n\n return {\n json: {\n \"Campaign Name\": campaign.name,\n \"Date\": campaign.sentDate.split('T')[0],\n \"Sent\": sent,\n \"Delivered\": delivered,\n \"Open Rate\": openRate,\n \"Click Rate\": clickRate,\n \"Bounce Rate\": bounceRate\n }\n };\n});" }, "position": [ 448, 0 ] }, { "id": "075b271c-3fbc-40da-9605-6c522f545531", "name": "Schedule Trigger", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "field": "weeks", "triggerAtDay": [ 1 ], "triggerAtHour": 8 } ] } }, "position": [ 0, 0 ] } ] }

Generate weekly Brevo campaign analytics reports with Groq and Google Sheets