First-Match Scoring Rules for Revenue Bands: SQL + Python

You already picked your revenue brackets and the points each one earns. That decision lives in your approved band-to-points table, and it is settled. This article is the next step: encoding that table as first-match rules so the same logic scores every record in SQL, Python, or pandas, not just one spreadsheet.

First-match logic, also called first-matching-condition scoring, evaluates your revenue bands in priority order and stops at the first rule that fits. Order the rules wrong and your best-fit accounts quietly score like your smallest ones. Order them right and one rule set scores a spreadsheet, a warehouse table, and a Python job identically.

This is an implementation reference, not a guide to choosing brackets. How many bands to use and how much each one is worth are part of a wider set of scoring criteria and signal weights; here, the table is a given. The job is encoding it so it runs the same way everywhere, then proving it does with a test.

What first-match logic means in revenue band scoring

First-match logic is a rule-evaluation pattern that checks revenue conditions in priority order and assigns the score of the first rule that evaluates true, ignoring every rule below it. The first match wins; the rest never run.

That “stop at the first hit” behavior is why order is the whole game. Checking revenue >= 1,000,000 before revenue >= 10,000,000 means a $42M account satisfies the $1M rule first and scores as your emerging band, never reaching the sweet-spot rule that should have caught it. List the most restrictive band first and the bug disappears.

You already use this pattern even if you have not named it. A SQL CASE WHEN returns the first branch that is true. A Salesforce or HubSpot lead-routing rule assigns the first matching condition. Power Query, numpy.select, and a plain chain of if/elif all resolve the same way. Revenue banding is the cleanest place to see it, because the bands are ordered numbers with no overlap once you sort them.

Build an ordered revenue-band rule table

An ordered rule table lists each revenue band once, sorted by priority, with a lower bound, an upper bound, a score, and a defined behavior for unknown values. Everything downstream reads from this one table, so it is the artifact to get right.

Here is the canonical table this article uses in every example that follows. It is a mid-market rubric: a sweet-spot band scores the most, an emerging band scores less, anything below the floor loses points, and missing revenue scores zero and gets flagged.

The below-floor band scores −10, a deduction rather than a zero. Whether too-small accounts should lose points or merely earn none is a negative-scoring policy call; this table just encodes whatever you have already decided. Notice the bounds: each band stops exactly where the next begins, so no revenue figure can match two bands.

In plain English the rule reads top to bottom: if revenue is missing, score 0; otherwise if it is at least $10M, score 25; otherwise if it is at least $1M, score 10; otherwise score −10. That ordering is the pseudocode, almost verbatim.

function score(revenue):
    if revenue is missing      -> 0     # unknown, flag for enrichment
    if revenue >= 10,000,000   -> 25    # sweet spot
    if revenue >= 1,000,000    -> 10    # emerging
    otherwise                  -> -10   # below ICP floor

Implement first-match rules in SQL (CASE WHEN)

To score revenue bands in SQL, write a CASE expression whose WHEN branches are ordered most-restrictive first; SQL returns the first branch that evaluates true. Put the unknown check at the top so a NULL never falls through to your lowest band.

SELECT
    company,
    revenue,
    CASE
        WHEN revenue IS NULL     THEN 0     -- unknown: score 0, flag for enrichment
        WHEN revenue >= 10000000 THEN 25    -- sweet spot
        WHEN revenue >= 1000000  THEN 10    -- emerging
        ELSE -10                            -- below ICP floor
    END AS revenue_score
FROM accounts;

Run that against the five sample accounts and the result matches the rule table exactly: NovaSoft and Orbit Labs score 25, Bridgeline scores 10, Tindale scores −10, and Halvorsen, whose revenue is NULL, scores 0. The WHEN revenue IS NULL line is doing real work. Without it, NULL fails every numeric comparison, slides past them into ELSE, and a missing revenue gets scored −10 as if the company were tiny.

Order is the other trap. Move the revenue >= 1000000 branch above revenue >= 10000000 and every account over $10M matches the $1M branch first and scores 10 instead of 25. PostgreSQL’s documentation is blunt about why: once a branch is true, “the remainder of the CASE expression is not processed”. The same CASE WHEN pattern also handles your other revenue metrics, like conversion and win rates computed straight in SQL, so the ordering reflex is worth building once.

Map revenue bands to scores in Python

To map revenue bands to scores in Python, write the same priority-ordered conditions as a chain of if/elif that returns the first match. Check for a missing value first, because comparing None with a number raises a TypeError in Python 3.

def revenue_score(revenue):
    if revenue is None:            # unknown: handle before any comparison
        return 0
    if revenue >= 10_000_000:      # sweet spot
        return 25
    if revenue >= 1_000_000:       # emerging
        return 10
    return -10                     # below ICP floor

That function is the rule table translated line for line, and it returns 25, 25, 10, −10, and 0 across the five sample accounts. For a whole DataFrame you have two good options and one to use with care.

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "company": ["NovaSoft", "Orbit Labs", "Bridgeline", "Tindale", "Halvorsen"],
    "revenue": [42_000_000, 150_000_000, 6_200_000, 480_000, None],
})

pandas.cut for clean, non-overlapping bins

pandas.cut is the most Pythonic way to bucket a numeric column, and it is the method the AI Overview reaches for first. Define the bin edges, the label for each bin, and let pandas assign every row at once, as the official pandas.cut reference documents.

bins   = [0, 1_000_000, 10_000_000, np.inf]
labels = [-10, 10, 25]

df["score"] = pd.cut(df["revenue"], bins=bins, labels=labels, right=False)
df["score"] = df["score"].astype("float").fillna(0).astype(int)   # NaN -> unknown -> 0

