Excel数据分析实例

问题背景

bi某教育机构遇到一个问题：近期课程销售量上升，但是计算出来的毛利额在下降。

如何分析并解决？

确认问题并拆解指标

确认毛利额数据异常问题

1. 确认数据源问题——数据缺失、口径异常

2. 确认是否是外因所致——用户消费能力下降or行业不景气

3. 产品问题

   • 销量上——低毛利率商品销量上升；高毛利率商品销量下降
   • 售价上——价格是否下降？是否打折？
   • 成本上——内容创作成本激增

4. 渠道问题——渠道成本激增、渠道质量下降、活动ROI

提升毛利率方案

1. 提升销量

   1. 营销优化

      • 增加优质渠道投放
      • 优化文案质量
      • 优化营销活动，避免亏本拉新
      • 增加体验课、公开课
      • 精细化用户运营：对用户分层，采用不同的营销方案，提高用户的小课复购率和大课转化

   2. 产品优化

      • 优化用户体验
      • 优化学习流程，增加用户停留时间

   3. 长远之计——内容优化形成口碑，提高完课率，满意度，就业率

2. 减少成本——优化渠道

整体框架

一、通过逻辑树拆解方法，逐步确认各渠道的问题

定位问题原因为：八月免费渠道做促销活动，导致部分课程亏本销售，导致部分盈利额下降

解决：优化渠道投放比例，提高课程成交量

1. 通过综合排名分析法，将渠道排名。找出转化率高、成本低、流量大的渠道
2. 与营销部门确认方案可行性
3. 不同渠道预算配比做ABtest
4. 7日为周期评估实验结果

具体操作

问题拆解——数据涨跌异动分析

绘制今年各月销售量与毛利额的折线图

结论：发现8月份销售额上升，毛利率下降。要继续看毛利率与毛利率环比查看是否是正常波动

结论：8月毛利率出现异常，确认为异常波动

渠道类型较多，先找大渠道，再找细分渠道：绘制销售量和利润率的矩阵关联图

结论：8月中的免费渠道是高销售额，但毛利率低

继续找免费渠道内的详细渠道，继续矩阵关联分析

结论：免费渠道下的站内广告位销售额较高，但是毛利率极低； EDM短信的销售额极高且毛利率较低。

继续查看7、8月份的毛利率对比图

结论：发现站内广告、EDM短信、0元体验课都导致8月的毛利率降低。

查询到：站内广告位渠道在8月17日做了促销活动，当日出现20单毛利额为负的订单。导致该渠道8月毛利率为负值。

继续查看7、8月份的销售额的对比图

结论：发现站内广告位、社群（站内）、外呼都使销售额上升

继续考察免费渠道下，不同渠道对销售额涨幅的贡献率

结论：确认站内广告这一渠道对销售额贡献达100%。说明8月份销售额上升，利润率下降主要是由免费渠道中的站内广告销售额上升，毛利额下降导致的。

该渠道在8 月17日的招生出现了毛利额为负的情况；而由于该活动促销的是本月一门新开大课，课程按本月成本分摊较高，所以出现了毛利额为负的情况，拉低了整体的毛利额。

综合分析渠道，进行渠道排名

综合分析法 ：将人脑模糊逻辑转化为计算机0/1逻辑思维，得出量化结果。

具体操作方法

1. 纵坐标上的指标与横坐标上的指标进行对比
2. 专家投票
3. 纵坐标比横坐标重要记为1，否则为0
4. 每行结果相加为综合得分（若有重要指标综合得分为0，可全体+1）
5. 通过综合得分求出权重

1. 对渠道四个指标通过优化矩阵进行权重划分，确定各指标的权重值

2. 对渠道各指标进行0-1标准化，去除量纲影响
3. 利用标准化后的指标与原指标进行成绩求和，得出综合得分
4. 利用综合得分进行排名

结论：先有渠道最好的为站内广告位。但是由于投放广告涉及很多因素，如广告复投问题、文案更新问题、转化周期长短问题。

后续的改进策略还需要进一步与营销部门商议。

二、优化产品用户体验，提升留电率、进群率、转化率

解决：

1. 方案——通过漏斗分析，找到需要提升的转化环节是留电环节，之后通过用户调研进一步找到结果，最终提升留电环节转化率22.72pp，提升整体转化率1.52pp。

2. 通过现有漏斗转化数据评估改进后的漏斗能够带来的销量提升。

3. 做ABtest：客服团队响应的实验和产品改进的实验。

4. 7日周期评估实验结果。

5. 结案报告并推广。

具体操作

漏斗分析，看各环节转化情况

漏斗分析：反映用户行为状态，反应从起点到终点各阶段用户转化率情况。

