AdvisorMap

A data pipeline that turns 670,000+ public Form 5500 retirement-plan filings into an answerable question: which retirement plans need an advisor?

Every U.S. retirement plan above a size threshold files a Form 5500 with the Department of Labor each year — who sponsors the plan, who services it, what it holds, and what everyone gets paid. The data is public, enormous, and messy: truncated names, typo'd EINs, free-text provider fields, and coding conventions that changed mid-decade. AdvisorMap ingests three years of filings (2022–2024), cleans and normalizes them, and answers prospecting questions that retirement-plan advisors actually ask:

• Which plans have no investment advisor on record? (23,213 plans, $3.7T in assets)
• Which plans changed, gained, or lost an advisor this year? (~7,000 "in motion" plans)
• Which incumbent advisors are winning or losing plans year over year?

Built with Python, Parquet, and DuckDB, with a Streamlit explorer on top and a Postgres/Supabase schema drafted for the hosted version. No scraping, no paid data — everything derives from public DOL FOIA files and Census geocoding data.

Why publish this? Prospecting datasets like this are usually sold as SaaS subscriptions. The underlying data is public. This repo shows the unglamorous 90% of the work — ingest, normalization, entity resolution, and validation — that turns government CSV dumps into something a sales team could use. If you work in financial services data, the provider-normalization and caveats sections are the interesting parts.

Quick start

Requires Python 3.11+. First run downloads ~1 GB of DOL source files.

python3 -m venv venv && source venv/bin/activate
pip install -r requirements.txt
python scripts/run_pipeline.py          # ingest + normalize + validate
streamlit run app/streamlit_app.py      # local explorer at localhost:8501

How it's built

The pipeline ran as six phases, each adding a data layer and a validation script (scripts/validate*.py):

• Phase 1: master plan registry → Parquet, ZIP→county geocoding, initial Postgres schema.
• Phase 2: Schedule C Part 1 Item 2 (service-provider rows) ingest.
• Phase 3: service-code ingest + decoder, pension-only filter, provider normalization v1 (EIN-collapse + manual firm rollup).
• Phase 4: multi-year backfill (2022-2024), advisor-change detection year-over-year, pension-code category decoder.
• Phase 5: Schedule H plan financials, provider-role classification (bundled platform vs independent advisor vs recordkeeper).
• Phase 6: extended provider rollups (38 firms / 86 EINs), name-pattern fallback for no-EIN rows, refined lead-advisor logic, prospect-quality classification (high_confidence vs jumbo_verify).

Headline numbers (2024 filings)

Master plans (Phase 1)

Metric	Count
Total plans	197,065
Plans with Schedule C attached	73,548
Schedule C AND >100 active participants	61,437

Schedule C Item 2 — service providers (Phase 2)

Metric	Count
Service-provider rows	239,653
Distinct provider EINs	18,648
Distinct raw provider names	49,198
Avg providers per filing	3.2
Total direct + indirect compensation	$33.8 B

Cleaned for retirement-advisor prospecting (Phase 3)

Metric	Count
Pension prospects (Sch C + pension + >100 active)	55,773
...with at least one coded investment advisor (codes 26/27/68)	32,560
...with NO investment advisor (open prospect segment)	23,213
Distinct providers (post-EIN-collapse + rollup)	18,641
Of those, manually rolled-up multi-EIN firms	5

The two prospect-facing numbers to anchor on:

