A Lead Scoring Model You Can Build in a Spreadsheet

You can have a working lead scoring model by the end of this week without a data team, a training set or machine learning. The build is two spreadsheet tabs. One turns a handful of signals into points and sorts leads into hot, warm and cold. The other scores last year's won and lost deals with the same formula, so you find out whether the model means anything before you rely on it.

This is the build guide. If you want the reasoning behind signal selection first - why six to eight signals is the right range, and which conversations produce them - read the companion article on lead scoring without a data team and come back. Here, we go cell by cell.

Key takeaways

• The model has four parts: fit points, engagement points, disqualifier subtractions and a decay rule that stops old activity from counting.
• A single spreadsheet formula can carry the entire model: weighted columns multiplied, capped, summed, then deductions applied.
• Thresholds become your routing rules: hot leads get a call today, warm leads get a sequence, cold leads get nurture. Pick cutoffs from closed deals, not instinct.
• Backtest before automating. Score six to twelve months of wins and losses with the same formula and compare averages with a function like AVERAGEIF.
• Keep one model version number on the sheet. When weights change, the version changes, and old comparisons stay honest.

The two tabs you are building

Tab 1, "Model". A short table of signals, their type, their points and where the data comes from. This is your single source of truth. If you want to change a weight, you change it here, and every formula reads from it.

Tab 2, "Leads". One row per lead, with columns for the raw signal values and one score column that combines them into a number and a bucket.

When the model earns trust, you add a third tab - "Backtest" - but you only need it at validation time.

Tab 1: the signals and their weights

Start with eight signals. More than that and the model gets hard to explain; fewer and it is noisy. Here is the sample model we will build. Adjust the points to your business; the structure is what transfers.

Signal	Type	Points	Where it comes from
Industry matches your top two customer types	Fit	+20	Form field or manual tag
Business size inside your serviceable range	Fit	+15	Form field or CRM property
Mentioned budget, deadline or active project	Fit	+20	Conversation notes or form
Owner, founder or decision-maker role	Fit	+10	Form field or call notes
Replied to any message	Engagement	+10 per reply, cap 2	CRM activity
Visited pricing or a core service page	Engagement	+5 per visit, cap 3	Site analytics or CRM
Job seeker, student, competitor or out of scope	Disqualifier	-50	Manual review or rule
No activity in the last 30 days	Decay	-10	Formula, automatic

Two mechanics in that table deserve a sentence each. The caps on engagement points stop one enthusiastic browser from outscoring a real buyer, and the decay row is what makes old scores shrink - a lead who replied two months ago is not the same lead today.

Tab 2: the Leads sheet, column by column

Set the columns up like this. Letters assume row 1 is a header row and data starts in row 2.

Column	Field	Type
A	lead_id	Text
B	name	Text
C	source	Text
D	created_date	Date
E	last_activity_date	Date
F	industry_fit	1 or 0
G	size_fit	1 or 0
H	budget_timeline	1 or 0
I	role_owner	1 or 0
J	replies_30d	Count
K	pricing_visits	Count
L	disqualified	1 or 0
M	booked	1 or 0
N	score	Formula
O	bucket	Formula

Keep the raw columns (F to M) as values a person or an automation can set. Never type a score by hand into column N, or the backtest stops meaning anything.

The score formula, explained piece by piece

The score formula in N2 multiplies each fit column by its weight, caps the engagement counts, subtracts disqualifiers and applies decay:

=F2*20+G2*15+H2*20+I2*10+MIN(J2,2)*10+MIN(K2,3)*5-L2*50-IF(TODAY()-E2>30,10,0)

Read it in four parts:

• Fit: F2*20+G2*15+H2*20+I2*10 turns yes-or-no columns into points.
• Engagement, capped: MIN(J2,2)*10+MIN(K2,3)*5 counts replies and pricing visits up to a ceiling, so volume cannot bulldoze fit.
• Disqualifiers: -L2*50 is a hard punishment. A competitor or student should fall out of the queue regardless of engagement.
• Decay: IF(TODAY()-E2>30,10,0) subtracts 10 points once a lead has gone quiet for more than a month. Update column E whenever any interaction happens.

Then the bucket formula in O2 converts the number into an instruction:

=IF(N2>=70,"Hot",IF(N2>=40,"Warm","Cold"))

If you prefer a single view without helper columns, SUMPRODUCT can do the fit and engagement math in one expression, and SUMIFS is what you will use to aggregate results in the backtest. Both are standard functions covered in Google Sheets' function list and Microsoft's SUMIFS documentation.

A worked example you can check by hand

Five illustrative leads, scored with the formula above. The arithmetic is deliberately simple so you can verify it in your head.

Lead	Fit points	Engagement points	Deductions	Score	Bucket
L-001: fits ICP, owner, budget mentioned, 2 replies, 3 pricing visits	65	35	0	100	Hot
L-002: fits ICP, right size, 1 reply, 1 pricing visit	35	15	0	50	Warm
L-003: fits ICP only, quiet for 3 days	20	0	0	20	Cold
L-004: fits ICP, owner, budget mentioned, 2 replies, no pricing visit	65	20	0	85	Hot
L-005: fits ICP and size, 1 reply, but is a competitor	35	15	-50	0	Cold
L-006: fits ICP and size, 1 pricing visit, silent for 45 days	35	5	-10	30	Cold