从总体最终转化来看低于行业转化水平3%-5%，并且咨询到预留电话环节转化明显较低，考虑是从 客服响应时间或话术方面进行优化。

结论：从人工客服响应时长分析，用户平均响应时长为187秒，约为3分钟。事后通过用户访谈，发现人工等待时间较长的问题也出现在较多反馈问题的区域中(该问题排名为30个中的第四个)。

还需要ABtest得出结论，可能需要改进的方面有：

1. 落地页功能：

   • 检验落地页打开是否正常；留电弹窗是否正常。

2. 落地页样式：

   • 通过问卷调查调查落地页样式的满意度；

   • 设计不同的落地页样式来实验，找到能够提高转化率的落地页样式。

3. 落地页页面布局：

   • 同上，调查布局的满意度；

   • 并设计不同的布局来实验。

4. 课程介绍方式：

   • 问卷调查课程介绍的满意度；

   • 通过不同的关键词组合和不同的内容组合方式来优化课程介绍方式；

   • 通过实验验证不同组合的优化效果。

5. 客服弹窗体验：

   • 问卷调查弹窗满意度；

   • 通过实验验证主动弹窗和用户点击弹窗的转化提升情况；

   • 设计不同的弹窗方式，验证不同的优化效果。

6. 机器人客服体验：

   • 问卷调查机器人满意度；

   • 优化机器人问答算法；

   • 通过实验对比优化前后的转化率情况。

7. 资料下载：

   • 问卷调查资料下载的满意度；

   • 按用户反馈改进资料下载。

8. 留电页面体验：

   • 同上。

三、通过精细化运营，增加用户小课转化量

1. 方案——通过RFM模型对小课用户进行分层，最终转化小课两万单

2. 通过不同类用户转化量评估完成目标份额

3. 随机分层抽样：通过对不同的RMF层级用户抽样，实验设计最有效的转化营销方案

4. 7日为周期评估实验结果

5. 报告结案

具体操作

用户价值分析，RFM建模

RFM客户价值模型：R(recency)黏性，F(frequancy)忠诚度，M(monetary)消费能力。

原始字段和客户分类标准如下：

构建出R、F、M列，并将其与均值比较得出RS、FS、MS列，将三列&起来得到辅助列，与客户分类表的辅助列关联起来得到客户类型。

得到客户各类型人数表格和饼状图如下：：

四、后续计划

1. 为学院设计精细化运营方案，利用推荐算法设计出客户的兴趣领域。

   • 重要价值客户——直接推荐大课购买的方案来吸引客户转化

   • 重要保持客户——小课促销来吸引客户复购，之后再逐步推荐大课来促进用户转化

   • 重要发展客户——可以用大课送会员的方式来吸引客户转化

   • 重要挽留客户——小课促销方案来吸引客户复购，之后再逐步用大课送会员方式吸引客户转化

   • 一般价值客户——大课限时促销方案吸引客户转化

   • 一般发展客户——小课转大课可优惠来吸引用户转化

   • 一般保持客户——小课促销来吸引客户复购，之后逐步推荐大课来促进转化

   • 一般挽留客户——先做用户召回，逐步开始用小课促销方案吸引用户复购

2. 对不同层级学员做运营方案实验

五、最终结论

1. 八月的免费渠道下的站内广告位虽然出现亏损活动，但从1-8月的数据来看，站内广告位仍然是优质渠道（在综合分析的渠道排名中居于第四），后需要重点关注。

   • 通过分析发现8月销售额上升，而毛利额异常下降是由于站内广告位做打折促销引发的。由于折价设置失误导致出现账面亏损，从而引发毛利额下降问题。

   • 该问题需反馈营销团队，后续设计价格预警系统，如有类似价格设置失误情况，会及时预警。

   • 通过综合分析，对各渠道的ROI、报名人数转化率、跳出率、线索量做综合指数，计算出19年8月的最优渠道为站内广告位渠道。后续需与营销团队依据各渠道特点进一步商议渠道改进方案。

2. 留电环节为重点优化环节，优化后对整体转化率有1.52pp的提升。

   • 通过漏斗分析，发现用户转化率环节中，留电环节转化率较低。

   • 与客服，产品团队商议后制定改进方案。经过一个月的改进，留电环节率从22.57%提升至44.16%，整体转化率有1.52pp的提高。大课转化人数较8月增加330人，按每人人均1600的毛利额计算，预计能提升52.8万的毛利额。按当月浏览人数16034人计算，预计报名人数有586人，预计改进能完成93.76万元的毛利额。

3. 经过用户精细化运营推广模型后，已转化大课501人，小课3377人。按照小课人均毛利额17元计算，小课预计能完成5.7万毛利额任务。