I turned public building violations into ~174 qualified leads a day.
Sixteen months and five contracts building the pipeline behind EG Constructs — daily NYC violation discovery, joined to the people legally responsible for each building, enriched into contactable leads.
A Lead List That Nobody Had Built Yet
Every day, New York City issues housing-maintenance violations against residential buildings. Mold. Lead paint. Each one is a building with a problem, a legal obligation to fix it, and someone responsible for paying to have that done. For a company selling remediation services, that is about as qualified as a lead gets.
The data is public. That is exactly why it looked easy — and why it wasn't. A violation record names a building. It does not name a person, it does not give you a company, and it certainly does not give you an email address. Between a public violation and a sales conversation sits a chain nobody had assembled: which building, who manages it, which company that is, and how to reach them.
Eli Glanz wanted that chain built once and then run on its own, every day, with the output landing in front of his team as a working list rather than a research project.
"Ultimately, we want to send automated emails to the property manager… without a human in the loop."
The opening brief was three lines long:
"– Scrape DOB
– filter in retool
– createGooglesheets"
Three lines that quietly assumed the hard part was already solved. It wasn't — and finding that out was the first real piece of work.
Three Datasets That Looked Right and Weren't
"Scrape DOB" presumes the DOB dataset contains what you need. Before writing a pipeline, the job was to prove that — and it kept failing.
3h2n-5cm96bgk-3dadwvxf-dwi5With the correct source identified, the next question was whether the opportunity was even big enough to automate. Querying the full year gave the answer:
That second number needed care. Filtering violation descriptions for the word "lead" also catches every instance of leading — pipes leading to a riser, a stair leading to a landing. Left alone it would have inflated the figure and set the client's expectations against a number that wasn't real.
"There were quite a few violations that contained the word 'leading' and were counted as lead violations (but actually were not), so I put another filter that it should contain the word 'lead' but not 'leading'."
A Pipeline That Runs Itself, Every Day
Daily violation discovery
A scheduled workflow queries the Open Data API once per day for every violation type in scope, pages through the results, strips duplicates, and hands a clean batch downstream. Volume limits and the API base live in a single configuration node, so the run can be widened without touching the logic.
From a building to the person answerable for it
This was the part the brief assumed away. A violation gives you a building identifier; it does not give you a human. New York requires residential buildings to register their owner and managing agent, and that registration lives in its own datasets — separate from violations, and keyed differently.
Joining registration records to multiple-dwelling registrations on the registration number produces what the violation feed alone never could: the managing agent responsible for that specific building, with a business address attached.
Turning a company name into someone you can email
A managing agent's name is still not a lead. Most of these firms are small and have no meaningful web presence, so looking them up by name returns little — but they do hold domains they use for email.
The enrichment step resolves each organization to a domain and then pulls the decision makers behind it, so what reaches the sales team is a person and a role rather than a company that technically exists somewhere.
"Problem is that if it gives just a name, there is nothing we can do with that. We need contact information."
Enrichment credits are finite, so the query feeding it was narrowed to only the organizations still missing a domain. Records that had already been resolved were never sent again, which kept a daily pipeline affordable rather than burning the quota re-enriching the same firms.
Getting it in front of people who act on it
A pipeline that only fills a database is a pipeline nobody uses. The last stage pushes the day's output outward: templated outreach to the responsible party, internal notifications the moment a new batch lands, weekly reporting on its own timer, and a filterable dashboard the team works from directly — so nobody has to ask for an export.
The Bug That Would Have Quietly Poisoned the Database
Partway through, the shared companies table was restructured by someone else on the project. The primary key became a generated uuid, and the identifier that actually mattered — the enrichment provider's own organization id — was moved into an ordinary column.
Everything kept running. Nothing errored. That is what made it dangerous:
"uuid changes on every entry, and even if there is already a record of the same apollo_id then it will insert it as a new entry."— Waqass, flagging it before the damage spread
An upsert keyed on a value that regenerates each time is not an upsert — it is an insert wearing a disguise. Left alone for a few daily runs, the enriched company table would have filled with duplicates of the same firms, and every lead count downstream would have been wrong in a way that looks plausible.
The fix was structural: restore uniqueness on the identifier that is actually stable, then check whether records already written under the broken schema had to be cleared before they were trusted.
Two other constraints shaped the build in the same way. Enrichment was capped at a few hundred calls a day, which is why the pipeline was made to skip anything already resolved rather than re-request it. And the city's property-tax lookup — the one source that reliably carried owner names — sat behind anti-scraping defenses, so it was ruled out in favour of the registration datasets rather than fought with.
Five Contracts and Sixteen Months Later
- A daily, unattended pipeline: violations discovered, normalized, joined to the responsible managing agent, enriched to a contactable person, and delivered — with no human in the loop, which was the brief.
- The research established the opportunity was real before the build started — roughly 174 qualifying violations a day, measured rather than assumed.
- Several public datasets that share no common key were reconciled into one store the team could query and segment for themselves.
- The relationship ran across five separate contracts — dashboards, enrichment, the violations pipeline, workflow automation, and bug work — over roughly sixteen months.
What a Research Phase Buys You
Much of the value on this project was delivered before a pipeline existed: proving which dataset was real, measuring whether the opportunity justified the build, and reporting the dead ends honestly rather than quietly building on top of a source that didn't hold up.
Eli paid for that research phase up front, unprompted — "to show my commitment on this one" — before any of the automation had been written.
Beautiful done actually.
Four more contracts followed it.
Is there a lead list hiding in public data that nobody has assembled yet? That is usually a pipeline problem, not a data problem.
Book a call with Botify →