使用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月之前就有购买记录，即为老会员。