项目背景：
整理记录UP主冷凡社长付费课程的第一个实战作品
数据来源：某北美商业分析专业的课程项目数据
使用工具：Excel
分析流程：
1. 确定分析目标
对销售数据进行分析，帮助提高利润收入。同时对货品选择备货等进行分析，尝试提出优化实际业务的方案。
2. 查看数据
源数据分为订单数据、产品数据、消费者数据与退货数据。
2.1 验证数据准确性
利用数据透视表查看数据，对数据去重处理，查看数据缺失情况
2.2 数据合并
使用vlookup函数或者power query合并数据
2.3 数据清洗
对数据格式转换，替换错误值
3. 数据探索
了解整体销售及利润情况；
从数据中发现可分析的维度：产品的类目及子类、销售地区、折扣、发货方式及运送时间、退货情况等。
4. 数据分析
4.1 货物分析
i. 分析整体销售与利润情况，得到销售趋势
ii. 分析产品大类销售与利润，找到利润较低的大类
iii. 进一步找到亏损的产品子类
4.1.1 整体销售及利润情况
整体销售及利润情况.png
商城整体销售额、利润基本都在逐渐增长。
4.1.2. 货物分析
4.1.2.1 产品大类销售及利润占比
产品大类销售占比.png
产品大类利润占比.png
Furniture类目每年平均销售占比达32.6％，但是利润平均占比却只占有6.5％，且呈现逐年下降的趋势。说明 Furniture 类目并非商城核心利润类目且呈现逐年下降的情况，需要进一步判断Furniture类目的情况。
4.1.2.2 产品子类每年的利润分布
产品子类每年的利润分布.png
对所有子类按利润排序，发现了在Furniture类目下的Tables和Bookcases几乎每年都在亏损，尤其tables子类累计亏损已达16667美元。
同时也在Office Supplies类目下发现Supplies也在逐年亏损，且亏损规模在扩大。
另外Technology下的Machines出现不稳定因素，可能需要调整销售策略。
初步判断应该停止tables、bookcases、supplies的进货，在处理完库存后，关闭该销售品类。
在确认该判断前，需要查看子类的销量情况，因为有些品类可能存在薄利多销或是引流款的情况。
4.1.2.3 各品类的销售占比情况
各品类的销售占比情况.png
发现tables加上bookcases在Furniture中占比仅占26%。Supplies在Office Supplies中占比仅为3%；Machines在Technology内占比为6%。
确认调整意见判断应该停止tables, bookcases, supplies的进货，在处理完库存后，关闭该销售品类。
4.2 地区分析
i. 按地区展示利润情况，找到亏损的地区
ii. 探究各地区折扣情况，探寻亏损原因，发现亏损地区折扣很大，需要调整运营策略和折扣情况
iii. 为排除引流产品，再按照品类查看折扣率与利润的关系
4.2.1 对销售地区的分析
各地区销售与利润情况
在去除三个子类后，查看发现有几个州销售额虽然不错，但却出现大量亏损的情况。由于地区无法像品类一样，可以简单地停止销售，所以需要具体查看是什么原因到导致了亏损。
利润=销售定价*（1-折扣）-成本，其中只有销售折扣会变动，故先探究地区和折扣的关系。
4.2.2 利润、折扣和地区的关系
各地区的折扣情况
由上图可以看到，利润为负的州折扣非常集中，且折扣价格都超过了20%。明显可见，超过了20%的折扣导致了近10个州的利润为负。
初步判断，对于个别州的运营策略需要调整，要调整商品折扣情况。
对折扣的建议：
将亏损的州设置为一个组，可以看到亏损组的平均折扣率为33.6%，而其他州的平均折扣率为4.2%。由于在售的商品的利润空间在34%，所以如果想达到其他组的利润率，可以将折扣设置在5%左右。
考虑到折扣会影响到销量，可以适当将折扣设置在15%左右，并根据后面的实际销售情况不断测试数据。
分组后的折扣与利润率.png
同时发现大部分州基本没有做折扣活动，由于折扣能会带来更多的销售量，所以可以考虑在未开展活动的州，尝试一些折扣活动，并监控销量及利润的情况，测试得出最优折扣率。
4.2.3 按品类查看折扣率和利润的关系
折扣与利润标靶图（按品类）
Average of profit_rate是每个品类对应的利润空间，计算方式为利润=（定价-成本）/定价，其中定价指折扣之前的售价。
Average of discount是每个品类目前实际的折扣率。可以看到目前实际折扣率大于利润空间的有tables and bookcases，这两个品类必然是会亏损的。
同样supplies也是亏损的，但是其实际折扣率仍在利润空间范围内。
针对实际折扣率已经在利润空间之外的品类，如果不考虑停掉其售卖，则需要将实际折扣率至少调整至利润空间以内，即tables调整到20%以内、bookcases调整至19%以内。同样，考虑到上述实际商品的影响，保险起见，折扣率调整在10%左右，可以保证利润。
4.3 用户分析
使用RFM模型，对用户进行打分，分析用户群的分布情况，调整营销方向。
4.3.1 RFM模型计算
RFM模型是一种用户价值模型，利用客户已有的购买记录，进行数字化的分析，并对用户进行分群。核心作用可以帮助企业减少营销成本，增加回报。
利用RFM三个维度对客户的价值进行判断并辨识出用户的类型，这有助于开展营销活动。其中R是最近消费日期距今天数、F是客户累计消费次数、M是客户累计消费金额。
用户定义.png
计算方法：
-
建立透视表，利用透视表的分组求和功能，得到每一个用户去重后的订单数、订单金额、最近一次消费时间
透视表.png
- 复制透视表结果，用公式计算得出Recency（="2018-1-1"-max of order date）数据是17年的，指定分析时间为2018-1-1。
- 分别得到标准化后的RFM分数
R-z-score=STANDARDIZE(J2,AVERAGE(J:J),STDEV.P(J:J))，F-z-score=STANDARDIZE(G2,AVERAGE(G:G),STDEV.P(G:G))，M-z-score=STANDARDIZE(H2,AVERAGE(H:H),STDEV.P(H:H)) - 判断RFM最终得分（以平均值为标准使用二分法，＞平均值为1，否则为0）
R=IF(K2>AVERAGE($K$2:$K$794),0,1)（R的逻辑跟F和M相反，最后一次消费的时间间隔小则评分高）F=STANDARDIZE(G2,AVERAGE($G$2:$G$794),STDEV.P($G$2:$G$794))，M=IF(M2>AVERAGE($M$2:$M$794),1,0) - 将RFM分数合并，将用户分群
Customer Segment=SWITCH(TRUE,Q2="111","Champions",Q2="101","Potential Loyalist",Q2="011","Can’t Lose Them",Q2="001","At Risk",Q2="110","Promising",Q2="100","Recent Customers",Q2="010","Hibernating",Q2="000","Lost")
最终结果.png
4.3.2 RFM模型分析与应用
用户分布柱状图.png
用户分布树形图.png
通过图表可以看到，有三个重点人群占比很大，分别是Recent Customers、Champions和Lost。
需要对重要的用户群进行不同的策略引导：
1.对于Champions、Hibernating 用户，我们可以保持现状不变。
2.对于Recent Customers我们要做好新用户引导。
3.对于Promising用户需要增加刺激力度，引导用户消费高客单价的产品。
4.对于Potential Loyalist用户，需要增加站内活动，引导用户进行多次消费
5.对于Can't Lose Them用户，需要尽一切可能将其召回。同样At Risk用户也需要挽留。
6.放弃Lost用户，或者在营销成本不高的情况下进行召回测试。
4.4 发货分析
分析运输方式与货物占比，优化运输周期
运输方式.png
常规运输方式的送达天数达5天，发货量却占比高达60%。物流速度严重影响用户体验，从长远角度考虑，公司应该不断优化自己的运输周期，以便更好的留存用户。
4.5 退货分析
分析退货率异常的地区
每年的退货率.png
查看了四年的退货率，逐年略有增长。
接着对数据进行探索，查看了退货率和产品、和顾客类型，发货方式等都没有明显差异。
在分析地理位置时发现，在不同的地区退货率有明显差异。
不同地区的退货率.png
西部地区平均退货率高达3.77％，高于其他地区退货率的四倍。建议对西部物流等影响物品运输的因素进行筛查，如果是因为物流供应商的问题，需尽快更换服务商。
5. 结论
1、 应该停止tables、bookcases、supplies的进货，在处理完库存后，关闭该销售品类。操作后预计2018年利润较不关闭额外增长8.9％。如果不停掉，可以将tables和bookcases的折扣率调整在10%左右。
利润预测（保持原先策略）.png
利润预测（停掉三个子品类）.png
2、 个别州的运营策略需要调整，调整商品折扣情况。可以将折扣设置在5%左右。
考虑到折扣会影响到销量，可以适当将折扣设置在15%以上，并根据后面的实际销售情况不断测试数据。
3、需要对重要的用户群进行不同的策略引导：
对于Champions、Hibernating 用户，我们可以保持现状不变；
对于Recent Customers我们要做好新用户引导；
对于Promising用户需要增加刺激力度，引导用户消费高客单价的产品；
对于Potential Loyalist用户，需要增加站内活动，引导用户进行多次消费；
对于Can't Lose Them用户，需要尽一切可能将其召回。同样At Risk用户也需要挽留；
放弃Lost用户，或者在营销成本不高的情况下进行召回测试。
4、西部地区平均退货率高达3.77％，高于其他地区退货率的四倍。建议对西部物流等影响物品运输的因素进行筛查，如果是因为物流供应商的问题，需尽快更换服务商。
网友评论