数据分析项目：CDNOW用户消费数据分析（基于MySQL实现）

最新推荐文章于 2022-08-04 16:35:14 发布

LdyLLLLLLLD

最新推荐文章于 2022-08-04 16:35:14 发布

阅读量1.4k

点赞数 5

分类专栏： SQL编程文章标签：数据分析 mysql CDNow数据集

本文链接：https://blog.csdn.net/LdyLly/article/details/119426427

版权

本报告通过MySQL对CDNOW用户消费数据进行深入分析，揭示了1997年至1998年间该平台的销售趋势、产品价格分布以及用户消费行为。数据清洗过程中处理了空格、数据类型、缺失值和重复记录。分析结果显示，销售额和用户数在1997年一季度达到高峰，后续趋于平稳。用户消费金额集中在低档，大部分用户消费1次后流失，但存在稳定消费群体。产品价格主要集中在10-15美元，1998年3月高价CD销售额显著提升。用户消费行为分析指出，新用户主要在1997年一季度加入，复购率稳定在20%左右，用户生命周期分布不均，营销策略应针对不同用户群体制定。

摘要由CSDN通过智能技术生成

项目背景

CDNow曾经是一家在线音乐零售平台，后被德国波泰尔斯曼娱乐集团公司出资收购，其资产总价值在最辉煌时曾超过10亿美元。

分析目的及思路

本次报告主要利用MYSQL和EXCEL对CDNOW网站用户的消费数据进行分析，了解该网站营业情况。
具体分析从产品分析和用户分析展开。

数据处理

数据导入

数据来源：
和鲸社区：https://www.heywhale.com/mw/project/5e67586ef278cf002d533f89/dataset
数据展示：
1、CDNow曾经是一家在线音乐零售平台，数据集为CDNow网站的用户在1997年1月1日至1998年6月30日期间内购买CD交易明细；
2、文件大小1.85M，数据格式TXT，原始数据共69659条记录；
3、数据集一共有用户ID，购买日期，订单数，订单金额4个字段，文件中无字段名；我理解的这里的订单数指的是该订单含有的CD张数；金额的单位是美元。
在这里插入图片描述
Navicat for MySQL导入数据

数据清洗

1、字段空格处理

用trim函数去除每一字段值中左右两边空格

update cdnow
set user_id =trim(user_id),
    purchase_date=trim(purchase_date),
    order_cdnum=trim(order_cdnum),
    order_amount=trim(order_amount);

2、数据类型转换

在设计表选项中修改数据类型，确保金额数据显示正常，并避免后续数据分析出现错误
在这里插入图片描述

3、缺失数据处理

select count(*) from cdnow;
#69659
select count(user_id),count(purchase_date),count(order_cdnum),count(order_amount) from cdnow;
#69659

表中不存在null值，无需处理

4、数据重复处理

看到别人的链接中没有处理重复数值，有的链接表示，重复记录是因为同一个用户一天下单两次。我对这个项目数据的理解是，每一记录都是已完成的订单，不存在取消订单等情况；其中订单数order_cdnum就是中用户购买的CD张数，4个字段都一样的记录是重复记录，所以在这里做了重复数据处理这一步。

#第一步，找是否有所有字段都重复的重复记录，确实存在重复记录
select count(user_id),count(purchase_date),count(order_cdnum),count(order_amount) from cdnow;
#69659
select count(distinct user_id,purchase_date,order_cdnum,order_amount) from cdnow;
#69404

#第二步，加上主键id列，辅助重复值的删除
ALTER table cdnow add column id int(14) primary key auto_increment first;

#第三步，删除重复记录
with t as (
select distinct c1.* FROM cdnow c1 join cdnow c2 
on c1.user_id=c2.user_id and c1.purchase_date=c2.purchase_date and c1.order_cdnum=c2.order_cdnum and c1.order_amount=c2.order_amount
and c1.id<> c2.id )

delete from cdnow 
where 
id in (select id from t)
and
id not in (select min(id) as id_min from t group by t.user_id,t.purchase_date,t.order_cdnum,t.order_amount);

#第四步，用第一步语句检查，并去掉添加的主键id列，这里是考虑到不同引擎可能会导致重新启动时候id列不连续，所以先去掉
alter table cdnow drop column id;

5、数据0值

观察到金额数据存在 0值。

