# USPS bounce audit — missing unit numbers are a First American gap, not an ETL drop (2026-06-09)

## Bottom line

Direct-mail bounces in the 65-row manual-USPS sample (`usps_manual_sample.xlsx`: 30 bounced +
35 delivered controls, 7 counties — Bristol/Essex/Middlesex/Plymouth/Suffolk/Worcester MA +
Providence RI) are a **vendor data gap, not ours**. All 30 bounced rows matched 1:1 in Bronze
`Assessor_Annual` by situs address; **First American sent no unit number in any mailing field for
30/30**, at **both** the 2025-09 and 2026-04 vintages. Silver `Real_Estate_Master_V1` is
byte-identical to Bronze on every unit field (no Bronze→Silver drop), and the mailed address
equals FA's `MailingFullStreetAddress` exactly in 29/30 (the one diff is an Ave/St suffix, #25).
**Zero ETL drops anywhere in the sample.** 4/30 bounces (all Woonsocket RI) carry the unit
inside the APN (`WOON M:48Q L:96 U:20`, `U:8`, `U:26`, `U:24`) — recoverable at list-build with a
regex. `U:0000` (#24) is a placeholder, not a unit.

## Triple check

| Check | Layer / vintage | Result |
|---|---|---|
| Vendor sent unit? | Bronze `Assessor_Annual` 2026-04 | `MailingUnitNbr`, `SitusUnitNbr`, unit token in `MailingFullStreetAddress`: **empty 30/30 bounced** |
| Vintage artifact? | Bronze `Assessor_Annual` 2025-09 | Same — **0/30** had a unit |
| ETL drop? | Silver `Real_Estate_Master_V1` 2026-04 | Identical to Bronze on all unit fields; mailed addr == FA mailing addr **29/30** |

Controls prove the pipeline carries units when FA sends them: 6 delivered rows with units
(`# 209`, `# 81`, `# 749A`, `# 1025`…) show the unit in Bronze → Silver → on the mail piece.
Note: 23/35 delivered controls *also* have no unit anywhere — a missing unit is necessary but
not sufficient to bounce (bounces concentrate in owner-occupied 2–3-unit multifamily, where FA's
mailing address = situs without unit; USPS DPV code D "missing secondary").

## Method + cost

DuckDB-over-S3 (httpfs), projected pull of 20/319 Bronze and 20/481 Silver columns, filtered to
the 40 sample ZIPs → 340,637 Bronze / 341,585 Silver rows cached locally. No Athena. **Total AWS
cost ≈ $0.06** (S3 GET + ~300MB egress). Artifacts beside this file:
[script](usps_bounce_unit_audit_2026-06-09.py) ·
[per-row results CSV](usps_bounce_unit_audit_results_2026-06-09.csv) (65 rows × all three layers
+ verdicts). Local cache: `/tmp/usps_audit/cache/` (re-runs free).

Side discovery: `ignacio-claudecode` now has **full Bronze S3 read** (List+Get on
`8020rei-bronze-data-lake` verified 2026-06-09, incl. MA/RI counties) — the Bronze-vs-Gold
anti-join IAM blocker in [README.md](README.md) is stale.

## Open questions

- Campaign snapshot month unknown — bracketed by 2025-09 / 2026-04, both agree.
- Sample is MA/RI multifamily-heavy; national bounce mix may differ.
- APN `U:NN` recoverability beyond Woonsocket RI unquantified.
- Fix path: USPS DPV/Smarty enrichment at list build — append units or suppress code-D
  addresses pre-postage (FA will not supply what it doesn't have).