How to Build a Revenue Range Lead Scoring Formula

Company revenue looks like an easy lead-scoring input: put smaller companies in one bucket, larger companies in another, and award more points as the number rises. That is also how teams end up with a score nobody can explain six months later.

There are really two separate decisions. First, decide what different revenue ranges mean for your sales motion. Then encode that decision so a spreadsheet, CRM, SQL query and Python job all return the same answer. A weak band-to-points table makes every system consistently wrong; inconsistent implementation makes good business logic disagree with itself.

This guide handles that full revenue-scoring job and stops there. Revenue is one firmographic fit signal, not a complete lead-scoring model and not a proxy for buying intent. HubSpot’s current lead-scoring documentation makes the same distinction at platform level: annual revenue can contribute to a fit score, while engagement can be scored separately or combined later.

Start with the decision, not the brackets

Before drawing cut points, define the revenue field you are actually scoring. “Revenue” may mean reported annual revenue, an enrichment provider’s estimate, ARR, or another financial measure. Those values are not interchangeable. HubSpot’s default company-property documentation, for example, defines annual revenue as the company’s actual or estimated annual revenue. If your CRM mixes both without provenance, the scoring model inherits that ambiguity.

A practical rule table should therefore have an owner and a data definition before it has points. At minimum, record the revenue metric, currency, source, observation date, and what should happen when the value is missing or invalid. If you operate across currencies, normalize the value before scoring or create market-specific models; do not quietly compare USD thresholds with unconverted local revenue.

Next, look for places where customer economics actually change. Useful calibration questions include:

• Where does win rate materially change?
• Where does median sales-cycle length change?
• Where do typical contract value or gross margin change?
• Where do retention and expansion patterns change?
• At what size do procurement, security, implementation or service requirements change enough to alter the sales motion?

That analysis is more defensible than choosing $1M, $10M and $100M because they are tidy numbers. It also explains why the largest band does not automatically deserve the most points. A mid-market offer can have an economic sweet spot and then become a worse fit as enterprise complexity rises.

One calibration caution matters if you use historical data: where possible, analyze the revenue value that was known when the account entered the pipeline or was scored. Using today’s enriched revenue for a deal from two years ago can leak future growth into the model and make the winning band look larger than it was at the decision point.

Turn the evidence into a small number of useful bands

Three to five scoring bands is a sensible default because the model stays legible to sales and each boundary can correspond to a real change in fit. It is not a law. Add another band only when it represents a decision you would make differently; a narrow interval that earns the same treatment as its neighbor is bookkeeping, not signal.

The shape of the points should follow the observed economics:

• Monotonic: higher revenue scores higher when bigger accounts are consistently better for the offer and delivery model.
• Top-heavy: lower bands receive little weight and the score jumps when an account enters the sales-worthy segment.
• Best-fit: the score peaks in the middle and falls again above a service or sales-complexity ceiling.

The table below is deliberately illustrative. It is not an industry benchmark and the point values should not be copied without calibration.

Notice the boundary convention: every numeric band is lower-inclusive and upper-exclusive. Exactly $10M belongs to Core, not Emerging; exactly $250M belongs to Enterprise. Writing that convention down removes the “which side owns the cutoff?” argument before it reaches code.

Also notice what the table does not decide. Whether a below-floor account should lose points rather than receive zero is a broader negative-scoring policy. How much revenue should count relative to industry, role, employee count or behavior belongs to the full lead-scoring model. This page only makes the revenue signal reliable.

Make the band table the contract

Once the band-to-points table is approved, the problem changes from model design to deterministic execution.

Do not let each system re-interpret the ranges independently. The approved table is the contract: same lower bounds, same labels, same points, same missing-value policy. A spreadsheet formula, SQL expression and Python function are merely different executors of that contract. For parity, those executors should receive either a finite, non-negative revenue value in the model’s agreed currency or an explicit unknown state; normalize text, currencies and malformed values before the score runs.

