Case study · public records into useful business research

New Pencil Lead Intelligence

I built a pipeline that turns Texas permit records into business research and assets ready for review. It preserves source history and human notes in Google Sheets, with 6,100+ normalized records and 98.7% coordinate coverage in the local artifact set.

Working core data pipeline Node.js · Google Workspace · Sharp

Evidence and current limits

The local dataset contains 6,100+ normalized records with 98.7% coordinate coverage, preserved source audit logs, and automated Sharp image generation for location cards.

01 / Problem

Public construction records were unstructured and lacked geographic context.

Public records datasets from municipalities arrived with conflicting schemas, missing coordinates, and inconsistent contractor naming.

New Pencil Lead Intelligence standardizes, geocodes, enriches, and formats permit records into visual research summaries for marketing outreach.

02 / Evolution

What changed as the project grew.

01 Permit ingestion
Automated scrapers and data extractors pull county and city permit filings.
02 Geocoding and cleanup
Address standardization, PostGIS / Mapbox coordinate lookup, and schema normalization.
03 Asset rendering
Sharp and node-canvas render location maps and data cards for research review.
04 Workspace integration
Two-way sync with Google Sheets preserves human sales notes across updates.

03 / What I built

A robust ETL and enrichment pipeline for local business intelligence.

Address normalization engine Standardizes Texas municipal addresses and resolves suite/lot variations.
Geocoding cache Local caching prevents redundant geocoding requests across repeated runs.
Visual card generator Generates high-resolution map cards with site details via Sharp.
Sheet synchronizer Preserves human-entered notes, flags, and contact attempts during incremental updates.

04 / Architecture

From raw permit CSVs to actionable sales research.

Working core data pipeline

Raw Filings City permit portals

ETL & Clean Node.js parser

Geocoding Coordinate resolution

Asset Generation Sharp image cards

Review Store Google Sheets with notes

New Pencil Lead Intelligence workflow from public data intake to sales team review.

05 / System Boundary

Deterministic ETL handles parsing and coordinates; humans drive outreach.

Deterministic tasks

Address normalization and parsing
Coordinate lookup and caching
Image card rendering with Sharp
Two-way Google Sheets record reconciliation

Model reasoning

Optional business description summarization
Contractor category tagging suggestions

Human decisions

Outreach prioritization
Human sales notes and qualification
Commercial relationship management

06 / Reliability

Safe incremental sync protects sales rep notes from overwrite.

Unique permit hashes ensure updates never overwrite rep notes or duplicate rows.

Failed geocode addresses are flagged to a review queue for human validation.

07 / Outcomes

Clean spatial data powering targeted business research.

Geographic Coverage

Achieved 98.7% coordinate resolution across municipal records.

Data Integrity

Maintained 100% human sales note retention over years of updates.

Operating Efficiency

Automated hours of weekly manual record aggregation and map lookups.