1、业务背景
某电商平台有一份2019年的销售数据,包含用户、商品、销量、订单时间,是否退款等数据。为了给2020年的平台运营提供业务指导,需对2019年的销售情况进行分析。
数据为数据分析部门提取以及手动录入,可能存在错误。
数据中下架商品id统一标识为PR000000。
平台规定用户下单后必须在30分钟内支付,否则订单自动取消。
2、理解数据
![4d21425ba67c7fbf7f4d573af19eec35.png](https://i-blog.csdnimg.cn/blog_migrate/93056a17d13d59b80cc0eccbb548678f.jpeg)
数据文件名为 order2019,包含一个 sheet 工作表,名为 data。
data 数据共 104558 行(含标题行),11 列(即11个字段)。
各字段信息如下:
- A: id: 数据行索引,用于标记数据的行数,不重复
- B: orderID: 订单编号,用户购买订单编号
- C: userID: 用户编号,每个用户都有自己的用户识别编号
- D: goodsID: 商品编号,用户购买商品识别编码
- E: orderAmount: 订单金额,用户下单时的金额
- F: payment: 用户支付金额,相对订单金额,用户支付金额可能会有折扣
- G: channelID: 用户渠道来源
- H: platformTypr: 用户购买平台,如 APP,网页,小程序等
- I: orderTime: 用户下单时间,年月日以及时间
- J: payTime: 用户支付时间,应晚于下单时间,一般 30 分钟内必须支付,否则订单自动取消
- K: chargeback: 是否退款,标记订单是否退款
3、分析思路
![c577e1dd88f8ed12ba2e763197db0a82.png](https://i-blog.csdnimg.cn/blog_migrate/e8b935bced929d89d2cd7f61029cf3b4.jpeg)
4、提取数据
4.1 备份数据
新建一个 sheet,命名为"备份",然后将 data 中的数据全选,复制到"备份"工作表进行数据备份。此操作的目的是暴增后续数据分析期间,出现异常时可用备份数据继续分析。
![c229223db1e0dd43e49db5aa81efa35d.png](https://i-blog.csdnimg.cn/blog_migrate/43211e9255736e51bd1b5fdb1f26b3b2.jpeg)
4.2 冻结首行
进入"data"工作表中,点击菜单栏【视图】【冻结窗格】【冻结首行】,用以固定标题行。字段比较多,Excel固定首行功能可以方便在滚动时,依然能查看数据字段名称。
![aec3c6be87fc473f214aa0683d8af045.png](https://i-blog.csdnimg.cn/blog_migrate/449fc3d9ca991280f4f0b6099527ff6a.jpeg)
4.3 筛选
选中数据标题行,单击菜单【开始】【排序和筛选】【筛选】
![60cba916080dc4801241c3a42578f613.png](https://i-blog.csdnimg.cn/blog_migrate/0808081227382b7d0dc0d33678562871.jpeg)
4.4 提取数据子集
本次业务分析范围为 2019 年的订单数据,需剔除订单时间不在 2019/1/1 至 2019/12/31 之间的数、不符合逻辑的数据以及支付时间超过30min的数据。
- 筛选 orderTime 不在在 2019/1/1 至 2019/12/31 之间的数据,删除。
- 点击 orderTime 的筛选按钮,依次点击【日期筛选】【自定义筛选】
![f32364dadfac1ab17d88b36c2b940813.png](https://i-blog.csdnimg.cn/blog_migrate/c40fc0823b05d5f248fa3db034af4f2e.jpeg)
- 在自定义筛选方式中,第一个选择"在以下日起之后",然后输入"2019/12/31 23:59";第二个选择"在以下日起之前",然后输入"2019/1/1 00:00",条件选择"或",然后点击确定。
![554775eab78eaaeb3eebba67589b044b.png](https://i-blog.csdnimg.cn/blog_migrate/c5e81feb4e75fe5aa35f630749a995d3.jpeg)
- 可以看到 261 条数据被筛选出来。这些数据订单日起不在 2019 年,所以选中这些行,全部删除。
![77f6edf6aa2132c7d5af0feafd0bbf66.png](https://i-blog.csdnimg.cn/blog_migrate/d0accee3a1d181593cf218d71f7eee8c.jpeg)
- 点击 A 列查看数据,还剩 102497 行。
![23b2fb83e99b44c7ab29f2b5c5a3edd6.png](https://i-blog.csdnimg.cn/blog_migrate/6c1b087afa9f290acea3c08a609ab9e5.jpeg)
- 删除支付时间早于订单时间的数据
购物流程是先下单,再支付。支付时间早于订单时间的数据属于错误数据,不符合业务逻辑,需要删除。如何判断是否有这个错误?需要新建一列辅助列。
- 在 L2 单元格输入公式"=IF(J2
![7feff6d4a540bbb80d929a95d30155f9.png](https://i-blog.csdnimg.cn/blog_migrate/506a2569b721c747422945397b13671c.jpeg)
- 鼠标放至 L2 单元格右下角,当鼠标形状变成十字形时,双击,即可向下填充所有数据,并且每个数据都自动按公式计算。
![2f3e368ecfbc7a522a2125766c1acc86.png](https://i-blog.csdnimg.cn/blog_migrate/867a14a61417c158e1e22099fe2f6570.jpeg)
- 筛选 L 列为'Y'的数据,即为支付时间早于下单时间的错误数据。
![b5f531fb66f76df65b606caa20bd441d.png](https://i-blog.csdnimg.cn/blog_migrate/ade8e206844cb68f2e3f4ece45756cff.jpeg)
- 可