💬 Route and reassign leads with SLA using Google Sheets and Slack
⚡ 14 views · 💬 Lead Nurturing & AI Sales Agents
Description
Lead-Routing-Engine-with-SLA-Auto-Reassignment
This repository contains an SLA-based lead routing workflow built in n8n, designed to ensure fast lead response, fair sales distribution, and controlled escalation without relying on a full CRM system.
The workflow focuses on routing discipline and operational safety, not feature completeness.
What This Workflow Does
At a high level, the system:
- Accepts new leads from a generic intake form
- Assigns leads to sales reps using round-robin
- Enforces a response SLA
- Automatically re-routes uncontacted leads
- Allows sales to mark leads as CONTACTED via Slack
- Escalates to a manager once if SLA is repeatedly violated
Architecture Overview
Core Components
- n8n
- Google Sheets
- Slack
Primary Data Stores
- sales_sheet (list of active sales reps)
- lead_sheet (lead state and routing history)
- routing_state_sheet (global routing + escalation flags)
End-to-End Flow
A) Lead Intake & Normalization
- New leads enter via Form Trigger
- Phone numbers are normalized to 62xxxxxxxx (Indonesia International Direct Dialing code)
- A unique lead_id is generated
- Lead is initialized with:
- stage = NEW
- route_count = 0
B) Initial Assignment (Round Robin)
- Active sales reps are loaded from sales_sheet
- Global last_index is read from routing_state_sheet
- Lead is assigned to the next sales rep in sequence
- Assignment metadata is stored:
- assigned sales
- timestamps
- route count
- last_index is updated centrally
C) Slack Notification (New Lead)
- Assigned sales receives a Slack message
- Message includes a “Mark as CONTACTED” button
- SLA expectation is clearly communicated (1 hour by default)
D) SLA Monitoring (Scheduled)
- A scheduled trigger runs every hour
- Workflow scans leads where:
- stage = NEW
- SLA window has elapsed since last assignment
E) SLA Re-Routing
For each qualifying lead:
- Lead is reassigned to the next sales rep
- Route count is incremented
- Timestamps are updated
- Slack notification is sent to the new assignee
This process repeats until the lead is contacted or escalated.
F) Controlled Escalation
If route_count >= threshold (default: 10):
- Workflow checks escalation state (escalated_<lead_id>)
- If not escalated yet:
- Manager is notified via Slack
- Escalation flag is written
- If already escalated:
- No further action is taken
Escalation is one-time per lead.
G) Stage Update via Slack (CONTACTED)
- Sales marks a lead as CONTACTED via Slack button
- Incoming Slack action is validated:
- Only assigned sales is allowed
- Only if current stage is NEW
- Update is idempotent
- Unauthorized or stale actions receive Slack feedback
Once contacted:
- SLA routing stops
- Lead remains stable
Safeguards Built In
- Ownership enforcement (Only the assigned sales rep can update a lead)
- Idempotent stage transitions (Prevents duplicate or stale Slack actions)
- One-time escalation (No notification spam)
- Fail-fast behavior (Missing sales data or malformed payloads halt execution early)
Google Sheets Schema
sales_sheet
| Column | Description |
|---|---|
| name | Sales name |
| Optional | |
| slack_id | Slack user ID |
| active | ON / TRUE |
lead_sheet
| Column | Description |
|---|---|
| lead_id | Unique identifier |
| name | Lead name |
| phone | Normalized phone |
| stage | NEW / CONTACTED / QUALIFIED / CLOSED LOST |
| assigned_sales | Current owner |
| sales_slack_id | Slack ID |
| route_count | Number of re-routes |
| created_at | Creation timestamp |
| assigned_at | Last assignment |
| last_routed_at | Last SLA routing |
| contacted_at | When marked contacted |
routing_state_sheet
| key | value |
|---|---|
| last_index | Last round-robin index |
| escalated_<lead_id> | Escalation timestamp |
Limitations (By Design)
- Google Sheets is not transactional
- SLA enforcement is time-bucketed, not real-time
- No concurrency locking across parallel runs
- Slack is required for interaction
- This is not a CRM, only a routing engine
These constraints are explicit and intentional.
When This Design Works Well
- Small to mid-size teams
- Human-response SLAs (minutes/hours)
- Teams needing discipline, not heavy tooling
- CRM-lite or pre-CRM environments
When to Migrate
Consider migrating if you need:
- High-volume ingestion
- Sub-minute SLA guarantees
- Strong transactional consistency
- Advanced analytics or forecasting
The routing logic itself is portable to SQL or CRM systems.
🔗 Nodes Used
Google Sheets, HTTP Request, Slack, Webhook, Schedule Trigger, n8n Form Trigger
📥 Import
Download workflow.json and import into n8n:
Workflow menu → Import from File