制定数据预处理步骤清单

以下为“多门店营销与销售日报汇总”数据集的预处理步骤，重点覆盖统一口径、去重与缺失填补，并给出可落地的规则与顺序。

一、目标与粒度定义

• 目标产出：按门店-日期为最小必备粒度的“营销与销售日报”事实表，可选分维度扩展至门店-日期-渠道、门店-日期-品类/商品。
• 主键建议：
  • 销售事实：business_date, store_id[, sku_id 或 category_id]
  • 营销事实：business_date, store_id, channel[, campaign_id]
• 关键约束：主键在清洗后唯一；指标为净额或毛额需事先约定并全局一致。

二、数据接入与基础标准化

• 编码与格式：统一为UTF-8；日期统一为ISO-8601（YYYY-MM-DD）；时区统一（例如 Asia/Shanghai）。
• 数据类型：对金额统一Decimal(18,2)，计数为整数型；布尔字段0/1统一；分类字段转为标准字典值。
• 货币与税：统一币种（CNY）；明确销售额与投放花费是否含税，必要时按税率转换到统一口径。
• 单位与精度：曝光为次、点击为次、花费为元；数量单位与换算率（箱/件）统一。

三、口径统一（指标定义与计算规则） 为避免混口径，先定义统一的业务指标与计算优先级，所有来源对齐到以下口径再汇总：

• 销售口径：
  • 订单数：支付成功订单数（去除取消/未支付）。
  • 毛销售额（GMV）：成交价×数量，含/不含税需统一。
  • 折扣额：活动立减+优惠券抵扣+会员折扣。
  • 退款额：按“退款完成日”或“原订单日”归属，需一致（推荐按退款完成日计入净额）。
  • 净销售额（Net Sales）= GMV − 折扣额 − 退款额。
  • 件数/销量：出库量或支付量，须统一。
• 营销口径：
  • 曝光/点击：去广告欺诈后的有效量（如可得）。
  • 花费：平台花费+服务费-平台返利（含税/未税统一）。
  • 转化：按选定归因窗口与模型（推荐店铺/本地线上以last-click同日或次日内进行）落入门店与日期。
  • 新老客界定：基于统一用户主键与回溯窗口。
• 归因与映射：
  • 渠道统一字典（如 SEM、信息流、联盟、线下DM、私域）。
  • 跨来源维度映射（utm_*、campaign_name、广告位ID）到标准channel/campaign_id。
  • 线上到门店映射：通过门店地理围栏、履约门店、活动门店表或POS绑定规则。

四、主数据与映射管理

• 门店主数据：store_id、名称标准化、营业状态（在营/停业/新开）、区域层级。
• 商品/品类主数据：sku_id->category_id->brand，名称、条码去噪与同义合并。
• 渠道/活动字典：channel_id、campaign_id标准化，活动生命周期与门店适用范围。
• 版本化映射：保存历史变更（门店更名/合并、SKU替代），便于回溯。

五、去重策略

• 完全重复记录：对原始行做行级哈希（全字段或业务字段）去重。
• 业务键去重：
  • 销售：同一（business_date, store_id, sku_id）出现多条记录时，选“数据质量优先级最高来源”或“最后更新时间最新”的记录。其余记录标记并保留审计表。
  • 营销：同一（business_date, store_id, channel[, campaign_id]）聚合为唯一记录，对数值型指标取求和（曝光/点击/花费），对单值属性取来源优先或非空优先。
• 近似重复与命名差异：
  • campaign_name、store_name 通过规范化（去空格、大小写、全半角、常见前后缀）+ 模糊匹配（如编辑距离）对齐至标准ID；设定阈值与人工白名单校正。
• 冲突解决优先级：
  1. 明确数据契约来源 > 2) 时间更近 > 3) 与主数据一致性更高 > 4) 值域校验通过。

六、缺失值处理策略

• 原则：先补维度键，再补度量值；基础度量补齐后，派生指标统一重算（如CTR、CVR、AOV、ROAS）。
• 维度字段：
  • store_id、business_date缺失：不可填补，行剔除或返回数据源修复。
  • channel/campaign缺失：若可由映射表推断则填补，否则填为“UNKNOWN”并标记数据质量告警。
  • sku_id缺失：若条码/名称可唯一映射则回填，否则聚合到“UNASSIGNED_SKU”且不进入SKU层报表。
• 数值字段（依据业务含义区分0与缺失）：
  • 明确“无事件即为0”的计数型（曝光、点击、订单数、件数）：若该粒度行存在且其他字段有效，缺失填0。
  • 金额类（花费、销售额、退款额、折扣额）：
    • 若该粒度行存在且可业务推断为无（如无投放日），填0。
    • 否则按分层时序插补：
      1. 小间隙（<=3日）：门店×同粒度线性插值或同星期几中位数插补。
      2. 较大间隙：门店×品类（或渠道）近4-8周移动中位数；无则区域×品类/渠道中位数；最终全局中位数。
  • 转化数/到店数等依赖上游事件的指标：优先不直接插补，按归因重算。确需插补时保持逻辑约束（转化数 ≤ 点击数）。