1. 32,560 plans where an advisor exists — this is the "incumbent advisor replacement" universe (you're competing for an existing seat).
2. 23,213 plans with no coded advisor — this is the "no incumbent" segment. Cleanest open territory.

Plans in motion (Phase 4) — 2024 vs 2023 comparison, prospect-quality only

Change type	Count
Changed advisor (different firm year over year)	2,399
New advisor (had none in 2023)	2,564
Lost advisor (had one in 2023)	1,994
Unchanged advisor	19,196

The "in motion" segment = 6,957 plans (2,399 changed + 2,564 new + 1,994 lost). These are the warmest prospect-quality plans because the sponsor is actively making decisions about who advises their plan. Caveat: 2024 data is incomplete (many plan-year-2024 filings hadn't yet arrived when the "latest" snapshot was taken on 2025-12-25), so the change counts will grow as later snapshots land.

Multi-year coverage (master plans):

Year	Plans	Schedule C	Prospects
2022	243,832	85,003	67,856
2023	231,046	78,210	63,876
2024	197,065	65,022	55,773

The declining counts reflect snapshot timing, not declining plans.

Plan-category mix on 2024 prospect plans:

Feature	Share
401(k) feature (token 2J)	84.0%
Profit-sharing (2E)	82.9%
Auto-enrollment (2T)	84.3%
Participant-directed (2G or 2P)	91.5%
SIMPLE 401(k) (2L)	6.2%
Defined benefit (1*)	4.5%
Money purchase (2H)	1.5%
ESOP (2N)	0.0%

Phase 5: Schedule H AUM and provider roles

Total addressable AUM in the 2024 prospect universe: ~$10.4 trillion. 98.7% of pension-prospect plans (55,052 / 55,773) have Schedule H attached, so we have asset data on essentially all of them.

Metric (2024 prospects)	Value
Total prospect AUM	$10.39 T
Average AUM per plan	$188.79 M
Median AUM per plan	$22.84 M
Largest single-plan AUM	$65.65 B
AUM where an advisor is coded	$6.68 T
AUM in the "no advisor coded" segment	$3.71 T

Asset-tier distribution (2024 prospects):

Tier	Plans	Share	AUM	Share of AUM
<$1M	572	1.0%	$0.27 B	0.0%
$1M – $10M	13,255	23.8%	$78 B	0.8%
$10M – $100M	30,981	55.5%	$1.03 T	9.9%
$100M – $1B	8,560	15.3%	$2.52 T	24.2%
$1B – $10B	1,548	2.8%	$4.08 T	39.3%
>$10B	136	0.2%	$2.68 T	25.8%

The $1B+ plans are 3% of prospects but 65% of total AUM. Classic long-tail concentration; segment your prospecting strategy accordingly.

Provider-role mix (2024) — what kind of provider each Schedule C row is:

Role	Provider rows	Distinct firms
Independent advisor (advisory codes, no recordkeeping)	63,433	8,975
Insurance only	53,096	5,100
TPA only	30,380	4,634
Bundled platform (advisory AND recordkeeping)	16,038	4,110
Recordkeeper only	16,092	4,071
Accountant	14,995	3,070
Trustee or custodian	11,229	3,192
Brokerage only	7,441	2,018
Actuary	3,821	556
Other	21,805	—

Why this matters: the same firm (Fidelity, Empower, Principal, etc.) shows up in different roles on different plans. Fidelity is a "bundled platform" on some 401(k)s and just an "independent advisor" or "recordkeeper only" on others. Filtering by role lets you ask the right prospect question:

• "Show me plans where the incumbent advisor is independent (not bundled)" — these are plans where you'd compete with a single-purpose advisory firm.
• "Show me plans with a bundled platform but no separate advisor" — these are plans where the recordkeeper is also acting as the advisor; an opening for an outside specialist.

Important caveat on "no advisor" mega-plans

Mega-plans in the "no advisor coded" segment (Bank of America $62.9B, RTX $58.7B, Walmart $50.8B, Boeing $23.3B, Pfizer $22B) almost certainly have advisors that are either coded under non-26/27/68 codes (e.g. 28 "investment management" for an OCIO), self-administered investment committees, or compensated under the $5,000 Schedule C reporting threshold.

Phase 6 introduced a prospect_quality classification that handles this: prospect plans are bucketed as high_confidence ($10M–$10B AUM, no advisor coded), jumbo_verify (>$10B AUM, no advisor coded — likely false-positive), small, missing_aum, or has_advisor. The big_open_prospects SQL view now defaults to high_confidence only. 56 plans / $1.08T in AUM are flagged jumbo_verify.

Top 5 high-confidence open prospects (2024):

AUM	Active	State	Sponsor
$9.97 B	7,790	NY	Pfizer Inc
$9.91 B	11,986	NJ	Novartis Corporation
$9.80 B	29,785	NJ	Siemens Corporation
$9.73 B	21,495	TX	Chevron Corporation
$9.35 B	29,566	NC	Nucor Corporation

Provider normalization (Phase 3-6)

Three layers stack on top of the raw Schedule C data:

1. Automated EIN-collapse. For each provider EIN, the most common name in the data (across all years) becomes the canonical name. Handles trivial spelling drift (the Item 2 name field is truncated to 35 chars) and rebrand events (e.g. "Resources Investment Advisors" became "OneDigital Investment Advisors" mid-period under the same EIN).
2. Manual firm rollup (data/reference/firm_rollups.csv). 38 real-world firms with curated EIN groupings — Phase 6 expanded this from 13 to 38 firms covering 86 EINs. The full list includes Fidelity, Empower, ADP, Schwab, Vanguard, John Hancock, LPL, Lincoln National, Wells Fargo, Morgan Stanley, JPMorgan, BlackRock, Invesco, Prudential, T. Rowe Price, Nationwide, Northwestern Mutual, Mercer, Aon, Marsh & McLennan, Willis Towers Watson, Northern Trust, Raymond James, Ameriprise, Loomis Sayles, Cetera, Kestra, Ascensus, Voya, Transamerica, Morningstar, State Street, Edward Jones, CAPTRUST, Creative Planning, Alight, Segal, Deloitte.
3. Name-pattern fallback (data/reference/name_patterns.csv, new in Phase 6). 57 regex patterns that map raw provider names to a normalized provider_id when the EIN is blank or doesn't match a curated rollup. This catches:
   • No-EIN rows. ~32K rows in 2024 had blank EINs but recognizable names (e.g. ~28K rows for Empower variants alone). These now route to the canonical firm.
   • EIN typos. Plan sponsors who wrote 041647786 instead of 042647786 for Fidelity still get rolled up correctly via the name.
   • Subsidiary EINs not in the manual list. A small Empower trust company with an obscure EIN but the name "Empower Trust Company LLC" now rolls up to empower.

Phase 6 results across 2022-2024:

• 102,664 raw (ein, raw_name) pairs → 48,891 distinct normalized providers
• 35.7% of all 782K provider-on-plan rows now attributed to a multi-EIN rolled-up firm (up from ~16% in Phase 5)
• The biggest jumps: Fidelity (16,758 → 48,418 rows attributed via patterns; this includes typo'd EINs and small affiliates), Empower (1,677 → 45,247), ADP (6,263 → 20,698), John Hancock (2,839 → 10,612), Wells Fargo (now 82 EINs across 4 business lines).

Caveats with name-pattern matching

Pattern matching is more aggressive than EIN-curation. Risks:

• Wrong-firm matches. "Fidelity National Financial" (title insurance) is unrelated to "Fidelity Investments." The patterns I wrote use specific prefixes (^FIDELITY INVESTMENTS, ^FIDELITY INVT) to avoid this. If you find a wrong rollup, edit name_patterns.csv to be more specific and re-run normalize_providers.py.
• Phase 6 caught a real Phase 4 mistake. EIN 841532243 was assigned to Empower in Phase 4 based on a 2024 filing. Across all years the EIN actually belongs to CAPTRUST (CapFinancial Partners), and only some 2024 filers used it under "Empower Advisory Group, LLC" (data entry errors). Phase 6 removed that EIN from the Empower rollup; the Empower-named rows now route via name pattern, and the EIN-tagged rows route to CAPTRUST.

Service codes (Phase 3)

The DOL Schedule C _CODES companion file is ingested as one row per (plan, provider, code). 583,369 code rows across 239,653 provider rows ≈ 2.4 codes per provider. Decoder lives in data/reference/service_codes.csv (55 codes mapped to short descriptions and coarse buckets like investment_advisory, recordkeeping, tpa).

Top codes by frequency (with descriptions):

50  78,074  Distribution (12b-1) fees
64  51,681  Commissions for insurance contracts
27  37,948  Investment advisory (plan)         ← prospecting filter
15  36,218  Brokerage (stocks/bonds/commodities)
37  33,936  Printing and duplicating
28  31,335  Investment management              ← prospecting filter
13  22,926  Administration
51  15,793  Recordkeeping fees
26  13,548  Investment advisory (participants) ← prospecting filter

For investment-advisor prospecting, the relevant codes are 26, 27, 68 (participant advisor / plan advisor / fund-level advisory fees).

Data sources

Source	File	Date pulled	Notes
DOL/IRS Form 5500 master plan registry	f_5500_{2022,2023,2024}_latest.csv	2025-12-25 / 2026-04-25	Downloaded from DOL ZIPs on first pipeline run
DOL Schedule C Part 1 Item 2	F_SCH_C_PART1_ITEM2_{2022,2023,2024}_Latest.zip	2026-04-25	https://www.askebsa.dol.gov/FOIA%20Files/{year}/Latest/
DOL Schedule C Item 2 service codes	F_SCH_C_PART1_ITEM2_CODES_{...}_Latest.zip	2026-04-25	same base URL pattern
DOL Form 5500 Schedule H	F_SCH_H_{2022,2023,2024}_latest.zip	2026-04-25	Plan financials (assets, contributions, expenses)
Census 2020 ZCTA-County relationship	tab20_zcta520_county20_natl.txt	2026-04-25	https://www2.census.gov/geo/docs/maps-data/data/rel2020/zcta520/tab20_zcta520_county20_natl.txt
Form 5500 Schedule C service code list	data/reference/service_codes.csv (committed)	2026-04-25	DOL 2024 Schedule C Instructions; code 73 needs verification
TYPE_PENSION_BNFT_CODE token decoder	data/reference/pension_codes.csv (committed)	2026-04-25	DOL 2024 Form 5500 Instructions; rare codes flagged "verify"
Manual firm rollup (multi-EIN groups)	data/reference/firm_rollups.csv (committed)	2026-04-25	38 firms, 86 EINs; extensible

Project layout

data/
  raw/           # source CSV + layout (gitignored)
  processed/     # parquet outputs (gitignored)
  reference/     # geocoding lookup (committed, ~770 KB)
scripts/         # python pipeline
sql/             # Supabase migrations (not yet executed)
venv/            # virtualenv (gitignored)

Running the pipeline

Requires Python 3.11+.

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

python scripts/run_pipeline.py

Local UI (Streamlit)

After the pipeline has run (so data/processed/ is populated):

source venv/bin/activate
streamlit run app/streamlit_app.py

Opens at http://localhost:8501. Three tabs:

• Prospects — filter the 55K+ prospect plans by state, AUM tier, advisor presence, year-over-year change, plan type, and sponsor name search. Click a row for a detail panel with the full provider list, year-over-year advisor history, Schedule H summary, and admin-fee ratio.
• Top movers — gainers/losers leaderboard (which advisors are picking up vs losing prospect-quality plans year-over-year).
• About — methodology and caveats.

The app reads parquet files directly via DuckDB — no separate database or backend service. Local-only; no auth, no public deploy.

run_pipeline.py runs:

1. For each year [2022, 2023, 2024]: convert_to_parquet.py, ingest_schedule_c.py, ingest_schedule_c_codes.py, ingest_schedule_h.py. Source files download as ZIPs from DOL on first run and are cached in data/raw/.
2. build_geocoding.py — Census ZCTA-County relationship → ZIP-county lookup.
3. normalize_providers.py — multi-year EIN-collapse + 13-firm manual rollup → providers.parquet and provider_ein_map.parquet.
4. classify_provider_roles.py — derive each (provider, plan) row's role (bundled_platform, independent_advisor, recordkeeper_only, etc.) from the Schedule C service codes.
5. detect_advisor_changes.py — for each (sponsor EIN, plan number, year) identify the lead investment advisor and compare year-over-year.
6. validate*.py — DuckDB queries against each layer.

All ingest scripts accept --year YYYY so single-year reruns are cheap.

Validation results (from scripts/validate.py)

Total plan count:           197,065

Top 10 states by plan count
  CA   21,650    TX   15,165    NY   13,271    PA    9,100
  FL    8,864    IL    8,791    OH    7,938    MA    6,963
  MI    6,318    NJ    5,998

Schedule C attached:         73,548
Active participants > 100:  118,442
Schedule C + >100 active:    61,437
Wisconsin (ZIP 53xxx):        3,065

Top NAICS BUSINESS_CODE values
  621111  Offices of physicians                  6,602
  541990  Other professional services            5,386
  611000  Educational services                   5,380
  541110  Offices of lawyers                     4,375
  541600  Management consulting                  3,540
  ...

The plan-type code distribution (TYPE_PENSION_BNFT_CODE) is dominated by multi-character bitmask strings like 2E2F2G2J2K2T3D (8,365 plans) — these encode multiple pension-plan features and need a small lookup table to make human-readable. That's a Phase 2 task.

Supabase schema (planned, not executed)

See sql/. Six migration files:

sql/0001_init_plans.sql — master plan tables:

• sponsors — one row per EIN, deduped. Indexes on state, zip, NAICS, and a trigram index on name (requires pg_trgm).
• plans — one row per ACK_ID. FK to sponsors. Indexes on EIN, filing year, Schedule C indicator (partial), and active participant count.
• zip_county — the geocoding lookup.
• prospect_plans view — Schedule C + >100 active.

sql/0002_schedule_c.sql — service-provider tables:

• plan_service_providers — one row per (ACK_ID, ROW_ORDER). FK to plans.ack_id. Stores raw provider name, EIN, address, direct + indirect compensation. provider_normalized_id column reserved here, populated in Phase 3.

sql/0003_phase3.sql — codes, normalized providers, prospect views:

• service_codes — 55-row decoder, populated from data/reference/service_codes.csv.
• plan_provider_service_codes — one row per (plan, provider, code).
• providers — normalized firm table, populated from data/processed/providers.parquet.
• provider_ein_map — every (EIN, raw_name) → provider_id.
• prospect_plans view rebuilt with the pension filter and a has_investment_advisor flag.
• open_prospect_plans view — prospect plans with no coded investment advisor (the 23,213-plan open-territory segment).

sql/0004_phase4.sql — multi-year + advisor change tracking:

• pension_codes — 41-row decoder for TYPE_PENSION_BNFT_CODE tokens.
• plan_categories view — derives is_401k, is_db, is_esop, has_auto_enrollment, etc. from the bitmask.
• advisor_changes — one row per (sponsor_ein, plan_number, year) with a change_type column (changed / new_advisor / lost_advisor / unchanged / no_advisor_either_year / no_prior_filing).
• in_motion_plans view — the headline prospect view: plans with changed/new/lost advisor, filtered to prospect-quality (>100 active, pension code present).

sql/0005_phase5.sql — Schedule H + provider roles:

• schedule_h — plan financials, one row per filing.
• plan_asset_tiers view — buckets each plan by AUM tier and computes admin_expense_ratio.
• plan_provider_roles — one row per (provider, plan, year) with the derived role classification.
• prospect_plans view rebuilt to include aum, admin_expense_ratio, has_independent_advisor, has_bundled_platform.
• big_open_prospects view — prospect plans with no coded advisor, sorted by AUM (rebuilt in Phase 6 to filter to high_confidence only).

sql/0006_phase6.sql — extended rollups + prospect quality:

• provider_name_patterns — regex name → provider_id table.
• prospect_quality view — classifies each prospect plan as has_advisor / high_confidence / jumbo_verify / small / missing_aum.
• big_open_prospects view rebuilt to filter on high_confidence.

Schema is intentionally raw at the load layer (single-char Schedule indicators kept as TEXT) so loads stay tolerant. Normalization happens in derived tables.

Open questions / Phase 7 prerequisites

1. Supabase load. Six SQL migrations now drafted across phases 1-6. Time to actually run them and benchmark prospect-query latency. Decision point: COPY from parquet via DuckDB? \copy? Foreign data wrapper?
2. Provider audit CSV. Phase 6 added aggressive name-pattern matching; ~280K rows are attributed to multi-EIN rollups. Worth generating an audit CSV (data/processed/provider_audit.csv) listing every (ein, raw_name) → provider_id mapping with the rule that triggered it (ein_rollup / name_pattern / synthetic). Lets us spot wrong rollups quickly.
3. Schedule A / Schedule R / Schedule SB ingest. Schedule A (insurance contract detail), Schedule SB (DB plan funding). Rounds out the data model for non-401(k) prospects.
4. Auto-refresh of 2024 snapshot. The 2024 numbers undercount because the "latest" snapshot was extracted on 2025-12-25 before all 2024 filings arrived. Re-pulling quarterly will increase change counts.
5. Pension code completeness. 11 rare tokens are flagged "(verify)" in pension_codes.csv — diff against the 2024 Form 5500 Instructions PDF.
6. Service code 73 description. Same situation in service_codes.csv.
7. PROVIDER_OTHER_RELATION bucketing. Free-text field — needs regex bucketing (none / sponsor / affiliate / fiduciary / other).
8. Geocoding edge cases. Filings with no ZIP, foreign address, ZIP+4.
9. API layer. Once Supabase loads cleanly, build a thin REST/GraphQL layer that exposes the prospect-search queries. Then frontend.

Status

Phases 1-6 done. python scripts/run_pipeline.py runs all multi-year steps clean end-to-end. Provider attribution: 35.7% of provider-on-plan rows now roll up to a multi-EIN normalized firm. Ready for Phase 7 (Supabase load, provider audit, and remaining schedule ingests) without cleanup.

About

Built by Spencer X Smith — I build AI and data systems for financial-services and legal clients. More at spencerXsmith.com.

Licensed under the MIT License. The underlying Form 5500 data is public information published by the U.S. Department of Labor; nothing in this repo redistributes it — the pipeline downloads it from DOL directly.