数据清洗建议草案

以下清洗步骤面向“维度口径不统一导致指标偏差”的场景，目标是在不改变业务事实的前提下，使维度定义、编码和时间/单位等口径一致化，消除因口径差异造成的统计偏差。

一、目标与问题界定

• 目标：统一维度口径，保证事实数据在相同维度切片下的可比性和可复算性。
• 常见偏差来源：
  • 维度定义差异（如“新客”“活跃用户”“渠道”定义不同）
  • 枚举/编码不一致（如渠道=APP/app/APP官方）
  • 标识符冲突或缺失（多源用户ID、订单ID不一致）
  • 时间口径不一致（事件时间 vs 入库时间；时区；周/月定义）
  • 单位口径不一致（币种、重量/体积单位）
  • 层级口径差异（区域/组织层级版本不一致，历史变更未管理）

二、治理前提（必须产出并冻结）

• 统一口径文档：对每个维度及其属性给出业务定义、取值范围、规则与边界条件。
• 统一维表与映射表：
  • 主数据/参考数据表（如渠道、区域、产品、组织）
  • 源值到标准值的映射表（含版本与生效区间）
• SCD策略：为维表确定缓慢变化维策略（Type 1/Type 2），并定义生效时间字段（effective_from/effective_to）。
• 数据契约：事实表必须以标准维的代理键（surrogate key）关联，不再直接使用源系统枚举。

三、数据清洗总体流程

1. 诊断与差异量化

• 以指标在不同维度切片上的差异作为入口，定位导致偏差的维度及来源系统。
• 计算偏差率：bias = |metric_sourceA - metric_sourceB| / metric_reference。
• 输出“维度-来源-偏差”矩阵，按影响度排序。

2. 标准化设计

• 为问题维度制定标准定义、合法取值、层级结构和时间/单位口径。
• 产出映射规则和冲突解决优先级。

3. 构建标准维与映射

• 建立统一维表（含代理键、自然键、SCD字段）。
• 构建源值到标准值的映射表（含来源、原始值、标准值、版本、有效期、置信度）。

4. 源数据清洗与对齐

• 在入仓层进行基础规范化（去空白、大小写、字符集、无效符号）。
• 在集成层进行口径映射、单位转换、时区/日历对齐、层级归并。

5. 事实表重键与回填

• 以标准维代理键替换事实表的源维值。
• 重算聚合层，标注版本号，分批回填历史数据。

6. 验证与回归

• 建立结构化校验（约束、域值、参照完整性、分布/稀疏度、度量平衡）。
• 前后版本A/B对比与抽样复核。

7. 持续监控与预防

• 指标口径漂移监控、映射覆盖率、未知/未映射比率、时区/单位异常报警。
• 映射变更审批与审计。

四、关键清洗步骤与技术要点

1. 标识符与实体对齐

• 用户/商户/商品等多源ID合并：优先使用权威主键；无法对齐时建立交叉映射（crosswalk）。
• 模糊匹配仅作为备选，需设阈值与人工复核通道；保存匹配分数与证据。
• 去重策略：确定性规则优先（同证件/同手机号+同姓名），概率模型作为补充。

2. 枚举与编码标准化

• 规范化操作：trim、lower/upper、全半角统一、同义词/别名归一。
• 非法值处理：映射失败的值标记为“UNKNOWN”并入库，但不得丢弃；输出异常清单推动源端修复。
• 映射表版本管理：新增、停用、合并需要记录生效区间，避免历史重算时口径穿越。

3. 单位与币种口径统一

• 明确标准单位与币种（如重量kg、金额CNY）。
• 转换规则需版本化：汇率取自权威源，标注基准时间与来源；物理单位转换须保留精度和舍入策略。
• 事实表保存原值与标准值，便于追溯。

4. 时间口径对齐

• 统一时间基准：统一到UTC存储，展现层按区域时区转换；或统一使用业务时区。
• 明确统计窗口：自然周/ISO周/账期、自然月/账月；定义T+N延迟规则和截数时间点。
• 明确事件时间字段优先级：event_time > business_time > ingestion_time；迟到数据采用水位线策略并触发补算。

5. 层级与版本管理

• 区域/组织/产品层级使用SCD2管理；事实表按发生时点关联当时有效层级。
• 层级变更影响聚合结果时，采用“历史重算”或“静态回溯口径”二选一，并在指标字典中明示。

6. 参照完整性与覆盖率

• 事实表外键必须在维表中可解（包括UNKNOWN代理键）；记录解码失败率并设阈值报警。
• 对关键维度（如渠道、区域）要求100%映射覆盖率，非关键维可设最大未知占比上限。

五、验证与回归策略

• 约束类：
  • 维表唯一性：unique(channel_code_std)
  • 事实-维参照：FK(fact.channel_sk -> dim_channel.channel_sk)
  • 域值合法性：channel_std in mapping_table
