推荐数据清洗步骤

Recommended data cleaning steps for an advertising and behavior dataset (impressions/clicks/conversions, UTM parameters, channel and creative IDs, A/B experiment grouping)

1. Schema normalization and typing

• Define a canonical event schema with required fields: event_id, event_type (impression/click/conversion), event_ts, platform, ad_account_id, campaign_id, ad_group_id, creative_id, channel_id, url, referrer_url, utm_source, utm_medium, utm_campaign, utm_content, utm_term, user_id, device_id, cookie_id, session_id (if exists), experiment_id, variant, order_id/transaction_id, revenue, currency, consent_flag.
• Enforce data types:
  • Timestamps: parse to UTC ISO 8601, store as datetime with timezone; retain raw ingestion_ts for auditing.
  • IDs: cast to string, strip whitespace; if numeric IDs are expected, validate parsability and store canonical string representation to preserve leading zeros.
  • Monetary fields: numeric with explicit currency; validate non-negative.
  • Categorical: lowercase, trimmed, normalized encoding (UTF-8).
• URL and parameter parsing:
  • Decode URLs (percent-encoding), extract query parameters to structured fields.
  • If UTM fields are missing, parse from landing/referrer URLs; preserve raw and parsed versions.

2. Event integrity and validation

• Validate allowed event_type values and remap common synonyms (e.g., “view” → impression; “purchase” → conversion) into the canonical set.
• Timestamp sanity checks:
  • Drop or flag events with event_ts outside campaign or data collection windows.
  • Remove obvious clock errors (e.g., year far in past/future); if platform ingestion_ts exists, cap discrepancies with a defined tolerance (e.g., ±3 days) and flag.
• Event ordering constraints:
  • Conversions must not precede the attributed click/impression for the same user within an attribution window; flag negative time-to-event as data errors.
  • Ensure click timestamps precede conversion timestamps; if not, investigate source-specific latency or duplicate conversion.

3. Identity resolution

• Construct a persistent person_id using deterministic keys in priority order: hashed_email > login user_id > device_id > cookie_id. Maintain a mapping table with versioning.
• Normalize device identifiers (consistent casing, remove separators for some IDs where applicable).
• Remove or flag events lacking any identifier if user-level attribution is required; otherwise route to aggregate-only analyses.

4. Deduplication

• Enforce uniqueness on event_id where available; if missing, generate a synthetic key (hash of source_system, event_type, user_id/device_id, ad_id/creative_id, event_ts rounded to suitable precision).
• Deduplicate within event type using keys and short time windows:
  • Impressions: drop exact duplicates; optionally compress bursts from the same ad server within milliseconds if known duplication pattern.
  • Clicks: collapse repeated identical clicks within 1–2 seconds per user-creative to mitigate multi-fire.
  • Conversions: dedupe by transaction_id/order_id and user_id; keep the first occurrence. If transaction_id missing, dedupe by (user_id, revenue, event_ts within N minutes) with conservative rules and flag.
• Cross-source dedup:
  • If multiple platforms report the same conversion, prefer primary source-of-truth (ecommerce or CRM) and mark ad-platform conversions as secondary.

5. UTM canonicalization

• Normalize utm_* fields: lowercase, trim, remove surrounding quotes, decode URL-encoding.
• Map synonyms into canonical values:
  • Medium: map to controlled vocabulary (cpc, display, social, email, affiliate, referral, organic, paid_social, other). Examples: “ppc” → cpc; “paid social” → paid_social.
  • Source: unify common variants (e.g., “google”, “googleads”, “adwords” → google; “fb”, “facebook” → facebook).
  • Campaign/content/term: enforce naming conventions (no spaces if policy requires; replace illegal characters; trim long values; optionally split structured names into parts using agreed delimiters).
• Validate UTM coherence:
  • Medium/source combinations must be allowed (e.g., cpc + google valid; email + google invalid).
  • Flag missing or inconsistent UTMs; backfill source/medium from channel_id/platform metadata where possible.
• Preserve both original and canonical UTM fields to avoid loss of detail.

6. Channel and creative metadata validation

• Join fact events to dimension tables for channel, campaign, ad group, creative. Validate existence and foreign key integrity.
• Fix known legacy ID changes via a mapping table; flag orphaned IDs with no metadata.
• Validate one-to-many relationships: a creative_id should map to a single campaign/ad_group within the same platform/time window; flag violations.
• Enforce active date ranges: drop or flag events occurring outside the metadata’s valid period.

7. A/B experiment data quality

• Validate experiment assignment:
  • Check that each person_id is assigned to exactly one variant within an experiment; flag crossovers (saw both variants) and decide on handling (exclude or assign by first exposure).
  • Ensure stable assignment over time; detect re-randomization.
• Experiment timing:
  • Exclude events before experiment start or after end for that experiment.
  • Confirm exposure prior to outcome for intent-to-treat vs per-protocol definitions; tag compliance.
• Sample ratio mismatch (SRM) check:
  • Compare observed variant counts to expected allocation using chi-square; flag significant mismatch.
• Contamination control:
  • Remove internal traffic from experiment measurements (see bot/internal filter below).
  • Ensure variants do not share creatives or UTMs that could blur attribution.

8. Bot, fraud, and internal traffic filtering

• User-agent filtering: remove known bot/crawler UA patterns; apply IAB/industry bot lists where available.
• IP and ASN filtering: exclude data center ranges and internal corporate IP ranges; use maintained lists.
• Behavioral heuristics:
  • Excessive clicks per minute, zero dwell time, impossible CTR (e.g., CTR > 1.0 for display), conversions occurring seconds after impression with no click where click is required.
  • Cookie-less sequences with high frequency across geographies.
