使用Excel分析B2B电商数据
1.原始数据的获取与处理
1）在kaggle上获取国外一家电商2010年12月-2011年12月的交易数据，如下，分别包括订单编号（InvoiceNo）、商品编码(Stockcode)、商品名称(description)、下单数量(quantity)、下单日期(InvoiceDate)、商品单价(Unitprice)、客户编号(coustomerID)和客户国籍(country)共8个字段。数据集链接https://www.kaggle.com/puneetbhaya/online-retail
2）对下载好的原始数据在Excel中打开，删除costomer ID的空行、unitprice值位0的行和quantity为负值的行。
3）增加单元格date和weekday列，设置日期列单元格格式为长日期，星期列用weekday函数从date列获取星期，设置单元格格式为常规,双击填充柄快速填充
2.数据分析
1）开发如下数据分析模版，计算总销售额、总销售量、顾客数、订单数、连带率、平均购买次数、件单价、客单价和订单均价。计算方法如下：
- 使用SUMPRODUCT(B2:B2724,C2:C2724)先乘再求和，计算总销售额；
- 计算连带率=总销售量/顾客数，反映每个顾客的平均单次消费产品件数；
- 使用删除重复项的功能统计customer列的顾客数和订单列的订单数；
- 计算每个顾客的平均购买金额即客单价=总销售额/顾客数；
- 计算平均购买次数=订单数/顾客数；订单均价=销售额/订单数；件单价=销售额/销售量。
2）代入2011数据分别得到日报，周报、2011年7月月报、2011年年报。其中，区分新会员和老会员的方法：IF(ISERROR(VLOOKUP(E2,Sheet6!A:A,1,FALSE)),"新会员","老会员")，sheet6表格中的A列是使用高级筛选功能筛选日期列2011年7月1日之前的customerID数据，即2011年7月当月的顾客ID若在7月之前就有购买记录，即为老会员。
| 日报数据 | 项目（价格为英镑） |
| 日期 | 2010/12/5 |
| 星期 | 7 |
| 总销售额 | 31380.6 |
| 总销售量 | 16393 |
| 成交会员数 | 75 |
| 客单价 | 418.41 |
| 件单价 | 1.90 |
| 连带率 | 218.57 |
| 订单总数 | 93 |
| 订单均价 | 337.43 |
| 平均购买次数 | 1.24 |
| 周报数据 | 项目（价格为英镑） |
| 日期 | 2010/12/5-2010/12/10 |
| 总销售额 | 222203.55 |
| 总销售量 | 105406 |
| 件单价 | 2.11 |
| 成交会员数 | 467 |
| 连带率 | 225.71 |
| 客单价 | 475.81 |
| 订单数 | 627 |
| 平均购买次数 | 1.34 |
| 订单均价 | 354.39 |
| 2011年7月 | 总体情况 | 老会员指标数值 | 老会员各项指标占比 | 新会员指标数值 | 新会员各项指标占比 | 新会员各项指标占老会员比重 |
| 总销售额 | 574238.48 | 508355.61 | 88.53% | 65882.87 | 11.47% | 12.96% |
| 总销售量 | 363406 | 314561 | 86.56% | 48845 | 13.44% | 15.53% |
| 件单价 | 1.58 | 1.62 | 102.27% | 1.35 | 85.36% | 83.46% |
| 成交会员数 | 993 | 802 | 80.77% | 191 | 19.23% | 23.82% |
| 连带率 | 365.97 | 392.22 | 107.17% | 255.73 | 69.88% | 65.20% |
| 客单价 | 578.29 | 633.86 | 109.61% | 344.94 | 59.65% | 54.42% |
| 订单数 | 1593 | 1358 | 85.25% | 235 | 14.75% | 17.30% |
| 平均购买次数 | 1.60 | 1.69 | 105.55% | 1.23 | 76.70% | 72.66% |
| 订单均价 | 360.48 | 374.34 | 103.85% | 280.35 | 77.77% | 74.89% |
| 成交商品种类 | 2370 | 2370 | 100% | 2370 | 100% | 100% |
| 年报 | 项目（价格为英镑） |
| 日期 | 2011年1月-2011年12月 |
| 总销售额 | 7745461.794 |
| 总销售量 | 4596657 |
| 件单价 | 1.69 |
| 成交会员数 | 4242 |
| 连带率 | 1083.61 |
| 客单价 | 1825.90 |
| 订单数 | 20477 |
| 平均购买次数 | 4.83 |
| 订单均价 | 378.25 |
3）根据2011年7月的数据分析7月新老会员的各项指标占比情况，由条形图和柱形图可见老会员带来的销售额、订单量等价值远远超过新会员。
4）使用数据透视表计算每位老会员的消费次数，用countif函数（COUNTIF(B2:B803,">1"）找出7月消费超过1次的老会员，count计算总消费人数，计算出会员的复购率为96.01%。
5）使用数据透视表计算销售额前20%的重点国家、重点商品和重点会员。
| 销售额前20%商品的总销售额占所有商品总销售额比重 | 78.62% |
| 销售量前20%商品的总销量占所有商品总销量比重 | 94.15% |
| 购买金额前20%会员的总购买金额占所有会员销售额比重 | 74.59% |
| 前20%国家总交易金额占所有国家交易金额比重 | 96.01% |
前20%的国家即销售额最高的前8个国家如下：
| Country | 求和项:price |
| United Kingdom | 6767873.394 |
| Netherlands | 284661.54 |
| EIRE | 250285.22 |
| Germany | 221698.21 |
| France | 196712.84 |
| Australia | 137077.27 |
| Switzerland | 55739.4 |
其他的重点商品和会员数量较多，此处不列举。
3.总结
本次数据使用Excel分析的主要内容有：
- 制作销售运营指标的日报、周报、月报和年报汇总统计表。
- 区分7月份新会员和老会员的购买情况，发现老会员对销售额的贡献远大于新会员，由此可知维护老顾客不流失至关重要。
- 利用二八法则找出了年度重点商品、重点会员和重点国家。
以上分析使用的Excel功能如下：
- 单元格格式设置；
- 数据透视表；
- 高级筛选功能；
- 条形图、柱形图的绘制；
- weekday、IF、ISERROR、vlookup、SUMPRODUCT、countif，count等函数。
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！