Перевірка та чищення клієнтської бази для рекламних розсилок з дедуплікацією та валідацією контактів (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, disponble_domain, test_domain, empty)
- Для кожного вихідного запису є або master_id, або причина виключення (наприклад, no_contacts)
- Пайплайн запускається на свіжому вивантаженні та дає результат без ручних правок

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