通过重复数据删除和联系人验证检查和清理广告邮件的客户端数据库 (CRM Bitrix24 + PostgreSQL)

初始情况
广告通信（电子邮件 + SMS + WhatsApp）的客户群来自 3 个来源：Bitrix24（潜在客户和联系人）、从呼叫中心下载 (CSV) 和网站表格 (Webhook)。在过去的一年里，递送失败、垃圾邮件投诉和重复邮件的比例显着增加，这就是广告活动变得更加昂贵且域名声誉下降的原因。现在数据库总共约有42万条记录，真正的唯一联系人较少，但没有确切的数字。

技术环境及数据
- Bitrix24（云），通过 REST API 访问
- PostgreSQL 14（在我们的服务器上）- 您可以在那里上传中间表
- 初始 CSV/XLSX 上传，不同的编码和格式
- 字段：全名、电话、电子邮件、城市、出生日期（通常为空）、来源/标签、上次活动日期、同意通信（部分）

该怎么办
1) 进行数据质量审核并起草一份简短报告：重复的比例、空/损坏的字段、同意冲突、可疑记录（服务/测试）、问题的典型原因。
2）在PostgreSQL中构建可重现的清理管道（SQL脚本+指令），其中：
- 将电话标准化为 E.164（RF 优先，但可以找到 KZ/BY）
- 验证电子邮件（语法、禁止/测试域、根据列表的一次性域），标记有风险的地址
- 使用多种策略查找并合并重复项：精确的电话/电子邮件匹配、相似的全名+电话、相似的电子邮件（考虑到流行域的点/加别名）、匹配多个属性
- 解决根据规则合并时的属性冲突（例如，较新的源的优先级和具有最新活动的记录）
- 生成“黄金”客户记录和对应映射 source_id -> master_id
3) 准备用于广告邮件的最终干净部分：按渠道（email_ok、sms_ok、wa_ok）、排除原因和同意标记列出具有适合性标记的唯一联系人列表。
4) 准备文件/上传以上传回 Bitrix24：要合并/标记哪些联系人、要更新哪些字段、要存档/删除哪些记录（没有实际删除 - 只有操作列表）。

限制
- 原始个人数据无法转移给第三方并上传到外部SaaS进行验证。允许使用本地工具和离线列表。
- 您无法发送批量电子邮件/消息。仅允许以针对有限数量的请求或基于样本的被动 MX/NS/DNS 检查的形式进行域检查。
- 需要可重复性：所有清理步骤都必须在新上传时重复，而无需在 Excel 中手动“窥视”和编辑。

具体结果
- 在 PostgreSQL 上启动管道的 SQL 脚本（或 dbt 项目）和自述文件
- 之前/之后的数据质量报告（带有指标的表格）
- 最终的master_contacts表和用于与源记录通信的映射表
- 最终 CSV 上传：准备分发的联系人 + 例外原因
- 上传/Bitrix24 合并/更新操作列表

可衡量的验收标准
- 处理后电话/电子邮件密钥重复的比例：不超过0.5%（根据内部指标，我们会在开始时就公式达成一致）
- 至少 95% 的电话转换为 E.164 或标有特定原因（号码短、数字不足、业务、不是 RF/KZ/BY 等）
- 至少 98% 的电子邮件要么通过语法验证，要么有拒绝原因（invalid_format、disposable_domain、test_domain、empty）
- 对于每个源记录，都有一个 master_id 或一个异常原因（例如，no_contacts）
- 管道从新上传开始，无需手动编辑即可给出结果

此外
- 最好仅在需要模糊匹配/语音时才使用 Python，但主要框架应采用 SQL（最好带有 pg_trgm/unaccent 扩展）。