使用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等函数。