crm_postgres

Normalize 19 city variants → 6 canonical Colombian cities

19 unique strings cluster into 6 canonical cities. rapidfuzz centroid similarity threshold 0.91. BOG / bogota / Bogt´a / bogot / Bogotá D.C. all map to Bogotá. Affects downstream city-level analytics and Habeas Data subject-location reporting.

UPDATE customers SET city = CASE
  WHEN lower(city) IN ('bog','bogota','bogt´a','bogot','bogotá d.c.','bogotá dc','santafe de bogota') THEN 'Bogotá'
  WHEN lower(city) IN ('mde','medellin','medellín') THEN 'Medellín'
  WHEN lower(city) IN ('cli','cali') THEN 'Cali'
  WHEN lower(city) IN ('baq','barranquilla','barranqilla') THEN 'Barranquilla'
  WHEN lower(city) IN ('ctg','cartagena','cartagena de indias') THEN 'Cartagena'
  WHEN lower(city) IN ('bga','bucaramanga') THEN 'Bucaramanga'
  ELSE city
END
WHERE city NOT IN ('Bogotá','Medellín','Cali','Barranquilla','Cartagena','Bucaramanga');

12,200 rows affected

Correct 8 typo-domain email addresses

Reference dictionary match against known typo-domain list. 3 rows have gmial.com, 2 hotmial.com, 2 yahooo.com, 1 gamil.com. These emails will hard-bounce in any campaign — and since they are PII, incorrect values create GDPR subject-access-request risk.

UPDATE customers SET email = REPLACE(email, '@gmial.com', '@gmail.com') WHERE email LIKE '%@gmial.com';
UPDATE customers SET email = REPLACE(email, '@hotmial.com', '@hotmail.com') WHERE email LIKE '%@hotmial.com';
UPDATE customers SET email = REPLACE(email, '@yahooo.com', '@yahoo.com') WHERE email LIKE '%@yahooo.com';
UPDATE customers SET email = REPLACE(email, '@gamil.com', '@gmail.com') WHERE email LIKE '%@gamil.com';

8 rows affected

Investigate 6 duplicate cédula document_ids in customers

6 customers share a document_id with another customer. In Colombia the cédula de ciudadanía is nationally unique. Likely causes: (1) manual data entry error, (2) shared family accounts, (3) ETL key collision. Must be resolved before any Habeas Data subject request response.

SELECT document_id, COUNT(*) cnt, array_agg(customer_id) ids
FROM customers
GROUP BY document_id
HAVING COUNT(*) > 1
ORDER BY cnt DESC;
-- Escalate each group to ops team for identity verification.

12 rows affected

Remove or correct 1 age=143 outlier in customers

One customer row has a date_of_birth yielding age=143 — clearly a data entry error (likely 1979 entered as 1879). Low urgency but the outlier inflates max-age statistics and may trigger false positives in age-gating logic.

SELECT customer_id, full_name, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) as age
FROM customers WHERE TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) > 120;
-- After manual verification, correct date_of_birth or NULL it.

1 rows affected