2. 竞价异常波动复盘：数据质量校验与清洗

为什么先做数据质量校验

竞价分小时报表在导出、拼接、跨平台合并时，常见三类问题会制造“假波动”：缺失（某小时整行丢失，消耗被低估）、重复（同一小时被多次追加，消耗被高估）、异常值（单位错位、小数点漂移、负数或极端离群）。若不先校验，后续的波动幅度计算会把数据缺陷误判为业务异常。本例以某账户 2024-06-18 的分小时报表为例，先做完整性、重复值、异常值三项校验，再清洗并输出可信数据集。

校验规则与判定口径

示例报表字段：date, hour, campaign, impressions, clicks, cost, conversions。校验口径如下：

• 完整性：单账户单日应有 24 个小时行；缺失小时用前后小时均值插补，并标记 imputed=1。
• 重复值：以 (date, hour, campaign) 为唯一键，重复行保留 cost 最大的一条（视为完整采集），其余丢弃并记录条数。
• 异常值：cost < 0、clicks > impressions、conversions < 0 判为非法；cost 用 IQR 法（Q3+1.5·IQR 上界）识别离群，离群点不删除，改为标记 outlier=1 供复盘时单独核查。

用 Python 完成校验与清洗

下面代码自包含：先构造一份含缺失、重复、非法值与离群的示例报表，再依次执行三项校验、清洗并输出可信数据集与处理日志。示例数据为教学构造，运行结果由代码直接推出。

import pandas as pd
import numpy as np

# 1. 构造示例报表（教学数据，含缺陷）
rows = []
base = {0:120,3:180,6:300,9:420,12:520,15:460,18:600,21:260}
for h in range(24):
    cost = base.get(h, 200 + (h % 5) * 30)
    rows.append({"date":"2024-06-18","hour":h,"campaign":"A",
                 "impressions":cost*10,"clicks":cost*0.8,
                 "cost":cost,"conversions":round(cost/50,1)})
df = pd.DataFrame(rows)

# 注入缺陷：删除 9 时、重复 12 时、非法负值、离群
df = df[df["hour"] != 9]
df = pd.concat([df, df[df["hour"] == 12]], ignore_index=True)
df.loc[df["hour"] == 12, "cost"] = [520, 780]   # 重复行消耗不一致
df.loc[df["hour"] == 3, "cost"] = -180          # 非法负值
df.loc[df["hour"] == 18, "cost"] = 600          # 真实突增，保留

# 2. 完整性校验
expected = set(range(24))
actual = set(df["hour"])
missing = sorted(expected - actual)
print("缺失小时:", missing)

# 3. 重复值校验与去重（保留 cost 最大）
key = ["date","hour","campaign"]
dup_cnt = df.duplicated(subset=key, keep=False).sum()
df = df.sort_values("cost", ascending=False).drop_duplicates(subset=key, keep="first")
df = df.sort_values("hour").reset_index(drop=True)
print("重复行数:", dup_cnt)

# 4. 异常值校验
df["imputed"] = 0
df["outlier"] = 0
illegal = (df["cost"] < 0) | (df["clicks"] > df["impressions"]) | (df["conversions"] < 0)
df.loc[illegal, "cost"] = np.nan
df.loc[illegal, "imputed"] = 1

# 5. 缺失小时插补（前后小时均值）
full = pd.DataFrame({"date":"2024-06-18","hour":range(24),"campaign":"A"})
df = full.merge(df, on=key, how="left")
df["imputed"] = df["imputed"].fillna(1).astype(int)
df["cost"] = df["cost"].interpolate(method="linear", limit_direction="both")
df["outlier"] = df["outlier"].fillna(0).astype(int)

# 6. IQR 离群标记（不删除）
q1, q3 = df["cost"].quantile([0.25, 0.75])
upper = q3 + 1.5 * (q3 - q1)
df.loc[df["cost"] > upper, "outlier"] = 1

print("清洗后行数:", len(df))
print("总消耗(清洗后):", round(df["cost"].sum(), 1))
print(df[["hour","cost","imputed","outlier"]].to_string(index=False))

结果判读与可信数据集输出

运行后应看到：缺失小时为 [9]，重复行数为 2（12 时两条），清洗后行数为 24。9 时被插补为前后均值，12 时保留消耗较大的 780 元一条，3 时负值被置空后插补，18 时 600 元若超过 IQR 上界则 outlier=1 但数值保留。最终输出字段包含 imputed 与 outlier 两个标记列，复盘时凡 imputed=1 的小时需在结论中注明“数据插补，波动可信度降低”，凡 outlier=1 的小时进入人工核查清单而非直接判为异常。