How to clean a list of businesses: duplicates, closures, categories and missing positions

Cleaning a list of businesses comes down to four fixes, done in this order: give every row a position, merge the duplicates, check each place is still open, and put every place into one category from a fixed list. Do them in that order, because finding duplicates and checking status both work far better once each row has a position, and counting by category only makes sense once the duplicates are gone.

Most lists that land on a sales or operations desk are a merge of several older lists: an export from the CRM, a spreadsheet from a regional manager, a trade-show scan, a purchased file. This guide walks through what goes wrong in such a list, how to spot each problem, and how to let an AI employee keep the list clean after the first pass.

The four problems, and how to spot them

A first diagnosis takes minutes in a spreadsheet; the fixes are below.

Step 1: give every row a position

Turning an address into latitude and longitude is called geocoding. Without it you cannot draw the list on a map, measure distances, split it into territories or match it reliably against other place data.

• Keep separate columns for street, city and country if you can. A full address in one cell works, but a city column on its own gives only the city centre.
• Be wary of rows that all land on the same point. That usually means the geocoder only understood the city, and those rows need a better address.
• Mark how precise each position is. A shop placed at its own door and a company placed at its city's centre should not be treated alike.

In xMap Flows, a CSV or Google Sheets list brought into My data is placed from its address and city columns when it has no latitude and longitude of its own.

Step 2: merge the duplicates

Two rows are the same business when they share something stable. In order of reliability:

1. A place's own ID from a place database, when both rows have one.
2. The phone number, compared by its digits only, since +44 20 7946 0000 and 020 7946 0000 are the same line.
3. The website, compared by its domain, ignoring http, www and the page.
4. The name and the position together: the same name, spelled loosely, within about 60 to 100 metres.

Name alone is the weakest test. Two branches of one chain share a name but are different places; one independent may appear as "Rosa's" and "Rosa's Kitchen & Bar". Decide which copy to keep before you merge: the most recent, the best rated, or the one with the most fields filled. Better still, merge the fields so nothing is lost.

Step 3: drop or flag places that closed

A list that is a year old will contain businesses that no longer trade, and some that moved. Look each row up against current place data and read its status: Open, Closed for now, or Closed. Do not delete on a single "Closed for now"; flag it and check again in a few weeks. A changed phone or website at a new address usually means a move, not a closure. Spotting closed businesses early covers the signals in more depth.

Step 4: one category per place

Category values from different sources never agree. A clean list maps every row into one value from a short, fixed list that matches how you work: your sales channels, your store formats, or a standard set such as Restaurant, Cafe, Bakery, Grocery, Pharmacy, Gym. Keep the original value in its own column, so the mapping can be checked and redone.

A worked example, made up

Suppose a beverage wholesaler merges three regional lists into 1,500 rows. Placing them shows 140 rows sitting on city centres, which are sent back to the regions for full addresses. Matching by phone and website finds about 200 repeats. Looking the rest up finds 60 closed and 25 moved. The category column had 48 distinct values; mapped to the company's six channels, the list can finally be totalled by channel and by rep. The numbers are invented for the example, but the order of work is the one that holds up.

Doing the clean-up with blocks

A one-off clean and a list that stays clean use the same blocks. The second simply runs them on a schedule.

1. My data: the list, placed from its addresses.
2. Map business scan set to look up each place itself: current name, address, phone, website, rating and whether it is open.
3. Remove duplicates: by the same place, phone, website or name, keeping the first, the best rated or the highest score.
4. Place classifier: one clean category per place from the xMap standard list or your own list of up to 60, and a second pass at merging places within 60 metres.
5. Filter or Branch to separate open places from closed ones.
6. Save to my database, Google Sheets or your CRM, so the cleaned rows go back where people use them.

Run weekly with What changed after the lookup, and the email holds only the rows that need attention: closed, moved, renamed. The templates Enrich a lead sheet and push it to HubSpot, Monthly outlet census of my city and Follow up a trade show list start from messy lists of this kind.

Mistakes that make lists worse

• Deleting instead of flagging. Keep removed rows in a separate tab with the reason, so a wrong merge can be undone.
• Merging on name alone, which collapses every branch of a chain into one row.
• Cleaning once and stopping. A list starts ageing the day it is cleaned; a scheduled check is the only cure.