1.9数据质量相关内容

9.数据质量相关内容开篇为什么数据质量比分析方法更重要我刚入行时接到一个任务分析“为什么上个月店铺复购率下降了”。我用SQL提取了订单数据用Excel做了漂亮的图表得出“高价值用户流失导致复购率下降”的结论。店长很重视立刻调整了高价值用户的维护策略。一个月后复购率不仅没回升反而继续下降。店长把我叫到办公室“你的分析到底准不准”后来我复查数据发现原始订单表中有大量重复订单系统重复导入和退款订单未剔除。我的分析基于“脏数据”结论自然不可靠。从那时起我养成了一个习惯任何分析之前先花20%的时间检查数据质量。数据质量有5个核心标准完整性、准确性、一致性、有效性、唯一性。任何一个标准不达标你的分析结论都可能误导业务决策。这一章帮你掌握这5个标准学会用系统的方法检测和修复数据质量问题。学习前准备下载1份“有问题”的电商订单样例数据比如包含空值、重复订单、格式错误等用于后续实操检测。电商场景下数据质量的核心意义数据质量是分析结论的“地基”如果地基不牢上面盖的房子再漂亮也会塌。数据质量就是数据分析的地基。一个常见的恶性循环数据质量差 → 分析结论错误 → 业务决策失误 → 浪费资源甚至造成损失 → 团队对数据部门失去信任。数据质量不达标的典型负面影响质量问题电商场景示例后果完整性订单金额为空GMV统计偏低低估业绩准确性金额列混入“¥”字符无法直接求和统计报错一致性同一店铺在不同表中名称不一致关联后数据丢失有效性日期字段出现“2025-02-30”时间分析出错唯一性同一订单号重复出现GMV重复计算虚高我的踩坑经历刚入职第一年我负责制作“月度经营分析报告”。我从数据库导出订单表直接用SUM(amount)算GMV结果是980万。财务那边对账只有920万差了60万。排查了一整天发现订单表里有几千条“已取消”的订单金额被计入了GMV但这些订单实际上没有成交。从那以后我养成了习惯每次统计前先过滤order_status 已支付。实操避坑提醒很多新人只关注分析方法是否“高级”却忽略了数据质量。实际上用高质量的数据做简单的统计远比用低质量的数据做复杂的模型更有价值。完整性标准详解核心定义完整性指数据没有缺失。关键字段如订单号、金额、用户ID必须完整不能有空值。电商场景下的常见问题订单金额为空可能是系统故障或退款单未处理用户ID缺失匿名用户但有时也代表数据采集问题下单时间为空无法分析时间趋势商品类目为空无法做品类分析检测方法Excel方法选中数据区域按CtrlA全选按CtrlG→ “定位条件” → “空值” → 确定Excel会高亮所有空白单元格并显示计数SQL方法SELECTCOUNT(*)AStotal_rows,SUM(CASEWHENamountISNULLTHEN1ELSE0END)ASamount_null_cnt,SUM(CASEWHENuser_idISNULLTHEN1ELSE0END)ASuser_id_null_cntFROMorders;Python方法df.isnull().sum()分步操作Excel步骤1打开订单数据选中金额列。步骤2查看状态栏的“计数”和“数值计数”。如果“数值计数”小于“计数”说明有非数值包括空值。步骤3用定位条件选中所有空值观察分布。步骤4记录缺失比例缺失行数/总行数。如果超过5%需要追溯数据源问题。修复方案场景修复方法关键字段订单号为空删除该行无法修复金额为空但订单状态为“已取消”填充0金额为空且订单状态正常用均值/中位数填充或从其他系统补录用户ID为空用“匿名用户”标识填充仅用于计数不可用于用户画像电商场景实操案例场景订单表中金额列有50个空值。其中30个订单状态为“已取消”填充020个状态为“已支付”需要向运营确认数据来源暂时用该店铺平均金额填充。-- SQL示例更新已取消订单的金额为0UPDATEordersSETamount0WHEREorder_status已取消ANDamountISNULL;避坑提醒不要随意删除含有空值的行除非关键字段缺失。删除前先分析缺失原因。填充均值会改变数据分布慎用。优先考虑按分组如店铺、类目填充中位数。准确性标准详解核心定义准确性指数据真实反映业务事实没有错误或偏差。包括数据类型正确、值域合理、无逻辑错误。电商场景下的常见问题金额列混入字符“¥299”而不是“299”日期格式错误“2025年1月1日”而不是标准日期金额为负数本应为正数金额超大如10万元可能是测试订单性别字段出现“男/女”以外的值检测方法Excel方法选中金额列看状态栏是否显示“求和”。如果不显示说明有非数字。用条件格式标记异常值大于10000或小于0。用数据验证检查枚举字段是否在允许范围内。SQL方法-- 检查金额是否包含非数字假设金额是字符类型SELECT*FROMordersWHEREamountREGEXP[^0-9.];-- 检查负数金额SELECT*FROMordersWHEREamount0;-- 检查超大金额SELECT*FROMordersWHEREamount100000;分步操作Excel步骤1选中金额列点击“数据” → “分列” → 完成可强制转为数字非数字会报错。步骤2用“查找和选择” → “替换”将“¥”替换为空。步骤3用条件格式 → “大于” → 输入10000标记异常值。步骤4筛选异常值与运营确认是否需要剔除或修正。修复方案问题类型修复方法金额带符号替换掉符号转为数字日期为文本用“分列”或DATEVALUE函数转为日期金额为负数如果是退款订单改为0或保留负数但统计时过滤如果是录入错误取绝对值金额超大与业务方确认是否为真实交易否则删除或标记枚举值错误用“查找替换”统一为正确值电商场景实操案例场景订单表中的金额列混入了“¥”符号和千分位逗号如“¥1,299.00”。修复步骤选中金额列按CtrlH打开替换对话框。查找“¥”替换为“”空全部替换。查找“,”替换为“”全部替换。将列格式设为“数值”小数2位。避坑提醒金额为负数不一定是错误。电商场景中退款订单可能记为负数。统计GMV时应过滤amount 0而不是简单取绝对值。日期转换时如果出现#VALUE!错误说明有些日期格式不标准需要先统一格式如用“分列”功能。我的踩坑经历有一次我直接用SUM(amount)计算GMV结果比财务少了30万。排查发现金额列里有几百个“¥”符号没去掉Excel把它们当文本忽略了。从那以后我每次收到数据都会先检查金额列是否能正常求和。一致性标准详解核心定义一致性指同一业务实体在不同数据源或不同时间点的表示方式一致。比如同一家店铺在订单表和店铺表中的名称必须相同。电商场景下的常见问题店铺名称不一致“时尚女装旗舰店”在订单表中写“时尚女装”在店铺表中写“女装旗舰店”商品ID在不同系统中编码不同状态值不一致“已支付”在一张表中是“paid”另一张是“PAYED”金额单位不一致有的以元为单位有的以分为单位检测方法Excel方法对店铺名称列做去重查看是否有相似但不同的值。用COUNTIFS检查两个表的关联字段是否都能匹配上。SQL方法-- 检查订单表中的店铺名称是否都能在店铺表中找到SELECTDISTINCTo.shop_nameFROMorders oLEFTJOINshops sONo.shop_names.shop_nameWHEREs.shop_nameISNULL;分步操作Excel步骤1复制店铺名称列到新工作表用“删除重复值”得到唯一值列表。步骤2肉眼检查是否有“时尚女装”“时尚女装旗舰店”“女装旗舰店”等相似值。步骤3建立一个映射表如“时尚女装” → “时尚女装旗舰店”用VLOOKUP统一替换。步骤4重新关联两个表验证匹配率。修复方案建立映射表将不规范的名称统一映射到标准名称。在数据清洗流程中加入标准化步骤例如用UPDATE语句将shop_name中的“女装旗舰店”改为“时尚女装旗舰店”。对于枚举值如状态统一使用代码表禁止自由文本。电商场景实操案例场景订单表中有店铺名称“京东自营”店铺表中是“京东自营旗舰店”导致JOIN后大量数据丢失。修复步骤创建映射表原名称标准名称京东自营京东自营旗舰店用VLOOKUP将订单表的店铺名称替换为标准名称。重新关联。避坑提醒一致性问题的根源通常是多系统独立录入。建议推动业务方统一主数据管理MDM。不要手动一个个改用映射表批量替换确保可复现。在数据库层面可以用CHECK约束或外键约束来强制一致性。有效性标准详解核心定义有效性指数据符合预定义的业务规则和格式规范。例如日期必须是有效日期年龄必须在0-120之间订单状态必须是枚举值之一。电商场景下的常见问题日期字段出现“2025-02-30”无效日期年龄字段出现“200”超出合理范围订单状态出现“已付”应该是“已支付”手机号位数不对金额超出商品单价上限检测方法Excel方法对日期列设置“条件格式” → “无效日期”Excel自动标记。对金额列设置数据验证允许小数介于0和10000之间。对状态列做去重检查是否有异常值。SQL方法-- 检查无效日期SELECT*FROMordersWHERESTR_TO_DATE(order_date,%Y-%m-%d)ISNULL;-- 检查状态是否在允许范围内SELECTDISTINCTorder_statusFROMordersWHEREorder_statusNOTIN(待支付,已支付,已取消,已完成);分步操作Excel步骤1选中日期列用“分列”强制转为日期无法转换的会变成错误值。步骤2筛选出错误值行查看原始数据是什么。步骤3用“数据验证” → “设置” → “允许日期” → “介于2020-01-01到2025-12-31”圈释无效数据。修复方案问题修复方法无效日期如果能推断如“2025-02-30”可改为“2025-03-02”否则删除或设为空超出范围的数值与业务方确认是否为录入错误修正或删除枚举值错误用映射表统一到标准值电商场景实操案例场景订单表中出现“2025-02-30”的无效日期。修复在Excel中用IFERROR(DATE(YEAR(A2), MONTH(A2), DAY(A2)), )判断如果无效则置空。然后根据相邻订单的时间推测合理日期或直接删除该行如数据量小。避坑提醒有效性检查要结合业务规则。比如生鲜商品的有效期可能只有几天但服装商品的有效期可以是几年。不要用统一规则。对于无效数据不要简单删除。先分析原因可能是上游系统bug需要反馈修复。唯一性标准详解核心定义唯一性指每条记录在业务主键上不重复。例如订单号应该唯一用户ID不应该重复。电商场景下的常见问题同一订单号重复出现系统重复导出、人为复制粘贴同一用户ID有多条注册记录多账号合并商品编码重复不同商品用了同一个编码检测方法Excel方法选中订单号列用“条件格式” → “突出显示单元格规则” → “重复值”。或者用COUNTIF函数COUNTIF(A:A, A2)1筛选出重复行。SQL方法SELECTorder_id,COUNT(*)AScntFROMordersGROUPBYorder_idHAVINGcnt1;分步操作Excel步骤1选中订单号列。步骤2点击“条件格式” → “突出显示单元格规则” → “重复值” → 设置格式。步骤3按颜色筛选查看所有重复行。步骤4用“删除重复值”功能数据选项卡去重注意选择“保留第一次出现”。修复方案去重保留一条有效记录通常是第一次出现或根据时间保留最新。如果重复是因为系统bug需反馈技术团队修复源头。对于业务上允许重复的情况如同一用户多笔订单主键应该是订单号商品行号需要重新定义主键。电商场景实操案例场景订单表中有5条重复的订单号“ORD12345”可能是运营重复粘贴了数据。修复步骤按订单号去重保留create_time最早的一条首次下单。如果重复订单的状态不同如一条“待支付”一条“已支付”保留“已支付”的状态并记录日志。去重后重新计算GMV。避坑提醒去重前要明确业务规则保留哪一条是第一次还是最后一次不要只凭订单号去重如果订单号本身有bug如重复生成需要联合其他字段如用户ID时间判断。去重后要对比去重前后的总行数和关键指标确认没有误删。我的踩坑经历有一次去重时我直接用删除重复值没注意Excel默认保留第一次出现。结果有个订单号对应两笔不同金额第一笔是取消后的重拍我保留了第一次的取消订单金额导致GMV统计少了3000元。从那以后我去重前都会先排序确保保留的是“已支付”状态的那条。综合实操案例生鲜类目店铺双11订单数据全维度质量检测与修复案例背景你拿到一份“生鲜店铺双11订单数据”共10万行。需要按照5个标准进行全维度质量检测并修复发现的问题最终输出一份“数据质量报告”。原始数据可能存在以下问题模拟完整性金额列有500个空值用户ID有200个空值。准确性金额列混有“¥”符号日期列有文本格式“2025年11月11日”。一致性店铺名称有“鲜果生鲜”和“鲜果生鲜旗舰店”两种写法。有效性订单状态出现“已付”等异常值金额有负数退款订单。唯一性订单号有20个重复。分步操作步骤1完整性检测与修复-- 检测SELECTCOUNT(*)AStotal,SUM(CASEWHENamountISNULLTHEN1ELSE0END)ASamount_nulls,SUM(CASEWHENuser_idISNULLTHEN1ELSE0END)ASuser_id_nullsFROMorders_20251111;修复金额为空且订单状态为“已取消”的填充0。金额为空且订单状态正常的用该商品类目的平均金额填充。用户ID为空的填充“ANON”。步骤2准确性检测与修复用替换功能去掉金额列中的“¥”符号转为数字。将日期列用“分列”功能转为标准日期格式。步骤3一致性检测与修复-- 检测店铺名称不一致SELECTDISTINCTshop_nameFROMorders_20251111WHEREshop_nameNOTIN(SELECTshop_nameFROMshops);修复建立映射表将“鲜果生鲜”统一替换为“鲜果生鲜旗舰店”。步骤4有效性检测与修复-- 检测订单状态是否在枚举范围内SELECTDISTINCTorder_statusFROMorders_20251111WHEREorder_statusNOTIN(待支付,已支付,已取消,已完成);修复将“已付”等替换为“已支付”将负数金额退款标记为“已取消”状态并金额置0。步骤5唯一性检测与修复-- 查找重复订单号SELECTorder_id,COUNT(*)FROMorders_20251111GROUPBYorder_idHAVINGCOUNT(*)1;修复保留订单状态为“已支付”的那一行删除其他重复行。步骤6生成数据质量报告输出一个Excel文件包含以下sheet原始数据质量统计各维度问题数量修复后的数据清洗后修复操作日志做了什么、改了哪些行案例小结通过这个案例你学会了系统性地检测和修复数据质量问题。在实际工作中建议将这套流程固化到数据处理脚本中每次新数据进来自动执行质量检测。 电商数据合规提示在修复数据时尤其是填充缺失值、替换错误值要保留原始数据的备份和修改日志。合规审计时你需要能说清楚“为什么改了这些数据”。涉及用户个人信息的字段如手机号不要随意填充或替换应联系数据负责人处理。本章踩坑清单与合规总结五大标准速查表标准核心检查点常用工具修复优先级完整性空值比例isnull()、定位条件高关键字段准确性数据类型、值域条件格式、分列高一致性跨表匹配VLOOKUP、JOIN中有效性业务规则数据验证、枚举检查中唯一性重复记录COUNTIF、GROUP BY高电商数据合规提示数据修复的合规边界不要擅自修改原始数据。应该先备份并在修复日志中记录每次修改的原因、修改人、修改时间。对于涉及用户个人信息的数据修复如修正错误的手机号必须遵循公司数据治理流程。缺失值填充的风险用均值或中位数填充缺失值会改变数据分布可能影响统计结论的准确性。在报告中应注明“金额缺失值已用中位数填充”供决策者参考。删除重复行如果重复行中包含了不同的业务状态如一笔订单既有“已支付”又有“已取消”删除前需与业务方确认保留逻辑。结语数据质量是数据分析的生命线。掌握完整性、准确性、一致性、有效性、唯一性这五个标准你就能系统地发现和修复数据问题确保分析结论可信。记住数据质量不是一次性的工作而应该嵌入到日常数据处理流程中。有问题的评论区留言我看到会回复。