使用Excel对国外B2B电商平台进行描述性数据分析

1. 原始数据&分析目的

原始数据

这是一家国外的B2B电商平台在2010年12月1日-2011年12月9日期间的交易流水数据，包括8个字段订单编号、商品编码、商品名称、下单数量、下单时间、商品单价、客户编号、客户国籍，共54万行数据。示例如下：

点此获取数据集

分析目的

a 制作日报，汇总主要运营指标数值。

b 制作周报，汇总主要运营指标数值。

c 制作月报，汇总主要运营指标数值。比较新、老会员的价值。计算会员复购率、会员回购率。

d 制作年报，汇总主要运营指标数值。找出重点商品，重点客户，重点国家。

数据处理、分析使用的工具：Excel。

2. 数据准备

1.清除原数据集中的脏数据行，包括客户编号字段下的空数据行和商品单价字段下的0值数据行。

2.把商品名称字段下的“亚马逊费用、银行手续费、手工费用、快递费用”数据行筛选出来，剪切到其他工作表留备它用。

3.通过int函数和weekday函数增加两个字段下单日期和星期，便于做各类报表时选取特定日期数据。

4.经过数据处理，得到包括10个字段订单编号、商品编码、商品名称、下单数量、下单时间、下单日期、星期、商品单价、客户编号、客户国籍，共39万6341行数据。示例如下：

3. 数据建模得出日报和周报中运营指标

如下思维导图拆解了平台的总销售额构成，可以确定各类报表中通用的运营指标：总销售额、总销售量、成交会员数、客单价、件单价、连带率，以及订单总数、订单均价、平均购买次数。

为快速计算出这些运营指标，我在Excel中开发了计算模板，在实线区域放入对应日期的商品下单数量、商品单价英镑、顾客编号、订单编号四个字段值，就可以得到以上9个运营指标。下图以2010/12/5的日数据为例，展示数据建模界面：

下面是2010/12/5的日报：

日报数据	货币为英镑
日期	2010/12/5
星期	7
总销售额	31315.64
总销售量	16370
成交会员数	75
客单价	417.54
件单价	1.91
连带率	218.27
订单总数	87
订单均价	359.95
平均购买次数	1.16

通过向以上计算模板带入2010/12/5-2010/12/10的周数据，可以得到相应指标数值。

下面是2010/12/5-2010/12/10的周报：

周报数据	货币为英镑
日期	2010/12/5-2010/12/10（12/11是周六休息日）
总销售额	225517.2
总销售量	106998
成交会员数	427
客单价	528.14
件单价	2.11
连带率	250.58
订单总数	531
订单均价	424.70
平均购买次数	1.24

4. 月报&比较新、老会员的价值

在月报中，除了整个月的总体运营指标外，我分别把新会员和老会员的数据分开统计，目的是比较新会员和老会员的价值。通过向模板带入2011年7月的数据，可以得到相应指标数值。

下面是2011年7月的月报：

2011年7月	总体情况	老会员指标数值	新会员指标数值	新会员各项指标占老会员的比重
总销售额	592731.90	520183.77	72548.13	13.95%
总销售量	369114.00	320090.00	49024.00	15.32%
成交会员数	946.00	759.00	187.00	24.64%
客单价	626.57	685.35	387.96	56.61%
件单价	1.61	1.63	1.48	91.06%
连带率	390.18	421.73	262.16	62.16%
订单总数	1321.00	1110.00	211.00	19.01%
订单均价	448.70	468.63	343.83	73.37%
平均购买次数	1.40	1.46	1.13	77.15%
成交商品种类	2348.00	2348.00	1638.00	69.76%

由2011年7月月报中的新会员各项指标占老会员的比重数据绘图如下，可以得出结论：老会员的价值全面大过新会员！老会员的总销售额是新会员的7.17倍， 维护好老会员是非常重要的工作！

认识到老会员的重要价值后，我继续挖掘本月的会员运营指标，包括注册会员数、活跃会员数、活跃会员率、会员复购率、会员回购率、新增会员数、新增会员率。

建立如下模型，带入本月相关数据可以得出注册会员数、活跃会员数、活跃会员率、新增会员数、新增会员率、会员回购率。

上面第一张图中，F列使用嵌套公式=IF(ISERROR(VLOOKUP(E2,A:A,1,FALSE)),"新会员","老会员")可以在当月消费过的所有会员中区分出新会员和老会员，再筛选出所有老会员的编号。

接下来，使用这些老会员的编号通过Excel的高级筛选功能，在2011年7月的所有交易流水中，筛出这些老会员的交易流水，取出订单编号、顾客编号两个字段用于计算会员复购率。如下图：

使用数据透视表得出每位老会员的消费次数，用countif函数找出消费一次以上的老会员数，用count函数统计本月有消费的老会员数，二者相除即可得出会员复购率。

下面是2011年7月的会员指标：

注册会员数	2958
活跃会员数	1988
活跃会员率	67.21%
会员复购率	26.88%
会员回购率	31.94%
新增会员数	187
新增会员率	6.32%

5. 年报&找出重点商品、客户、国家

下面是2010年12月-2011年11月的年报：

总销售额	8247836.223
总销售量	4870891
成交会员数	4293
客单价	1921.23
件单价	1.69
连带率	1134.61
成交商品种类	3652
订单总数	17626
订单均价	467.94
平均购买次数	4.11

(计算方法同日、周、月报)

下面利用二八法则找出重点商品、客户、国家

以重点国家计算方法为例：

建立如上图的模型，把全年交易流水的商品编码、商品下单数量、商品单价、金额、顾客编号、顾客国籍6个字段数据带入，通过数据透视表可以得到每个国家的交易金额，排序后取前20%位次的国家，计算这些国家的总交易金额占所有国家交易金额的比重，比值为96.27%，可得结论：排名前20%的国家是年度重点国家。

同样的方法，可以计算出年度重点商品和重点客户。相关比重值汇总如下（重点商品和会员数量较多，具体编号不在此列出）：

销售额前20%商品的总销售额占所有商品总销售额比重	78.05%
销售量前20%商品的总销量占所有商品总销量比重	76.11%
购买金额前20%会员的总购买金额占所有会员销售额比重	73.95%
前20%国家总交易金额占所有国家交易金额比重	96.27%

这些重点商品、重点会员和重点国家都是工作中的重点运营对象，他们对全年销售目标的达成具备重要意义。

6. 总结与反思

总结

本次分析主要是描述性统计分析，包括以下内容：

1.通过日报、周报、月报、年报汇总统计了不同时间段内平台的销售运营指标。

2.发现了老会员的价值远大于新会员，统计了会员相关指标。

3.找出了年度的重点商品、重点会员和重点国家。

在得出以上内容过程中，主要运用了以下Excel功能：

数据透视表

数据建模方法

高级筛选功能

柱形图

vlookup，countif，int，weekday等函数

反思

1.这个数据集只有1年的交易流水数据，在分析过程中，无法采用与去年同比的维度。这样也导致，无法准确把握销售规律，具体体现为无法评估今年的某个波动是正常波动，还是异常波动。

2.如果做出全年每天的日报和每个月的月报，可以发现更多销售规律。