Verificarea și curățarea bazei de date de clienți pentru mailing-uri publicitare cu deduplicare și validare de contact (CRM Bitrix24 + PostgreSQL)

Situația inițială
Există o bază de clienți pentru comunicări publicitare (e-mail + SMS + WhatsApp) din 3 surse: Bitrix24 (leads și contacte), descărcări din Call Center (CSV) și formulare de site (Webhook). În ultimul an, eșecurile de livrare, reclamațiile de spam și ponderea duplicatelor au crescut semnificativ, motiv pentru care campaniile de publicitate devin mai scumpe și reputația domeniului scade. Acum baza de date este de aproximativ 420.000 de înregistrări în total, există mai puține contacte unice reale, dar nu există o cifră exactă.

Mediu tehnic și date
- Bitrix24 (cloud), acces prin API-ul REST
- PostgreSQL 14 (pe serverul nostru) - puteți încărca acolo tabele intermediare
- Încărcări inițiale CSV/XLSX, diferite codificări și formate
- Câmpuri: nume complet, telefon(e), e-mail(e), oraș, data nașterii (deseori goale), surse/etichete, datele ultimei activități, consimțământ pentru comunicări (parțial)

Ce să fac
1) Efectuați un audit de calitate a datelor și întocmiți un scurt raport: proporția de duplicate, câmpuri goale/întrerupte, conflicte de consimțământ, înregistrări suspecte (serviciu/test), cauze tipice ale problemelor.
2) Construiți o conductă de curățare reproductibilă în PostgreSQL (scripturi SQL + instrucțiuni), care:
- normalizează telefoanele la E.164 (prioritate RF, dar KZ/BY poate fi găsit)
- valideaza email (sintaxa, domenii interzise/test, domenii de unica folosinta conform listei), marcheaza adresele riscante
- găsește și îmbină duplicate folosind mai multe strategii: potrivire exactă telefon/e-mail, nume complet + telefon similar, e-mail similar (ținând cont de puncte/plus aliasuri pentru domeniile populare), potrivirea mai multor atribute
- rezolvă conflictele de atribute la fuzionarea conform regulilor (de exemplu, prioritatea unei surse mai recente și înregistrările cu cea mai recentă activitate)
- generează o înregistrare client „de aur” și o hartă de corespondență source_id -> master_id
3) Pregătiți segmentul final curat pentru e-mailurile publicitare: o listă de contacte unice cu semnalizări de adecvare pe canal (email_ok, sms_ok, wa_ok), motivul excluderii și marcarea consimțământului.
4) Pregătiți un fișier/încărcare pentru încărcare înapoi în Bitrix24: ce contacte să îmbinați/etichetați, ce câmpuri să actualizați, ce înregistrări să arhivați/ștergeți (fără ștergere reală - doar o listă de acțiuni).

Restricții
- Datele personale originale nu pot fi transferate către terți și încărcate în SaaS extern pentru verificare. Instrumentele locale și listele offline sunt permise.
- Nu puteți trimite e-mailuri/mesaje în vrac. Verificarea domeniului este permisă numai sub forma unei verificări pasive MX/NS/DNS pentru un număr limitat de solicitări sau pe bază de eșantion.
- Este necesară reproductibilitatea: toți pașii de curățare trebuie repetați la încărcările noi, fără „peeping” manual și editare în Excel.

Rezultat specific
- Scripturi SQL (sau proiect dbt) și README cu lansarea pipeline-ului pe PostgreSQL
- Raport privind calitatea datelor înainte/după (tabele cu valori)
- Tabelul final master_contacts și tabelul de mapare pentru comunicarea cu înregistrările sursă
- Încărcare CSV finală: contacte gata pentru distribuție + motive pentru excepții
- Încărcare/lista de acțiuni pentru Bitrix24 pentru fuziune/actualizare

Criterii de acceptare măsurabile
- Ponderea duplicatelor pentru cheile de telefon/e-mail după procesare: nu mai mult de 0,5% (conform metrici interne, vom conveni asupra formulei la început)
- Cel puțin 95% dintre telefoane sunt convertite la E.164 sau marcate cu un motiv specific (număr scurt, cifre insuficiente, afaceri, nu RF/KZ/BY etc.)
- Cel puțin 98% dintre e-mailuri fie trec validarea sintactică, fie au un motiv de respingere (invalid_format, disposable_domain, test_domain, empty)
- Pentru fiecare înregistrare sursă există fie un master_id, fie un motiv de excepție (de exemplu, no_contacts)
- Conducta începe cu o încărcare nouă și oferă rezultate fără modificări manuale

În plus
- Este de preferat să folosiți Python numai dacă este necesar pentru potrivirea neclară/fonetică, dar cadrul principal ar trebui să fie în SQL (ideal cu extensii pg_trgm/unaccent).