select * from cdnow where order_amount=0 or order_cdnum=0;#80
select * from cdnow where order_amount=0 and order_cdnum=0; #null
select * from cdnow where order_amount=0;#80，存在80条有订单数但无订单金额的记录
select * from cdnow where order_cdnum=0;#=' '和=0是等价的，null

存在80条订单金额为0的数据，但是订单数存在。这些数据可能是优惠活动赠送、限免、优惠券抵扣的订单，或者是数据缺失。无法判断数值意义，先不删除，当作正常数据。

数据预处理后得到69404条数据。

数据分析

产品销售分析

数据集的时间跨度为1997年1月1日至1998年6月30日，可以做季度或月度为单位的销售分析。
在这里插入图片描述

以上4个图表说明：
（1）1997年的第一季度，所有指标的数据较高，从1997年4月开始便处于平稳状态，至1998年6月，数据维持在一定水平上下波动；
（2）一般，我们以销售额作为电商零售业务的核心指标。观察CD销售额，发现在1997年3月，1997年7月，1997年11月，1998年3月，销售额相较于相邻前后两月数据，均出现小幅提升。

通过4个图表相互对比发现：
（1）每个月的CD销量和CD销售额变动趋势完全一致；
（2）1997年3月，环比上月用户数下降，但订单数、销量和销售额均提升，说明该月用户有多次购买的行为；
（3）1997年7月，环比上月用户数下降，订单数下降，但销量和销售额均有提升，说明该月订单中有较大额度的订单；
（4）1997年11月和1998年3月，环比上月销量和销售额提升的同时，用户数和订单数都在提升；
（5）后续分析用户价值时可以着重考虑引起1997年3月和1997年7月销量和销售额增长的用户行为规律。

产品价格分析

浏览部分数据，发现每张CD的单价大多在10元左右，于是分析网站的产品价格，对网站的产品价格档次进行探索。

1、所有订单CD均价

先考虑所有订单的平均单张CD价格（order_amount/order_cdnum），分布整体右偏，均价在10元到15元之间的产品最多，98.5%的产品分布在0元至40元区间内。大于40元的CD销量在图上几乎看不到。
在这里插入图片描述
为了避免均值处理的对实际订单情况的统计误差，下面只对仅1张CD的订单，即单品订单进行分析。

2、单品订单CD价格

对于order_cdnum=1的所有订单（31221条，占总体45.0%），各个区间的频数由以下条形图展现，可以看出该网站的CD价格分布整体右偏，单价在10元到15元之间的产品最多，97.3%的产品分布在0元至40元区间内。由于CD数为1的订单数量较大，CD数为1的订单与整体订单结论类似。大于40元的CD销量在图上几乎看不到。
在这里插入图片描述
初次了解情况时将价格细分较细，也可以将分组间隔取大。

select sum(case when a.cd_price>=0  and a.cd_price <5  then 1 end) as num_5,
	   sum(case when a.cd_price>=5  and a.cd_price <10 then 1 end) as num_10,
	   sum(case when a.cd_price>=10 and a.cd_price <15 then 1 end) as num_15,
	   sum(case when a.cd_price>=15 and a.cd_price <20 then 1 end) as num_20,
	   sum(case when a.cd_price>=20 and a.cd_price <25 then 1 end) as num_25,
	   sum(case when a.cd_price>=25 and a.cd_price <30 then 1 end) as num_30,
	   sum(case when a.cd_price>=30 and a.cd_price <35 then 1 end) as num_35,
	   sum(case when a.cd_price>=35 and a.cd_price <40 then 1 end) as num_40,
	   sum(case when a.cd_price>=40 and a.cd_price <45 then 1 end) as num_45,
	   sum(case when a.cd_price>=45 and a.cd_price <50 then 1 end) as num_50,
	   sum(case when a.cd_price>=50 and a.cd_price <55 then 1 end) as num_55,
	   sum(case when a.cd_price>=55 and a.cd_price <60 then 1 end) as num_60,
	   sum(case when a.cd_price>=60 and a.cd_price <65 then 1 end) as num_65,
	   sum(case when a.cd_price>=65 and a.cd_price <70 then 1 end) as num_70,
	   sum(case when a.cd_price>=70 and a.cd_price <75 then 1 end) as num_75,
	   sum(case when a.cd_price>=75 and a.cd_price <80 then 1 end) as num_80,
	   sum(case when a.cd_price>=80 and a.cd_price <85 then 1