Automate Marketing Revenue Attribution & ROI Analytics with Airtable and Slack
## This n8n is a daily analytics automation that calculates which lead sources generate actual revenue, not just leads. Provides ROI data, conversion rates, and budget allocation recommendations.
Use Case: automates marketing ROI tracking by linking closed deals to their lead sources in Airtable, calculating revenue and ROI per channel, and sending daily insights to Slack.
## **What It Does**
- Runs nightly to analyze closed deals from last 30 days
- Matches deals to their original lead sources
- Calculates total revenue per source
- Computes ROI (revenue vs. cost per lead)
- Determines conversion rates by source
- Updates Lead Sources table with metrics
- Sends weekly reports to team
## How It Works
**Step 1: Schedule Trigger**
Runs daily at midnight
**Step 2: Fetch Closed Won Deals**
Gets all deals where:
- Stage = "Closed Won"
- Actual Close Date in last 30 days
**Step 3: Fetch Lead Sources**
Gets cost and lead count data from Lead Sources table
**Step 4: Calculate ROI (JavaScript)**
For each source:
- Total revenue = Sum of all deals from that source
- Total cost = Cost per lead × Total leads
- ROI = ((Revenue - Cost) / Cost) × 100
- Conversion rate = Deals closed / Total leads × 100
- Average deal size = Revenue / Deal count
**Step 5: Update Lead Sources**
Writes calculated metrics back to Airtable
**Step 6: Send Report**
Slack message with top 3 performing sources
## **Business Impact**
- **Marketing ROI:** Know exactly which channels generate revenue
- **Budget optimization:** Allocate spend to highest-ROI sources
- **Data-driven decisions:** Stop guessing, start knowing
- **Cost reduction:** Cut low-performing channels
- **Revenue growth:** Double down on what works
## **Technical Requirements**
- n8n (self-hosted or cloud)
- Airtable (uses existing tables)
- Slack (for reports)
- Gmail for reminder incase CEO missed the report in the Slack channel (optional)
