Scrubbing a big file before it becomes somebody's database
There's a file. It has several hundred thousand rows, it came from a merger or an old system or three exports stapled together, and somebody has to make it usable.
Doing this while it is still a file is much easier than doing it after the import. A CSV has no integrations reading it, no reports built on it, and no automation firing off a change to one of its rows.
The order the work happens in
Normalize before anything else
Trim whitespace, lowercase the domains, strip the display names that came through as 'Jane Smith <jane@...>'. Malformed rows fail checks for reasons that have nothing to do with the mailbox.
Deduplicate on a normalized key
Gmail ignores dots and everything after a plus sign, so three rows can be one mailbox. Collapsing them first is free and cuts the volume you pay to check.
Drop the rows you already know about
Anything on a suppression list, anything that hard bounced before. Paying to verify a known failure is the most avoidable line on the invoice.
Verify the remainder in one batch
One job rather than several, so the results are consistent and you have a single file to reason about afterwards.
Split the output before importing
Valid and engaged goes in first. Everything unresolvable goes into a separate file with a date, which keeps it available without putting it in the sending list.
What each result means here
The same four results and their flags, read against this job. A catch-all worth keeping in one situation is one to exclude in another.
| Result | What to do with it |
|---|---|
| Valid | Import. |
| Invalid | Hold in a rejected file rather than deleting. On a merged file the invalid share tells you which source was worst. |
| Catch-all | Import into a held segment. On a business file this is frequently a quarter of what is left. |
| Unknown | Import into the same held segment and re-check next cycle. |
| Role flag | Import with a flag, so a later campaign can decide rather than the import deciding for it. |
| Disposable flag | Leave out. A throwaway address in an archive file has been abandoned for years. |
How you know it is finished
The import ran, every row carries a verdict and a date, and the rows you did not import are sitting in a named file rather than deleted. The rejected file is what you will want when somebody asks where a contact went.
What this does not fix
The order above is the price lever: duplicates and unknown results are never billed, so normalizing and deduplicating first frequently takes a 500,000-row file well under the $549 tier before a single check runs.
Questions people ask
Should I deduplicate before or after verifying?
Before, always. Duplicates are not billed here, but sending the same mailbox three times still wastes the job's time and leaves you three rows to reconcile.
How long does a large batch take?
It depends far more on the domains in your file than on its size. A list heavy in slow or throttling corporate servers takes longer than a consumer list several times bigger.
What should I do with the rows that came back unresolved?
Keep them, out of the sending list, with a date. A server that would not answer today may answer next quarter, and deleting them means re-acquiring the contact later.
Try it on the file in front of you
100 free checks a month, no card. Addresses we could not get an answer on come back labeled as unknown, and those are not billed.