Calculadora de atribución de ingresos y ROI

• •Cuenta de Google y credenciales de API de Gmail
• •Bot Token de Slack o URL de Webhook
• •Clave de API de Airtable

{
  "id": "amCkThATp8WSOqFz",
  "meta": {
    "instanceId": "aa63297b1b91e208b636dd926ed91cc26a0d2bb14cd724f85cdd3d9fcfd9cbe7",
    "templateCredsSetupCompleted": true
  },
  "name": "Revenue Attribution & ROI Calculator",
  "tags": [],
  "nodes": [
    {
      "id": "15341b2a-14b2-4341-8b18-8cd1fc5c79f1",
      "name": "Activador Programado",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -400,
        144
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "639b483f-1347-4361-990a-499d7a985398",
      "name": "Buscar Fuente de Lead",
      "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": "Código en 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": "Oportunidades",
      "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": "Iterar sobre Elementos",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        624,
        144
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "fbe7af8c-9d26-4f68-a78b-f35ece1c958e",
      "name": "Buscar registros",
      "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": "Actualizar registro",
      "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": "Enviar mensaje",
      "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": "Enviar mensaje1",
      "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": "Nota Adhesiva4",
      "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": "Nota Adhesiva5",
      "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": "Nota Adhesiva6",
      "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": "Nota Adhesiva7",
      "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": "Nota Adhesiva9",
      "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": "Nota Adhesiva",
      "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
          }
        ]
      ]
    },
    "e92d2b3c-16ac-4979-8368-4208b533bab0": {
      "main": [
        [
          {
            "node": "fbe7af8c-9d26-4f68-a78b-f35ece1c958e",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "e92d2b3c-16ac-4979-8368-4208b533bab0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "15341b2a-14b2-4341-8b18-8cd1fc5c79f1": {
      "main": [
        [
          {
            "node": "0542491a-d85d-4431-8ac0-fa8278a58088",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d2904611-761a-4c85-8445-b2f60caf5672": {
      "main": [
        [
          {
            "node": "e92d2b3c-16ac-4979-8368-4208b533bab0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "639b483f-1347-4361-990a-499d7a985398": {
      "main": [
        [
          {
            "node": "d2904611-761a-4c85-8445-b2f60caf5672",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}