Back to Templates
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)