Überprüfung und Bereinigung der Kundendatenbank für Werbemailings mit Deduplizierung und Kontaktvalidierung (CRM Bitrix24 + PostgreSQL)

Ausgangssituation
Es gibt einen Kundenstamm für Werbekommunikation (E-Mail + SMS + WhatsApp) aus 3 Quellen: Bitrix24 (Leads und Kontakte), Downloads aus dem Call Center (CSV) und Website-Formulare (Webhook). Im vergangenen Jahr haben Lieferausfälle, Spam-Beschwerden und der Anteil an Duplikaten deutlich zugenommen, weshalb Werbekampagnen teurer werden und die Reputation der Domain sinkt. Mittlerweile umfasst die Datenbank insgesamt etwa 420.000 Datensätze, es gibt weniger wirklich eindeutige Kontakte, aber eine genaue Zahl gibt es nicht.

Technische Umgebung und Daten
- Bitrix24 (Cloud), Zugriff über REST API
- PostgreSQL 14 (auf unserem Server) - Sie können dort Zwischentabellen hochladen
- Erste CSV/XLSX-Uploads, verschiedene Kodierungen und Formate
- Felder: vollständiger Name, Telefonnummer(n), E-Mail(s), Stadt, Geburtsdatum (oft leer), Quellen/Tags, Daten der letzten Aktivität, Einwilligung zur Kommunikation (teilweise)

Was zu tun
1) Führen Sie ein Datenqualitätsaudit durch und erstellen Sie einen kurzen Bericht: Anteil an Duplikaten, leeren/fehlerhaften Feldern, Einwilligungskonflikten, verdächtigen Datensätzen (Dienst/Test), typische Problemursachen.
2) Erstellen Sie eine reproduzierbare Reinigungspipeline in PostgreSQL (SQL-Skripte + Anweisungen), die:
- normalisiert Telefone auf E.164 (RF-Priorität, aber KZ/BY ist zu finden)
- validiert E-Mails (Syntax, verbotene/Test-Domains, verfügbare Domains gemäß der Liste), markiert riskante Adressen
- Findet und führt Duplikate mithilfe mehrerer Strategien zusammen: genaue Übereinstimmung von Telefon/E-Mail, ähnlicher vollständiger Name + Telefonnummer, ähnliche E-Mail (unter Berücksichtigung von Punkten/Plus-Aliasnamen für beliebte Domänen), Übereinstimmung mit mehreren Attributen
- löst Attributkonflikte beim Zusammenführen nach Regeln (z. B. Priorität einer neueren Quelle und Datensätze mit der neuesten Aktivität)
- generiert einen „goldenen“ Kundendatensatz und eine Quell-ID der Korrespondenzzuordnung -> Master-ID
3) Bereiten Sie das endgültige, saubere Segment für Werbemailings vor: eine Liste einzigartiger Kontakte mit Eignungskennzeichen nach Kanal (email_ok, sms_ok, wa_ok), Ausschlussgrund und Zustimmungszeichen.
4) Bereiten Sie eine Datei/einen Upload zum Zurückladen auf Bitrix24 vor: welche Kontakte zusammengeführt/markiert werden sollen, welche Felder aktualisiert werden sollen, welche Datensätze archiviert/gelöscht werden sollen (ohne tatsächliche Löschung – nur eine Liste von Aktionen).

Einschränkungen
- Ursprüngliche personenbezogene Daten können nicht an Dritte weitergegeben und zur Überprüfung auf externe SaaS hochgeladen werden. Lokale Tools und Offline-Listen sind erlaubt.
- Sie können keine Massen-E-Mails/Nachrichten versenden. Die Domänenprüfung ist nur in Form einer passiven MX/NS/DNS-Prüfung für eine begrenzte Anzahl von Anfragen oder auf Stichprobenbasis zulässig.
- Reproduzierbarkeit ist erforderlich: Alle Reinigungsschritte müssen bei neuen Uploads ohne manuelles „Blicken“ und Bearbeiten in Excel wiederholt werden.

Spezifisches Ergebnis
- SQL-Skripte (oder DBT-Projekt) und README zum Starten der Pipeline auf PostgreSQL
- Vorher/Nachher-Datenqualitätsbericht (Tabellen mit Metriken)
- Endgültige Tabelle „master_contacts“ und Zuordnungstabelle für die Kommunikation mit Quelldatensätzen
- Endgültiger CSV-Upload: Kontakte zur Verteilung bereit + Gründe für Ausnahmen
- Upload/Aktionsliste für Bitrix24 zum Zusammenführen/Aktualisieren

Messbare Akzeptanzkriterien
- Der Anteil der Duplikate für Telefon-/E-Mail-Schlüssel nach der Verarbeitung: nicht mehr als 0,5 % (nach internen Kennzahlen werden wir uns zu Beginn auf die Formel einigen)
- Mindestens 95 % der Telefone werden auf E.164 umgestellt oder mit einem bestimmten Grund gekennzeichnet (kurze Nummer, nicht genügend Ziffern, geschäftlich, nicht RF/KZ/BY usw.)
- Mindestens 98 % der E-Mails bestehen entweder die syntaktische Validierung oder haben einen Grund für die Ablehnung (ungültiges_Format, Einwegdomäne, Testdomäne, leer).
- Für jeden Quelldatensatz gibt es entweder eine Master_ID oder einen Ausnahmegrund (z. B. no_contacts).
– Die Pipeline startet mit einem neuen Upload und liefert Ergebnisse ohne manuelle Änderungen

Zusätzlich
- Es ist vorzuziehen, Python nur zu verwenden, wenn es für Fuzzy-Matching/Phonetik benötigt wird, aber das Hauptgerüst sollte in SQL sein (idealerweise mit pg_trgm/unaccent-Erweiterungen).