• 分布类：
  • 类别分布漂移：PSI/KL对比前后版本；阈值例如 PSI < 0.2
  • 空值/未知占比阈值：unknown_rate <= 0.5%
• 业务对账：
  • 总量/金额守恒：清洗前后订单数、GMV差异在容许阈值内（如≤0.1%），差异需可解释（重复移除、无效枚举剔除）
  • 核心指标回归：以周/月级为粒度做A/B对比并抽样核验

六、监控与告警

• 口径漂移：同一维度下核心指标的同比/环比异常波动告警（排除季节性）
• 映射完整性：新增源值未被映射的累计数和7日触发告警
• 时间对齐：迟到数据占比、跨时区异常
• 单位对齐：币种/单位字段异常值比例

七、实施顺序建议

1. 冻结口径与维表模型
2. 建立映射表与标准维，完成历史映射覆盖
3. 事实表外键重构并小流量回填验证
4. 全量回填与聚合重建
5. 上线监控与问题回溯机制

八、示例SQL片段

1. 渠道值标准化与映射

• 预清洗
  • select distinct trim(upper(replace(channel_raw,'　',' '))) as channel_norm from stg_orders;
• 源值到标准值
  • select o.order_id, m.channel_sk, coalesce(m.channel_std, 'UNKNOWN') as channel_std from stg_orders o left join ref_channel_mapping m on trim(upper(o.channel_raw)) = m.channel_raw_norm and o.event_time between m.effective_from and m.effective_to;

2. 时区与周口径统一

• with x as ( select order_id, (event_time at time zone 'UTC' at time zone 'Asia/Shanghai') as event_time_cst from stg_orders ) select order_id, date_trunc('week', event_time_cst)::date as iso_week_start from x;

3. SCD2 维度关联（按事实发生时间）

• select f.fact_id, d.channel_sk from fact_orders f join dim_channel d on f.channel_nk = d.channel_nk and f.event_time >= d.effective_from and f.event_time < d.effective_to;

4. 质量校验示例

• 未映射占比
  • select count() filter (where channel_std='UNKNOWN')::decimal / count() as unknown_rate from dwd_orders;
• 对账差异
  • select abs(sum_amt_new - sum_amt_old)/nullif(sum_amt_old,0) as diff_rate from ( select sum(amount) as sum_amt_old from dwd_orders_old ) a cross join ( select sum(amount) as sum_amt_new from dwd_orders ) b;

九、常见陷阱与规避

• 映射表无生效期导致历史穿越：必须SCD化并按事实时间关联。
• 在事实层直接写死口径逻辑：应使用标准维代理键与映射表，避免逻辑分散。
• 丢弃未知/不合法数据：应纳入UNKNOWN并监控占比，推动源端治理。
• 单位与币种未标注来源与时间：导致回溯不可复现，需版本化。

以上步骤可作为统一维度口径的数据清洗与验证蓝本。实际实施时应先在受影响最大的维度上试点，验证后再扩展至全量维度和历史数据。

以下为面向“投放与CRM数据存在重复与缺失，ID映射混乱”的数据清洗与校验步骤。内容以可实施的技术流程为主，覆盖标准化、去重、缺失处理与ID映射重建，并配套验证与监控策略。

一、数据盘点与分层

• 划分数据域与对象：
  • 投放域：平台事件（曝光、点击、转化）、活动/素材维表（campaign_id、ad_id、creative_id）。
  • CRM域：客户/线索/联系人（customer_id/lead_id）、商机、订单。
• 建立暂存层（staging）承接原始数据；处理层（curation）输出清洗结果；成品层（gold）供分析与报表使用。
• 定义关键主键与外键：
  • 事件主键：platform_event_id 或由来源ID+时间+去重哈希构成的唯一键。
  • 实体主键：建议引入全局唯一ID（Global ID，GID），如 global_customer_id、global_campaign_id。
  • 外键约束：事实表中的 customer_id、campaign_id 必须指向维表存在记录。

二、统一ID策略与映射字典构建

• 设立ID映射表（ID Dictionary）：
  • 字段示例：global_customer_id、source_system、source_id、start_ts、end_ts、status（active/merged/retired）、confidence_score、last_verified_ts。
  • 约束：同一（source_system, source_id）唯一映射；支持历史别名与有效期。
• 规范ID格式与校验：
  • 验证ID模式（正则/长度/字符集）；去除前后空格、零前缀、大小写不一致。
  • 对邮箱、手机号进行标准化：邮箱小写化、去除别名“+tag”；手机号统一国家码与格式。
• 明确身份粒度：
  • 区分“个人”“设备”“Cookie”“账号（平台/CRM）”四类标识，避免跨粒度硬映射。
  • 仅在有确定性证据（同邮箱、同手机号、平台统一账号）时将不同标识合并至同一 global_customer_id。

三、字段标准化与基础清洗

• 文本规范：去空格、统一大小写、清除不可打印字符、标准化分隔符与缩写。
• 日期与时区：统一ISO格式与时区；将投放事件与CRM时间线统一到UTC并保留原始时区。
• 枚举值：统一渠道、平台、活动命名（UTM参数、渠道枚举）；建立字典并做值合法性校验。

