Sales Propensity Scoring & Lead Prioritization

Executive Summary

Problem

The client, a US commercial services company backed by a private equity firm, relied on field sales reps to originate new business. The reps were spending a large share of their time searching for and mapping potential customers instead of knocking on doors and converting.

Three problems compounded each other:

• Slow prospect discovery: Finding and mapping prospects was a manual process that took hours per rep, per territory.
• No prioritization: Leads were pursued on gut feel rather than predicted lifetime value, leaving a highly addressable market untapped.
• Poor CRM data quality: Years of acquisitions and inconsistent processes had degraded the HubSpot data, making it hard to even acquire accurate contact details from third parties.

Solution

We built a propensity scoring and zoning platform that tells each rep where to go and who to talk to.

The platform enriches prospects with third-party and public data, scores them with a model trained on the client’s own transaction history, and clusters the high scorers into geographic zones. Every score comes with a plain-language explanation of what drove it, and the results are served as PowerBI dashboards surfaced directly inside HubSpot — so reps never have to leave the tool they already use.

Outcome

The engagement ran for 12 weeks in fortnightly sprints and delivered a repeatable, testable and auditable pipeline:

1. A Python ingestion layer that validates and loads third-party enrichment exports (Clay) into Snowflake, alongside public reference data (census business statistics, fire-incident statistics, rurality classifications).
2. A dbt transformation layer with layered staging and conformed models, data-quality tests and statistical drift checks.
3. A gradient-boosted model that predicts customer value, trained and tuned with a fully reproducible, version-controlled process.
4. Human-readable reason codes for every score, generated with SHAP and a template system.
5. In-warehouse inference: the trained model scores new prospects inside Snowflake, so no data ever leaves the secured environment.
6. A zoning solution that clusters high-scoring prospects into right-sized geographic zones, plus PowerBI dashboards integrated into HubSpot.
7. A HubSpot data quality assessment with recommendations for improvement.

The target was to cut prospect discovery from hours to minutes and lift conversion through better targeting. The tool was handed over before a full sales cycle completed, so conversion impact was not yet measured at the time of writing.

---

The challenge

1. You can’t model what you can’t trust

The obvious place to start was the CRM. Unfortunately, our initial assessment confirmed what the client already suspected: years of integrations and non-standardized ways of working had left the HubSpot data patchy and inconsistent.

Rather than trying to fix years of CRM history first, we changed the anchor. The client’s field-service platform (ServiceTrade) held the “bottom-of-the-funnel” truth: jobs, invoices and revenue. That data was reliable, because it was tied to money actually changing hands.

So the data strategy became:

1. Use ServiceTrade as the ground truth for what a valuable customer looks like.
2. Use HubSpot for upper-funnel sales activity, but only where the quality allowed.
3. Use Clay as the enrichment engine, filling in the attributes neither system had: square footage, NAICS codes and contact details.
4. Layer on public reference data — census business statistics, fire-incident statistics and rural/urban classifications — rolled up to ZIP code level.

The enrichment data arrived as periodic file exports, and export formats have a habit of drifting over time. So the Python ingestion layer normalizes column headers to a canonical scheme, validates structure and types with a schema-validation library before anything is loaded, and stamps every row with an ingestion timestamp and pipeline version. When something looks wrong six weeks later, every row can be traced back to the run and code version that produced it.

Downstream, a dbt transformation layer turns the raw tables into clean, conformed dimensions: one row per location, a ZIP-code rollup, and an industry-code rollup that reconciles multiple vintages of the NAICS standard into one consistent hierarchy. Deterministic tests (uniqueness, null thresholds, accepted values) fail the build when violated. On top of those we added a custom statistical drift test that compares a column’s distribution against a historical reference using a chi-square-style statistic — a cheap guardrail that catches a broken join or a bad ingest before it silently propagates into the model. It can be noisy around legitimate shifts, but we would rather investigate a false alarm than ship a quietly broken score.

As a side effect, the enrichment pipeline also cleaned up a chunk of the CRM: bulk record updates pushed the enriched attributes back into HubSpot.

2. Defining what a “good customer” actually means

