USASpending Enrichment Pipeline

Objective

My company sells compliance certifications, and one of the biggest is CMMC, a mandatory cybersecurity standard for companies that do business with the U.S. Department of Defense. The question was broader than any one list: who has actually been awarded federal money, and at what level, tracked systematically and on a recurring basis rather than pulled once and left to go stale. Nobody had ever answered it that way, the two data sources that could answer it are both public, both messy, and neither speaks to the other or to Salesforce out of the box.

So I built the bridge. This pipeline combines USASpending.gov (award-level federal contract spending) and SAM.gov (the federal contractor registration database) into a single, continuously refreshed federal-contracting layer on the Salesforce Account object.

The hard part: knowing what a "company" is

Federal award data doesn't identify companies, it identifies UEIs (Unique Entity Identifiers), which are registrations, not businesses. One real company can hold several UEIs, one per division, subsidiary, or office. So before any of this data means anything to a salesperson, it has to be resolved down to actual businesses:

• Same company, same country → every UEI resolving to it collapses into one Salesforce Account, holding all of its UEIs.
• Same company, different countries → each country gets its own Account, linked parent to child, so a US parent and its Canadian subsidiary don't get flattened into one confusing record.
• Genuinely different companies with confusingly similar names → resolved through a four-pass name and domain deduplication process, plus a persistent registry so the same award is never processed twice.
• Prime and subcontractor relationships → a dedicated junction object links the two directly, so an Account's record shows which primes it has worked under as a subcontractor, and which subs work beneath it as a prime, a relationship the raw award data only implies rather than states.

Getting the entity resolution wrong either merges unrelated companies together or splits one company into duplicate records, either one quietly poisons every report built on top of it later. Every pipeline script normalises dates immediately on ingest and never round-trips through Excel, for exactly that reason.

Keeping it current: the monthly refresh

New award data keeps arriving, so the pipeline has to keep matching new activity back to existing Salesforce Accounts without a stable ID to lean on for every case. It tries three tiers, in order, and gets progressively more cautious:

Every field that changes carries a CURRENT / DELTA / NEW TOTAL triple before it reaches Salesforce, so any update is reviewable against what it's replacing rather than landing as a silent overwrite. And because the most recent months of federal reporting are always underreported, catching up gradually as agencies file, the refresh deliberately pulls a wide window every run and relies on the dedup registry to safely ignore anything it's already processed, rather than guessing where "new" begins.

Where the data goes

The pipeline was originally built for one purpose, flagging CMMC targets, and now feeds several: a direct CMMC flag on the Account, a second pass reusing the same method to source FedRAMP 20x candidates among non-DoD software and cloud vendors, and a cross-sell trigger surfaced to reps who already own an account with an active federal contracting footprint. I designed and built the infrastructure end to end, and I've co-authored some of the sales plays that put the data to work once it lands.

Scale