淘宝数据分析

08/03 11:44
阅读数 17

淘宝数据分析

此次数据分析基于阿里云天池数据集(用户行为数据集),对常见电商分析指标,包括转化率,PV,UV,复购率等进行分析,分析过程中使用Python进行数据的清洗,清洗后的数据导入MySQL数据库,运用MySQL进行数据提取,使用Excel进行数据可视化。

一、数据集

在这里插入图片描述
可见收集的数据的各种类型。

二、提出问题

1.用户购物情况

PV(总访问量),UV(用户总数),日均访问量,跳失率,用户各种行为类型,复购率分别是多少。

2.用户的购物行为转化

用户购物各个环节的转化率转化为漏斗图。

3.各种行为类型用户的特征

购买率高和购买率低的人群以及只收藏加购不消费和消费次数最多的人群分别有什么特征,如何针对各类人群推送信息

4.不同时间段用户的活跃情况

三、数据清洗

解压后的数据有3.4G,这样基本就告别EXCEL了,我们将数据导入python进行清洗数据

#导入相关包
import pandas as pd
import numpy as np
import time
#导入数据
data_origin = pd.read_csv(r'F:/111/UserBehavior.csv')
#设置列名
data_origin.columns = ['User_Id','Item_Id','Category_Id','Behavior_type','Timestamp']
#观察数据
data_origin.head(10)
#观察缺失值
#data_origin.isnull().sum()
index_lose = data_origin[data_origin['Timestamp'].isnull()].index
data_origin.iloc[index_lose,:]
#可以看到,缺失时间戳的那一行Behavior_type也是异常,因此决定丢掉这一行
data = data_origin.dropna(axis = 0)
#此时Timestamp列为科学计数法显示,将其改成整型
data[:,'Timestamp']=data['Timestamp'].astype(int)
data.reset_index(drop=True,inplace=True)
#时间戳列转换为日期、时间数据。并把日期和时间分为两列
data.loc[:,'Timestamp']=data['Timestamp'].apply(lambda x:time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(x)))
data.loc[:,'Date']=data['Timestamp'].apply(lambda x:x.split(' ')[0])
data.loc[:,'Time']=data['Timestamp'].apply(lambda x:x.split(' ')[1])
#删除时间戳列
data=data.drop(columns='Timestamp',axis=1)
#我们只取日期为2017-11-25到2017-12-03之间的数据,查看一下这个时间段之外的具体情况
data[data['Date']<'2017-11-25'].shape
data[data['Date']<'2017-11-25']['Date'].value_counts()
data[data['Date']>'2017-12-03'].shape
data[data['Date']>'2017-12-03']
#时间区间外的日期数据剔除
data=data[(data['Date']>='2017-11-25')&(data['Date']<='2017-12-03')]
#查看重复值
data[data.duplicated()]
#删除重复值
data.drop_duplicates()
#重置索引
data.reset_index(drop=True,inplace=True)
#数据清洗完成,导出数据
data.to_csv(r'F:\taobao\UserBehavior_Done.csv',index=False)

接下来将数据导入sql

四、导入SQL

#建立数据库
create database taobao;
#建立表格
user taobao;
#创建表格
create table user (
 user_id int not null,
 item_id int not null,
 category_id int not null,
 behavetype varchar(10) not null,
 dates date not null,
 times time not null);
 #导入数据
LOAD DATA LOCAL INFILE 'F:/taobao/UserBehavior_Done.csv'
INTO TABLE USER 
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

五、分析问题

1、用户流量参数及漏斗模型

(1)流量指标

PV(总访问量)

用户访问页面的总数,用户每访问一个页面就算一个访问量,同一页面刷新多次也算一个访问量。

SELECT COUNT(behavetype) AS 每日访问量,dates
FROM USER 
WHERE behavetype = 'pv'
GROUP BY dates
ORDER BY dates

在这里插入图片描述
可以看出访问量在12月1日开始有了激增。

UV(访问用户总数)

即一台设备(电脑,手机)为一个独立的访问人数。一般以以天为单位来统计24小时的UV总数。一天内重复访问的只算一次。

SELECT COUNT(DISTINCT user_id) AS 总人数
FROM USER 
#总用户数 37376

跳失率

只有点击行为的用户/总用户数。

SELECT COUNT(user_id) AS 只有点击行为的用户 FROM(
SELECT user_id
FROM USER
GROUP BY user_id
HAVING COUNT(DISTINCT behavetype) = 1) a;

在这里插入图片描述
在统计时间的9天中,只有点击行为的用户数为2217,那么跳失率为2217/37376=5.93%,占比很少。

(2)用户行为漏斗模型

用户行为视观表

#观察用户行为
CREATE VIEW user_acts AS
SELECT user_id,COUNT(behavetype) AS 行为次数,
SUM(CASE WHEN behavetype = 'pv' THEN 1 ELSE 0 END) AS 点击次数,
SUM(CASE WHEN behavetype = 'fav' THEN 1 ELSE 0 END) AS 收藏次数,
SUM(CASE WHEN behavetype = 'cart' THEN 1 ELSE 0 END) AS 加购次数,
SUM(CASE WHEN behavetype = 'buy' THEN 1 ELSE 0 END) AS 购买次数
FROM USER
GROUP BY user_id
ORDER BY 行为次数 DESC;
#查看用户购物情况
SELECT * FROM user_acts;

