某电商平台2019年销售数据分析
时间：2023-03-15 23:52:01 | 来源：电子商务
时间：2023-03-15 23:52:01 来源：电子商务
1、业务背景
某电商平台有一份2019年的销售数据，包含用户、商品、销量、订单时间，是否退款等数据。为了给2020年的平台运营提供业务指导，需对2019年的销售情况进行分析。
数据为数据分析部门提取以及手动录入，可能存在错误。
数据中下架商品id统一标识为PR000000。
平台规定用户下单后必须在30分钟内支付，否则订单自动取消。
2、理解数据
数据文件名为 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、分析思路
4、提取数据
4.1 备份数据
新建一个 sheet,命名为"备份"，然后将 data 中的数据全选，复制到"备份"工作表进行数据备份。此操作的目的是暴增后续数据分析期间，出现异常时可用备份数据继续分析。
4.2 冻结首行
进入"data"工作表中，点击菜单栏【视图】【冻结窗格】【冻结首行】，用以固定标题行。字段比较多，Excel固定首行功能可以方便在滚动时，依然能查看数据字段名称。
4.3 筛选
选中数据标题行，单击菜单【开始】【排序和筛选】【筛选】
4.4 提取数据子集
本次业务分析范围为 2019 年的订单数据，需剔除订单时间不在 2019/1/1 至 2019/12/31 之间的数、不符合逻辑的数据以及支付时间超过30min的数据。
- 筛选 orderTime 不在在 2019/1/1 至 2019/12/31 之间的数据，删除。
- 点击 orderTime 的筛选按钮，依次点击【日期筛选】【自定义筛选】
- 在自定义筛选方式中，第一个选择"在以下日起之后"，然后输入"2019/12/31 23:59"；第二个选择"在以下日起之前"，然后输入"2019/1/1 00:00"，条件选择"或"，然后点击确定。
- 可以看到 261 条数据被筛选出来。这些数据订单日起不在 2019 年，所以选中这些行，全部删除。
- 删除支付时间早于订单时间的数据
购物流程是先下单，再支付。支付时间早于订单时间的数据属于错误数据，不符合业务逻辑，需要删除。如何判断是否有这个错误？需要新建一列辅助列。
- 在 L2 单元格输入公式"=IF(J2<I2,'Y','N')"。
- 鼠标放至 L2 单元格右下角，当鼠标形状变成十字形时，双击，即可向下填充所有数据，并且每个数据都自动按公式计算。
- 筛选 L 列为'Y'的数据，即为支付时间早于下单时间的错误数据。
- 可以发现有 5 条数据，选中这 5 条，然后删除。
- 删除支付时间间隔大于 30min 的数据
一般平台为了确保商品不被占用，规定用户从下单到支付时间不得小于 30min，超过此时间支付的，属于异常数据，需删除。
- 新建一列辅助列，判断支付时间与下单时间间隔是否小于 30min.在 L2 单元格输入公式"=(J2-I2)*24*60"，由于 J 列和 I 列单元格是带时间的日期，两个日期相减的单位是天数，需要转换成分钟数，1 天 24 小时，1 小时 60min，所以两个日期差值要乘以 24 再乘以 60。双击 L2 单元格右下角下填充公式。
- 可以发现有 937 条数据属于支付时间超 30min 的数据。选中并删除这 937 行数据。
- 删除支付金额为负数的数据
- 支付金额应为正数，需删除金额为负数的数据。筛选F列小于0的数据。发现有6条数据。选中并删除这6条数据。
5、清洗数据
5.1 查看数据
鼠标选中各个字段所在的列。可以在底部状态栏查看数据计数，对于数值型数据，很能看到平均值、总和等。
5.2 清洗 orderID
- 删除 orderID 重复数据。选中orderID列，点击菜单栏【数据】【删除重复项】，选择【扩展选定区域】，点击【删除重复项】
5.3 清洗 goodsID
- PR000000 属于下架商品，需删除。筛选goodsID列为PR000000的数据，选中相关行，删除。
5.4 清洗 channelID
- 选中G列，发现数据计数比其他列少，说明有空值存在。对于空值，可选用众数填充。
- 新建一个sheet并命名为数据透视表。
- 查找channelID众数，选中channelID列，点击菜单栏【插入】【数据透视表】
- 放置数据透视表的位置选择现有工作表，点击向上箭头符号，选择【数据透视表】sheet,点击A1单元格，点击确定。
- 将chennelID字段拖放到行、值区域，自动生产一个透视表。
- 在数据透视表中B列任意单元格右键，点击【排序】【降序】
- 得到一个降序排列的表，第一个【渠道-0896】就是出现次数最多的众数。
- 回到data表中，选中chanelID列，按住ctrl + G，弹出定位框，点击【定位条件】。
- 选择【空值】，点击【确定】，这样就定位到chanelID为空值的所有单元格。
- 直接输入"渠道-0896"，然后按ctrl + Enter，这样所有的空值都会填充"渠道-0896"
5.5 清洗 platformtype
- 利用数据透视表来查看 platformtype 的类别数。发现有些相同的数值，由于空格原因被分位两类。
- 清除空格。选中H列，点击菜单栏【开始】【查找与替换】【替换】
- 查找内容输入快输入一个空格，替换为输入框不输入任何字符。然后点击【全部替换】
、
5.6 清洗 payment
- 新建计算折扣字段discount.在G2单元格输入公式：=F2/E2，然后向下填充。
- 选中G列，点击菜单栏【数据】【数据分析】，选择【描述统计】，点击确定。
- 查看折扣的描述统计平均值为1.11029，说明有折扣大于1的情况，这类数据属于错误数据，需进行修正。
- 这2004条数据对应的支付金额payment大于订单金额orderamount。我们使用正常数据（即折扣小于等于1）中的折扣均值乘以订单金额作为填充值。
- 筛选G列<1 的数据，然后查看Excel状态栏显示的平均值为0.92.
- 新建一列payment作为payment修正值。在G2单元格输入公式：=IF(H2>1.E2*0.92,F2)。公式表示如果折扣大于1，name支付金额等于订单金额乘以平均折扣0.92，折扣小于1则按原支付金额填充。
- 填充完成后，将带公式的数据粘贴为数值，然后删除原有的payment列。
6、分析数据
6.1 总体概览
销售 GMV：所有订单金额（含退款产生的订单金额）
成交总额：用户支付金额（含退款部分）
实际成交额：平台实际销售收入（不含退款支付金额）
订单数量：orderID去重后的数量
退货率：退货订单数量/总订单数量
用户数：userID去重后的数量
6.2 销售情况
- 创建订单月份字段，输入公式=month(J2),然后向下填充。
- 数据透视每月 GMV，然后复制粘贴为数值，并除以10000将单位转换为万元
- 数据透视每月实际付款，然后复制粘贴为数值，并除以10000将单位转换为万元
- 数据透视每月不含退单销售额，然后复制粘贴为数值，并除以10000将单位转换为万元
- 从每月销售折线图可以看出，2019年2月份全年销量最低，2月份正值春节，销售额可能受物流影响
- 2019年2、3、4月销量逐渐上升，5月份增长较快，6、7、8月份保持平稳，9、10月略有下降。说明下半年下销量比上半年好，在5月、11月可加大促销力度。
6.3 渠道分析
- 利用数据透视对渠道 ID、销量、销售额进行透视分析
- 渠道-0896产生的销量和销售额最高，需重点发展该渠道业务
- 各渠道销售额与各渠道销量呈线性关系
6.4 下单平台分析
- 用户下单主要通过APP和WechatMP,分别占50.14%和41.19%，通过WEB、WechatShop和Wap平台下单相对较少，由此可见用户主要通过移动端app和微信小程序进行下单购买商品。
6.5 用户行为
- 创建星期字段，输入公式=CHOOSE(WEEKDAY(J2,2),"周一","周二","周三","周四","周五","周六","周日"),然后向下填充。
- 利用数据透视分析周一至周日订单量变化，并绘制柱形图。
- 创建时间段字段，输入公式=hour(J2)，然后向下填充。
- 利用数据透视分析一天中24小时订单量变化，并绘制柱形图。
- 周日、周一、周二订单量相对较高、周三至周五订单量有下降，说明客户偏向于在周日、周一、周二购物
- 每天上午订单量主键增加，到中午13点达到最大，下午销量主键下降，到下班之后，销量又主键上升，在8点钟再次达到高峰，之后销量逐渐下降。说明用户偏向于总中午和晚上睡前购物，可在此时间进行业务推送。
6.6 复购率、回购率
- 客户复购率：同一时间段内（这里为一个月）购物超过两次的用户
- 利用数据透视表，行区域拖入month、orderTime、userID,值区域拖入userID，计算方式设为计数。
- 复制粘贴为数值，然后新建辅助列，用于统计是否产生复购。I列大于1则产生复购，否则没有复购。
- 加上辅助再次进行数据透视，得到每个月下单总人数和复购人数，新建一列计算复购率。
- 客户回购率：上一个月有购买的客户在本月也购买的比例
- 利用数据透视表，行区域拖入userID，列区域拖入月份，值区域拖入userID，计算方式设为计数。
- 复制粘贴一份，并对每一个月新建一列计算验证是否产生回购。计算是否产生回购的公式(以2月为例）：=IF(AND((AK6<>0),(AL6<>0)),1,0)。产生回购则标记为1，否则为0。
- 将每月购买人数和下月回购人数制成下标，计算每月回购率，并绘制折线图
- 复购率整体偏低，说明客户大多数为一次性购买客户
- 上半年回购客户呈上涨趋势，下半年回购客户基本保持稳定。
6.7 客户 RFM 模型
- RFM的含义：
- R（Recency）最近一次消费时间：表示用户最近一次消费距离现在的时间。消费时间越近的客户价值越大。1年前消费过的用户肯定没有1周前消费过的用户价值大。
- F（Frequency）消费频率：消费频率是指用户在统计周期内购买商品的次数，经常购买的用户也就是熟客，价值肯定比偶尔来一次的客户价值大。
- M（Monetary）消费金额：消费金额是指用户在统计周期内消费的总金额，体现了消费者为企业创利的多少，自然是消费越多的用户价值越大。
- 数据透视表生成每个用户最近一次消费时间、消费次数、消费金额。userID拖入行区域，payTime、userID、payment拖入值区域，分别设置计数方式为最大值、计数、求和。
- 复制粘贴为数值，新建一列为最近消费时间差，输入公式：=DATEDIF(G4,$J$2,"D")，计算客户最近一次消费距离2020年1月日的时间差。
- 选中H、I、J列，点击【数据分析】查看三列描述统计的均值
- 新建辅助列K L M列，对R、F、M进行均值比较，N列生产RFM识别码，O列匹配客户分类。
当客户价值分类完成，需要针对不同层级的客户实施不同的运营策略。
（1）重要价值客户：维持现状，重点维护
最近消费时间近，消费频次和消费金额都很高；重点维护对象，VIP大用户组织建设是必要的，权益专享、定期福利等；
（2）重要发展客户：提升频次，重点深耕
最近消费时间较近，消费金额高，但频次不高；属于忠诚度不高用户。着力让用户在平台上活跃，优化产品和服务，帮助用户提升频次。
（3）重要保持客户：用户回流，重点挽回
最近消费时间较远，但消费频次和金额都很高；说明这是个一段时间没来的忠实客户，定期的EDM、PUSH、短信，主动和用户保持联系和互动；
（4）重要挽留客户：提前预警，重点召回
最近消费时间较远，消费频次不高，消费金额高；属于重点的预流失用户，提前做好预流失预警和策略触达。
（5）一般价值用户：潜力用户，刺激复购
最近消费时间较近，消费频次高，就是消费金额低；属于重点潜力用户，可以发放大额卡券，引导此类用户不断增加投资。
（6）一般发展用户：多为新用户，挖掘需求
最近消费时间较近，消费频次低，消费金额也低；可能是新用户，最近投资过，需要客服回复工作加强，以及福利及时提醒。
（7）一般保持用户：流失召回
最近消费时间较远，消费频次高，消费金额低；属于流失用户，可能前期在普通很活跃，后期情感受挫，或是产品、服务、奖励力度达不到心里预期，需要做好利益与情感双重触达。
（8）一般挽留用户：可放弃治疗
最近消费时间，消费频次与消费金额都很低，此类用户流失已久，较难挽回，预算受限的情况下，可以放弃此类用户。
在线咨询