The point of the table is not the specific numbers. It is that every score can be explained in one sentence, which is what keeps a sales team using it.

Setting thresholds like an operator, not a mathematician

Two forces set your cutoffs. The first is separation: score your closed deals, and set the hot line where wins cluster and losses thin out. The second is capacity: the hot bucket has to be small enough that someone calls every lead in it today. A threshold that produces 40 hot leads a day for one salesperson is a fiction.

A practical starting method:

1. Set provisional lines at 70 for hot and 40 for warm. They are easy to remember and usually close enough to start learning.
2. For two weeks, review every hot lead after disposition. If most were not real buyers, the line is too low. If obvious buyers sat in warm, it is too high.
3. Adjust one weight at a time, never several at once, or you will not know what moved the result.
4. Write the change and the date in the Model tab. A model without a history is a rumor.

The backtest tab: does the model actually predict anything?

This is the step almost everyone skips, and it is the only one that proves the model deserves to exist.

1. Export your closed deals. Pull six to twelve months of closed-won and closed-lost deals from the CRM, with the contact data attached. Forty to sixty deals is enough to start.
2. Add the same columns and formula. Fill in the signal values, even if that means an afternoon of reading old conversations. Then let column N score them exactly as it scores new leads.
3. Compare the averages. For example, =AVERAGEIF($G$2:$G$61,"won",$N$2:$N$61) for wins against the same formula with "lost". Wins should average clearly higher. If they do not, the model is not ready.
4. Check the buckets, not just the average. =COUNTIFS($O$2:$O$61,"Hot",$G$2:$G$61,"won") tells you how many hot leads actually closed. Compare that rate with the base rate across all deals.
5. Read the misses. A lost deal that scored 90 usually reveals a missing disqualifier. A won deal that scored 10 usually reveals a signal you are ignoring - often referral source or relationship.
6. Re-run quarterly. A model tuned on referral leads will misjudge paid traffic, and vice versa. The mix changes; the model has to follow.

HubSpot describes the same tuning loop inside its own scoring tool - score criteria in, outcomes out - and the logic carries to a spreadsheet unchanged. If the tabs above are your first step, the tool can be your second.

Failure modes that quietly break scoring sheets

• Typed-over formulas. One manual edit in the score column and the backtest is measuring two models. Lock the formula column.
• Event timestamps nobody records. Decay only works if last_activity_date updates on replies, calls and bookings. Automate that before trusting the number.
• Scoring spam and vendors. Without the disqualifier column, junk inflates the queue and the team stops believing the scores.
• Too many signals. Every added rule is another thing to explain when a rep asks why a lead is hot. Eight is plenty to start.
• Changing weights mid-backtest. If you move the goalposts halfway through validation, you have two half-tests. Freeze the model, run the backtest, then revise.
• No owner. Scoring dies when nobody owns the model. Name one person to review it monthly and one date to revisit.

From spreadsheet to system

A spreadsheet is the right place to prove the model and the wrong place to run it at scale. Once the backtest passes, move the same logic into your CRM in this order: make sure replies, visits and bookings land as events with timestamps; encode the weights as properties or workflow rules; route hot leads to a call and warm leads to a sequence; alert someone when a cold lead suddenly spikes. The CRM automation service is the natural home for that build.

When the model holds up in the CRM and you want the scoring itself to run conversationally - asking the qualifying questions, capturing the answers, updating the score in real time - that is what an AI lead scoring setup or the AI sales qualification workflow provides. And if you are still deciding whether to automate before or after proving the model manually, the answer is always after; automation multiplies whatever accuracy you built. When you are ready to write the actual qualifying questions, our guide to lead qualification questions covers the wording.

FAQ

What is a simple example of a lead scoring model?

A simple model multiplies yes-or-no fit signals by weights, adds capped engagement points, then subtracts disqualifiers and decay. For example, industry fit adds 20 points, a budget or timeline signal adds 20, each reply adds 10 up to two replies, and a competitor or student status subtracts 50. Anything above 70 is hot, 40 to 69 is warm, and below 40 is cold.

Can I build lead scoring in Google Sheets or Excel?

Yes. You need three things a spreadsheet does well: yes-or-no columns for fit signals, count columns for engagement, and one formula that sums weighted values. Functions like SUMPRODUCT, SUMIFS, AVERAGEIF and COUNTIFS are enough for both scoring and backtesting the model against closed deals.

How do I choose the score thresholds?

Pick them from your own closed deals, not from a blog. Sort last year's won and lost deals by score and find the cutoffs that separate them. A practical constraint matters too: the hot bucket should be small enough that a person can respond to every lead in it the same day.

How do I know the model is working?

Backtest it. Score six to twelve months of closed-won and closed-lost deals with the same formula and compare the averages. If won deals do not score meaningfully higher than lost deals, fix the model before automating anything. Re-run the backtest quarterly.

Next step

Build the two tabs this week with your own signals, then run the backtest before you touch automation. If the sheet passes but nobody has time to maintain it, that is the point where a scored qualification workflow pays for itself; start at the funnel breakdown on our home page or book a call and we will map the model into your CRM as a working system.