Score and route website leads with Google Sheets and Gmail

Quick Overview

This workflow receives website lead submissions via webhook, normalizes the payload, validates contact details, scores intent to assign an SLA bucket, logs each lead to Google Sheets, and routes hot and review leads to the right inbox using Gmail.

How it works

1. Receives a POST request on a webhook endpoint containing a website/form lead payload.
2. Normalizes incoming fields (name, email, phone, company, message, budget, timeline, page URL, source) into a consistent structure.
3. Rejects submissions that lack a valid email or phone number and returns a 400 JSON response explaining what’s missing.
4. Scores the lead based on intent keywords and data completeness, then assigns a priority and SLA bucket.
5. Builds a daily dedupe key and routing owner, then appends the full lead and scoring details to a Google Sheets “Lead audit” worksheet.
6. Sends hot leads (score ≥ 70) to a sales Gmail address and same-day review leads (score ≥ 40) to an ops-review Gmail address.
7. Returns a JSON webhook response with the lead’s priority, score, SLA, and routing owner.

Setup

1. Configure the Webhook URL in your form/website so it sends lead submissions to this workflow’s webhook endpoint.
2. Add Google Sheets credentials and replace the placeholder spreadsheet ID, ensuring a sheet named “Lead audit” exists with matching columns.
3. Add Gmail credentials and update the recipient addresses (e.g. [email protected] and [email protected]) to your real inboxes.