Automated Lead Intelligence Pipeline
Six-stage scheduled pipeline that processes hundreds of government records, ranks potential membership leads, and delivers Excel workbooks with no manual steps during routine runs.
The challenge
A regional trade association representing ~1,500 member construction companies needed to identify non-member firms doing active business in their territory. The data source: weekly government monitoring reports -- ~700-row Excel spreadsheets of municipal agenda items (city council meetings, planning commission hearings) with links to 100MB PDFs.
A row like "Approval of ABC Engineering's Traffic Impact Study" signals a named engineering firm doing active work. A row like "City Manager's Report on Q3 Budget Update" is noise. The pipeline needed to read linked documents, identify companies buried in agenda text, score each entry for sales relevance, extract contact information, cross-reference against 11,500+ member records, and deliver a prioritized briefing -- all autonomously, every week.
What was built
A six-stage pipeline deployed as a FastAPI microservice on Google Cloud Run (scale-to-zero), orchestrated by n8n:
Six processing stages
Parse: Spreadsheet ingestion with flexible column matching and hyperlink extraction, tolerant of header variations.
Deduplicate: If 20 agenda items reference the same 200-page PDF, it's only processed once.
Scrape: The system extracts content from PDFs, HTML pages, and municipal government portals — handling bot-blockers and malformed responses gracefully.
Score: Each row is analyzed and assigned a priority score with entity name, project type, summary, and personalized outreach message. Calibration rules apply a ranking preference to private companies associated with active work over government entities and policy-only items.
Enrich: Leads are cross-referenced against existing member records. Web research fills gaps: company website lookup, contact extraction, email quality verification. Government-only contacts are flagged — the sales team needs builder contacts, not city planners.
Assemble: Ranked leads sorted by actionability. The output is a multi-tab Excel workbook with top leads, priority queues, outreach messages, and scoring rationale — delivered directly to the sales team's inbox.
Scheduled orchestration
n8n runs on Docker with a queued (schedule-driven) pattern that processes one file per tick at 45-minute intervals. The production workflow watches a Google Drive folder for new files, triggers the pipeline, converts output to Excel, and emails it to the sales team. Routine batches run unattended from file drop through email delivery; failures and flagged exceptions remain subject to operational review.
The result
- Ranked leads delivered weekly — hundreds of government records processed into a prioritized workbook with actionable outreach messages
- ~2 hours per batch for a full weekly run, unattended
- No manual steps during routine runs from file drop through email delivery; exceptions subject to operational review