电商数据分析excel表格「电商财务数据分析报表怎么做」
今天给大家普及一下电商数据分析excel表格「电商财务数据分析报表怎么做」相关知识，最近很多在问电商数据分析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）一般挽留用户：可放弃治疗
最近消费时间，消费频次与消费金额都很低，此类用户流失已久，较难挽回，预算受限的情况下，可以放弃此类用户。
