使用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.90520183.7772548.1313.95%
总销售量369114.00320090.0049024.0015.32%
成交会员数946.00759.00187.0024.64%
客单价626.57685.35387.9656.61%
件单价1.611.631.4891.06%
连带率390.18421.73262.1662.16%
订单总数1321.001110.00211.0019.01%
订单均价448.70468.63343.8373.37%
平均购买次数1.401.461.1377.15%
成交商品种类2348.002348.001638.0069.76%

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

月报新老会员价值对比

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

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

会员指标分析1

会员指标分析2

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

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

会员指标分析3

使用数据透视表得出每位老会员的消费次数,用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.如果做出全年每天的日报和每个月的月报,可以发现更多销售规律。

  • 1
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值