电商数据分析excel表格「电商财务数据分析报表怎么做」

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 年，所以选中这些行，全部删除。

点击 A 列查看数据，还剩 102497 行。

删除支付时间早于订单时间的数据

购物流程是先下单，再支付。支付时间早于订单时间的数据属于错误数据，不符合业务逻辑，需要删除。如何判断是否有这个错误？需要新建一列辅助列。

在 L2 单元格输入公式"=IF(J2<I2,'Y','N')"。

鼠标放至 L2 单元格右下角，当鼠标形状变成十字形时，双击，即可向下填充所有数据，并且每个数据都自动按公式计算。

筛选 L 列为'Y'的数据，即为支付时间早于下单时间的错误数据。

可以发现有 5 条数据，选中这 5 条，然后删除。

点击 A 列查看数据，还剩 102495 行。

删除支付时间间隔大于 30min 的数据

一般平台为了确保商品不被占用，规定用户从下单到支付时间不得小于 30min，超过此时间支付的，属于异常数据，需删除。

新建一列辅助列，判断支付时间与下单时间间隔是否小于 30min.在 L2 单元格输入公式"=(J2-I2)*24*60"，由于 J 列和 I 列单元格是带时间的日期，两个日期相减的单位是天数，需要转换成分钟数，1 天 24 小时，1 小时 60min，所以两个日期差值要乘以 24 再乘以 60。双击 L2 单元格右下角下填充公式。

筛选 L 列大于 30 的数据。

可以发现有 937 条数据属于支付时间超 30min 的数据。选中并删除这 937 行数据。

删除支付金额为负数的数据支付金额应为正数，需删除金额为负数的数据。筛选F列小于0的数据。发现有6条数据。选中并删除这6条数据。

选中A列，查看数总数，剩余103349条数据。

5、清洗数据5.1 查看数据

鼠标选中各个字段所在的列。可以在底部状态栏查看数据计数，对于数值型数据，很能看到平均值、总和等。

5.2 清洗 orderID删除 orderID 重复数据。选中orderID列，点击菜单栏【数据】【删除重复项】，选择【扩展选定区域】，点击【删除重复项】

取消全选，然后选中orderID，点击确定。

执行完成后，提示如下：

5.3 清洗 goodsIDPR000000 属于下架商品，需删除。筛选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列，点击菜单栏【开始】【查找与替换】【替换】

查找内容输入快输入一个空格，替换为输入框不输入任何字符。然后点击【全部替换】

替换完成。提示116630处替换。

再次用数据透视表查看类别数，观察已无问题。5.6 清洗 payment新建计算折扣字段discount.在G2单元格输入公式：=F2/E2，然后向下填充。选中G列，点击菜单栏【数据】【数据分析】，选择【描述统计】，点击确定。

查看折扣的描述统计平均值为1.11029，说明有折扣大于1的情况，这类数据属于错误数据，需进行修正。

筛选G列大于1的数据，发现有2004条数据。

这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列，点击【数据分析】查看三列描述统计的均值

建立RFM客户类别识别码

新建辅助列K L M列，对R、F、M进行均值比较，N列生产RFM识别码，O列匹配客户分类。

数据透视生成客户类型与销量、销售额的表格

绘制RFM 销量/销售额图表

分析结论

当客户价值分类完成，需要针对不同层级的客户实施不同的运营策略。

（1）重要价值客户：维持现状，重点维护

最近消费时间近，消费频次和消费金额都很高；重点维护对象，VIP大用户组织建设是必要的，权益专享、定期福利等；

（2）重要发展客户：提升频次，重点深耕

最近消费时间较近，消费金额高，但频次不高；属于忠诚度不高用户。着力让用户在平台上活跃，优化产品和服务，帮助用户提升频次。

（3）重要保持客户：用户回流，重点挽回

最近消费时间较远，但消费频次和金额都很高；说明这是个一段时间没来的忠实客户，定期的EDM、PUSH、短信，主动和用户保持联系和互动；

（4）重要挽留客户：提前预警，重点召回

最近消费时间较远，消费频次不高，消费金额高；属于重点的预流失用户，提前做好预流失预警和策略触达。

（5）一般价值用户：潜力用户，刺激复购

最近消费时间较近，消费频次高，就是消费金额低；属于重点潜力用户，可以发放大额卡券，引导此类用户不断增加投资。

（6）一般发展用户：多为新用户，挖掘需求

最近消费时间较近，消费频次低，消费金额也低；可能是新用户，最近投资过，需要客服回复工作加强，以及福利及时提醒。

（7）一般保持用户：流失召回

最近消费时间较远，消费频次高，消费金额低；属于流失用户，可能前期在普通很活跃，后期情感受挫，或是产品、服务、奖励力度达不到心里预期，需要做好利益与情感双重触达。

（8）一般挽留用户：可放弃治疗

最近消费时间，消费频次与消费金额都很低，此类用户流失已久，较难挽回，预算受限的情况下，可以放弃此类用户。