Master Data Management / Data Matching / CRM
Why a Single Customer Might Exist Multiple Times in Corporate Systems
For enterprises using CRM, ERP, electronic document management systems, and corporate analytical platforms, the problem of duplicate customer records is one of the most common causes of information inconsistency. The same counterparty can be entered into CRM by a sales manager, registered by accounting in ERP, and added to the document management system during contract preparation.
At the same time, the organization’s name, address, contact persons, and other details may differ. For example, a company might appear in information systems as ‘LLC Alpha Trade’, ‘Alpha-Trade’, ‘ALPHA TRADE’, or under a shortened trade name.
If a common corporate identifier is not established for these records, information systems may perceive them as different organizations. As a result, errors occur in management reporting, difficulties arise in consolidating debt, communications are duplicated, and the overall sales volume to a client is incorrectly determined.
The problem becomes particularly complex after the merger of several companies, the implementation of a new CRM, or data migration from legacy information systems.
To solve such tasks, deduplication is used — the process of identifying records that may describe the same object and organizing their further use.
Deduplication is an important component of Data Quality and Master Data Management, but it cannot be reduced to simply removing identical rows from a database.
Why Duplicates Occur
The causes of duplication can be both technical and organizational.
Independent Maintenance of Directories
In large organizations, different departments often use their own information systems. The sales department registers clients in CRM, accounting creates counterparties in ERP, and the legal department works with contract party details.
If there is no coordinated identification mechanism between systems, one organization may end up with multiple independent records.
Manual Entry Errors
Employees may record names differently, omit characters, use abbreviations, or make typographical errors.
For example:
- “Technology Plus”;
- “Technology+”;
- “Technologiya Plus”;
- “LLC Technology Plus”.
To a human, such names might seem similar, but for a database, these are different text values.
Information Import
When importing contacts from Excel files, external directories, or other CRMs, records can be created repeatedly. This is especially common if the import process only checks the internal identifier of the source system.
At the same time, the same client may already exist in the target database under a different code.
Changing Details
Organizations change their names, addresses, and contact information. If the information system does not support change history, new details may be perceived as indicators of a different counterparty.
However, the reverse situation is also possible: two independent organizations may have similar names, a common address, or the same contact persons.
This means that deduplication must consider not only textual similarity but also the legal and business characteristics of entities.
Stage One: Defining Identification Rules
Before applying duplicate search algorithms, it is necessary to define what exactly the company considers a single customer. This question seems obvious, but in practice, it has several possible answers.
For accounting purposes, a customer may be considered a specific legal entity. For the sales department, it is a group of related companies with which joint commercial work is conducted.
For marketing, it is an individual contact person or consumer.
Therefore, two records that should be merged for one analytical task do not necessarily have to be merged in the operational database.
Legal and Commercial Identity
For example, a company might have a head office and several branches. From the perspective of consolidated sales, all divisions may belong to the same customer group.
However, contracts, invoices, and financial transactions may be associated with different legal entities.
Automatic merging of such records can lead to accounting errors.
Therefore, the enterprise needs to separately define the rules for establishing legal identity, belonging to a corporate group, and matching contact persons.
These rules must be approved by the responsible business units.
Exact Matching
The simplest method for finding duplicates is to compare values exactly.
If two records have the same unique identifier, this can be a strong basis for establishing their correspondence.
For Ukrainian legal entities, one of the key attributes is the EDRPOU code.
In the relevant directory, you can search for repetitions of this value.
Example SQL query:
SELECT
edrpou_code,
COUNT(*) AS record_count
FROM customers
WHERE edrpou_code IS NOT NULL
GROUP BY edrpou_code
HAVING COUNT(*) > 1;
The result will be a list of identification codes that appear in the table more than once. However, even such a result does not mean that all records should be immediately deleted or merged.
For example, one legal entity may have several contractual relationships, different delivery addresses, or separate accounts in the information system. Therefore, it is necessary to distinguish between duplication of the main customer card and the existence of several related business objects.
Exact matching works best when the enterprise has reliable and stable unique identifiers.
Normalization Before Comparison
Duplicate searching by names requires preliminary standardization. Two names may differ only in character case, spaces, or punctuation.
For example:
LLC "Alpha-Trade"
LLC ALPHA TRADE
Alpha Trade
For comparison, normalized representations can be created that apply uniform rules for processing case, spaces, and punctuation. At the same time, the deletion of the organizational and legal form should be done carefully.
Companies with the same commercial name but different legal forms or registration codes may be independent entities.
Therefore, a normalized name is preferably used as an auxiliary feature, not as the sole criterion for automatic merging.
Linguistic Peculiarities of Ukrainian Data
For Ukrainian enterprises, an additional complexity is the use of Ukrainian and Russian languages, as well as Latin transliteration. One organization may appear in different sources under names that sound similar but are spelled differently.
Simple character comparison may not detect such a match. Therefore, normalization rules must consider linguistic peculiarities, common abbreviations, and possible transliteration variants.
At the same time, automatic name conversion should not alter official details in legally significant documents.
Fuzzy Matching
When records differ due to typos, abbreviations, or partially filled attributes, exact matching is insufficient. In such cases, fuzzy matching algorithms are used.
Their task is to evaluate the degree of similarity between records.
Levenshtein Distance
One of the well-known algorithms is the Levenshtein distance. It determines the minimum number of single-character insertions, deletions, or substitutions required to change one word into the other.
For example, the names “Technology” and “Technolgy” differ by a missing character. The algorithm can determine that the respective strings have a high similarity.
However, Levenshtein distance does not consider business context. The names of two different companies may be similar, while legally the same organization might use a completely different trade name.
Therefore, this algorithm is just one of the possible components of a matching system.
Jaro and Jaro-Winkler Algorithms
Jaro and Jaro-Winkler can be used for comparing short text values. They consider the similarity of characters and their order in strings.
Such algorithms can be useful for analyzing names, surnames, and short organization names.
However, their effectiveness needs to be verified on real corporate data, as the nature of errors depends on the language and method of information input.
Phonetic Algorithms
For certain categories of records, phonetic methods, such as Soundex and Metaphone, can be used. They attempt to establish a correspondence between words that have similar sounds.
However, traditional implementations of such algorithms are primarily focused on English names. Specialized rules or adapted algorithms may be required for Ukrainian and Russian names.
Without such adaptation, phonetic matching can produce a significant number of incorrect results.
Blocking Records - Blocking
In large corporate databases, a full pairwise comparison of all records can require excessive computational resources. If a directory contains a million clients, the number of possible pairs is almost 500 billion.
For practical deduplication, it is necessary to reduce the number of comparisons. One of the approaches is Blocking – preliminary grouping of records by defined features.
For example, one can primarily compare records that have the same first characters of the normalized name, belong to the same region, or have common identifier fragments. This significantly reduces the number of operations.
However, overly strict blocking rules can lead to missing true duplicates. For example, if a client changes their address or a name contains a typo at the beginning of the word, records may end up in different groups.
Therefore, it is advisable to use several methods for generating matching candidates.
Weighted Rules and Probabilistic Matching
More complex Data Matching systems can use multiple attributes simultaneously.
For example, the algorithm analyzes the organization’s name, registration code, phone number, address, and contact person. A weight of correspondence is set for each attribute.
High similarity in names can increase the overall score, but a contradiction in reliable legal identifiers should be a basis for additional verification.
One of the classic approaches is probabilistic record linkage, based on the Fellegi-Sunter model. It allows evaluating how the obtained set of matches and mismatches supports the hypothesis that two records describe the same entity.
In practice, three categories of results can be used:
- High probability of match — candidates for automatic merging according to approved rules;
- Intermediate probability — records requiring manual verification;
- Low probability — records considered different.
It is important that the threshold values are determined based on testing with real data.
In some systems, erroneously merging two different clients can be significantly more dangerous than keeping duplicate records.
Therefore, the level of automation must be set taking into account business risk.
Golden Record: forming a consistent client record
After identifying duplicates, the next task arises: to determine which information should remain in the corporate directory.
In Master Data Management, the concept of a Golden Record is used for this — a consistent record formed based on defined sources and rules.
For example, one record may contain the correct legal code, another — the current address, and a third — the verified phone number. Simply deleting two out of three records will lead to the loss of useful information.
Instead, a unified customer representation needs to be formed.
Attribute Selection Rules
For each attribute, it is advisable to define an authoritative source.
Legal details can come from the ERP or an approved corporate registry.
Contact information comes from the CRM.
Contract details come from the electronic document management system.
The history of financial transactions must remain linked to the corresponding primary documents.
It is also necessary to consider the date of value updates, the level of trust in the source, and the procedure for confirming changes.
History Preservation
When merging records, it is important not to lose connections with historical operations. If two client records have their own orders, invoices, and contracts, the system must retain the ability to trace the origin of each transaction.
For this purpose, mapping tables of old and new identifiers can be used.
Physical deletion of records is far from always the right solution.
In many cases, it is more advisable to create a single logical representation of the client, preserving historical references.
Practical Scenario: Merging Client Databases After CRM Implementation
Consider a hypothetical company that has used a local client accounting system for several years and is now transitioning to a centralized CRM.
The old system contains 120 thousand records.
The new CRM already has 40 thousand client cards created by the sales department.
During the transfer, it becomes necessary to determine which records match.
Stage 1. Creating an Intermediate Environment
Information from both sources is copied to a separate Staging database.
For each record, the source system, internal identifier, and export date are stored.
This allows for cleansing without directly modifying production systems.
Stage 2. Normalization
Organization names are converted to a consistent format for comparison. Phone numbers are cleaned of auxiliary characters, and addresses are split into structured components as much as the source data allows.
Legal codes are checked for compliance with allowed formats.
Stage 3. Exact Match Finding
First, records with identical reliable registration identifiers are identified. Such records may receive a high priority during further analysis.
However, before merging, it is necessary to check if individual records are not used for different business roles of the same counterparty.
Stage 4. Fuzzy Matching
For records without reliable identifiers, comparisons of names, addresses, and contact details are used. Candidates are distributed by confidence level.
Unambiguous cases are processed according to approved rules, and ambiguous ones are passed to the Data Steward.
Stage 5. Formation of Consolidated Records
For confirmed duplicates, master attribute values are determined. A mapping table of source system identifiers to corporate identifiers is maintained.
Special attention is paid to preserving links with orders, contracts, and financial transactions.
Stage 6. Results Control
After deduplication, checks are performed for completeness, number of client records, and link preservation. It is also necessary to compare aggregated financial indicators before and after migration.
A decrease in the number of clients in the directory should not lead to the disappearance of operations or changes in total sales amounts.
Deduplication Tools in 2015
For large corporate environments, specialized Data Matching and MDM tools exist. IBM InfoSphere QualityStage supports standardization, record matching, and data preparation procedures.
Informatica Data Quality offers tools for profiling, cleansing, and establishing correspondence between records. Microsoft SQL Server Data Quality Services allows the creation of matching rules and their use for finding potential duplicates.
In the SQL Server 2014 environment, DQS capabilities can be used in conjunction with Master Data Services and Integration Services. Talend Open Studio for Data Quality can be used for profiling and matching information, particularly in open technology projects.
The choice of software depends on the data volume, number of sources, complexity of matching rules, and integration requirements. For a small directory, SQL procedures and controlled manual verification are sufficient.
For an enterprise with millions of customer records and dozens of information systems, industrial MDM solutions may be appropriate.
How to Prevent Duplicates from Reoccurring
One-time deduplication will not provide a lasting result if the enterprise does not change the rules for creating customer records.
The main task is to shift control to the moment of data entry.
Verification Before Customer Creation
During the registration of a new counterparty, the CRM can search by EDRPOU code, normalized name, phone number, or other attributes. If a potential match is found, the user is prompted to verify the existing record.
At the same time, the system should not unconditionally prohibit the creation of a new client solely due to name similarity.
Centralized Master Data
For large enterprises, it is advisable to define a single authoritative source for core customer data. Other information systems can retrieve relevant records through integration interfaces.
This allows for a reduction in the number of independent counterparty creation procedures.
Regular Monitoring
Even with checks in place, new duplicates can arise due to imports, synchronization errors, and changes in business processes. Therefore, it is necessary to periodically run matching procedures and generate reports for responsible employees.
Special attention should be paid to sources where duplication occurs most frequently.
Organizational Risks of Automatic Merging
The most dangerous deduplication error is not a missed duplicate, but the incorrect merging of different clients. If the system mistakenly identifies two legal entities as one, this can lead to incorrect debt display, disclosure of confidential information, or violation of contractual accounting.
Therefore, it is necessary to establish different levels of automation. For records with reliable unique identifiers, automatic processing can be applied after appropriate checks.
Ambiguous cases require approval from the responsible employee.
Critical changes must be logged.
A mechanism for undoing incorrect merges should also be provided.
For this, it is not enough to simply store a backup of the entire database: information is needed about which records and relationships were changed by a specific operation.
Deduplication and Corporate Analytics
Duplicate detection is important not only for operational systems. In a corporate DWH, duplicated customer records can lead to incorrect calculation of the number of customers, average revenue per customer, and other analytical indicators.
For example, if one counterparty is represented by three records, their sales may be distributed among different customer cards. As a result, the analytical system may incorrectly identify the largest customers or assess customer activity.
To solve this problem in DWH, identifier mapping tables and consolidated customer dimensions can be used. At the same time, it is necessary to preserve the historicity of information.
If two customer records were merged today, it does not mean that all previous management reports should automatically change without a documented recalculation procedure.
The enterprise must determine whether the reporting uses the current customer structure or reflects the state of the directories on a specific historical date.
Role of Intecracy Group Competencies
Deduplication of client records is an example of a task where data management technologies are closely related to corporate business processes. For the Data Management IG, relevant areas include Data Quality, MDM, ETL, and corporate information integration. They cover customer database profiling, matching rule construction, and the formation of consistent records.
IQusion IT’s competencies in system integration, BPM, and electronic document management are important for organizing the processes of creation, verification, and approval of customer data. In large corporate environments, mass record matching may require significant computational resources. Server, network infrastructure, and information storage systems fall under Softline IT’s specialization.
If corporate directories are used simultaneously by local and cloud applications, tasks arise for integrating distributed environments, which fall under SL Global Service’s competencies in cloud and hybrid infrastructure.
However, defining the legal and business compliance rules for customers remains the responsibility of the enterprise itself.
Practical Implementation Guidelines
Deduplication of customer data is an important tool for improving the quality of corporate information. Its effectiveness depends on combining several approaches: value normalization, exact and fuzzy matching, the use of reliable identifiers, control of ambiguous cases, and the formation of consistent master records.
Automated algorithms can significantly reduce manual work, but should not replace business rules for determining customer identity. It is especially important to maintain the history of operations, the relationships between information systems, and the ability to verify the result of the merge.
For long-term quality maintenance, it is necessary not only to eliminate existing duplicates but also to implement mechanisms for their prevention.
Properly organized deduplication allows the enterprise to see the client as a single business object, while maintaining the accuracy of legal, financial, and historical data.
Author