A naive portfolio-wide relief-rate comparison flags the wrong company. Using 391,728 real CA consumer complaints (Jan 2024–Apr 2025), this project finds why, and what to compare against instead.
Deliverables: Executive Brief — misrouting · Decision Memo · BRD · KPI Dictionary · Data Notes · Live Dashboard
Stack: Python (API ingestion) · DuckDB / SQL (analysis) · Tableau (dashboard) · Claude + Copilot (AI-assisted)
Problem. Companies handling consumer financial complaints are often benchmarked against a single portfolio-wide "relief rate" target. Does that comparison actually identify real service-quality problems, or does it just reflect which product a company happens to service?
Approach. Pulled 391,728 real CFPB consumer complaints for California (Jan 2024–Apr 2025) via CFPB's public API, cleaned and scoped the data (excluded credit reporting — 84.9% of volume, a separate workflow), and analyzed relief and timely-response rates by product and by company using SQL (DuckDB).
Findings.
- Relief rate varies 14x by product alone — 2.6% (student loan) to 36.8% (credit card) — before any company-level effect is considered.
- A naive company-vs-portfolio-average comparison flagged MOHELA as a poor performer (2.8% relief vs. 23.4% portfolio average). That's a false positive: MOHELA is roughly mid-pack within its own product category — 4 of 8 major student loan servicers show 0.0% relief.
- The real, evidence-backed outlier is EdFinancial Services: 50.7% timely response, about half of every peer servicer (88–100%), while still granting 0% relief.
- AI-assisted feasibility check: an LLM classification eval on 500 real complaint narratives (79.8% overall agreement with official categories) surfaced a likely intake-labeling problem, not a model-accuracy problem — one category ("Debt or credit management," 44% agreement) appears to absorb misfiled debt-collection complaints. Full method and hand-inspected examples in evals/README.md.
Recommendation. Replace portfolio-wide comparison with within-product-category benchmarking (KPI-04). Pilot on the student loan category (8 servicers, already scoped) for one quarter before extending. Full case in the decision memo.
Explore the interactive version on Tableau Public →
| Path | What it is |
|---|---|
docs/01_decision_memo.md |
1-page recommendation, with the corrected finding and why the naive comparison failed |
docs/02_brd.md |
Requirements for a within-category servicer scorecard |
docs/03_kpi_dictionary.md |
4 KPIs, including the mandatory product-stratification rule for relief rate |
docs/04_data_notes.md |
6 real API/ingestion bugs found (incl. a currently-open upstream CFPB bug) + 4 data-quality findings |
sql/05_dq_*.sql |
Data quality checks: coverage, duplicates, taxonomy drift, narrative bias |
sql/10_*.sql |
Core analysis: routing/timeliness, relief outcomes (findings F1–F3) |
sql/20_kpi_within_category_deviation.sql |
KPI-04 implemented — the within-category scorecard the memo recommends, with the verified flag output in the header |
src/ingest.py |
CA-scoped CFPB API ingestion (search_after pagination, documented API workarounds) |
This is a self-directed analysis on public data. It was not commissioned by CFPB, any servicer, or any employer. The stakeholder roles in the decision memo and BRD (VP of Consumer Response Operations, compliance analyst, etc.) are illustrative — they define who the deliverables are written for, not a real engagement. The data, the bugs found, and the findings are real.
This project hit a real methodological trap and corrected course in the open rather
than quietly fixing it: an early cut of the analysis compared each company's relief
rate against a pooled "all other companies" average, which flagged MOHELA as an
outlier. Checking that finding against product-level base rates (not just
company-level) showed the comparison was confounded — student loan servicing is
structurally low-relief across the board, not specific to MOHELA. The corrected
analysis, which controls for product category, is what's reflected in the memo and
BRD. The near-miss itself is documented in docs/03_kpi_dictionary.md's change log
and docs/04_data_notes.md, since knowing what comparison NOT to make is as much a
BA finding as the final recommendation.
pip install -r requirements.txt
# To approximate the snapshot analyzed here (391,728 rows). The original pull
# targeted a full 2 years and was stopped deliberately at 2025-04-13 — see
# data notes, ingestion issue #5:
python src/ingest.py --from 2024-01-01 --to 2025-04-13 --state CA --db data/complaints.db --snapshot my-pull
# create the analysis-scope view first, then run the numbered queries in order:
duckdb data/complaints.db < sql/00_views.sql
# then sql/05_*.sql, sql/10_*.sql, sql/20_*.sql against data/complaints.db (DuckDB)Note: a wider --to date will pull more data but won't reproduce the row
counts in this analysis, and sustained pulls get heavily throttled by the API
(see data notes, ingestion issue #5 — the original 15.4-month pull took ~9h40m
wall-clock). The CFPB database also updates daily, so even the exact command
above will return slightly different rows over time as new complaints for the
window are published.
CFPB Consumer Complaint Database (public, updated daily): https://www.consumerfinance.gov/data-research/consumer-complaints/search/
- CA only (deliberate scope decision — see data notes)
- Jan 2024–Apr 2025, not the full 2 years (stopped deliberately after a 15.4-month pull rather than burn ~6 more hours for diminishing-returns data — see data notes)
- Complaints ≠ incidents: this measures who complained to a federal regulator, not underlying service-failure rates
- Full caveat list in docs/04_data_notes.md