四、重复数据识别与去重

• 事件级去重（投放日志）：
  • 规则优先级：platform_event_id 完全一致 → 同源ID+时间戳在短窗口内（如5分钟）重复 → 全字段哈希重复。
  • 处理策略：幂等写入（唯一索引）；滑窗去重；保留首条或最新一条并记录去重原因。
• 客户/线索级去重（CRM与投放跨域）：
  • 阻塞（Blocking）：按规范化邮箱、手机号、姓名+邮编、device_id/cookie_id 分组。
  • 匹配规则：
    • 确定性匹配（优先）：同邮箱或同手机号完全一致；同平台账号ID一致。
    • 概率匹配（补充）：姓名相似度（Jaro-Winkler/Levenshtein）、地址标准化相似度、邮箱本地部分相似且域一致；设定阈值如 ≥0.9 自动合并、0.8–0.9人工复核。
  • 生存者（Survivorship）与字段级冲突处理：
    • 优先来源：CRM > 自投放平台 > 第三方聚合；或使用最近更新时间/更高数据质量评分。
    • 字段级合并：在来源优先级下，保留最完整值（非空、格式合法、最新）。
  • 合并写法：软合并（保留原记录、建立别名关系到同一 global_customer_id）；写审计日志与理由。

五、缺失数据处理

• 划分关键与非关键字段：
  • 关键字段：ID、事件时间、平台/活动ID、客户唯一识别信息（邮箱/手机号/账号）。禁止推断或填充；缺失即标记为不可用并进入回填流程。
  • 非关键字段：地域、设备、UTM参数、来源渠道等可按规则补全或推断。
• 回填与补全：
  • 通过跨表关联（如同一平台账号或同邮箱）补齐缺失字段。
  • 标准化来源标签（如渠道/活动名称）从字典回填。
• 缺失原因与质量标签：
  • 对缺失字段记录 null_reason（not_collected、system_error、parse_error、unknown）。
  • 计算字段完整率并设阈值（如邮箱完整率<70%触发告警与流程调整）。

六、ID映射清理与重建

• 冲突检测：
  • 一对多冲突：同（source_system, source_id）映射到多个 global_customer_id → 置为冲突状态，进入人工或规则解歧。
  • 多对一过度合并：短期内大量不同 source_id 映射到同一 global_customer_id → 检查误合并，必要时回滚。
• 有效期与漂移：
  • 记录映射有效期；当ID变更（账号迁移、邮箱更新）时关闭旧映射并建立新映射，防止历史事件断链。
• 置信度治理：
  • 确定性=1.0，概率匹配按分数写入；低于阈值仅保留候选关系，不用于主连接。
• 参照完整性：
  • 每日检查事实表外键是否都能在维表或映射表找到对应条目；孤儿记录入隔离区并驱动回填。

七、跨域对账与一致性校验（投放↔CRM）

• 关键校验：
  • 转化事件数量与CRM新建线索/商机数量在时间窗口内的匹配率（设目标如≥95%）。
  • 投放渠道/活动在CRM中归因一致性（活动ID/名称对齐、UTM映射命中率）。
  • 去重前后指标对比：重复率下降、合并后唯一客户数稳定、无异常断崖。
• 误差排查：
  • 时区差异、窗口期选择、平台重试造成的重复；CRM延迟入库；多设备跨账号导致分裂身份。

八、落库与审计

• 采用不可破坏写入策略：保留原始数据与清洗结果，两者可追溯。
• 审计日志内容：去重/合并规则、匹配分数、来源优先级、操作时间、操作者或作业ID。
• 版本化：维表与映射表支持版本号，便于回滚与重处理。

九、监控指标与阈值

• 完整性：关键字段缺失率、UTM/渠道字段填充率。
• 唯一性：客户/线索重复率；事件重复率；ID冲突数。
• 一致性：事实表外键命中率（孤儿率）；跨域对账匹配率。
• 漂移与稳定性：映射变更率（global_customer_id变更占比）、高频合并回滚率。
• 及时性：数据延迟分布（P95落地时延）。
• 告警规则：超阈值自动告警并输出问题样本用于复核。

十、工具与实现建议（示例）

• 规则引擎与相似度计算：SQL/UDF配合文本相似度库；或使用开源实体解析工具进行阻塞与匹配。
• 幂等与唯一约束：在事件表对 platform_event_id 建唯一索引；在映射表对（source_system, source_id）建唯一约束。
• 数据字典与参考表：渠道、平台、活动命名标准化表；邮箱域名白名单/常见别名处理表。

通过上述清洗与治理流程，可系统性地降低重复与缺失、修复ID映射混乱，最终实现投放与CRM数据的准确对齐与可追溯管理。建议先在样本数据上跑通阻塞与匹配规则，确定阈值与生存者策略，再推广至全量并建立持续监控。