Vérification et nettoyage de la base de données clients pour les mailings publicitaires avec déduplication et validation des contacts (CRM Bitrix24 + PostgreSQL)

Situation initiale
Il existe une base de clients pour les communications publicitaires (email + SMS + WhatsApp) provenant de 3 sources : Bitrix24 (leads et contacts), téléchargements depuis le Call Center (CSV) et formulaires du site web (Webhook). Au cours de l'année écoulée, les échecs de livraison, les plaintes pour spam et la part des doublons ont considérablement augmenté, ce qui explique pourquoi les campagnes publicitaires deviennent plus coûteuses et la réputation du domaine s'affaisse. Maintenant, la base de données compte environ 420 000 enregistrements au total, il y a moins de véritables contacts uniques, mais il n'y a pas de chiffre exact.

Environnement technique et données
- Bitrix24 (cloud), accès via API REST
- PostgreSQL 14 (sur notre serveur) - vous pouvez y télécharger des tables intermédiaires
- Téléchargements initiaux CSV/XLSX, différents encodages et formats
- Champs : nom complet, téléphone(s), email(s), ville, date de naissance (souvent vide), sources/tags, dates de dernière activité, consentement aux communications (partiel)

Ce qu'il faut faire
1) Réaliser un audit de la qualité des données et rédiger un bref rapport : proportion de doublons, champs vides/cassés, conflits de consentement, enregistrements suspects (service/test), causes typiques de problèmes.
2) Construire un pipeline de nettoyage reproductible dans PostgreSQL (scripts SQL + instructions), qui :
- normalise les téléphones à E.164 (priorité RF, mais KZ/BY peut être trouvé)
- valide l'email (syntaxe, domaines interdits/test, domaines jetables selon la liste), marque les adresses à risque
- trouve et fusionne les doublons en utilisant plusieurs stratégies : correspondance exacte téléphone/e-mail, nom complet + téléphone similaires, e-mail similaire (en tenant compte des points/plus alias pour les domaines populaires), correspondance avec plusieurs attributs
- résout les conflits d'attributs lors de la fusion selon des règles (par exemple, priorité d'une source plus récente et des enregistrements avec la dernière activité)
- génère une fiche client « dorée » et une carte de correspondance source_id -> master_id
3) Préparez le segment final propre pour les envois publicitaires : une liste de contacts uniques avec des indicateurs d'adéquation par canal (email_ok, sms_ok, wa_ok), motif d'exclusion et marque de consentement.
4) Préparez un fichier/téléchargement à télécharger sur Bitrix24 : quels contacts fusionner/étiqueter, quels champs mettre à jour, quels enregistrements archiver/supprimer (sans suppression réelle - seulement une liste d'actions).

Restrictions
- Les données personnelles originales ne peuvent pas être transférées à des tiers et téléchargées sur un SaaS externe pour vérification. Les outils locaux et les listes hors ligne sont autorisés.
- Vous ne pouvez pas envoyer d'e-mails/messages en masse. La vérification de domaine n'est autorisée que sous la forme d'une vérification passive MX/NS/DNS pour un nombre limité de requêtes ou sur la base d'un échantillon.
- La reproductibilité est requise : toutes les étapes de nettoyage doivent être répétées sur les nouveaux téléchargements sans « regard » manuel ni édition dans Excel.

Résultat spécifique
- Scripts SQL (ou projet dbt) et README avec lancement du pipeline sur PostgreSQL
- Rapport sur la qualité des données avant/après (tableaux avec métriques)
- Table finale master_contacts et table de mappage pour la communication avec les enregistrements source
- Téléchargement CSV final : contacts prêts à être distribués + raisons des exceptions
- Téléchargement/liste d'actions pour Bitrix24 pour la fusion/mise à jour

Critères d'acceptation mesurables
- La part des doublons pour les clés téléphone/email après traitement : pas plus de 0,5% (selon les métriques internes, nous nous mettrons d'accord sur la formule au début)
- Au moins 95 % des téléphones sont convertis en E.164 ou marqués d'un motif spécifique (numéro court, pas assez de chiffres, professionnel, pas RF/KZ/BY, etc.)
- Au moins 98 % des e-mails réussissent la validation syntaxique ou ont un motif de rejet (invalid_format, jetable_domain, test_domain, vide)
- Pour chaque enregistrement source, il existe soit un master_id, soit un motif d'exception (par exemple, no_contacts)
- Le pipeline démarre lors d'un nouveau téléchargement et donne des résultats sans modifications manuelles

En plus
- Il est préférable d'utiliser Python uniquement si cela est nécessaire pour la correspondance floue/phonétique, mais le framework principal doit être en SQL (idéalement avec les extensions pg_trgm/unaccent).