电商网站案例分析

二七官

本文是对某电商网站的销售数据进行分析，该电子商务销售数据集包括3个csv文件-订单列表，订单详细信息，销售目标，主要任务是用Mysql处理数据并且进行数据分析。

分析结果展示：销售报表

一、分析背景

本数据集包含了2018年4月1日-2019年3月31日之间网站订单的详细情况，数据集中的字段含义为：

网站销售的产品包括三种品类，分别是家具品类，服饰品类，电器品类。其中家具品类的子类别包括装饰品，书桌，椅子，书架。腐蚀品类的子类别包括衬衫，裙子，披肩，T恤，长裤。电器类的子类别包括零部件，电子游戏机，手机，打印机。

二、分析思路

项目目标：通过现有的数据监控商品销售情况，获取各地区商品销售趋势，地域分布情况，为公司销售提供建议，增加收益。

分析方法：

三、数据清洗

1.缺失值处理

-- 订单列表
SELECT * FROM `list of orders`
WHERE `order id` IS NULL 
OR `order date` IS NULL  OR `customerName` IS NULL OR `state` IS NULL OR `city` IS NULL

-- 订单细节
SELECT * FROM `order details`
WHERE `order id` IS NULL 
OR `amount` IS NULL OR `profit` IS NULL OR `quantity` IS NULL OR `category` IS NULL OR `sub-category` IS NULL

-- 销售目标
SELECT * FROM `sales target`
WHERE `month of order date` IS NULL 
OR `category` IS NULL  OR `target` IS NULL

2.判断是否为空字符串

-- 订单列表
SELECT * FROM `list of orders`
WHERE `order id`='' 
OR `order date`='' OR `customerName` = '' OR `state` = '' OR city = ''

-- 订单细节
SELECT * FROM `order details`
WHERE `order id`='' 
OR `amount`='' OR `profit` = '' OR `quantity` = '' OR `category` = '' OR `sub-category` = ''

-- 销售目标
SELECT * FROM `sales target`
WHERE `month of order date`='' 
OR `category`='' OR `target` = ''

四、数据分析

查看数据库中的表信息，主要可以将表中数据分成两类，一类是维度，一类是事实，两种表通过订单ID连接。

维度：表示对分析主题属性的描述。如表中的地理位置维度，包含城市，州的名称；产品维度则包含产品名称，产品类别的描述，信息较为固定，维度也被称为空间。

事实：表示对分析主题的度量。如网络销售是事实的一种，包含了客户姓名，下单的时间，销售额，下单量，事实与维度两表通过join相连，事实中罗列的数据通常是数值类型，通常随客户数量的增多表规模也不断增长。

1.从时间与空间角度分析

a) 统计不同城市下单的人数

SELECT city , COUNT(`customerName`) AS num FROM `list of orders`
GROUP BY `city`
ORDER BY num DESC

如图，Indore地区下单人数全国最多，Chennai地区下单人数最少，接下来将地区与利润之间结合来看。

b) 统计不同地区给网站带来的利润差

SELECT city , SUM(profit) AS total_pro 
FROM `list of orders` AS l1
INNER JOIN `order details` AS o2
ON l1.`orderId` = o2.`orderId`
GROUP BY city
ORDER BY total_pro DESC

结合不同城市下单人数和利润差来看，下单人数最多的Indore(共76人下单)获得的总利润仅次于Pune(共22人下单)，Pune作为最高价值回报城市以及Indore作为最多购买人次城市应当重点维护。

c) 统计不同产品的获利情况

SELECT category , SUM(profit)  FROM `order details`
GROUP BY category

如图可知，服装品类是收益最多的品类，可以考虑在服装品类上增加投入，增加各类品牌日营销活动，进一步拓宽市场，增加竞争力。

已知各品类总利润，接下来分析各品类下子类目的盈利情况：

SELECT `sub-category` , category , SUM(profit) AS total_pro  
FROM `order details`
GROUP BY `sub-category`  
ORDER BY  total_pro DESC

子类目中电器类中打印机收益最高，服装品类中收益最高的品类是裤子，应增加相关产品的投放量，保证其供应。

d) 不同产品销售情况

SELECT l1.`orderId` , `customerName` , SUM(quantity) AS total_quantity   
FROM `list of orders` AS l1
INNER JOIN `order details` AS o2
ON l1.`orderId` = o2.`orderId`
GROUP BY o2.`orderId`
ORDER BY total_quantity DESC

订单号为B-26055的用户购买量最大，共计57件，下面查看其购买的日期和具体品类购买量。

