Marketing

Lead Scoring in 30 Min: 5 Dimensions, 0-100 Score (Gemini + Google Sheet)

Lead Scoring in 30 Min: 5 Dimensions, 0-100 Score (Gemini + Google Sheet)
Contents

A B2B client sent me 1,400 HubSpot leads last quarter and asked why their sales team wasn't closing them. The answer: sales was working every lead equally, top 20% buried in the middle. The 30-minute fix wasn't a 40-attribute predictive model — it was a 5-dimension rubric, one =AI() formula in Google Sheets, and the discipline to color-code the result. I've built this four times now. Setup: 30 minutes, rows scored: thousands.

The 5 dimensions (each 0-20, total 0-100)

Most lead scoring projects die trying to model 30 attributes. Five is the magic number — enough to differentiate, few enough to argue about:

  1. Firmographic fit (0-20) — does company size + industry match the ICP? 5,000-employee retailer = 20, 5-person plumber = 0.
  2. Behavior (0-20) — 2+ high-intent actions in the last 14 days (pricing, demo, case study, ROI calc). 0 = none, 10 = 2-3, 20 = 4+.
  3. Engagement recency (0-20) — last touchpoint within 7 days = 20, 8-30 days = 10, 31+ days = 0.
  4. Source (0-20) — inbound (organic, referral) = 20. Outbound (paid, SDR cold outreach) = 10. List-buy / scraped = 0.
  5. Buying signals (0-20) — pricing page revisit + demo request + competitor comparison. One = 5, two = 10, three+ = 20.

The rubric for each dimension lives in a single cell on the side of the sheet, copied verbatim into the AI prompt. Don't bury it in a doc — sales needs to read it.

The formula

Google Sheets now ships an =AI() function powered by Gemini. One formula, one cell, scales to any volume:

=AI("Score this lead 0-100 based on: [paste rubric]. Return: total_score | top_dimension | one_sentence_reason. Field name: "&A2&", Company: "&B2&", Industry: "&C2&", Last touchpoint days ago: "&D2&", Page views last 14d: "&E2&", Source: "&F2&", Pricing visits: "&G2&", Demo requested: "&H2&", Competitor research: "&I2&", Company size: "&J2, A2:J2)

The function takes a natural-language instruction + a range, and returns whatever you asked for. Drop the formula into column L, drag it down 1,400 rows, the sheet scores every lead in the time it takes to refill a coffee. The most important line in the prompt is Field name: — it forces Gemini to attribute the score to a specific row, which prevents the "all scores look the same" failure mode of generic rubrics.

Three leads, scored

  • Lead A — 2,800-person SaaS, last touchpoint 2 days ago, 5 page views in 14 days, inbound from a case study, 1 pricing visit, no demo. Returns: 84 | behavior | "Strong ICP fit plus recent multi-page engagement; only one buying signal so far." → Green, work now.
  • Lead B — 22-person dental practice, last touchpoint 41 days ago, 1 page view, paid Google ad, 0 pricing visits, no demo. Returns: 8 | source | "Wrong ICP, stale engagement, low-intent paid source; deprioritize." → Red, recycle to nurture.
  • Lead C — 800-person retailer, last touchpoint 9 days ago, 3 page views, referral from a partner, 2 pricing visits, demo requested. Returns: 92 | buying_signals | "Demo requested plus repeat pricing visits within 9 days; route to AE today." → Green, work now.

The top_dimension + one_sentence_reason output is what makes this work — sales can read column L in 10 seconds and know why a lead scored 84, not just that it did.

What to do with the colors

A 0-100 number on its own is decoration. Two rules I always enforce:

  • Sales works green only. Anyone below 70 goes into a nurture sequence. The first time I shipped this, sales complained they had "no leads" — then closed more in the next month than the prior quarter, because they were working the right 20%.
  • Re-score weekly. A 92 on Monday can be a 60 on Friday, once the touchpoint ages out. That's the feature, not a bug.

When this stops working

Three honest limits:

  1. High-velocity B2C — the rubric assumes a sales team reads individual scores. If your funnel is self-serve and transactional, replace sales filtering with automated routing.
  2. Under 100 leads/month — the rubric prompt will work, but you don't need a formula. Just read them.
  3. The rubric goes stale — ICP, pricing page, and "buying signal" definitions shift. Audit quarterly.

A predictive lead score that takes six months to ship never ships. This one ships in 30, the rubric redone in 30 the day your ICP shifts.