So how do you determine the address quality within the data quality management activities? We have developed a simple method for this. What are the most important measures? Please carry out the following simple checks:
Step one – Visual inspection
You transfer all existing addresses (customers, prospective customers, competitions, customer service inquiries, etc.) from a contiguous zip code area (ideally one in which you are personally familiar) into an Excel file. A number of approx. 5,000 addresses, for example, is sufficient. Before the check begins, add one or more columns in which comments can be entered for each address.
Then sort the addresses according to the various criteria and take a closer look at the first 1,000 and the fourth 1,000 addresses, for example, on a random basis.
Each of these 1,000 address packages is now examined as follows:
First, sort the addresses by surname and first name regardless of zip code. Take a look at the spelling of the surnames and first names and you will quickly notice the different ways in which unique surnames and first names have been entered: incorrect capitalization. The first name is in the surname field or vice versa. The company name is in the name field. The company form is missing.
Then check whether the salutation matches the first name. The title is also regularly entered incorrectly in address fields. One time it is next to the first name, the other time it is in its own field, then “Dr.” is written next to “Doktor” and “Prof.” next to “Professor” and so on. Now sort the addresses by zip code, street, surname and first name.
You can quickly see whether there are duplicates of people in the file or whether several family members are registered at the same address. Is it grandma, mother, daughter? Or is this a coincidence? In the last step, check whether all zip codes have five digits. Is the leading “zero” missing from the East German addresses (which unfortunately very often creeps into an Excel export)? Have any foreign addresses crept in? Are these marked accordingly? Now count the number of addresses with errors within the packages. If the error rate is higher than two to three percent, you should take the following steps immediately.
A brief digression into B2B on the subject of data quality management:
Very often, the address models of ERP systems, e-commerce or other systems have two or three fields intended for the company name. This usually leads to a huge problem: the first part of a company is entered in the first field, the addition to the company name in the second field, the rest and the legal form can then be found in the third field.
Once such a company name has been created, the following could happen: When creating a new company, an employee checks whether this company already exists. However, he/she enters the company name in a different way and it is not found by the duplicate check program. The supposedly new company is created a second time.
In connection with e-commerce, this company would also be created a second time because the duplicate check program usually does not recognize the duplicate here either. If an employee creates this name, it is often argued afterwards that “the customer wanted it this way”, so I created it in exactly the same way.
Interim conclusion on B2B data quality management:
In B2B in particular, there is often a lot of crap in the databases at precisely this simple point. In our projects, we have often found between 6 and 10 different spellings as duplicates. And this can be avoided through training and rules.
Step two – address audit
Many address service providers offer an inexpensive address audit. Your addresses are compared with different reference data. As a result, you receive an assessment of how good the overall data is. After this check, you are in a position to manage the necessary qualification measures in a more targeted manner. This means you can avoid the far too expensive “one size fits all” approach.
Practical tip: Do not send a file with all addresses for the check. Divide your data into meaningful groups and have them checked separately for quality. For a duplicate check, all of them should of course be checked at once.
Step three – data audit
Here, the contents of the variables are analyzed using univariate or simple statistical methods and “anomalies”, incorrect entries or unnecessary values are identified. More on this – below in the section Basic know-how on data quality.
Step four – Summary of the audits and visual inspection
The three audit steps result in a summary for the management. The detailed analysis includes the identification of weaknesses and good performance. For the weak points, there are recommendations a) for one-off rectification and b) for ongoing optimization and monitoring.
Ideas for key performance indicators (KPIs), special management tasks, process optimizations or IT support round off the picture.
This is the basis for further action and control.
Step five – “Do it yourself” or “Let it be done” decision
Before the whole clean-up procedure can be started, the question arises: do it yourself or have it done by a service provider?
The rule clearly speaks for “do it yourself”: “Addresses belong in the core competence of every company that does CRM and dialog marketing”. Only with smaller address lists or in the initial phase can it be quicker and easier to use a service provider.
In the medium term, you should always process addresses in-house. Addresses are the capital of every company. A service provider (unless he is a proven specialist in this sector) cannot reflect the individuality of a company. This also goes hand in hand with employee training. Rules are drawn up on how addresses are to be entered in future and how data qualification is to be carried out.
► Practical tip: International companies should also have the topic of address quality dealt with in the respective country. The head office often has too little knowledge of the regional particularities and framework conditions.
Step six – one-off or initial cleansing
Normalization or standardization: You prepare the address data so that all information that can be processed is written to the corresponding fields. You then check and correct the salutation using a prefix table and the correct salutation. These tables are available from various providers, including for many Western and Eastern European countries.
Postal cleansing: Use the tables from Swiss Post to standardize the spelling of the street, the city name and possibly the zip code. For addresses that have not been validated for some time (six to twelve months), a relocation check is recommended. You can use this to switch to the new address accordingly. By comparing the data of deceased or insolvent persons and companies, you can clean up your addresses in a further step.
Completion: With the correct address, it is now possible to complete or correct company names.
Duplicate correction: Once you have made all necessary or possible corrections and enrichments, the duplicate matching is useful. You must check for personal and family duplicates (business-to-consumer) as well as company and contact person (business-to-business).
Manual correction: The last step is to make manual corrections. This is certainly time-consuming, but must be carried out depending on the customer value. Unfortunately, the software does not recognize all errors and therefore cannot correct or clear them automatically. These “unsafe duplicates or spellings” are now checked record by record by your address quality experts, possibly followed by a search on Google, the imprint or at the residents’ registration office and then either confirmed as “correct” or corrected accordingly.
External enrichment: Only now can you enrich your addresses with telephone numbers, industry or micro-geographical or lifestyle data.
Create links: Furthermore, you should create links between several people from a family or company. In addition, practice recommends creating group links for company addresses or linking parent companies and subsidiaries.
Here is an overview of the complete process:
Fig. 7.24 Example of address and data quality cycle (source: bdl, 2014)
► Practical tip: Data records that have been checked against each other should be marked so that the same data records are not processed again the next time. Then, only the new uncertain problem cases should be checked again.
Now comes the endurance run: ongoing cleansing or sustainable data quality management
All of the above-mentioned test steps for initial or one-off cleansing must of course be carried out repeatedly and regularly as part of ongoing processes. In companies where a large number of people touch and possibly correct the addresses, ongoing quality management is necessary. The same applies if there are web stores or other Internet data entry sources (newsletters etc.) in which customers enter themselves.
In addition, customers should be asked about possible changes at regular intervals, but at least once a year, or they should receive a letter or e-mail with a personalized landing page and a request to correct the incomplete address. A response incentive for more attention or response is recommended.
In principle, it is about avoiding a) typical errors, b) insufficient data quality, c) unnecessary costs and thus achieving high quality.
Concluding remarks on the subject of operational data quality management (DQM) and better address quality:
Depending on the address quantity and quality/condition of the necessary addresses, an initial cleansing can take between three and nine months. The costs naturally vary greatly. This depends, for example, on the software used and how much manual post-processing is required and how often the addresses have to be checked during ongoing business. Companies that send a mailing to the majority of their customers every month have different processes than companies that only send four mailings a year to a selected target group. A sufficiently large budget must be made available for initial external support, software, validation and manual maintenance.
Don’t be surprised if your bank checks this aspect “How good are your addresses?” the next time you apply for a loan.
Perfect address management is the necessary basis for your future success and therefore one of the most important tasks in any company – regardless of whether you are dealing with just 500 or five million addresses. These targeted cleansing and quality measures usually pay off after six – at the latest after twelve months.
