To clean a B2B lead list, work field by field: standardise names, reduce every website to a bare domain, store phones in +44 international format, format postcodes, verify and label emails, flag closed businesses, then merge duplicates on reliable keys. Finish with a gate that only passes rows that are complete enough, current enough and lawful enough to contact. The checklist below gives the rule, an example and the reason for each field.
This post is the hub for lead data cleaning on this blog. The Excel, encoding and dedupe problems each have their own fix page, linked where they come up.
Before you touch a cell
Three habits prevent most cleaning disasters:
- Keep the raw file untouched. Clean a copy. When a rule turns out to be wrong you can re-run it on the original.
- Add provenance columns. For every row record the source (Google Maps, Bing Maps, a website crawl, Companies House), the source URL and the date collected. You will need them for dedupe, for UK GDPR transparency and for answering "where did you get my details?".
- Decide what one row means. One row per business, or one row per location? A chain of five vet surgeries is one business with five locations. Pick before you dedupe, because the answer changes which keys you merge on.
Field rules at a glance
| Field | Rule | Before | After |
|---|---|---|---|
| Business name | Trim, collapse spaces, remove keyword stuffing, keep original in a source column | Bright Smile Dental - Best Dentist Leeds | Bright Smile Dental |
| Website | Lowercase, strip scheme, www., path and tracking; keep the registrable domain | https://WWW.BrightSmile.example/contact?utm_source=gmb | brightsmile.example |
| Phone | Digits only, then E.164 with +44 | (0113) 496 0000 | +441134960000 |
| Postcode | Uppercase, one space before the last three characters | ls61aa | LS6 1AA |
Trim, lowercase, strip mailto: and trailing punctuation, then verify | Mailto:Info@BrightSmile.example. | info@brightsmile.example | |
| Category | Map to your own short taxonomy; keep the source category | Cosmetic dentist | Dental practice |
| Status | Open, temporarily closed, permanently closed, dissolved, unknown | Permanently closed | excluded |
| Legal form | Limited company, LLP, sole trader, partnership, unknown | blank | Limited company (registered number found on site) |
| Provenance | Source, URL, date, search used | blank | google_maps, listing URL, 2026-11-10, dentist in LS6 |
All examples use fictional .example domains and Ofcom's reserved drama numbers.
Business name
- Trim leading and trailing spaces and collapse double spaces.
- Remove text appended for search ranking, such as " - Best Dentist Leeds" or "| Emergency 24/7". Google's own guidelines say unnecessary information in a business name is not permitted, but plenty of listings still carry it.
- Do not run blanket title case. It breaks "JD Plumbing", "McKenzie & Co" and "iFix Phones".
- Keep trading name and legal name in separate columns if you have both. "Bright Smile Dental" may trade through "B S D Holdings Ltd".
- Keep the original value in a
name_sourcecolumn so you can audit changes.
Website and domain
The domain is the most useful field in the whole record: it links the listing to the website, the email addresses and the company register.
- Lowercase it and strip
http://,https://,www., paths, query strings and fragments. - Keep the registrable domain, which for UK businesses is often two labels plus
.co.uk:shop.brightsmile.co.ukbecomesbrightsmile.co.uk. - If the "website" is a Facebook page, a booking platform or a directory profile, move it to a social or booking column and leave the website blank. It is not the business's own domain and will create false duplicates (every business on the same booking platform would share a domain).
- Check the domain resolves and serves the business's own site. Parked pages, expired domains and redirects to another business are common; see parked domains and dead sites in lead lists.
Phone numbers
Store every phone number in E.164 form: a plus sign, the country code and the national number without its leading zero. The ITU's E.164 recommendation is the international numbering plan, and E.164 numbers are at most 15 digits. For the UK that means +44 followed by the number without its first 0.
| As found | Cleaned | Type (Ofcom ranges) |
|---|---|---|
0113 496 0000 | +441134960000 | 01 and 02: geographic |
020 7946 0000 | +442079460000 | Geographic (London) |
07700 900000 | +447700900000 | 07: mobile |
0808 157 0000 | +448081570000 | 080: free to call |
+44 (0)113 496 0000 | +441134960000 | Remove the (0) |
1134960000 (zero lost in Excel) | +441134960000 | Repair: add +44 |
Ofcom's guide to phone numbers and call costs says geographic numbers start with 01 or 02, mobiles with 07, 080 numbers are free to call, and 084, 087 and 09 numbers may be charged extra. A type column lets you route mobiles (often a sole trader's own phone) differently from switchboards.
Excel will strip the leading zero from 01134960000 the moment it treats the column as a number. The import fix and the repair formula are in Excel removes the leading zero from phone numbers. E.164 storage removes the leading zero problem, because +441134960000 has no leading zero to lose, but Excel can still read it as a number and drop the plus sign, so import phone columns as Text either way. To convert a whole column to E.164 at once, paste it into the free phone number formatter.
Address and postcode
- Uppercase UK postcodes and put a single space before the last three characters (the inward code):
LS6 1AA,SW1A 1AA,B1 1AA. - Keep street, town and postcode in separate columns as well as the full raw address.
- Check the postcode is in your target area. A plumber in a Leeds list with a Manchester postcode is either a service-area business or a wrong-area result.
- Remember that service-area businesses may hide their address on Google. A blank address is not a reason to drop them.
- Validate the format before you trust it; a postcode that matches the pattern can still be terminated or mistyped. The free UK postcode lookup checks a pasted list against the postcode register.
- Registered offices are not trading addresses. An accountant's address on Companies House is often the company's registered office, not where it works.
Email addresses
Cleaning and verifying an email are separate jobs.
Clean:
- Trim, lowercase, strip
mailto:,?subject=and trailing full stops or commas left by the page text. - Fix common obfuscations only when unambiguous (
info [at] brightsmile [dot] example). - Drop image file names that look like emails (
logo@2x.png) and placeholder addresses (you@example.com).
Label by purpose:
- Contactable: general enquiries, sales, bookings, named staff at the business.
- Not for outreach: privacy@, dpo@, abuse@, unsubscribe@, no-reply, postmaster@.
Verify: every address needs a result before sending. A good verifier separates deliverable, invalid, catch-all (the server accepts any address, so the mailbox cannot be confirmed) and unknown (the check was inconclusive). Treat catch-all and unknown as their own categories, not as "valid". The meaning of each result is in email verification results explained, and the free email list cleaner handles the email-only clean-up (duplicates, malformed and role addresses) in your browser.
Category
Source categories are chosen by business owners and are inconsistent. Keep the original, then map it to your own short list of segments. "Cosmetic dentist", "Dental clinic" and "Orthodontist" might all map to "Dental practice" for your campaign, while "Dental laboratory" maps to "Exclude".
Status and closures
Check status on two levels:
- Trading status. Map platforms flag permanently and temporarily closed listings. A dead website, a disconnected phone and reviews that stop two years ago are softer signals.
- Registered status. For UK companies, check Companies House. A company that is dissolved or in liquidation should not be contacted as a going concern, and a missing company number is not proof of closure.
Mark closed records rather than deleting them, so the next run does not re-add them as new.
Legal form: company or sole trader
UK email rules depend on it. The ICO's business-to-business marketing guidance says the PECR electronic mail rule does not apply to corporate subscribers, but sole traders and some partnerships are treated the same as individuals. UK GDPR still applies whenever an address identifies a person, such as sarah@brightsmile.example. Record legal form where you can find evidence: a registered company number in the website footer, "Ltd" or "LLP" in the legal name, or a Companies House match. Leave it as unknown otherwise, and treat unknown with the stricter rules.
Dedupe keys, in order of trust
Merge duplicates after normalising, never before, or BrightSmile.example/ and brightsmile.example will look like two businesses.
- Platform ID (Google place ID, Bing or Apple listing ID). Google notes that the same place can have more than one place ID and that IDs can change, so this key catches most but not all repeats.
- Registrable domain. Reliable for independent businesses; branches of a chain share it.
- E.164 phone. Good, but call centres and franchise switchboards are shared.
- Normalised name plus postcode. Last resort, needs fuzzy matching.
Branches, chains and fuzzy name rules are covered in how to deduplicate business leads. When you merge, keep the most recent value per field and keep every source URL.
Merging lists from several sources
Most lead lists are assembled from more than one place: a Google Maps run, a Bing Maps run, a website list from a search engine and an old spreadsheet from a previous campaign. Normalise each source into the same columns first, tag every row with its source, then merge. When two sources disagree, prefer the value with the more direct evidence (the business's own website over a directory) and the more recent date, and keep both values until a person has looked at the conflict. To see which rows two lists share before you merge, use the free merge and compare lists tool.
CSV and Excel hazards
Most damage to lead lists happens when a file is opened, not when it is collected.
- Quoting. RFC 4180 says fields containing commas, line breaks or double quotes should be enclosed in double quotes, and a double quote inside a field is escaped by doubling it. A business description with a comma in it will shift every column to its right if the exporter forgets this.
- Encoding. RFC 4180 does not fix a character set. If accented names turn into
éor the pound sign becomes£, the file is UTF-8 being read as something else. See CSV shows é or £ in Excel. - Type guessing. Microsoft's guidance on leading zeros notes that Excel keeps only 15 significant digits and removes leading zeros from numbers. Import ID, phone and postcode columns as Text.
- Format choice. CSV is the most portable but carries no types; XLSX keeps text columns as text; JSONL keeps nested data such as several emails per business. Encoding pitfalls are in CSV shows é or £ in Excel.
A worked example (fictional)
Five rows from a fictional dental list, before cleaning:
| Name | Website | Phone | Postcode | Status | |
|---|---|---|---|---|---|
| Bright Smile Dental - Best Dentist Leeds | https://www.brightsmile.example/ | 0113 496 0000 | ls61aa | Info@BrightSmile.example | Open |
| Bright Smile Dental | brightsmile.example | 1134960000 | LS6 1AA | info@brightsmile.example | Open |
| Kirkgate Dental Care | facebook.com/kirkgatedental | 07700 900123 | LS1 6AA | Open | |
| Moor Lane Dentistry | moorlane-dental.example | 0113 496 0555 | LS17 5AA | privacy@moorlane-dental.example | Permanently closed |
| Aire Valley Orthodontics | airevalley-ortho.example | 01632 960000 | BD18 3AA | hello@airevalley-ortho.example | Open |
After cleaning:
- Rows 1 and 2 merge on domain and phone into one Bright Smile record with
+441134960000. - Kirkgate's Facebook link moves to the social column; it has a mobile number and no email, so it goes to a phone or form list. As a possible sole trader, it is flagged for the stricter email rules.
- Moor Lane is permanently closed and its only email is a privacy inbox: excluded and kept as a suppressed record.
- Aire Valley stays, with
hello@labelled as general enquiries and sent for verification.
Five rows became three usable records, and only two are candidates for email.
The ready-to-send gate
A row passes only if every box is ticked:
- Status is open, and the company is not dissolved or in liquidation where a company number is known.
- The location is inside the campaign area.
- The category is in the campaign's segment list.
- The domain loads the business's own website, or the record is on a non-email list.
- The email is verified as deliverable, or as catch-all where your campaign policy explicitly allows it.
- The inbox purpose is suitable for outreach.
- Legal form is known, or the stricter consent rules are applied.
- The record is not on your suppression list (previous opt-outs and complaints, matched by email and by domain).
- Provenance is recorded: source, URL and date.
Everything that fails stays in the file, marked "held for review" with the reason, so it can be fixed rather than lost.
How Map Extract helps
Map Extract does much of the cleaning as it collects, and keeps the evidence for the rest.
- The website data extractor canonicalises each domain, checks DNS and reachability, reads robots.txt, and records parked, dead, blocked and CAPTCHA outcomes instead of treating them as data. It extracts up to ten emails and ten phone numbers per business with the page they came from, labels each email by inbox purpose, and the email verifier gives it a verification result (deliverable, invalid, catch-all, unknown and others).
- Map listings carry the platform ID, and Google Maps phone numbers are saved in E.164 form as well as as shown. Closed and sponsored listings are flagged.
- Records from different sources are consolidated into master records, and quality rules decide which ones are ready to send and which are held for review with their reasons.
- CSV export can use Excel-compatible UTF-8 with a byte order mark, and you can export only ready records or only held ones, as CSV, XLSX or JSONL.
- The workflow builder saves the sources and their order as a repeatable recipe, so the next run is collected and cleaned the same way.
What it does not do: it cannot tell you a business's legal form when nothing public states it, it does not decide your lawful basis for you, and an email result describes the checks performed rather than guaranteeing delivery.
Public business and social results the app collects are uploaded automatically to Map Extract's servers while it is licensed and running; the privacy notice explains this.
Key takeaways
- Clean a copy, add provenance, and decide what one row means before you start.
- Domain, E.164 phone and formatted postcode are the three normalisations that make everything else work.
- Clean emails, label their purpose, then verify; catch-all and unknown are not "valid".
- Dedupe after normalising, on place ID, then domain, then phone, then name plus postcode.
- Gate every row on status, area, inbox purpose, legal form and suppression before sending.
Frequently asked questions
What does cleaning a lead list involve?
Cleaning a lead list means making every field consistent and checkable, then removing records you should not contact. In practice: trim and standardise names, reduce websites to a bare domain, store phones in +44 international form, format postcodes, verify emails, flag closed or dissolved businesses, merge duplicates on reliable keys, and record where each record came from. The last step is a gate that decides which rows are ready to send.
Which field is the best key for removing duplicate businesses?
Use the most specific identifier you have. A map platform's place ID is best for one listing. Next is the registrable website domain, then a normalised phone number, then a normalised business name combined with the postcode. Domains and phones can be shared by branches of a chain, so decide whether you want one row per business or one per location before merging.
How do I stop Excel damaging my lead list?
Do not double-click a CSV to open it. Use Data, From Text/CSV, set the file origin to UTF-8, and set phone, postcode and ID columns to Text before loading. Store phones as +44 numbers so they stay unambiguous wherever they end up. When saving, choose CSV UTF-8 so accented business names and the pound sign survive the round trip.
How often should a B2B lead list be cleaned?
Clean every list before each campaign, not once a year. Businesses close, change websites and swap staff continuously, and email addresses that were valid last quarter may now bounce. A practical routine is to re-verify emails and re-check business status within a few weeks of sending, and to re-run the original searches on a schedule so new and closed businesses are both picked up.
Should I remove role addresses like info@ from a B2B list?
Not automatically. For small local businesses, info@ or hello@ is often the only inbox and the one the owner reads. Remove addresses whose purpose is not sales contact, such as privacy@, abuse@, unsubscribe@ and no-reply addresses, and label the rest by purpose so you can choose per campaign. Role addresses still need verifying like any other address.
Sources
- RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files
- ITU-T Recommendation E.164: The international public telecommunication numbering plan
- Keeping leading zeros and large numbers, Microsoft Support
- Phone numbers and call costs, Ofcom
- Place IDs, Google Maps Platform documentation
- Business-to-business marketing, Information Commissioner's Office
Written by the Map Extract editorial team. Found an error? Email support@mapextract.uk and we will check and correct it. How we research guides: editorial policy.