Umsatzzuordnung und ROI-Rechner
Dies ist ein Content Creation, Multimodal AI-Bereich Automatisierungsworkflow mit 15 Nodes. Hauptsächlich werden Code, Gmail, Slack, Airtable, SplitInBatches und andere Nodes verwendet. Automatisierung der Marketing-Einnahmenzuweisung und ROI-Analyse mit Airtable und Slack
- •Google-Konto + Gmail API-Anmeldedaten
- •Slack Bot Token oder Webhook URL
- •Airtable API Key
Verwendete Nodes (15)
Kategorie
{
"id": "amCkThATp8WSOqFz",
"meta": {
"instanceId": "aa63297b1b91e208b636dd926ed91cc26a0d2bb14cd724f85cdd3d9fcfd9cbe7",
"templateCredsSetupCompleted": true
},
"name": "Revenue Attribution & ROI Calculator",
"tags": [],
"nodes": [
{
"id": "15341b2a-14b2-4341-8b18-8cd1fc5c79f1",
"name": "Zeitplan-Trigger",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-400,
144
],
"parameters": {
"rule": {
"interval": [
{}
]
}
},
"typeVersion": 1.2
},
{
"id": "639b483f-1347-4361-990a-499d7a985398",
"name": "Search Lead Source",
"type": "n8n-nodes-base.airtable",
"position": [
176,
144
],
"parameters": {
"base": {
"__rl": true,
"mode": "list",
"value": "appeCsmVosYvWkVM3",
"cachedResultUrl": "https://airtable.com/appeCsmVosYvWkVM3",
"cachedResultName": "Contacts"
},
"table": {
"__rl": true,
"mode": "list",
"value": "tblPecJqyJn6lmSDj",
"cachedResultUrl": "https://airtable.com/appeCsmVosYvWkVM3/tblPecJqyJn6lmSDj",
"cachedResultName": "Lead Sources"
},
"options": {},
"operation": "search"
},
"credentials": {
"airtableTokenApi": {
"id": "ifs1pAy5yAfPBZtY",
"name": "Airtable Personal Access Token account"
}
},
"typeVersion": 2.1
},
{
"id": "d2904611-761a-4c85-8445-b2f60caf5672",
"name": "Code in JavaScript",
"type": "n8n-nodes-base.code",
"position": [
400,
144
],
"parameters": {
"jsCode": "// @ts-nocheck\n\n// Access data from specific nodes by their names\nconst dealsData = $('Deals').all();\nconst leadSourcesData = $('Search Lead Source').all();\n\n// Create a map for lead sources\nconst sourceMap = new Map();\n\nleadSourcesData.forEach((item) => {\n const fields = item.json.fields || item.json;\n const sourceName = fields['Source Name'] || fields['Lead Source'];\n \n sourceMap.set(sourceName, {\n sourceType: fields['Source Type'],\n cost: parseFloat(fields['Cost per Lead']) || 0,\n totalLeads: parseInt(fields['Total Leads Generated']) || 0\n });\n});\n\nconst sourceRevenue = new Map();\n\ndealsData.forEach((item) => {\n const fields = item.json.fields || item.json;\n const source = fields['Deal Source'] || fields['Lead Source'];\n const revenue = parseFloat(fields['Deal Value']) || 0;\n \n if (!sourceRevenue.has(source)) {\n sourceRevenue.set(source, {\n totalRevenue: 0,\n dealCount: 0,\n source: source\n });\n }\n \n const current = sourceRevenue.get(source);\n current.totalRevenue += revenue;\n current.dealCount += 1;\n});\n\n// Calculate ROI for each source\n/** @type {any[]} */\nconst results = [];\n\nsourceRevenue.forEach((data, sourceName) => {\n const sourceInfo = sourceMap.get(sourceName) || { cost: 0, totalLeads: 0 };\n \n const totalCost = sourceInfo.cost * sourceInfo.totalLeads;\n const roi = totalCost > 0 ? ((data.totalRevenue - totalCost) / totalCost) * 100 : 0;\n const avgDealSize = data.dealCount > 0 ? data.totalRevenue / data.dealCount : 0;\n const conversionRate = sourceInfo.totalLeads > 0 ? (data.dealCount / sourceInfo.totalLeads) * 100 : 0;\n \n results.push({\n json: {\n source: sourceName,\n totalRevenue: data.totalRevenue,\n dealCount: data.dealCount,\n avgDealSize: avgDealSize,\n totalCost: totalCost,\n roi: parseFloat(roi.toFixed(2)),\n conversionRate: parseFloat(conversionRate.toFixed(2)),\n updated_at: new Date().toISOString()\n }\n });\n});\n\n// Sort by ROI descending\nresults.sort((a, b) => b.json.roi - a.json.roi);\n\nreturn results;"
},
"typeVersion": 2
},
{
"id": "0542491a-d85d-4431-8ac0-fa8278a58088",
"name": "Deals",
"type": "n8n-nodes-base.airtable",
"position": [
-80,
144
],
"parameters": {
"base": {
"__rl": true,
"mode": "list",
"value": "appeCsmVosYvWkVM3",
"cachedResultUrl": "https://airtable.com/appeCsmVosYvWkVM3",
"cachedResultName": "Contacts"
},
"table": {
"__rl": true,
"mode": "list",
"value": "tblJlUYIVVJrYrUZl",
"cachedResultUrl": "https://airtable.com/appeCsmVosYvWkVM3/tblJlUYIVVJrYrUZl",
"cachedResultName": "Deals"
},
"options": {},
"operation": "search",
"filterByFormula": "=AND({Stage} = \"Closed Won\", IS_AFTER({Actual Close Date}, DATEADD(NOW(), -30, 'days')))"
},
"credentials": {
"airtableTokenApi": {
"id": "ifs1pAy5yAfPBZtY",
"name": "Airtable Personal Access Token account"
}
},
"typeVersion": 2.1
},
{
"id": "e92d2b3c-16ac-4979-8368-4208b533bab0",
"name": "Über Elemente schleifen",
"type": "n8n-nodes-base.splitInBatches",
"position": [
624,
144
],
"parameters": {
"options": {}
},
"typeVersion": 3
},
{
"id": "fbe7af8c-9d26-4f68-a78b-f35ece1c958e",
"name": "Search records",
"type": "n8n-nodes-base.airtable",
"position": [
832,
160
],
"parameters": {
"base": {
"__rl": true,
"mode": "list",
"value": "appeCsmVosYvWkVM3",
"cachedResultUrl": "https://airtable.com/appeCsmVosYvWkVM3",
"cachedResultName": "Contacts"
},
"table": {
"__rl": true,
"mode": "list",
"value": "tblPecJqyJn6lmSDj",
"cachedResultUrl": "https://airtable.com/appeCsmVosYvWkVM3/tblPecJqyJn6lmSDj",
"cachedResultName": "Lead Sources"
},
"options": {},
"operation": "search",
"filterByFormula": "={Source Type} = \"{{ $json.source }}\""
},
"credentials": {
"airtableTokenApi": {
"id": "ifs1pAy5yAfPBZtY",
"name": "Airtable Personal Access Token account"
}
},
"typeVersion": 2.1
},
{
"id": "85233782-5af1-44e7-80e6-4385c5c0f279",
"name": "Update record",
"type": "n8n-nodes-base.airtable",
"position": [
1040,
160
],
"parameters": {
"base": {
"__rl": true,
"mode": "list",
"value": "appeCsmVosYvWkVM3",
"cachedResultUrl": "https://airtable.com/appeCsmVosYvWkVM3",
"cachedResultName": "Contacts"
},
"table": {
"__rl": true,
"mode": "list",
"value": "YOUR_AIRTABLE_VALUE",
"cachedResultUrl": "https://airtable.com/appeCsmVosYvWkVM3/tblPecJqyJn6lmSDj",
"cachedResultName": "Lead Sources"
},
"columns": {
"value": {
"id": "={{ $json.id }}",
"Cost per Lead": "={{ $json['Cost per Lead'] }}",
"Conversion rate": "={{ $json['Conversion rate'] }}",
"Total Leads Generated": "={{ $json['Total Leads Generated'] }}",
"Total Revenue Attributed": "={{ $json['Total Revenue Attributed'] }}"
},
"schema": [
{
"id": "id",
"type": "string",
"display": true,
"removed": false,
"readOnly": true,
"required": false,
"displayName": "id",
"defaultMatch": true
},
{
"id": "Source Name",
"type": "string",
"display": true,
"removed": false,
"readOnly": false,
"required": false,
"displayName": "Source Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Source Type",
"type": "options",
"display": true,
"options": [
{
"name": "Paid Advertising",
"value": "Paid Advertising"
},
{
"name": "Organic Search",
"value": "Organic Search"
},
{
"name": "Referral ",
"value": "Referral "
},
{
"name": "Direct Traffic",
"value": "Direct Traffic"
},
{
"name": "Social Media",
"value": "Social Media"
},
{
"name": "Email Marketing",
"value": "Email Marketing"
},
{
"name": "Content Marketing",
"value": "Content Marketing"
},
{
"name": "Events/Trade Shows",
"value": "Events/Trade Shows"
}
],
"removed": true,
"readOnly": false,
"required": false,
"displayName": "Source Type",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Cost per Lead",
"type": "number",
"display": true,
"removed": false,
"readOnly": false,
"required": false,
"displayName": "Cost per Lead",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Conversion rate",
"type": "number",
"display": true,
"removed": false,
"readOnly": false,
"required": false,
"displayName": "Conversion rate",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Total Leads Generated",
"type": "number",
"display": true,
"removed": false,
"readOnly": false,
"required": false,
"displayName": "Total Leads Generated",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Total Revenue Attributed",
"type": "number",
"display": true,
"removed": false,
"readOnly": false,
"required": false,
"displayName": "Total Revenue Attributed",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "ROI",
"type": "string",
"display": true,
"removed": false,
"readOnly": true,
"required": false,
"displayName": "ROI",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"id"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update"
},
"credentials": {
"airtableTokenApi": {
"id": "ifs1pAy5yAfPBZtY",
"name": "Airtable Personal Access Token account"
}
},
"typeVersion": 2.1
},
{
"id": "3e7d6497-552d-4639-a911-23ce6412ee90",
"name": "Send a message",
"type": "n8n-nodes-base.slack",
"position": [
1264,
160
],
"webhookId": "ec7a05db-673f-46b4-ae45-d29ced093b6a",
"parameters": {
"text": "=📊 Weekly Revenue Attribution Report - {{ new Date().toLocaleDateString() }} \n\nSource: {{ $json.fields['Source Type'] }}\nRevenue: ${{ $json.fields['Total Revenue Attributed'] }}\nROI: {{ $json.fields.ROI }}% \nConversion: {{ $json.fields['Conversion rate'] }}%",
"select": "channel",
"channelId": {
"__rl": true,
"mode": "list",
"value": "C09JRB0G41J",
"cachedResultName": "sales-analytics"
},
"otherOptions": {},
"authentication": "oAuth2"
},
"credentials": {
"slackOAuth2Api": {
"id": "2wgflAhMrOB8rL2A",
"name": "Slack account"
}
},
"typeVersion": 2.3
},
{
"id": "9eb3a37c-bb4b-4c82-967a-d0c365456eea",
"name": "Send a message1",
"type": "n8n-nodes-base.gmail",
"position": [
1472,
160
],
"webhookId": "4d7bb8f7-5c99-4176-a587-f43366aa8677",
"parameters": {
"sendTo": "yourname@company.com",
"message": "=Hi Management,<br><br> \n\nIncase you missed it, an update was sent to the sales-analytics Channel.<br><br>\n\nRegards.",
"options": {
"appendAttribution": false
},
"subject": "Sales Analytics Update"
},
"credentials": {
"gmailOAuth2": {
"id": "RF7J7ND9aW8kJnUo",
"name": "Gmail account"
}
},
"typeVersion": 2.1
},
{
"id": "b9f57a73-014e-4364-ac84-e51895d8f7d7",
"name": "Haftnotiz4",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1072,
-784
],
"parameters": {
"color": 2,
"width": 752,
"height": 752,
"content": "# 📊 REVENUE ATTRIBUTION & ROI CALCULATOR\n\nThis workflow calculates which lead sources generate actual revenue.\n\nRuns: Daily at midnight\nDuration: ~2-3 minutes\nPurpose: Update Lead Sources table with ROI metrics\n\n## Requirements:\n✅ Airtable base with Deals & Lead Sources tables\n✅ Closed Won deals from last 30 days\n✅ Cost data for each marketing source\n\n## What gets calculated:\n- Total revenue per source\n- ROI percentage\n- Conversion rates\n- Average deal size\n\n## 🚀 Getting Started Checklist\n\n- [ ] **Gmail account** with API access enabled\n- [ ] **Airtable Account [https://airtable.com/]**\n- [ ] **N8N instance** running\n- [ ] **Slack workspace [https://slack.com/intl/en-gb/]** \n- [ ] **Import this workflow** and configure credentials"
},
"typeVersion": 1
},
{
"id": "68282373-6e14-4b27-8e7c-17caf82c1dc9",
"name": "Haftnotiz5",
"type": "n8n-nodes-base.stickyNote",
"position": [
-192,
-352
],
"parameters": {
"width": 512,
"height": 592,
"content": "## 📥 STEP 1 & 2: COLLECT DATA FROM AIRTABLE\n\nNODE 1 - Fetch Closed Won Deals:\nGets deals from last 30 days where Stage = \"Closed Won\"\n\nRequired fields:\n- Stage (Single select)\n- Deal Value (Currency)\n- Deal Source (Single select)\n- Actual Close Date (Date)\n\nNODE 2 - Fetch Lead Sources:\nGets all marketing sources with cost data\n\nRequired fields:\n- Source Name (Single line text)\n- Cost per Lead (Currency)\n- Total Leads Generated (Number)\n\n⚠️ Deal Source values must EXACTLY match Source Name\n (case-sensitive, no extra spaces)"
},
"typeVersion": 1
},
{
"id": "91441ad1-38f5-4b5b-a103-66f5b769d3d4",
"name": "Haftnotiz6",
"type": "n8n-nodes-base.stickyNote",
"position": [
352,
-336
],
"parameters": {
"color": 4,
"width": 432,
"height": 560,
"content": "## 🧮 STEP 3 & 4: CALCULATE ROI & PROCESS\n\nNODE 3 - Code: Calculate Metrics\nFor each source, calculates:\n- Total Revenue = Sum of Deal Values\n- Total Cost = Cost per Lead × Total Leads \n- ROI = ((Revenue - Cost) / Cost) × 100\n- Conversion Rate = (Deals / Leads) × 100\n- Avg Deal Size = Revenue / Deal Count\n\nNODE 4 - Loop: Process Each Source\nSplits results array to update sources individually\n\n🔧 CUSTOMIZATION:\n- Edit ROI formula in Code node (line 35-42)\n- Change sorting logic (line 50-55)\n- Adjust date range in Deals node filter\n\n💡 Add // @ts-nocheck at top of Code if errors appear"
},
"typeVersion": 1
},
{
"id": "f992e5a9-3a68-4b07-86a2-12b07ea3e417",
"name": "Haftnotiz7",
"type": "n8n-nodes-base.stickyNote",
"position": [
816,
-272
],
"parameters": {
"color": 6,
"width": 800,
"height": 464,
"content": "## UPDATE DATA & SEND REPORT\n\nSearches to ensure the lead source exists, if it doesn't exist, it gets skipped to the next. \nUpdate record node updates the existing lead source record with the calculated metrics.\n\nFields updated:\n- Total Revenue Attributed\n- Conversion Rate\n- ROI\n\nNotifications (OPTIONAL):\nSlack sends report of top 3 performing sources to the #sales-analytics channel.\nGmail sends a personal mail to the CEO, incase the slack analytics report was missed.\n\n\n⚠️ Field names must match Airtable EXACTLY\n (case-sensitive)\n\n💡 Remove Slack and email nodes if not needed."
},
"typeVersion": 1
},
{
"id": "77f9cd46-e398-4151-a549-4d929979a703",
"name": "Haftnotiz9",
"type": "n8n-nodes-base.stickyNote",
"position": [
1664,
-496
],
"parameters": {
"width": 432,
"height": 640,
"content": "## ⚠️ TROUBLESHOOTING GUIDE\n\nISSUE: No deals returned from first node\nFIX: \n- Check date filter (last 30 days)\n- Verify \"Closed Won\" spelling in Stage\n- Ensure Actual Close Date is filled\n\nISSUE: Code node errors\nFIX:\n- Add // @ts-nocheck at top of code\n- Check field names match Airtable (case-sensitive)\n- Verify numeric fields contain numbers not text\n\nISSUE: Updates not writing\nFIX:\n- Check Airtable API permissions\n- Verify Record ID mapping in Update node\n- Confirm field names match exactly\n\nISSUE: Wrong calculations\nFIX:\n- Verify Cost per Lead and Total Leads have data\n- Check Deal Source matches Source Name exactly\n- Review Code node logic (line 35-55)\n\n📧 Need help? Full docs: https://www.notion.so/RevOps-Intelligence-Hub-Complete-Documentation-282ca81d5bda804a9ccdfc974b6f089d?source=copy_link"
},
"typeVersion": 1
},
{
"id": "3265038a-842d-47fc-bb2b-8c604709f009",
"name": "Haftnotiz",
"type": "n8n-nodes-base.stickyNote",
"position": [
-496,
80
],
"parameters": {
"color": 3,
"width": 256,
"height": 224,
"content": "## Start Here"
},
"typeVersion": 1
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "08aa22ad-e397-4ad5-9cb3-03743078508f",
"connections": {
"0542491a-d85d-4431-8ac0-fa8278a58088": {
"main": [
[
{
"node": "639b483f-1347-4361-990a-499d7a985398",
"type": "main",
"index": 0
}
]
]
},
"85233782-5af1-44e7-80e6-4385c5c0f279": {
"main": [
[
{
"node": "3e7d6497-552d-4639-a911-23ce6412ee90",
"type": "main",
"index": 0
}
]
]
},
"fbe7af8c-9d26-4f68-a78b-f35ece1c958e": {
"main": [
[
{
"node": "85233782-5af1-44e7-80e6-4385c5c0f279",
"type": "main",
"index": 0
}
]
]
},
"3e7d6497-552d-4639-a911-23ce6412ee90": {
"main": [
[
{
"node": "9eb3a37c-bb4b-4c82-967a-d0c365456eea",
"type": "main",
"index": 0
}
]
]
},
"Loop Over Items": {
"main": [
[
{
"node": "fbe7af8c-9d26-4f68-a78b-f35ece1c958e",
"type": "main",
"index": 0
}
],
[
{
"node": "Loop Over Items",
"type": "main",
"index": 0
}
]
]
},
"Schedule Trigger": {
"main": [
[
{
"node": "0542491a-d85d-4431-8ac0-fa8278a58088",
"type": "main",
"index": 0
}
]
]
},
"d2904611-761a-4c85-8445-b2f60caf5672": {
"main": [
[
{
"node": "Loop Over Items",
"type": "main",
"index": 0
}
]
]
},
"639b483f-1347-4361-990a-499d7a985398": {
"main": [
[
{
"node": "d2904611-761a-4c85-8445-b2f60caf5672",
"type": "main",
"index": 0
}
]
]
}
}
}Wie verwende ich diesen Workflow?
Kopieren Sie den obigen JSON-Code, erstellen Sie einen neuen Workflow in Ihrer n8n-Instanz und wählen Sie "Aus JSON importieren". Fügen Sie die Konfiguration ein und passen Sie die Anmeldedaten nach Bedarf an.
Für welche Szenarien ist dieser Workflow geeignet?
Fortgeschritten - Content-Erstellung, Multimodales KI
Ist es kostenpflichtig?
Dieser Workflow ist völlig kostenlos. Beachten Sie jedoch, dass Drittanbieterdienste (wie OpenAI API), die im Workflow verwendet werden, möglicherweise kostenpflichtig sind.
Verwandte Workflows
Ruth Olatunji
@ruthie-oeAutomation Consultant with over 3 years of experience automation revenue pipelines for business.
Diesen Workflow teilen