• 比率/派生指标：
  • CTR=点击/曝光、CVR=订单/点击、AOV=净销售额/订单、ROAS=净销售额/花费等，一律由底层原子指标重算，不直接填补。
• 标记与可追溯：
  • 为每个可插补字段增加 is_imputed 与 impute_method，记录层级与方法；对大额插补设置阈值告警（如超过门店近4周P95）。

七、数据一致性与质量校验

• 结构与唯一性：主键唯一；数据类型与范围检查（非负、金额精度、日期连续性）。
• 业务约束：
  • 点击≥转化；GMV≥净销售额；净销售额=GMV−折扣−退款；花费≥0。
  • 门店停业期间指标应为0或缺失且被合理处理。
  • 汇总对账：门店/日期汇总与来源系统（POS、投放平台）日总差异在容忍阈值内（如≤1%或≤50元），超限报警。
• 交叉校验：
  • ROI、ROAS极值与异常跳变检测（IQR或季节性残差法）；库存/销量约束（若可得）。

八、合并与产出

• 事实表建议：
  • 销售日报：business_date, store_id[, category_id/sku_id], orders, units, gmv, discount, refund, net_sales。
  • 营销日报：business_date, store_id, channel[, campaign_id], impressions, clicks, spend, conversions。
  • 宽表（可选）：按business_date, store_id[, channel]对齐后左连接，确保营销为空的门店/日也存在销售数据，反之亦然。
• 元数据与字典：输出数据字典、口径说明、归因与税口径声明、主数据版本号。
• 审计表：记录去重数量、插补比例、对账差异、异常清单。

九、流程与落地建议

• 增量与幂等：按business_date分区，支持回填与重跑；每步生成中间层（raw→standardized→conformed→curated）。
• 源优先级与回写：建立source_priority配置；出现冲突或高缺失时回写工单至数据源。
• 自动化监控：数据延迟、主键唯一性、缺失率、异常跳变、对账差异的自动告警。

以上流程确保在多来源、多门店的场景下，实现口径统一、可靠去重与可解释的缺失填补，支撑后续建模与可视化的稳定性与一致性。

Preprocessing plan for a cross-project dataset with mixed text and numeric features, inconsistent encodings, and heterogeneous missing-value rules

Scope and objectives

• Harmonize schemas, encodings, and missing-value semantics across projects.
• Produce clean, typed, and comparable features for modeling while preventing leakage and preserving cross-project generalization.

Pipeline steps

1. Data inventory and schema harmonization

• Compile a data dictionary per project: column names, types, units, allowed ranges, label definitions, missing-value codes, and known sentinels (e.g., -999, 9999, “N/A”, “Unknown”).
• Create a canonical schema with:
  • Standardized column names and data types.
  • Crosswalk tables mapping project-specific categories/labels to canonical codes.
  • Explicit unit definitions for numeric fields.
• Add source metadata columns (e.g., project_id, source_version, ingestion_timestamp).

2. File and character encoding standardization

• Detect file encodings via heuristic tools (e.g., uchardet/chardet) and fallback logic.
• Convert all text to UTF-8 with strict error handling:
  • Replace invalid byte sequences with U+FFFD and log occurrences.
  • Normalize Unicode to NFC or NFKC consistently.
• Normalize line endings and ensure consistent delimiter, quote, and escape rules for structured files.

3. Locale and format normalization

• Dates/times: parse with project-specific formats; standardize to ISO-8601; convert time zones to UTC; record original timezone if needed.
• Numbers: normalize decimal and thousands separators; enforce dot-decimal; strip locale artifacts (e.g., non-breaking spaces).
• Units: convert to canonical units (e.g., kg, m/s, °C); document conversion rules and edge cases.
• Booleans: unify to {0,1} or {False,True}; standardize truthy/falsey strings.

4. Column alignment and feature presence

• Align to the canonical schema:
  • For columns missing in some projects, create empty columns with NaN (or explicit missing-category token for categoricals).
  • Resolve name collisions or synonyms via a mapping table.
• Drop columns deemed non-informative or leakage-prone (e.g., direct label proxies, unique IDs used as features).
• Handle duplicate columns (same semantics, different names) by merging with precedence rules.

5. Missingness harmonization (core for inconsistent rules)

• Build a missingness lexicon per column and project:
  • String tokens: “NA”, “N/A”, “—”, “?”, “unknown”, “none”, “null”.
  • Numeric sentinels: -1, -999, 999, 9999, 1e9, 0 for non-zero-only measures, out-of-range flags.
  • Domain-based invalids: negative durations, impossible dates, zero divisions.
• Convert all such values to a single NaN representation; preserve a “missing_reason” code if useful.
• Create missingness indicators:
  • One binary flag per feature (is__missing).
  • Optional reason-specific flags if they carry signal (e.g., system_not_collected vs user_refused).
• Quantify per-record missingness to support downstream filtering or modeling strategies.

6. Type inference and casting

• Infer and enforce types: numeric (int/float), categorical, text, datetime, boolean, ordinal.
• Detect numeric-coded categoricals (e.g., 1=Red, 2=Blue) and cast to categorical with proper labels.
• Ensure consistent types across projects for the same field.