部分数据如下所示
在这里插入图片描述

漏斗模型

SELECT
CONCAT(ROUND(SUM(点击次数)/SUM(点击次数)*100,2),'%') AS pv,
CONCAT(ROUND((SUM(收藏次数)+SUM(加购次数))/SUM(点击次数)*100,2),'%') AS pv_favCart,
CONCAT(ROUND(SUM(购买次数)/SUM(点击次数)*100,2),'%') AS pv_buy,
CONCAT(ROUND(SUM(购买次数)/(SUM(收藏次数)+SUM(加购次数))*100,2),'%') AS favCart_pv
FROM user_acts;

在这里插入图片描述
在这里插入图片描述

2、用户行为分析

(1)浏览及收藏次数最多的用户

SELECT user_id,收藏次数+加购次数 AS 收藏及加购次数
FROM user_acts
GROUP BY user_id
ORDER BY 收藏及加购次数 DESC;

部分数据如下所示
在这里插入图片描述此类用户可能购买能力不够强,可以针对对此类用户多投放优惠券,刺激他们的购买欲望。

(2)购买次数最多的用户

SELECT user_id,购买次数
FROM user_acts
GROUP BY user_id
ORDER BY 购买次数 DESC;

部分数据如下所示
在这里插入图片描述
此类用户购买能力以及购买欲望很强,可以多给此类用户推送类似商品,刺激用户连带性消费。

(3)用户复购率

产生两次或以上购买次数的顾客所占购买用户的比例

SELECT
SUM(CASE WHEN 购买次数>1 THEN 1 ELSE 0 END) AS 复购的用户,
SUM(CASE WHEN 购买次数>0 THEN 1 ELSE 0 END) AS 购买的用户总数,
CONCAT(ROUND(SUM(CASE WHEN 购买次数 > 1 THEN 1 ELSE 0 END)/SUM(CASE WHEN 购买次数 > 0 THEN 1 ELSE 0 END)*100,2),'%') AS 复购率
FROM user_acts

结果如下所示在这里插入图片描述

3、不同购买率用户的特征

(1)高购买率人群特征

SELECT user_id,点击次数,收藏次数,加购次数,购买次数,
SUM(购买次数)/SUM(点击次数) AS 购买率
FROM user_acts
WHERE 点击次数 > 0
GROUP BY user_id
ORDER BY 购买次数 DESC

部分数据如下图所示

(2)购买率低的人群特征

SELECT user_id,点击次数,收藏次数,加购次数,购买次数,
SUM(购买次数)/SUM(点击次数) AS 购买率
FROM user_acts
WHERE 点击次数 > 0
GROUP BY user_id
ORDER BY 购买次数 ASC

在这里插入图片描述
由以上结果可以看出,购买率高的人群有的购买率已经超过了1,这可能是因为客户以前将商品加入到购物车过,然后这次购买一起购买。而且,购买率高的人群加购率远远高于购买率低的人群,说明购买率高的人群有很强的购买目标。
低购买率人群大致分为两类,一种是点击商品后直接退出了,既无收藏也没有加购,对于这类人群,应该集中推送他可能感兴趣的商品。另一种就是有较多的收藏以及加购行为,可是没有最终转化成购买,对于这类人群,应该多推送类似打折的商品。

4、不同时间用户的活跃度

(1)一天用户活跃度

#给原表user加入新列hours
ALTER TABLE USER ADD hours CHAR(10) NULL;
UPDATE USER
SET hours =LEFT(times,2);
#用户活跃时间段视图
SELECT hours,COUNT(behavetype) AS 用户行为总量,
SUM(CASE WHEN behavetype = 'pv' THEN 1 ELSE 0 END) AS 点击次数,
SUM(CASE WHEN behavetype = 'fav' THEN 1 ELSE 0 END) AS 收藏次数,
SUM(CASE WHEN behavetype = 'cart' THEN 1 ELSE 0 END) AS 加购次数,
SUM(CASE WHEN behavetype = 'buy' THEN 1 ELSE 0 END) AS 购买次数
FROM USER 
GROUP BY hours
ORDER BY hours;

在这里插入图片描述

(2)一周用户活跃度

我们截取11月25号到12月1号的数据

CREATE VIEW weeks AS
SELECT DATE_FORMAT(dates,'%W')AS 星期,COUNT(behavetype) AS 用户行为总量,
SUM(CASE WHEN behavetype = 'pv' THEN 1 ELSE 0 END) AS 点击次数,
SUM(CASE WHEN behavetype = 'fav' THEN 1 ELSE 0 END) AS 收藏次数,
SUM(CASE WHEN behavetype = 'cart' THEN 1 ELSE 0 END) AS 加购次数,
SUM(CASE WHEN behavetype = 'buy' THEN 1 ELSE 0 END) AS 购买次数
FROM USER 
WHERE dates BETWEEN '2017-11-25' AND '2017-12-01'
GROUP BY 星期
ORDER BY 星期

SELECT * FROM weeks
ORDER BY 星期 

在这里插入图片描述
我们可以看出客户主要活跃时间在10点到23点,在20点到22点达到顶峰;工作日用户活跃度比较平稳,周末有明显提升。可以在用户活跃时间高的时间段集中推送相应优惠券以及优惠方案。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部