OpenAI + Google Sheets: smarter lead routing
Your leads are coming in. The problem is what happens next. Someone has to pull signals from different tools, decide who owns the lead, update the CRM, and keep a scorecard. It’s easy to miss a follow-up when the “system” is a bunch of tabs.
This lead routing automation hits marketing ops first, but sales managers and agency teams feel it too. You get consistent lead scoring, cleaner handoffs, and a Google Sheets dashboard that shows what’s happening without chasing updates.
Below is the workflow, what it automates, and how to run it without turning your routing rules into a science project.
How This Automation Works
The full n8n workflow, from trigger to final output:
n8n Workflow Template: OpenAI + Google Sheets: smarter lead routing
flowchart LR
subgraph sg0["Schedule Flow"]
direction LR
n0@{ icon: "mdi:play-circle", form: "rounded", label: "Schedule Trigger", pos: "b", h: 48 }
n1@{ icon: "mdi:swap-vertical", form: "rounded", label: "Workflow Configuration", pos: "b", h: 48 }
n2["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Fetch Demographic Data"]
n3["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Fetch Behavioral Data"]
n4["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Fetch Transactional Data"]
n5@{ icon: "mdi:cog", form: "rounded", label: "Merge Lead Data Sources", pos: "b", h: 48 }
n6["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>MCDM Scoring Engine (AHP-TOP.."]
n7@{ icon: "mdi:robot", form: "rounded", label: "AI Lead Qualification Agent", pos: "b", h: 48 }
n8@{ icon: "mdi:wrench", form: "rounded", label: "Lead Enrichment Tool", pos: "b", h: 48 }
n9@{ icon: "mdi:swap-vertical", form: "rounded", label: "Prepare Lead Scores", pos: "b", h: 48 }
n10@{ icon: "mdi:swap-horizontal", form: "rounded", label: "Route by Lead Quality", pos: "b", h: 48 }
n11["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Assign to Enterprise Sales T.."]
n12["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Assign to Mid-Market Team"]
n13["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Assign to SMB Team"]
n14["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Send to Nurture Campaign"]
n15@{ icon: "mdi:cog", form: "rounded", label: "Collect Routing Results", pos: "b", h: 48 }
n16["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Update CRM with Lead Scores"]
n17["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Calculate Performance KPIs"]
n18["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Log KPIs to Analytics Dashbo.."]
n19@{ icon: "mdi:brain", form: "rounded", label: "OpenRouter Chat Model", pos: "b", h: 48 }
n0 --> n1
n13 --> n15
n9 --> n10
n8 -.-> n7
n3 --> n5
n19 -.-> n7
n10 --> n11
n10 --> n12
n10 --> n13
n10 --> n14
n2 --> n5
n1 --> n2
n1 --> n3
n1 --> n4
n15 --> n16
n5 --> n6
n4 --> n5
n14 --> n15
n12 --> n15
n17 --> n18
n7 --> n9
n16 --> n17
n11 --> n15
n6 --> n7
end
%% Styling
classDef trigger fill:#e8f5e9,stroke:#388e3c,stroke-width:2px
classDef ai fill:#e3f2fd,stroke:#1976d2,stroke-width:2px
classDef aiModel fill:#e8eaf6,stroke:#3f51b5,stroke-width:2px
classDef decision fill:#fff8e1,stroke:#f9a825,stroke-width:2px
classDef database fill:#fce4ec,stroke:#c2185b,stroke-width:2px
classDef api fill:#fff3e0,stroke:#e65100,stroke-width:2px
classDef code fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
classDef disabled stroke-dasharray: 5 5,opacity: 0.5
class n0 trigger
class n7 ai
class n19 aiModel
class n8 ai
class n10 decision
class n2,n3,n4,n11,n12,n13,n14,n16,n18 api
class n6,n17 code
classDef customIcon fill:none,stroke:none
class n2,n3,n4,n6,n11,n12,n13,n14,n16,n17,n18 customIcon
The Problem: Manual lead routing breaks at scale
Lead routing sounds simple until you actually do it all day. One source has demographic info, another has behavioral signals, and transactional data lives somewhere else entirely. You piece it together, make a judgement call, then push it into a CRM. Next week, someone asks, “Why did this lead go to SMB?” and the answer is buried in a Slack thread. The worst part is the silent failure. Leads don’t always “fail loudly”; they just sit too long, then go cold.
It adds up fast. Here’s where the cracks usually show.
- Scoring rules drift over time, so reps stop trusting the tiers and revert to gut feel.
- Routing decisions become inconsistent when different people interpret the same signals differently.
- CRM updates lag behind reality, which means reporting turns into guesswork by Friday.
- Without a KPI loop, you can’t see if the router is helping or quietly hurting conversion.
The Solution: OpenAI scoring + Google Sheets routing visibility
This workflow runs on a schedule, collects lead signals from multiple sources, and builds a unified profile per prospect. It starts with demographic, behavioral, and transactional pulls via HTTP requests, then combines everything into a single dataset for scoring. A multi-criteria scoring step handles your hard rules (the stuff you absolutely know matters), and then an AI Qualification Analyst (using an OpenAI chat model) adds a structured “second opinion” based on the full context. Once a lead is scored, n8n routes it into the right tier using a switch (Enterprise, Mid-Market, SMB, or Nurture). Finally, it syncs scores back to your CRM and computes performance KPIs so your Google Sheets dashboard stays current.
The workflow begins with the scheduled trigger and data fetch. Then it scores, assigns a tier, and dispatches the lead to the correct team endpoint. After routing, it aggregates outcomes, syncs to the CRM, calculates KPIs, and pushes those metrics to a dashboard endpoint (with Google Sheets available for reporting and tracking).
What You Get: Automation vs. Results
| What This Workflow Automates | Results You’ll Get |
|---|---|
|
|
Example: What This Looks Like
Say you handle 50 new leads a day and you check three places for signals (demographics, behavior, transactions). Even at maybe 3 minutes per source plus 2 minutes to decide routing and update the CRM, that’s about 11 minutes per lead. Roughly 9 hours of manual work in a day if you try to keep up. With this workflow, you spend about 15 minutes setting rules and prompts up front, then each scheduled run scores and routes automatically while Sheets updates in the background. The human work becomes spot-checking and improving, not moving data around.
What You’ll Need
- n8n instance (try n8n Cloud free)
- Self-hosting option if you prefer (Hostinger works well)
- OpenAI for AI lead qualification scoring
- Google Sheets to log tiers and KPI tracking
- CRM API credentials (get it from your CRM’s developer settings)
Skill level: Intermediate. You’ll connect a few APIs and adjust routing rules, but you won’t be writing an app from scratch.
Don’t want to set this up yourself? Talk to an automation expert (free 15-minute consultation).
How It Works
A scheduled run kicks everything off. n8n starts at the interval you choose, so routing happens reliably without someone pressing a button. You can run it more often during business hours if speed-to-lead is a priority.
Lead signals are fetched and unified. The workflow uses HTTP requests to retrieve demographic info, behavioral signals, and transactional metrics, then combines them into a single lead profile. No more scoring from partial context.
Scoring happens in two layers. First, a multi-criteria scoring step applies the rules you define (thresholds, must-have fields, disqualifiers). Then the OpenAI-powered agent evaluates the same lead in plain language and returns structured qualification notes that map back to your tiers.
Routing and tracking happen automatically. A switch routes the lead to Enterprise, Mid-Market, SMB, or Nurture via the right endpoints, then outcomes are aggregated, synced into your CRM, and posted to a dashboard. Google Sheets is where tiers and KPIs stay visible for the whole team.
You can easily modify the score tiers to match your sales org, then change the destinations to fit your CRM or notification tools based on your needs. See the full implementation guide below for customization options.
Step-by-Step Implementation Guide
Step 1: Configure the Scheduled Run Trigger
Set the automation to run on a fixed schedule so lead scoring and routing execute automatically.
- Select Scheduled Run Starter and set the schedule rule to run every hours interval.
- Confirm the node is connected to Automation Settings as the first step in the workflow.
Step 2: Connect Lead Data APIs and Parallel Ingestion
Define your API endpoints and pull lead data from multiple sources in parallel.
- Open Automation Settings and set the following fields: demographicApiUrl to
<__PLACEHOLDER_VALUE__Demographic Data API Endpoint__>, behavioralApiUrl to<__PLACEHOLDER_VALUE__Behavioral Data API Endpoint__>, transactionalApiUrl to<__PLACEHOLDER_VALUE__Transactional Data API Endpoint__>, crmApiUrl to<__PLACEHOLDER_VALUE__CRM API Endpoint__>, and analyticsApiUrl to<__PLACEHOLDER_VALUE__Analytics Dashboard API Endpoint__>. - Set thresholds in Automation Settings: enterpriseThreshold to
85, midMarketThreshold to70, smbThreshold to50, and ahpWeights to{"demographic": 0.3, "behavioral": 0.4, "transactional": 0.3}. - Configure Retrieve Demographic Info with URL set to
{{ $('Automation Settings').first().json.demographicApiUrl }}and Content-Type header set toapplication/json. - Configure Pull Behavioral Signals with URL set to
{{ $('Automation Settings').first().json.behavioralApiUrl }}and Content-Type header set toapplication/json. - Configure Gather Transactional Metrics with URL set to
{{ $('Automation Settings').first().json.transactionalApiUrl }}and Content-Type header set toapplication/json. - Automation Settings outputs to both Retrieve Demographic Info and Pull Behavioral Signals and Gather Transactional Metrics in parallel.
<__PLACEHOLDER_VALUE__...__> API endpoints in Automation Settings before testing to avoid failed HTTP calls.Step 3: Set Up Scoring and AI Qualification
Aggregate the lead sources, compute the MCDM score, and run AI qualification analysis.
- In Combine Lead Sources, set Aggregate to
aggregateAllItemDataand Destination Field Name toleadData. - Review Multi-Criteria Scoring to ensure the JS code references Automation Settings for weights and criteria values.
- Open AI Qualification Analyst and keep the prompt text as defined for lead qualification insights.
- OpenRouter Chat Assistant is connected as the language model for AI Qualification Analyst — ensure credentials are added to OpenRouter Chat Assistant.
- Credential Required: Connect your openRouterApi credentials in OpenRouter Chat Assistant.
- Lead Enrichment Utility is connected as an AI tool for AI Qualification Analyst; add any required credentials on the parent node if you extend the tool with external calls.
Step 4: Assemble Scores and Route by Tier
Map AI output into a clean scoring payload and route leads to the correct tier.
- In Assemble Lead Scores, set fields using expressions: leadScore to
{{ $json.mcdmScore }}, leadTier to{{ $json.tier }}, aiInsights to{{ $json.output }}, and routingDecision to{{ $json.routingDecision }}. - In Route by Score Tier, confirm the rule thresholds:
{{ $json.leadScore }}≥{{ $('Automation Settings').first().json.enterpriseThreshold }}for Enterprise, ≥{{ $('Automation Settings').first().json.midMarketThreshold }}for Mid-Market, ≥{{ $('Automation Settings').first().json.smbThreshold }}for SMB, and{{ $json.leadScore }}<{{ $('Automation Settings').first().json.smbThreshold }}for Nurture. - Verify that Route by Score Tier sends output to Dispatch to Enterprise Team, Dispatch to Mid-Market Team, Dispatch to SMB Team, and Send to Nurture Stream depending on the matched rule.
Step 5: Configure Routing Actions and CRM Sync
Send routed leads to the correct team endpoints, then sync results and aggregate outcomes.
- Set the URL in Dispatch to Enterprise Team to
<__PLACEHOLDER_VALUE__Enterprise Sales Team Assignment API__>and keep Method asPOSTwith body fields mapped to{{ $json.leadId }},{{ $json.leadData }},{{ $json.score }},{{ $json.tier }}, and{{ $json.aiInsights }}. - Set the URL in Dispatch to Mid-Market Team to
<__PLACEHOLDER_VALUE__Mid-Market Team Assignment API__>and keep Method asPOSTwith body fields mapped to{{ $json.lead_data }},{{ $json.score }},{{ $json.tier }}, and{{ $json.ai_insights }}. - Set the URL in Dispatch to SMB Team to
<__PLACEHOLDER_VALUE__SMB Team Assignment API__>and keep Method asPOSTwith body fields mapped to{{ $json.lead_id }},{{ $json.lead_data }},{{ $json.score }},{{ $json.tier }}, and{{ $json.ai_insights }}. - Set Send to Nurture Stream to
POSTand set JSON Body to{{ { "leadId": $json.leadId, "email": $json.email, "name": $json.name, "score": $json.score, "qualificationStatus": $json.qualificationStatus, "nurtureRecommendations": $json.nurtureRecommendations, "assignedCampaign": "nurture", "timestamp": $now } }}. - In Aggregate Routing Outcomes, set Aggregate to
aggregateAllItemDataand Destination Field Name toroutingResults. - Configure Sync CRM Lead Scores with URL set to
{{ $('Automation Settings').first().json.crmApiUrl }}and keep the JSON Body expression to sync lead score, tier, routing decision, and AI insights.
Step 6: Compute and Publish KPI Analytics
Calculate performance KPIs and push them to your analytics dashboard.
- Review Compute Performance KPIs to confirm it calculates tier distribution, conversion rates, and timing metrics based on the routing results.
- Set Post KPIs to Dashboard URL to
{{ $('Automation Settings').first().json.analyticsApiUrl }}and keep the JSON Body expression{{ { "kpis": $json, "timestamp": $now } }}.
Step 7: Test and Activate Your Workflow
Run a manual test to validate routing, CRM sync, and KPI publication before enabling the schedule.
- Click Execute Workflow and verify that Retrieve Demographic Info, Pull Behavioral Signals, and Gather Transactional Metrics all return data.
- Confirm Multi-Criteria Scoring outputs ranked leads and AI Qualification Analyst returns AI insights in Assemble Lead Scores.
- Check that Route by Score Tier sends each lead to the correct dispatch node and that Aggregate Routing Outcomes aggregates routing results.
- Verify Sync CRM Lead Scores posts to your CRM endpoint and Post KPIs to Dashboard posts KPI payloads.
- When results look correct, toggle the workflow to Active to enable scheduled runs from Scheduled Run Starter.
Common Gotchas
- Google Sheets credentials can expire or need specific permissions. If things break, check the Google connection inside n8n’s Credentials tab first.
- If you’re using Wait nodes or external processing on your data sources, processing times vary. Bump up the wait duration if downstream nodes fail on empty responses.
- Default prompts in AI nodes are generic. Add your brand’s lead definitions early (what “Enterprise-ready” actually means) or you will be editing outputs forever.
Frequently Asked Questions
About 45 minutes if your APIs and Sheets are ready.
No. You’ll mostly connect accounts, paste API keys, and adjust a few scoring and routing rules.
Yes. n8n has a free self-hosted option and a free trial on n8n Cloud. Cloud plans start at $20/month for higher volume. You’ll also need to factor in OpenAI API costs, which are usually a few cents per lead depending on prompt size.
Two options: n8n Cloud (managed, easiest setup) or self-hosting on a VPS. For self-hosting, Hostinger VPS is affordable and handles n8n well. Self-hosting gives you unlimited executions but requires basic server management.
Yes, and you should. You can adjust tier thresholds in the Multi-Criteria Scoring step, then update the Route by Score Tier switch so Enterprise, Mid-Market, SMB, and Nurture match your actual sales structure. Common tweaks include adding an “Existing Customer” route, changing what counts as high intent, and routing by region or account owner when you have that data.
Most of the time it’s expired Google authorization or the wrong spreadsheet permissions. Reconnect Google Sheets in n8n, then confirm the account can edit the specific file and tab you’re writing to. If it fails only on some runs, check for changed column names or empty fields that your “set” steps expect. Also worth checking: Google’s API quota if you are writing lots of rows quickly.
Hundreds a day is realistic, and more if your data sources and CRM APIs can keep up. On n8n Cloud, limits depend on your plan’s monthly executions, while self-hosting has no execution cap (it’s mostly about server size). If you score one lead at a time with an AI call, OpenAI speed becomes the practical bottleneck, so batching leads per run is often the easiest scale win.
Often, yes. This workflow uses multi-branch routing, aggregation, and an AI agent step, which can get expensive or awkward in Zapier-style task pricing. n8n also gives you more control over how lead records are merged and transformed before scoring, which matters when signals come from multiple sources. The tradeoff is setup: you’ll spend a bit more time defining rules and credentials up front. If you only need a simple “new lead → send Slack message” flow, Zapier or Make can be quicker. If you’re unsure, Talk to an automation expert.
Routing stops being a daily scramble when scoring, ownership, and reporting happen automatically. Set it up once, then use the time you get back to improve the funnel instead of babysitting it.
Need Help Setting This Up?
Our automation experts can build and customize this workflow for your specific needs. Free 15-minute consultation—no commitment required.