The fastest way to find duplicate email addresses in a lead list is to group by the address and count the rows, and on a scraped list the answer is usually worse than you expect. We ran that query on a client's accommodation dataset last week. 60,128 email rows came back as 3,921 distinct addresses.
The scrape itself was the clean part: 126,676 property pages fetched from the platform's own published sitemap, all 126,676 successful, zero failures, 10.4 hours at 3.39 pages per second. The interesting failure was not in the collection. It was in what the collected column actually meant.
The short version
- 60,128 scraped email rows collapsed to 3,921 distinct addresses across 126,676 accommodation properties.
- 290 addresses appeared on 10 or more properties each and covered 54,751 of them: 43% of the country's inventory on that platform.
- The largest single address managed 10,062 properties. Emailing it reaches a management company, not an owner.
- One group-by query separates property-direct contacts from intermediary inboxes. We now split every delivery on that line.
Property count is a vanity metric on a listing platform
A listing platform counts listings, not businesses, and the two numbers diverge badly in any market where managing other people's inventory is a trade.
We knew the ratio would not be one to one. We did not expect it to be 15 to 1. Only 2,776 addresses in the whole dataset were unique to a single property.
The rest of the file was concentration. 855 addresses looked like small managers, sitting on 2 to 9 properties each and covering roughly 3,600 between them. Then a much smaller group did most of the work.
290 addresses controlled 43% of a national market
290 email addresses appeared on 10 or more properties each, and between them they covered 54,751 listings. That is 43% of everything the platform published for the country.
The largest single address covered 10,062 properties on its own. Four more sat between 2,700 and 8,200. These are property management companies, and every one of their listings had quietly written the same inbox into our email column.
Properties covered, by address type
Read that chart the wrong way and you have a list of 126,676 businesses. Read it correctly and you have 2,776 owners, 855 small operators, and 290 intermediaries.
The concentration is invisible at row level. Every one of those 54,751 rows looks like a normal, complete, well-enriched contact record, correctly parsed and correctly stored. The duplication only exists after you aggregate, which is why nothing upstream flags it.
We tried to predict the collapse from a sample, and missed
Before committing to the full sweep we ran two projections off a random sample, and they disagreed by nearly 5x.
Fitting a curve to how fast new distinct addresses accumulated in random order predicted somewhere around 3,000 to 3,300 distinct addresses at full scale. Scaling up the count of single-property addresses predicted about 14,400. We noted at the time that both methods were biased and neither should be trusted.
The real answer, 3,921, landed just above the pessimistic estimate. That is worth knowing for the next market: address concentration saturates early, so a sample tells you the shape long before it tells you the number, and the optimistic method was the one that scaled a bucket rather than modelling the curve.
How to check for duplicate email addresses in a lead list
Group by email address, count rows per address, sort descending. That is the whole diagnostic, and it works on any list from any source.
Separating direct contacts from intermediary inboxes
Collapse to distinct addresses
Count rows per address. 60,128 rows became 3,921 addresses.
Bucket by row count
1 property, 2-9 properties, 10+ properties. The thresholds are a judgment call, the shape is not.
Label, do not delete
The 10+ bucket becomes an agency segment with its own copy, not a deletion.
Ship two files
Property-direct contacts and intermediary contacts leave as separate deliverables.
We now split every delivery on that line. The client gets a property-direct file and an agency file, because the message that works on an owner with one apartment does not work on an operations manager with 10,000.
Stated the other way, this source yielded 2,779 property-direct contacts against 55,575 that were shared with an intermediary. A second source we piloted for the same market returned 254 addresses, of which 243 were property-direct. A fraction of the volume, an entirely different value per row, and no way to see that from either row count.
Why we bucket at 10 properties, and why the threshold is arbitrary
There is nothing special about 10. It is where the distribution visibly changes shape in this dataset, and it is a number a human can hold in their head when reading a segment definition.
The honest version is that the boundary between "owner with a portfolio" and "agency" is continuous, not categorical. A family running 6 apartments in one town is an owner. A company running 6 apartments across three regions is an agency. Row count alone cannot tell them apart, and we do not pretend it can.
What the threshold buys is a sorted list. Everything above it gets reviewed as a segment rather than mailed as an individual, and everything below it keeps the ordinary treatment. Getting the boundary slightly wrong costs a handful of misfiled records. Not drawing it at all costs the credibility of the entire deliverable, because the first agency that receives owner-directed copy will say so.
We also keep the row count itself as a stored column rather than only the bucket label. Thresholds move; the underlying count does not, so re-segmenting later never means re-running the aggregation.
The honest limit: this is a fill-rate problem too
Only 47.5% of the properties returned an operator email at all, and the same share returned a phone number. So the 3,921 addresses are not "all the contactable businesses in the market", they are all the contactable businesses this one source exposes.
Named individuals were rarer still: 3.4% of properties, 4,270 people across the whole country. If your outreach depends on writing to a person rather than to an inbox, the usable list is a small fraction of a small fraction.
The check also says nothing about deliverability. A distinct address is not a live address, and an agency inbox is often better maintained than an owner's. Concentration analysis and validation answer different questions, and you need both.
And intermediary contacts are not junk. For some offers they are the better segment: one conversation reaches 10,000 properties. The mistake is not keeping them, it is counting them as 54,751 prospects.
What to do with this on Monday
Run the group-by on whatever list you are working with right now, before you use it for anything. It takes one query and it tells you how many businesses you actually bought.
If a single address is sitting on hundreds of rows, your list size and your addressable market are two different numbers, and only one of them is on the invoice.
Key takeaways
- A group-by on the email column tells you how many distinct businesses a list really contains, and scraped lists routinely collapse by an order of magnitude.
- Any address appearing on 10 or more records is almost certainly an intermediary, not the business you meant to reach.
- Label and segment intermediary inboxes rather than deleting them: they are a different offer, not a worse one.
- Store the per-address row count as a column so re-segmenting later never requires re-running the aggregation.
- Row-level enrichment quality tells you nothing about this problem. Concentration is only visible after aggregation.
Related reading: the MX record check is the other free query worth running on a list before anyone sends to it, and how to build a lead list that converts covers the sequencing of these checks. If this kind of validation should be running inside your operations rather than as a one-off, that is what we build into companies.
Common questions
How do you check for duplicate email addresses in a lead list?
Group the list by email address and count the rows per address. Anything sitting on 10 or more records is almost never the business itself, it is an intermediary managing many of them from one inbox.
Why does a large scraped list collapse to so few real contacts?
Because listing platforms show the managing party's contact details, not the property owner's. In our Croatian accommodation scrape, 60,128 email rows collapsed to 3,921 distinct addresses, and 290 of those addresses covered 54,751 properties between them.
Are agency inboxes worth keeping in a lead list?
They are worth keeping and worth separating. A message to an address managing 10,000 properties is a different conversation than a message to an owner with one, so it belongs in a different segment with different copy, not in the same file.