7. De-duplication and record linkage

• Remove exact duplicates (all fields identical) per project, then cross-project (if meaningful).
• Identify probable duplicates via:
  • Key-based (stable IDs) when available.
  • Fuzzy matching for text fields (e.g., MinHash/LSH or cosine for embeddings).
• Keep a linkage table for traceability; choose deduplication priority rules.

8. Outlier and invalid value handling

• Define column-wise validation rules from domain knowledge (ranges, regexes, business constraints).
• Flag violations as invalid; convert to NaN if irrecoverable.
• For continuous features, detect outliers using robust methods (IQR/winsorization or robust z-scores using MAD) computed on the training split only.
• Decide on clipping, winsorization, or leaving as-is depending on model sensitivity.

9. Text preprocessing (multi-project, potentially multi-lingual)

• Language detection per text field; if multi-lingual, consider:
  • Language-specific pipelines, or
  • A language-agnostic representation (e.g., multilingual embeddings).
• Normalize text:
  • Unicode normalization already enforced.
  • Lowercasing (unless casing is informative).
  • Remove/control punctuation, excessive whitespace, control characters.
  • Normalize URLs, emails, mentions, hashtags to placeholders if not informative.
  • Handle emojis/symbols based on task needs.
• Tokenization:
  • Wordpiece/BPE for transformer models, or
  • Standard tokenizers for classical models.
• Optional normalization:
  • Lemmatization/stemming (if using sparse models).
  • Stopword handling by language.
• Vectorization choices (decide once based on downstream model):
  • Sparse: TF-IDF with n-grams; limit vocabulary; handle OOV.
  • Dense: pretrained embeddings (e.g., multilingual models) with pooling; store embedding version.
• Truncation strategy for long texts; log truncation rates.

10. Numeric and continuous feature processing

• Unit-harmonized values already ensured.
• Transformations as needed:
  • Log1p for heavy-tailed distributions.
  • Box-Cox/Yeo-Johnson if appropriate.
• Scaling:
  • Use robust scaler (median/IQR) or standard scaler.
  • Compute statistics on training data only.
  • Consider project-aware scaling if strong covariate shift exists (fit per project, then standardize to global space or use project residualization).
• Handle zero-inflation explicitly if present (e.g., hurdle features).

11. Categorical feature processing

• Harmonize category labels across projects via crosswalk tables.
• Treat rare categories: collapse into “Other” using a frequency threshold per training data.
• Encoding:
  • One-hot for low-cardinality features.
  • Target encoding for high-cardinality with K-fold, noise, and project-aware folds to prevent leakage.
  • Hashing trick as an alternative when cardinality is extreme.
• Reserve an “Unknown” category for unseen levels at inference.

12. Temporal features

• From standardized datetimes, derive consistent time-based features (day-of-week, month, hour, season).
• For time series, handle resampling/alignment; ensure no forward-looking leakage.

13. Cross-project distribution shift mitigation (optional but recommended)

• Add project_id as a feature only if the downstream objective benefits and it does not encode leakage.
• Reweight samples to align marginal distributions if class or feature imbalance is severe.
• Consider per-project normalization or domain adaptation preprocessing (e.g., CORAL) where justified.

14. Train/validation/test splitting and leakage control

• Split by project or by time to reflect deployment scenario:
  • Cross-project generalization: leave-one-project-out or group-wise splits by project_id.
  • Temporal generalization: chronological splits.
• Fit all imputers, encoders, scalers, and vectorizers on training data only; apply to val/test.
• Ensure any target-based encodings use only in-fold target information.

15. Imputation strategy (after harmonized missingness)

• Numeric:
  • Simple: median or constant with missingness indicator.
  • Advanced: KNN imputer or model-based imputation; ensure scalability and leakage control.
• Categorical:
  • Mode or “Unknown”.
• Text:
  • Empty string or a special token [MISSING_TEXT].
• Evaluate imputation impact per feature; prefer simple, robust methods unless gains justify complexity.

16. Feature selection and redundancy checks

• Remove features with:
  • Near-zero variance globally.
  • Excessive missingness beyond a threshold.
  • High multicollinearity (e.g., VIF or correlation pruning).
• Preserve interpretability by documenting dropped features.

17. Quality assurance and validation

• Automated checks:
  • Type and range assertions post-transform.
  • Missingness rates before/after.
  • Category coverage and unseen-level handling.
  • Text length and tokenization diagnostics.
• Drift checks across projects/splits (e.g., PSI/KL) for key features.

18. Reproducibility and artifact management

• Implement the pipeline as a single, versioned transformer (e.g., scikit-learn Pipeline/ColumnTransformer with custom steps).
• Version control for:
  • Data dictionaries, crosswalks, missingness lexicons, unit conversions, vectorizer vocabularies/embeddings.
• Log configuration and random seeds; fix library versions; store training statistics for all preprocessors.

Deliverables

• Canonical schema and crosswalk tables.
• Missingness lexicon and imputation plan per feature.
• Encoding and unit normalization rules.
• A versioned preprocessing pipeline artifact with train-only fitted statistics.
• QA report with pre/post distributions, missingness, and drift summaries.