A lead-scoring model you can build in a spreadsheet

Lead scoring has a reputation problem, and it's mostly earned. Most teams' first attempt at it lives inside a marketing automation tool, gets built by one person in an afternoon, assigns points for things like "opened an email" that have approximately no relationship to who actually buys, and gets quietly ignored by sales within two months. The failure isn't the idea of lead scoring. It's building the model inside a tool before anyone has tested whether the inputs actually predict anything.

The fix is almost embarrassingly simple: build the first version in a spreadsheet, score your last ninety days of leads retroactively, and see whether the score actually correlates with who closed. If it does, you've validated a model worth automating. If it doesn't, you've saved yourself from automating a guess.

Why lead scoring breaks when it lives in a tool first

Marketing automation platforms make it very easy to assign points to behaviors — a form fill is worth 10 points, an email click is worth 2, a pricing-page visit is worth 15 — and very easy to ship that model without ever checking whether those point values bear any relationship to actual deal outcomes. Once it's live, it's also harder to iterate on, because changing the model means touching workflows, routing rules, and whatever dashboard leadership is already looking at. A spreadsheet has none of that friction, which is exactly why it's the right place to start.

The four inputs worth scoring

Keep the first version simple. We'd start with exactly four categories of input:

• Firmographic fit — company size, industry, and tech stack signals that match your actual ICP, not your aspirational one.
• Role — is this person in a role that has historically bought, influenced, or blocked deals at your company?
• Engagement recency — when did they last do something, weighted so a visit yesterday counts more than one from six weeks ago.
• Engagement depth — how many distinct touchpoints, not just how many total events (ten email opens is one touchpoint repeated, not ten signals).

Building the point scale

Resist the urge to use a 100-point scale with granular increments — it creates a false sense of precision. A 0–5 scale per category, for a maximum of 20 points, is usually enough resolution to separate "clearly a fit" from "clearly not" without pretending you can distinguish a 73 from a 76. Pull 20–30 of your best closed-won deals and 20–30 closed-lost or never-converted leads, and score them by hand first. You're not building the final model yet — you're building intuition for what the categories should actually weigh.

Write the point definitions down as you go, in plain language, rather than keeping them in your head. "3 points for role" means something different to you than it will to the next person who has to apply the same rubric to a new lead, unless you've written "3 points: a director or VP in a function that has historically championed or blocked a deal; 1 point: an individual contributor in that same function; 0 points: any role outside revenue, finance, or operations." That specificity is what makes the model reproducible instead of a vibe only you can apply consistently.

Weighting fit against behavior

The most common mistake in homegrown models is weighting behavior too heavily relative to fit. A marketing director at a company that matches your ICP perfectly, who's visited your site twice, is usually a better lead than a random individual contributor at a company you've never sold into who's opened every email you've sent. If your retroactive scoring test shows high-behavior, low-fit leads scoring above low-behavior, high-fit leads that actually closed, that's a sign your weights need rebalancing before you trust the model with anything operational.

Where a scoring tool eventually earns its keep

A spreadsheet model doesn't scale past a certain lead volume — once you're routing hundreds of leads a week, manual scoring and static spreadsheet formulas stop being practical, and that's the point where a dedicated scoring feature inside your CRM or marketing automation platform (most of the tools we've reviewed, including Driftwell and Mailgrove, offer rules-based scoring natively) starts to earn its cost. The mistake is buying that capability before you know your weights are right. Validate the model by hand first; let the tool enforce a model you already trust, not generate one you haven't tested.

Testing the model against 90 days of closed deals

Once you have point values you believe in, score every lead from the last 90 days retroactively — both the ones that closed and the ones that didn't. Group leads into bands (say, 0–7, 8–13, 14–20) and calculate the close rate within each band. A working model should show a clear, roughly monotonic increase in close rate as the score band goes up. If your middle band closes at a higher rate than your top band, something in the weighting is off, and it's much cheaper to find that out in a spreadsheet than after it's been driving lead routing for a quarter.

What to do when the model and the sales team disagree

At some point, a rep is going to push back on a lead the model scored highly, or chase one it scored low, and win anyway. Treat these as data points, not arguments to settle in the moment. Log them, and when you rerun the quarterly retroactive test, check whether the rep's instinct was systematically catching something your four inputs miss — a buying-committee structure, a competitive displacement signal, something qualitative that doesn't show up as a form field. If the pattern holds across several deals, that's a sign your model needs a fifth input, not a sign the rep should ignore the score going forward.

Rolling it into your CRM once it's proven

Once the model has held up against a second quarter of fresh data — not just the data you built it on — it's reasonable to translate the point values into a scoring rule inside your CRM or marketing automation tool, so routing happens automatically instead of by hand. Keep the spreadsheet alive even after that, though: rerun the retroactive test quarterly, because what counts as a good-fit lead will drift as your ICP, pricing, and competitive landscape change, and a model nobody revisits degrades quietly.