MYSQL数据分析项目 - 淘宝用户行为分析

用户行为间的转化情况：分析用户从浏览到购买整个过程中，常见的电商分析指标，确定各个环节的流失率，提出改善转化率的意见；
用户的行为习惯：以PV、UV等作为指标，找出用户最活跃的日期及每日活跃时间段；
用户类目偏好：从商品的点击、收藏、加购、购买的频率，探索用户对商品的偏好，找到不同商品的针对性营销策略（e.g.什么产品以及产品类目的购买率最高，找出最受欢迎的产品，优化产品推荐；
用户价值：找出价值高的核心用户群体，并根据不同的用户等级划分，作针对性的购买偏好推送，及个性化的产品销售方案。

二、数据说明与清洗操作

1 取样说明

由于数据源过大，用excel无法直接打开，所以将其导入Navicat for MySQL，在Navicat中进行数据处理。为减少运算时间，本次分析选取100000行的数据进行抽样分析。

导入Navicat时的相关设置如下：

2 设置新列名

由于本次数据没有列名，第一行也是数据，因此需要修改列名。操作如下：

右键表 - 设计表 → 修改字段名 → 保存

列名含义分别为：用户ID、商品ID、商品目录ID、行为类型、时间节点

3 数据去重

查询重复值的语句如下

select * from userbehavior
GROUP BY user_id,item_id,category_id,behavior_type,timestamp
HAVING count(*)>1;

查询结果如下，由于本次没有重复值，故不需要去重

另，假如字段1与字段2中有重复值，可用如下去重语句：

DELETE FROM 表名 
WHERE 字段1,字段2
IN (SELECT 字段1,字段2 FROM ( SELECT 字段1,字段2 FROM 表名 
                             GROUP BY 字段1,字段2
                             HAVING COUNT(*)>1)          AS  s1) 
AND
id NOT IN (SELECT id FROM ( SELECT id FROM 表名 
                            GROUP BY 字段1,字段2
                            HAVING COUNT(*)>1)           AS   s2);

4 检查是否有缺失的数据

对每个字段计数，如计数值一样，则无缺失值，语句如下：

select  count(user_id),count(item_id),count(category_id),count(behavior_type),count(timestamp)
from userbehavior;

查询结果如下，各字段计数都为99999，故每行数据无空值：

5 字段一致化处理

① timestamp字段转化

timestamp列无法直接进行分析，故将其转为三列：时间，日期，小时

另：原语句 ALTER TABLE userbehavior add COLUMN Date_time TIMESTAMP(0) NULL;
如一直报错，可删掉“ (0) NULL ”

-- 添加新列Date_time，根据Timestamp返回日期时间
ALTER TABLE userbehavior add COLUMN Date_time TIMESTAMP;
UPDATE userbehavior
set Date_time = FROM_UNIXTIME(`Timestamp`);

-- 添加新列Date，根据Timestamp列返回日期
ALTER TABLE userbehavior ADD COLUMN Date char(10) NULL;
UPDATE userbehavior
SET Date = FROM_UNIXTIME(`Timestamp`,'%y-%m-%d');

-- 添加新列time，根据Timestamp列返回时间
ALTER TABLE userbehavior ADD COLUMN Time char(10) null;
update userbehavior
set Time = SUBSTRING(Date_time FROM 12 FOR 2);

一致化处理后，查询结果如下：

② 导入的数据集大小预览

输入以下去重计数语句 DISTINCT

SELECT count(DISTINCT User_ID) as 用户数,
count(DISTINCT Item_ID) as 商品数量,
count(DISTINCT Category_ID) as 商品类目数量,
count(Behavior_type) as 行为数量
FROM userbehavior;

得到的运算结果如下

三、用户总体分析

以下内容皆先用Navicat运算得出 → 导出为xlsx文件 → 再导入POWER BI作图进行分析

1 总体UV、PV、人均浏览次数、成交量

指标说明：

behavior_type属用户行为类型字段，记录了用户对应的pv、buy、cart、fav，即浏览、购买、加入购物车、收藏这四个用户行为
uv即独立访客，可通过user_id字段获取对应数据，pv和成交量通过behavior_type字段获取数据，人均浏览次数通过对behavior_type字段进行运算获得。

运算语句如下

select COUNT(DISTINCT User_ID) AS '独立访客数',
      sum( case when Behavior_type='pv' then 1 else 0 END) as '点击数',
      sum( case when Behavior_type='pv' then 1 else 0 END)/COUNT(DISTINCT User_ID) as '人均浏览次数',
      sum(case when Behavior_type ='buy' then 1 else 0 END)as '成交量'
from userbehavior;

运算结果如下

作对应的趋势图，后续默认皆通过POWER BI 作图，操作小点如下：

从mysql导出上述数据另存为xlsx，导出后导入power bi
另：如数据无法导入power bi，可以看有无下载access，若无可下载access（如2010版×64

① 日点击量趋势

最低0.47元/天解锁文章

Venassa佳

关注

0
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
MYSQL数据分析项目 - 淘宝用户行为分析

一、项目简要二、数据说明三、用户总体分析1 总体UV、PV、人均浏览次数、成交量2整体用户行为次数3 总体用户的复购率和跳失率四、用户行为分析：1 用户行为转化及流失分析2用户行为习惯分析五、用户类目偏好分析：1 商品销售情况分析 2商品品类top20分析六、用RFM模型进行用户价值分析：1 时间间隔指标 R 2 购买频率指标 F七、报告结论...
复制链接

扫一扫