That separation is operationally useful. If sales disagrees with the Enterprise band scoring 25 instead of 40, change the model decision in one place. If SQL returns 40 while the spreadsheet returns 25 for exactly $250M, the model is fine and the implementation is broken. Mixing those two failure classes makes debugging much harder.

Score the table in Excel or Google Sheets

For a numeric revenue field, an approximate lookup is enough. Put the lower-bound table in ascending order and return the points associated with the largest lower bound that does not exceed the company revenue.

=IF(OR(B2="",NOT(ISNUMBER(B2))),0,IF(B2<0,0,VLOOKUP(B2,$H$2:$J$6,3,TRUE)))

Here, B2 is annual revenue and H2:J6 contains lower bound, band and points. The formula returns points from column 3; use the same lookup with column index 2 when you also need the band label. Microsoft documents that approximate VLOOKUP assumes the first lookup column is sorted; an unsorted table can return the wrong result. That makes “sorted ascending by lower bound” part of the implementation contract, not an optional spreadsheet preference.

For the illustrative table, $49,999,999 returns 25 and $50,000,000 returns 40. $250,000,000 returns 25 because the Enterprise row starts exactly at that value.

Run the same first-match rule in SQL

SQL makes the execution order visible. List the highest lower bound first, handle NULL explicitly, and let the first true branch win:

SELECT
    company,
    annual_revenue,
    CASE
        WHEN annual_revenue IS NULL OR annual_revenue < 0 THEN 'Unknown'
        WHEN annual_revenue >= 250000000 THEN 'Enterprise'
        WHEN annual_revenue >=  50000000 THEN 'Sweet spot'
        WHEN annual_revenue >=  10000000 THEN 'Core'
        WHEN annual_revenue >=   1000000 THEN 'Emerging'
        ELSE 'Below floor'
    END AS revenue_band,
    CASE
        WHEN annual_revenue IS NULL OR annual_revenue < 0 THEN 0
        WHEN annual_revenue >= 250000000 THEN 25
        WHEN annual_revenue >=  50000000 THEN 40
        WHEN annual_revenue >=  10000000 THEN 25
        WHEN annual_revenue >=   1000000 THEN 10
        ELSE 0
    END AS revenue_points
FROM accounts;

PostgreSQL documents the behavior that matters here: once a CASE condition is true, later branches are not processed. Put the $1M condition first and a $75M company will match it immediately, scoring 10 instead of 40. “First match” is therefore not a special lead-scoring feature; it is the consequence of ordered conditional evaluation. It matters for ordered CASE/if chains; clean, non-overlapping bins such as the pandas.cut example do not depend on first-match precedence.

Encode the same contract in Python

A plain function is the clearest general Python implementation:

import math

def score_revenue(revenue):
    if revenue is None:
        return "Unknown", 0

    try:
        revenue = float(revenue)
    except (TypeError, ValueError):
        return "Unknown", 0

    if not math.isfinite(revenue) or revenue < 0:
        return "Unknown", 0
    if revenue >= 250_000_000:
        return "Enterprise", 25
    if revenue >= 50_000_000:
        return "Sweet spot", 40
    if revenue >= 10_000_000:
        return "Core", 25
    if revenue >= 1_000_000:
        return "Emerging", 10
    return "Below floor", 0

For a dataframe whose bands are clean, numeric and non-overlapping, pandas.cut is more compact. The pandas documentation defines cut as a way to turn continuous values into discrete intervals and supports explicit bin edges. Using right=False gives the same lower-inclusive / upper-exclusive convention as the table:

import numpy as np

bins = [
    float("-inf"),
    1_000_000,
    10_000_000,
    50_000_000,
    250_000_000,
    float("inf"),
]

labels = ["Below floor", "Emerging", "Core", "Sweet spot", "Enterprise"]
points = {
    "Unknown": 0,
    "Below floor": 0,
    "Emerging": 10,
    "Core": 25,
    "Sweet spot": 40,
    "Enterprise": 25,
}

