Finding & Eliminating Duplicated Customers
Customer confusion often refers to a state of mind that leads to consumers making imperfect purchasing decisions or lacking confidence in the correctness of their purchasing decisions. It’s sometimes a deliberate and ethically questionable (at best) marketing strategy.
Another form of customer confusion is inadvertently introduced within a company when employees enter the same customer into their databases multiple times using slightly different variations of the customer’s name. For example, the same customer entered four ways:
- ABC Company
- ABC Co.
- ABC, Inc.
- The ABC Co.
will each be treated as a separate entity by a computer system. This in turn causes confusion when tracking customer relations information, processing purchase orders, and issuing invoices to that customer (just to give a few examples).
This problem is exacerbated when companies use multiple systems that incorporate the same type of information, but those systems are not integrated. To continue the example above, what issues are induced when:
- ABC Co. is used in the CRM program by the Sales team,
- ABC, Inc. is used in a job tracking program by shop management,
- and The ABC Co. is used in the Accounting program by office personnel?
In our experience, these conflicts lead to a variety of problems from unnecessary delays and wasted time to unsent invoices on the other end of the spectrum. As one shop owner recently put it during an initial consult, “I know we’re doing work were not billing for…”
Dedicated effort focused on cleaning the customer database can eliminate these problems. Before jumping into that though, it’ll be wise to put a few measures in place to prevent recurrence of this common occurrence.
Establish Customer Naming Controls
The first step in eliminating this costly conundrum is to establish a solid internal policy for the customer naming convention in order to prevent any new occurrences of the problem. Each company should have a written policy specifying these items at a minimum:
- Who (which person, people, or office) will enter new customer information (and is authorized to make changes in the future)?
- What determines the name you enter into your system to designate the new customer? After years of being less formal (and thereby learning some of these lessons the hard way), we now use the full legal name for each customer, and we collect this information during the [written] onboarding process.
- Address how you will handle DBAs? If a legal company name is entered in one system and the DBA name is also entered into the same or another system, this will create additional confusion since the two names are often nothing alike, making this situation even trickier to catch. More on that below.
- How you address multi-location companies may depend largely on the corporate structure of those companies and the nature of your business dealings with them. For example, deliveries may be made to various ‘ship to’ addresses while all invoices go to a corporate headquarters. So, consideration should be given to each of these situations on a case-by-case basis and the logic for the naming decisions should be documented for future reference (e.g., when adding a new location – you don’t want to inadvertently change the naming convention).
- While having customers with similar names happens regularly, true duplicates are rare, but they do happen. Plan for handling this with consistency in your organization. We have a few customers with duplicated names. Addition of a state abbreviation after the name works for us to differentiate the customers, but you would need a different discriminator (like city) if your duplicates were in the same state. [ABC Co. (TX), ABC Co. (OH) – for example]
- Where do these names need to be entered? Document each place the name is to be entered. Ensure if they need to be entered more than once that they are entered the exact same way in each system, even if only on a spreadsheet.
- While we’re at it, the policy should address all other customer information that should be collected, entered into your system(s), and the formats for those items. Consistency is key!
- Finally, if sales reps are responsible for gaining new customers, they are each often dealing with many leads and prospects and generally use less formal approaches. Once the sale is made; however, and the prospect becomes an active customer, there needs to be a process for adding that new customer into the general system(s), including updating the name in the sales rep’s CRM to keep everyone on the same page and avoid confusion down the road.
Identify Duplicated Customer Names
Next, identify any existing customer duplications. This can be time consuming if you have a lot of customer names residing in your database. Dropping all customers into a spreadsheet makes the process easier. Bring in more than just the customer name to help resolve conflicts and to identify and correct any information gaps. Essential column headings for the spreadsheet include: Customer Name, ID/Number, address information (physical, billing, ship-to), phone number, website, and primary points of contact.
Once the spreadsheet is populated begin by sorting on the customer name. Identify duplicates by highlighting the entire rows of all duplicated companies. If there are two companies that appear to be duplicates, highlight both, not just one. This is to easily spot duplicates that have already been identified when you change sort orders in the next steps.
That was the easy part. This will help you identify situations like this:
- ABC Co.
- ABC, Inc.
But it won’t help you catch situations like this:
- ABC Co.
- ABC, Inc.
- The ABC Co.
because the “The” in the name will separate the third variation from the first two in the alphabetical sort order. Next arrange the data in a variety of other orders by sorting on different column headers such as address, contact person, phone number, website, etc. Each time you do this look down the list under the sorted column as well as the company name to identify the trickier duplicates such as ‘The ABC Co.’ example above and the DBA situations discussed earlier. Again, highlight the entire row for each duplicated company. This time you may want to take a few notes to more easily go back and find these trickier duplicates.
Eliminate Extraneous Records
Finally: database clean-up. This is now the perfect time to consolidate customer records under the correct name, moving all pertinent information into the correct record, and expunging anything obsolete or redundant. Use this opportunity to supply any missing information as those gaps will be obvious by the empty fields in the spreadsheet. It’s also the time to investigate and resolve any conflicts (and you can expect to find them!).
But before you can remove the customers with duplicate or similar (but erroneous) names from your database, there are a few things to consider:
- Ensure you keep the correct customer name. Don’t inadvertently delete the primary record.
- When looking at presumed duplicates, determine they are, in fact, duplicates and not actually different companies with similar or even the same names.
- The clean-up process may be a team effort involving people from your Sales, Operations, and Accounting teams. It’s generally best for these people to review the identified duplicates together, in the same room or via online collaboration whenever possible.
- Better systems often will not allow you to delete customers that have any associated transactions, and you may be forced into a work-around. In some cases your software provider may be able to provide assistance.
- With that in mind, be careful not to delete attachments or other information inherent in the extraneous records. A customer record may have quote, job, or invoice histories, payment information, contact information, special requirements, etc. Deleting a record without first checking for this information can cause significant problems. Files and information associated to an extraneous customer record need to be moved to the primary record being careful not to duplicate information already associated to the primary record (a big job, indeed!). Again, depending on your system, you may need tech support assistance from your software provider to help with this step.
Make no mistake, this clean-up effort will take time – lots of it. But the efficiency gained on the other side will be well worth it.
Next steps
Now that you’ve established a policy for controlling customer names and purged your database of duplicated information, what can be done to make life easier and more efficient in this area?
Are customer records only included in a comprehensive system that integrates accounting, quoting, job scheduling and tracking, as well as CRM functions? This is the ideal situation because information is visible and consistent across all functions and is updated in real time. If your systems are not integrated and your accounting, job tracking, and CRM programs (for example) function independently, you run the risk and likely scenario of repeating the errors that originally created the problems.
Most modern programs have an API that allows for integration with other platforms. There are pros and cons to connecting your unrelated systems and the process normally requires assistance from IT professionals. If the systems you currently use are not capable of integration or the process proves too expensive or ineffective, maybe it’s time for a new system…