This is the right tool when your bands are clean, non-overlapping numeric ranges, which revenue bands usually are. The catch is that pd.cut returns NaN for anything outside the bins, including missing revenue, so you finish with .fillna(0) to land unknowns in the score-0 bucket on purpose rather than by accident.

numpy.select when precedence and unknowns matter

When the rules can overlap, need an explicit order, or have to handle missing values inline, numpy.select mirrors first-match logic directly. The NumPy docs describe it as choosing each value “using the first true condition”, which is exactly the behavior you want.

conditions = [
    df["revenue"].isna(),         # unknown, checked first
    df["revenue"] >= 10_000_000,  # sweet spot
    df["revenue"] >= 1_000_000,   # emerging
]
choices = [0, 25, 10]
df["score"] = np.select(conditions, choices, default=-10)   # default is "below floor"

Putting df["revenue"].isna() first is the vectorized version of the if revenue is None guard, and default=-10 is the ELSE. In a DataFrame the missing value arrives as NaN, not None, so the guard is .isna() here and is None in the scalar function. Same intent, different spelling per context. Reach for numpy.select when “first match wins” has to be obvious in the code, which is exactly when precedence is load-bearing.

Parse and normalize revenue-band strings (“0–1 million”, “$1M–$5M”)

To handle a revenue-band string, parse it down to its numeric lower bound, attach the right multiplier, and treat anything you cannot read as missing. Enrichment tools rarely hand you a clean integer; revenue arrives as text like “0–1 million”, “$1M–$5M”, “$100M+”, or “Unknown”, and a first-match rule needs a number.

The lower bound is enough because the bands are contiguous: for scoring, a record in the “$1M–$5M” band is a record at $1M. Pull the first number, attach its unit, and send unreadable values to the unknown bucket.

import re

UNIT = {"k": 1e3, "thousand": 1e3, "m": 1e6, "mm": 1e6,
        "million": 1e6, "b": 1e9, "bn": 1e9, "billion": 1e9}

def parse_band_lower_bound(raw):
    if raw is None:
        return None
    t = str(raw).strip().lower()
    if t in {"", "unknown", "n/a", "na", "none", "not available"}:
        return None
    t = t.replace(",", "").replace("$", "").replace("usd", "")
    tokens = [(float(n), u) for n, u in re.findall(r"(\d+(?:\.\d+)?)\s*([a-z]+)?", t) if n]
    if not tokens:
        return None
    trailing = next((u for _, u in reversed(tokens) if u in UNIT), None)
    low_val, low_unit = tokens[0]
    return low_val * UNIT.get(low_unit or trailing, 1)

One subtlety trips up naive parsers: in “1–5 million” the word “million” applies to both ends, so the lower bound is $1,000,000, not $1. This parser handles it by letting the first number inherit the last unit in the string when it has none of its own. Run it across a realistic mix and every string lands in the right band:

The two rows that should hold your attention are the ones that return None: “Unknown” and an empty cell. Both score 0, not −10, which is the whole point of the next section.

Handle missing, unknown, and overlapping bands

Three edge cases break revenue-band scoring in production: missing revenue, unparseable values, and overlapping bands. Each has a defined answer, and each belongs in the rule rather than in a cleanup pass afterward.

Missing and unknown revenue score 0, not your lowest band. A company you have no revenue for is unknown, not small. Scoring it −10 punishes an account that might be your best lead; scoring it 0 keeps it neutral and flags it for enrichment. This matters at scale because B2B data goes stale fast: contact and account records decay at about 22.5% a year, so a real share of your revenue fields are blank or wrong at any moment. It is also why pd.cut needs that trailing .fillna(0): its NaN is the unknown bucket, and leaving it as NaN means the account silently has no score at all.

Overlapping bands are a definition bug, not a runtime one. If two bands can both match a value, first-match logic still returns an answer, just not always the one you meant. The fix lives in the table: set each band’s lower bound equal to the previous band’s upper bound, so the edges meet without overlapping. A value of exactly $10,000,000 then belongs to one band by rule, and >= in descending order puts it cleanly in the sweet spot.

This is the real divide between the tools. pandas.cut assumes clean, non-overlapping bins, which forces the table to be well-formed before it will run. Ordered if/elif, CASE WHEN, and numpy.select will happily evaluate overlapping rules in priority order, which is what you want when precedence is deliberate. Pick the tool that matches whether your bands are guaranteed clean or intentionally ordered.

Test the rules against expected output

To trust a scoring rule, run it against a small set of records whose scores you already know and compare the result to an expected table. Five accounts, one per band plus an unknown, are enough to catch an ordering bug or a NULL leak.

Every implementation above, the SQL CASE WHEN, the Python if/elif, pandas.cut, and numpy.select, was run against these same five records and produced identical output:

Bake that table into an assertion so a future edit cannot drift the rules without the test failing:

records  = [("NovaSoft", 42_000_000), ("Orbit Labs", 150_000_000),
            ("Bridgeline", 6_200_000), ("Tindale", 480_000), ("Halvorsen", None)]
expected = [25, 25, 10, -10, 0]

assert [revenue_score(rev) for _, rev in records] == expected   # raises if a rule drifts

That agreement across four implementations is the reason to keep one canonical rule table: the SQL in your warehouse, the pandas job in your pipeline, and the spreadsheet on a rep’s laptop all score an account the same way, and a change to the table re-scores every one of them from the same source of truth. Once each record has its band score, that number is one input into a wider account-fit rubric that routes the account to sales, marketing follow-up, or decline.

Frequently Asked Questions