A propensity model is only as good as its training target, and “customer value” turned out to be the most debated artifact of the project. Annual revenue alone was misleading: a customer with one large invoice is not the same as one with steady recurring work across several service lines. Together with the client’s SMEs we settled on an explicit objective function that combined annual revenue, invoice duration and the number of unique service lines.

The weights were iterated on during the EDA workshops, and keeping the formula configurable and documented (rather than hard-coded in a pipeline) meant the debate could happen with the sales leadership in the room.

One subtle decision: the target is constructed in Python, not SQL. That way the normalization statistics are fit strictly on training data and reapplied at evaluation and inference time — avoiding a quiet form of data leakage where held-out data influences training.

The model itself is a gradient-boosted decision tree regressor. Nothing exotic, and that is the point: the interesting engineering was in making it reproducible. Every hyperparameter lives in a typed configuration object, tuning uses randomized search (an exhaustive grid becomes infeasible fast) with k-fold cross-validation and fold-level early stopping, trials are ranked by mean absolute percentage error, and the full leaderboard is persisted as a versioned artifact. Seeds and splits are fixed, so any trained model can be traced back to the exact configuration and data that produced it.

The EDA itself produced a satisfying result: location square footage and industry classification (NAICS) were the strongest predictors of high lifetime value. Which conveniently were exactly the attributes Clay could enrich at scale — so the model could score net-new prospects that had never touched the CRM.

One design decision was non-negotiable: no black box. Sales reps do not change how they work because a spreadsheet told them to. We used SHAP values to quantify each feature’s contribution to a prediction, and a template system converts the top contributors into short natural-language sentences — e.g. whether a specific industry, building size or geographic context pushed the predicted value up or down, and whether the score sits in a low, average, high or very high band relative to the training population. A rep can sanity-check the model against their own experience. Trust first, accuracy second.

3. The model never leaves the warehouse

Two failure modes kill scoring systems like this in production: training/serving skew, and data sprawl. We designed against both.

For skew, we maintain two parallel feature tables with identical structure — one built from known locations (where historical outcomes exist, used for training) and one from candidate locations (used for scoring). A shared dbt macro assembles the feature set for both, so the two cannot drift apart structurally. A data-contract validation step runs before every training job and fails fast with a specific error if columns are missing, unexpectedly null, or would produce invalid results (say, a negative value heading into a log transform).

For sprawl, inference runs as a stored procedure directly inside Snowflake, using the warehouse’s native support for server-side Python. Trained models are registered in the built-in model registry with a hash of the feature schema, the target formula version and the evaluation metrics. No feature data is ever exported to an external scoring service, and the identical Python code path handles both training and scoring.

The trade-off is real: tighter coupling to the warehouse platform, inference compute billed on the warehouse’s cost model, and debugging code that runs inside the warehouse is less pleasant than debugging it locally. We took that deal — for a batch scoring workload, no data egress and a single code path were worth more than portability.

The pipeline runs under CI, with the transformation tests executed on every change. Honest limitations: there is no dedicated monitoring dashboard for model performance and drift over time yet, and the orchestration schedule was not formalized during the engagement — both are documented as planned improvements rather than quietly ignored.

4. Turning scores into a sales motion

A ranked list of 10,000 prospects is not a plan. The reps’ day is a physical route, so the scores had to become geography.

We clustered high-scoring prospects into zones (by zip code or proximity), sized so a rep can work one in a trip. The internal vocabulary that emerged: each zone should mix “buffalo” (large anchor accounts worth the drive) with “squirrels” (smaller, high-margin deals a rep can pick up while in the area). Optimizing for buffalo alone leaves the rep with long empty drives; squirrels alone never pays for the fuel.

To make the scores digestible, a final SQL layer keeps the most recent score per location and adds tiered bands — bronze, silver and gold — at both the individual-location and zone level. A rep does not need to reason about a regression output; “a gold zone with two gold buffalo in it” is a plan.

The last mile was adoption. We evaluated three ways of getting the output in front of the reps, from manually copying records out of PowerBI, through Power Automate flows, to bulk-inserting all prospects into HubSpot and letting the team deduplicate. The guiding principle was to keep the user experience simple: PowerBI dashboards embedded directly in HubSpot, with prospects added to the CRM only once they qualify as leads. No new tool to log into, no parallel source of truth to maintain.