SELECT l1.`orderId`, l1.`orderDate` , quantity , `category` 
FROM `list of orders` AS l1
INNER JOIN `order details` AS o2
ON l1.`orderId` = o2.`orderId`
WHERE l1.`orderId` = 'B-26055'

该客户下单量57件，为潜力客户，应当通过营销等手段提高其成为忠诚客户可能性。

2.用户对比分析

a) 不同用户贡献价值

SELECT l1.`orderId` , l1.`customerName` , o2.`amount` , o2.`profit` , o2.`quantity` , o2.`category` , o2.`sub-category`
FROM `list of orders` AS l1
INNER JOIN `order details` AS o2
ON l1.`orderId` = o2.`orderId`
GROUP BY l1.`orderId`
ORDER BY o2.profit DESC
LIMIT 1

重要价值客户Surabhi (贡献利润最多) 。

SELECT l1.`orderId` , l1.`customerName` , o2.`amount` , o2.`profit` , o2.`quantity` , o2.`category` , o2.`sub-category`
FROM `list of orders` AS l1
INNER JOIN `order details` AS o2
ON l1.`orderId` = o2.`orderId`
GROUP BY l1.`orderId`
ORDER BY o2.quantity DESC
LIMIT 1

服装品类潜在重要价值客户Jitesh (购买数量最多) 应当重点关注。

b) 用户消费频次

SELECT `customerName` , COUNT(1) AS counts 
FROM `list of orders`
GROUP BY `customerName`
HAVING counts > 3
ORDER BY counts DESC

结果显示：在该年度内消费大于3次的有10人，在有过去年份的销售情况时应当与曾经的销售情况进行对比，此处缺少之前的数据，但仍应当提高忠诚客户的数量。

SELECT `customerName` , COUNT(1) AS counts 
FROM `list of orders`
GROUP BY `customerName`
HAVING counts = 3

消费次数为3次的人数为40人，该批次客户可转化为忠诚客户。

SELECT `customerName` , COUNT(1) AS counts , `orderDate`
FROM `list of orders`
GROUP BY `customerName`
HAVING counts =1

消费次数为1次的有229人，该批次用户应该防止流失。并且按照二八法则来看，高忠诚度客户个数较低，该网站需要从各方面提升每个环节的转化率，提高高价值客户的数量。

3.从目标角度分析

a) 按照品类拆分目标值

家具类

SELECT  `category` , SUM(target) FROM `sales target`
WHERE `category` = 'Furniture'

结果显示：家具品类的目标收益是132900，下面来看实际收益。

SELECT l1.`orderId` , l1.`orderDate` , o2.amount , o2.`category` 
FROM `list of orders` AS l1
INNER JOIN `order details` AS o2
ON l1.`orderId` = o2.`orderId`
WHERE `category` = 'Furniture' 
ORDER BY `orderDate` 
-----------------------------------------------
SELECT SUM(amount) FROM `order details`
WHERE `category` = 'Furniture'

结果显示，家具品类距离目标销售额还差5719 。

服饰类

SELECT `category` , SUM(target) FROM `sales target`
WHERE `category` = 'Clothing'

SELECT `category` , SUM(target) FROM `sales target`
WHERE `category` = 'Clothing'

结果显示，服饰品类销售目标为截止2019年3月31日达到174000，实际销售额为139054，距离目标销售额还差34946。

电器类

SELECT  `category` , SUM(target) FROM `sales target`
WHERE `category` = 'Electronics'

SELECT `category` , SUM(amount) FROM `order details`
WHERE `category` = 'Electronics'

电器类销售目标为129000，实际销售额为165267，超预期36267，完成目标。

五、制作报表

目的：将数据导入Power BI，建立各表之间的关联，制作网站销售报表。

报表展示，报表分为三页，主页，时间趋势图，区域分布图。

从品类分析：电器品类销售额最高，利润不及销售额第二的服饰品类，且电器品类完成了目标销售额。服饰类、家具类没有完成目标销售额，因此基于服饰利润高的基础上，可以继续进行城市活动推广，扩大服饰生产效能，持续提高服饰行业吸金能力。电器品类完成了销售目标，利润在三类品类中排名第二，在此基础上保持即可。家具类目利润少，需要在生产供应链计算其成本，保证达成目标销售值的同时确保能够盈利不至于亏损。

从区域分析：客户数高于50的仅有两个区，其余区均低于50，需要在确保产品质量的同时思考是否是区域喜好，区域经济能力还是竞争对手等大环境的影响造成客户数过低的现象。销售应遵循因地制宜的方法，按照不同区域采取不同销售策略，同时跟随国家大方针及时变更销售策略，保证市场敏感度和警觉性。

编辑于 2020-07-23 18:02

数据清洗

数据分析