Проверка и чистка клиентской базы для рекламных рассылок с дедупликацией и валидацией контактов (CRM Bitrix24 + PostgreSQL)

Исходная ситуация
Есть клиентская база для рекламных коммуникаций (email + SMS + WhatsApp) из 3 источников: Bitrix24 (лиды и контакты), выгрузки из Call-центра (CSV) и формы сайта (Webhook). За последний год заметно выросли отказы по доставке, жалобы на спам и доля дублей, из-за чего рекламные кампании дорожают, а репутация домена проседает. Сейчас база около 420 000 записей в сумме, реальных уникальных контактов меньше, но точной цифры нет.

Техническая среда и данные
- Bitrix24 (облачный), доступ по REST API
- PostgreSQL 14 (на нашем сервере) - туда можно выгружать промежуточные таблицы
- Исходные выгрузки CSV/XLSX, кодировки и форматы разные
- Поля: ФИО, телефон(а), email(ы), город, дата рождения (часто пусто), источники/теги, даты последней активности, согласие на коммуникации (частично)

Что нужно сделать
1) Провести аудит качества данных и составить краткий отчёт: доли дублей, пустых/битых полей, конфликтов по согласию, подозрительных записей (служебные/тестовые), типовые причины проблем.
2) Построить воспроизводимый пайплайн чистки в PostgreSQL (SQL-скрипты + инструкции), который:
- нормализует телефоны к E.164 (приоритет РФ, но встречаются KZ/BY)
- валидирует email (синтаксис, запрещённые домены/тестовые, одноразовые домены по списку), помечает рискованные адреса
- находит и склеивает дубли по нескольким стратегиям: точное совпадение телефона/email, похожие ФИО + телефон, похожие email (учёт точек/плюс-алиасов для популярных доменов), совпадение нескольких атрибутов
- разрешает конфликты атрибутов при склейке по правилам (например, приоритет более свежего источника и записей с последней активностью)
- формирует «золотую» запись клиента и карту соответствий source_id -> master_id
3) Подготовить итоговый чистый сегмент для рекламных рассылок: список уникальных контактов с флагами пригодности по каналам (email_ok, sms_ok, wa_ok), причиной исключения и отметкой согласия.
4) Подготовить файл/выгрузку для обратной загрузки в Bitrix24: какие контакты объединять/помечать, какие поля обновить, какие записи в архив/удаление (без фактического удаления - только список действий).

Ограничения
- Нельзя передавать исходные персональные данные третьим сторонам и загружать в внешние SaaS для проверки. Разрешены локальные инструменты и офлайн-списки.
- Нельзя выполнять массовую отправку писем/сообщений. Проверка доменов допускается только в виде пассивной проверки MX/NS/DNS по ограниченному числу запросов или на выборке.
- Требуется воспроизводимость: все шаги чистки должны повторяться на новых выгрузках без ручного «подглядывания» и правок в Excel.

Конкретный результат
- SQL-скрипты (или dbt-проект) и README с запуском пайплайна на PostgreSQL
- Отчёт по качеству данных до/после (таблицы с метриками)
- Итоговая таблица master_contacts и таблица mapping для связи с исходными записями
- Финальная выгрузка CSV: готовые к рассылке контакты + причины исключений
- Выгрузка/список действий для Bitrix24 по объединению/обновлению

Измеримые критерии приёмки
- Доля дублей по ключам телефон/email после обработки: не более 0.5% (по внутренней метрике, согласуем формулу в начале)
- Не менее 95% телефонов приведены к E.164 или помечены с конкретной причиной (короткий номер, недостаточно цифр, служебный, не РФ/KZ/BY и т.п.)
- Не менее 98% email либо проходят синтаксическую валидацию, либо имеют причину отклонения (invalid_format, disposable_domain, test_domain, empty)
- Для каждой исходной записи есть либо master_id, либо причина исключения (например, no_contacts)
- Пайплайн запускается на свежей выгрузке и даёт результат без ручных правок

Дополнительно
- Предпочтительно использовать Python только если он нужен для fuzzy-matching/phonetic, но основной каркас должен быть в SQL (в идеале с расширениями pg_trgm/unaccent).