• Platform fraud signals: use platform flags (invalid click, suspected bot) to exclude or down-weight.
• Document and tag exclusions for auditability.

9. Sessionization and sequencing

• Build sessions per person_id using inactivity threshold (commonly 30 minutes). Assign session_id and order within session.
• Sequence events: impression → click → landing page → downstream events → conversion; check for missing steps and tag inferred paths (e.g., view-through).
• Compute derived fields: time_to_click, time_to_convert, touchpoint_index.

10. Attribution-prep flags

• Create is_click_through and is_view_through flags for conversions.
• Assign attributed_touchpoint_id(s) based on chosen model (e.g., last-click within 7-day window, or position-based). For cleaning, ensure window boundaries and event ordering are correct and tag unattributed conversions.
• Retain both raw and modeled attribution fields.

11. Handling missing, malformed, and outliers

• Quantify missingness per critical field (IDs, timestamps, UTMs). Set thresholds for exclusion (e.g., drop events missing event_ts or event_type; retain events with missing utm_term).
• Do not impute keys or timestamps. For categorical fields used in grouping, assign “unknown” category rather than drop unless analysis requires otherwise.
• Outlier checks:
  • CTR, CVR by creative/channel outside reasonable bounds; flag and review for tracking errors.
  • Revenue outliers; check currency mismatches; standardize currency to a reporting currency with a dated FX rate.
• Remove duplicated transactions and extreme anomalies caused by tracking misfires.

12. Timezone, localization, and calendar normalization

• Standardize all event_ts to UTC; retain local_time and timezone where user-level analysis depends on local behavior.
• Handle daylight saving transitions carefully (use timezone-aware libraries).
• Align reporting calendars (ISO week, fiscal period) and store precomputed date keys.

13. Consent and privacy compliance

• Respect consent_flag and jurisdictional rules (GDPR/CCPA). Exclude or aggregate events without consent as required.
• Mask or hash PII; maintain salted hashes consistently across systems.
• Apply data retention policies; remove expired identifiers.

14. Quality assurance checks and metrics

• Duplicates rate per event type after dedup.
• Join rates to metadata dimensions; orphaned ID percentage.
• Missing rate per UTM field and proportion successfully backfilled.
• SRM p-value for experiments; crossover rate.
• Bot/internal exclusion share; monitor over time.
• Volume reconciliation against platform reports (impressions, clicks, spend, conversions) within acceptable variance.
• Lag distributions (impression→click, click→convert) to detect ingestion delays or ordering errors.

15. Outputs and documentation

• Produce a cleaned fact table with canonical fields:
  • event_id, event_type, event_ts_utc, person_id, session_id, platform, channel_id, campaign_id, ad_group_id, creative_id, url, referrer_url, utm_source_raw/canonical, utm_medium_raw/canonical, utm_campaign_raw/canonical, utm_content_raw/canonical, utm_term_raw/canonical, experiment_id, variant, order_id, revenue, currency, is_click_through, is_view_through, attributed_touchpoint_id, consent_flag, quality_flags (dedup, bot, timestamp_error, orphan_metadata, unattributed).
• Maintain a data dictionary covering field definitions, valid values, and cleaning rules.
• Version and log the cleaning pipeline; store anomaly reports for audit.

Implementation notes

• Prefer SQL for deterministic dedup and joins; use window functions for time-window de-duplication.
• Use robust URL parsing and timezone-aware datetime libraries.
• Keep original raw tables immutable; write cleaned tables with lineage columns (source_system, load_batch_id).

These steps establish consistent, analyzable data for attribution, channel/creative performance, and A/B experiment evaluation while preserving auditability and minimizing bias introduced by data quality issues.

以下为多渠道运营数据（App/网页/小程序）在存在“重复用户标识”和“字段命名不一致”情况下的推荐清洗步骤与实施要点。目标是建立统一、可追溯、可复用的数据底座，支持后续统计分析与归因。

一、总体流程与分层

• 原始层 Raw：保持来源原貌，仅做基础解码与落盘分区。
• 标准化层 Staging：字段命名、数据类型、时区、事件映射、去重、ID 统一。
• 规范层 Canonical：统一实体模型（用户、事件、订单、商品、渠道），完成主外键关联、明细拆分。
• 应用层 Mart：面向分析的宽表与汇总（转化漏斗、留存、归因、LTV 等）。
• 增量与回填：采用基于事件时间分区（event_date）与主键去重的幂等加载。

二、字段命名与数据类型标准化

• 制定统一命名规范（小写、下划线、英文、含义稳定）。建议核心字段：
  • 通用：source（app/web/mp），channel（渠道/媒体），event_id（若缺失需生成），event_name，event_ts_utc，event_local_ts，ingest_ts，user_key（统一用户键），session_id，device_id，cookie_id，openid，unionid，login_id（账号），page_url，page_referrer，screen_name，app_version，os，browser，geo_country/region/city，utm_source/medium/campaign/content/term，event_params（JSON）。
  • 订单：order_id_global，source_order_id，order_ts_utc，user_key，order_status，payment_status，total_amount，currency，discount_amount，shipping_fee，tax_amount，line_items（拆分为子表：order_id_global, sku_id, product_id, qty, unit_price, currency）。
• 数据类型统一：时间统一为 UTC 时间戳（秒或毫秒统一），数值列显式精度（金额使用 decimal(18,2)），布尔类型规范化为 true/false。
• 字段