How to organise customer data without losing relationships or history
Organising customer data means finding the right person, understanding their relationship with the company and consulting associated requests. A clean name list is only part of it. This guide helps define structure, sources and maintenance rules before importing data into a CRM or business management system.
By the end, you can:
- Define entities and identifiers
- Resolve duplicates without deleting context
- Create a quality routine
Separate things with independent lifecycles
A person, company and request change independently. Someone may change role; a company may have several establishments; the same contact may make several requests. Putting everything in one row causes repetition and makes it difficult to update a detail without changing the context of old requests.
In a fictional example, Ana Exemplo works at Oficina Horizonte and follows two requests, P-101 and P-118. Rui Exemplo works at the same company but follows only P-118. The model should represent these relationships without duplicating the company or merging requests. If Ana leaves, history still identifies who participated in previous requests.
- Company E-01
Oficina Horizonte, with its own identity.
- Contacts C-01 and C-02
Ana and Rui, associated with the company.
- Requests P-101 and P-118
Separate journeys with their own participants.
The relationship may need a role, validity dates and a primary contact.
Define each field’s meaning and source
Create a dictionary with name, meaning, format, required status and owner. Decide when the field becomes necessary. Asking for irrelevant information at first contact increases effort and may produce invented answers merely to pass validation.
Microsoft’s data-management checklist recommends defining the model and maintaining quality rules. The practical application is simple: two people should interpret a field the same way. “Status” must state whether it describes the contact, request or commercial relationship. “Date” must identify which event happened that day.
| Field | Example definition | Rule |
|---|---|---|
| contacto_id | Stable person identifier | Independent of name or email |
| empresa_id | Link to the organisation | May be awaiting confirmation |
| email_contacto | Address supplied for contact | Validate format and source |
| funcao | Person’s role in the relationship | Value reviewed when it changes |
| fonte_atualizacao | Source of the change | Distinguish form, team and import |
Find duplicate candidates before merging them
The same name does not prove someone is the same person. A shared email may also represent a team. Use stable identifiers and treat matches as review candidates when evidence is insufficient. Normalise spacing and formats without destroying the original value that may be needed to check its source.
PostgreSQL documentation distinguishes primary keys, uniqueness and foreign-key relationships. These mechanisms help apply rules already decided, but do not choose the correct identity themselves. An overly aggressive rule may join two people; a permissive one may create several records for the same person.
Before merging, show conflicting values, affected relationships and the record that will remain primary. Preserve decision history and a correction method. Test linked requests, activities and attachments in particular: resolving a duplicate contact must not delete a legitimate request.
Choose the authoritative source for each information type
If the website, CRM and invoicing system keep copies of the same data, define who may change it and which change takes precedence. The rule may vary by field. A customer-confirmed email and a name used on an issued document have different purposes; neither needs to be replaced by the last imported file.
Record the source and update time where this helps resolve conflicts. An empty imported field may mean “no information”, rather than “delete”. Define the difference explicitly. When someone changes data during synchronisation, the application needs to detect or resolve the conflict according to the agreed rule.
Keep data with a purpose and restrict access
Link each field to a task. Free-text notes tend to accumulate information nobody knows how to classify or maintain; prefer structured fields where there is a clear rule. Do not use commercial history to store passwords, credentials or personal details unnecessary for the process.
Define who can view, change and export each dataset. Permission to reply to a request must not be confused with a marketing preference. Record preferences and their source separately where the process uses them. Retention periods and applicable requirements must be defined for the business context rather than choosing one duration for everything.
Create checks showing the problem and its owner
Start with problems blocking work: requests without contacts, nonexistent relationships, likely duplicates or missing required fields. Count and show affected records rather than only a global quality percentage. Each group needs an owner and a resolution rule.
In a fictional sample of 50 contacts, 6 have no email. That does not automatically mean six errors: some may use only a phone and meet the process rule. Conversely, a single request linked to the wrong person may be a priority. Assess meaning and impact rather than frequency alone.
| Test | Expected result |
|---|---|
| Two people with the same name | Remain separate without evidence of a common identity. |
| One contact with two requests | Both requests remain independent. |
| Import with an empty field | Follows the rule for preserving or clearing the value. |
| Deactivated company | History retains relationships and an understandable status. |
| Export by a restricted role | Excludes data outside authorised access. |
Document rules before the next import
Choose a sample, apply the rules and ask the team to find some cases. If they find the person, understand the relationship and reach the correct request, the structure is starting to fulfil its purpose. Name who approves dictionary changes so new fields do not multiply without a purpose.
Worksheet for your project
Customer data organisation worksheet Necessary entities and relationships: Stable identifier for each entity: Fields, meaning, format and point when required: Authoritative source and update rule for each field: Handling empty and unknown values: Criteria for suggesting and confirming duplicates: Relationships and history to preserve in a merge: Roles authorised to view, change and export: Purpose, retention and owner of each dataset: Quality checks and affected records: Sample tested and unresolved problems: Dictionary owner and review date:
Sources and criteria
Content prepared with AI assistance and research using technical sources, with checks of the relevant examples and calculations. The references support the guide’s decisions; examples identified as fictitious let you try out the method.
