🔓 Unlock all 10,000+ workflows & prompts free Join Newsletter →
✅ Full access unlocked — explore all 10,000 AI workflow and prompt templates Browse Templates →
Home n8n Workflow
January 22, 2026

OpenAI + Google Sheets: smarter lead routing

Lisa Granqvist Partner Workflow Automation Expert

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

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

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.

  1. Select Scheduled Run Starter and set the schedule rule to run every hours interval.
  2. 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.

  1. 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__>.
  2. Set thresholds in Automation Settings: enterpriseThreshold to 85, midMarketThreshold to 70, smbThreshold to 50, and ahpWeights to {"demographic": 0.3, "behavioral": 0.4, "transactional": 0.3}.
  3. Configure Retrieve Demographic Info with URL set to {{ $('Automation Settings').first().json.demographicApiUrl }} and Content-Type header set to application/json.
  4. Configure Pull Behavioral Signals with URL set to {{ $('Automation Settings').first().json.behavioralApiUrl }} and Content-Type header set to application/json.
  5. Configure Gather Transactional Metrics with URL set to {{ $('Automation Settings').first().json.transactionalApiUrl }} and Content-Type header set to application/json.
  6. Automation Settings outputs to both Retrieve Demographic Info and Pull Behavioral Signals and Gather Transactional Metrics in parallel.
⚠️ Common Pitfall: Replace all <__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.

  1. In Combine Lead Sources, set Aggregate to aggregateAllItemData and Destination Field Name to leadData.
  2. Review Multi-Criteria Scoring to ensure the JS code references Automation Settings for weights and criteria values.
  3. Open AI Qualification Analyst and keep the prompt text as defined for lead qualification insights.
  4. OpenRouter Chat Assistant is connected as the language model for AI Qualification Analyst — ensure credentials are added to OpenRouter Chat Assistant.
  5. Credential Required: Connect your openRouterApi credentials in OpenRouter Chat Assistant.
  6. 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.

  1. 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 }}.
  2. 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.
  3. 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.

  1. Set the URL in Dispatch to Enterprise Team to <__PLACEHOLDER_VALUE__Enterprise Sales Team Assignment API__> and keep Method as POST with body fields mapped to {{ $json.leadId }}, {{ $json.leadData }}, {{ $json.score }}, {{ $json.tier }}, and {{ $json.aiInsights }}.
  2. Set the URL in Dispatch to Mid-Market Team to <__PLACEHOLDER_VALUE__Mid-Market Team Assignment API__> and keep Method as POST with body fields mapped to {{ $json.lead_data }}, {{ $json.score }}, {{ $json.tier }}, and {{ $json.ai_insights }}.
  3. Set the URL in Dispatch to SMB Team to <__PLACEHOLDER_VALUE__SMB Team Assignment API__> and keep Method as POST with body fields mapped to {{ $json.lead_id }}, {{ $json.lead_data }}, {{ $json.score }}, {{ $json.tier }}, and {{ $json.ai_insights }}.
  4. Set Send to Nurture Stream to POST and 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 } }}.
  5. In Aggregate Routing Outcomes, set Aggregate to aggregateAllItemData and Destination Field Name to routingResults.
  6. 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.
Tip: There are multiple httpRequest nodes for team routing and CRM syncing. Align authentication headers consistently across these endpoints to avoid partial delivery.

Step 6: Compute and Publish KPI Analytics

Calculate performance KPIs and push them to your analytics dashboard.

  1. Review Compute Performance KPIs to confirm it calculates tier distribution, conversion rates, and timing metrics based on the routing results.
  2. 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.

  1. Click Execute Workflow and verify that Retrieve Demographic Info, Pull Behavioral Signals, and Gather Transactional Metrics all return data.
  2. Confirm Multi-Criteria Scoring outputs ranked leads and AI Qualification Analyst returns AI insights in Assemble Lead Scores.
  3. Check that Route by Score Tier sends each lead to the correct dispatch node and that Aggregate Routing Outcomes aggregates routing results.
  4. Verify Sync CRM Lead Scores posts to your CRM endpoint and Post KPIs to Dashboard posts KPI payloads.
  5. When results look correct, toggle the workflow to Active to enable scheduled runs from Scheduled Run Starter.
🔒

Unlock Full Step-by-Step Guide

Get the complete implementation guide + downloadable template

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

How long does it take to set up this lead routing automation?

About 45 minutes if your APIs and Sheets are ready.

Do I need coding skills to automate lead routing?

No. You’ll mostly connect accounts, paste API keys, and adjust a few scoring and routing rules.

Is n8n free to use for this lead routing automation workflow?

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.

Where can I host n8n to run this automation?

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.

Can I customize this lead routing automation workflow for different score tiers?

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.

Why is my Google Sheets connection failing in this workflow?

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.

How many leads can this lead routing automation handle?

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.

Is this lead routing automation better than using Zapier or Make?

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.

Lisa Granqvist

Workflow Automation Expert

Expert in workflow automation and no-code tools.

×

Use template

Get instant access to this n8n workflow Json file

💬
Get a free quote today!
Get a free quote today!

Tell us what you need and we'll get back to you within one working day.

Get a free quote today!
Get a free quote today!

Tell us what you need and we'll get back to you within one working day.

Launch login modal Launch register modal