Route and reassign leads with SLA using Google Sheets and Slack

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:

1. Accepts new leads from a generic intake form
2. Assigns leads to sales reps using round-robin
3. Enforces a response SLA
4. Automatically re-routes uncontacted leads
5. Allows sales to mark leads as CONTACTED via Slack
6. Escalates to a manager once if SLA is repeatedly violated

Architecture Overview

Core Components

1. n8n
2. Google Sheets
3. Slack

Primary Data Stores

1. sales_sheet (list of active sales reps)
2. lead_sheet (lead state and routing history)
3. routing_state_sheet (global routing + escalation flags)

End-to-End Flow

A) Lead Intake & Normalization

1. New leads enter via Form Trigger
2. Phone numbers are normalized to 62xxxxxxxx (Indonesia International Direct Dialing code)
3. A unique lead_id is generated
4. Lead is initialized with: stage = NEW route_count = 0

B) Initial Assignment (Round Robin)

1. Active sales reps are loaded from sales_sheet
2. Global last_index is read from routing_state_sheet
3. Lead is assigned to the next sales rep in sequence
4. Assignment metadata is stored: assigned sales timestamps route count
5. last_index is updated centrally

C) Slack Notification (New Lead)

1. Assigned sales receives a Slack message
2. Message includes a “Mark as CONTACTED” button
3. SLA expectation is clearly communicated (1 hour by default)

D) SLA Monitoring (Scheduled)

1. A scheduled trigger runs every hour
2. Workflow scans leads where: stage = NEW SLA window has elapsed since last assignment

E) SLA Re-Routing

For each qualifying lead:

1. Lead is reassigned to the next sales rep
2. Route count is incremented
3. Timestamps are updated
4. 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):

1. Workflow checks escalation state (escalated_<lead_id>)
2. If not escalated yet: Manager is notified via Slack Escalation flag is written
3. If already escalated: No further action is taken

Escalation is one-time per lead.

G) Stage Update via Slack (CONTACTED)

1. Sales marks a lead as CONTACTED via Slack button
2. Incoming Slack action is validated: Only assigned sales is allowed Only if current stage is NEW
3. Update is idempotent
4. Unauthorized or stale actions receive Slack feedback

Once contacted:

1. SLA routing stops
2. Lead remains stable

Safeguards Built In

1. Ownership enforcement (Only the assigned sales rep can update a lead)
2. Idempotent stage transitions (Prevents duplicate or stale Slack actions)
3. One-time escalation (No notification spam)
4. Fail-fast behavior (Missing sales data or malformed payloads halt execution early)

Google Sheets Schema

sales_sheet

| Column | Description | |----------|-----------------| | name | Sales name | | email | 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)

1. Google Sheets is not transactional
2. SLA enforcement is time-bucketed, not real-time
3. No concurrency locking across parallel runs
4. Slack is required for interaction
5. This is not a CRM, only a routing engine

These constraints are explicit and intentional.

When This Design Works Well

1. Small to mid-size teams
2. Human-response SLAs (minutes/hours)
3. Teams needing discipline, not heavy tooling
4. CRM-lite or pre-CRM environments

When to Migrate

Consider migrating if you need:

1. High-volume ingestion
2. Sub-minute SLA guarantees
3. Strong transactional consistency
4. Advanced analytics or forecasting

The routing logic itself is portable to SQL or CRM systems.