numeric = pd.to_numeric(df["annual_revenue"], errors="coerce")
invalid = numeric.isna() | ~np.isfinite(numeric) | (numeric < 0)

band = pd.cut(
    numeric.mask(invalid),
    bins=bins,
    labels=labels,
    right=False,
)

df["revenue_band"] = band.astype("object").fillna("Unknown")
df["revenue_points"] = df["revenue_band"].map(points)

Keep missing or invalid values separate after the cut instead of coercing them into “Below floor.” An unknown company and a known company with $0 revenue are different data states even if both happen to receive zero points in this illustrative policy.

Normalize text revenue before it reaches the score

Many enrichment feeds do not provide a clean number. They provide strings such as $1M-$5M, $200M+ or 10-50M. Do not scatter parsing logic through every scoring branch. Normalize once, then score the numeric output.

A deliberately narrow parser can extract the lower bound for common K/M/B forms after the currency has already been normalized. It accepts an optional dollar sign but rejects arbitrary currency codes or symbols rather than silently treating them as equivalent:

import re

def parse_revenue_lower(value):
    if value is None:
        return None

    text = str(value).strip().upper().replace(",", "")
    if not text:
        return None

    match = re.fullmatch(
        r"\$?\s*(\d+(?:\.\d+)?)\s*([KMB])?"
        r"(?:\s*-\s*\$?\s*(\d+(?:\.\d+)?)\s*([KMB])?)?"
        r"\s*\+?\s*",
        text,
    )
    if not match:
        return None

    number = float(match.group(1))
    unit = match.group(2)

    # For forms such as 10-50M, inherit the upper-bound unit.
    if unit is None and match.group(3) is not None:
        unit = match.group(4)

    multiplier = {None: 1, "K": 1_000, "M": 1_000_000, "B": 1_000_000_000}[unit]
    return number * multiplier

That parser is intentionally not an international finance parser. It does not convert currencies or interpret locale-specific number formats, and it should only run after the source currency has been normalized to the model contract. If a provider supplies fixed text bands, an even safer approach is often to map each provider band explicitly to your canonical band rather than infer meaning from a string.

Prove the boundaries before the score touches a workflow

A scoring rule is not finished when the formula looks plausible. It is finished when the exact edge cases produce the expected result in every implementation.

For this MAESIR draft, the normalized fixture was executed against the Python function, SQLite CASE, pandas binning and an approximate-lookup implementation that follows the spreadsheet table. Every tested record returned the expected band and score after malformed text was normalized to the explicit unknown state. The parser was separately tested against $1M-$5M, $200M+, 10-50M, $500K, $1B, blank, unparseable input and a non-normalized currency string.

Before activation in a real CRM, add two more checks: no numeric record should match zero bands, and no numeric record should match more than one band under the canonical interval definition. Then preview the score distribution before letting the score trigger routing, lifecycle changes, alerts or sales tasks. HubSpot’s current scoring tools expose record tests and distribution previews for precisely this kind of pre-activation check.

Version the rule, not just the code

Revenue scoring becomes part of reporting the moment another workflow consumes it. If the sweet spot changes from $50M-$250M to $25M-$100M next quarter, recalculating every historical record with the new table can change cohort counts, routing history and any dashboard that treats the score as if it were timeless.

For reproducible operations, keep a small model record alongside the logic: model version, effective date, revenue definition, currency policy, source, bands, points, missing-value behavior, and approver. Whether you snapshot the resulting score or recompute it depends on the reporting job, but the version that produced a decision should be recoverable.

That is the real finish line for a revenue range lead scoring formula. The bands reflect a business decision; the table states that decision unambiguously; every executor reads the same contract; and a fixture proves the boundaries. Once those four pieces move together, revenue becomes a reliable scoring input instead of a number that merely happens to be in the CRM.