大数据

Hi, 请登录

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

网站SEO诊断与整站SEO优化模板,拿去就可以用>>    SEO干货网站内部链接优化与整站优化>>   

2 项目目标

通过对淘宝用户行为数据分析,为客户提供更精准的反馈推荐

3 分析思路

根据分析目的,就以下四个维度进行分析,并提出建议:

二、数据说明与清洗操作 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、人均浏览次数、成交量

指标说明:

淘宝直通车在哪里分析数据_淘宝网数据分析_淘宝如何分析同行数据

运算语句如下

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 作图,操作小点如下:

①日点击量趋势

淘宝如何分析同行数据_淘宝直通车在哪里分析数据_淘宝网数据分析

② 日人均浏览次数趋势

淘宝如何分析同行数据_淘宝网数据分析_淘宝直通车在哪里分析数据

③ 日成交量趋势

淘宝网数据分析_淘宝如何分析同行数据_淘宝直通车在哪里分析数据

④ 日访客数趋势

淘宝直通车在哪里分析数据_淘宝网数据分析_淘宝如何分析同行数据

2整体用户行为次数

指标说明:每个用户的行为数据都由(用户行为)字段计数得到

create view 用户行为数据 AS
select User_ID,COUNT(Behavior_type) as '用户行为数',
      sum(case when Behavior_type ='pv' then 1 else 0 end ) as '点击',
      sum(case when Behavior_type ='fav' then 1 else 0 end) as '收藏',
      sum(case when Behavior_type ='cart' then 1 else 0 end) as '加购',
      sum(case when Behavior_type ='buy' then 1 else 0 end) as '购买'
from userbehavior
GROUP BY User_ID
ORDER BY 用户行为数 DESC;

语句运算结果如下

淘宝如何分析同行数据_淘宝网数据分析_淘宝直通车在哪里分析数据

3 总体用户的复购率和跳失率 ① 用户复购率

指标说明:用户复购情况通过(购买行为)字段中的“购买”行为进行细化分析。由上述语句,已将购买行为和用户id即单个用户一一对应,因此可以用(购买)字段直接测算

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)*100/ 
              sum(case when 购买>0 then 1 else 0 end), 2),
       '%') as 复购率
from 用户行为数据;

语句运算结果如下

淘宝如何分析同行数据_淘宝直通车在哪里分析数据_淘宝网数据分析

② 用户跳失率

指标说明:

跳失量 语句如下

注:“as a” 命名引用表的别名,这一步是一定要加的,每个引用表都要有别名,否则会报错

select COUNT(*) 
from (
       SELECT User_ID from userbehavior
       GROUP BY User_ID 
       HAVING COUNT(Behavior_type)=1)as a;

运算结果如下

淘宝直通车在哪里分析数据_淘宝网数据分析_淘宝如何分析同行数据

分析说明:没有只拜访了一次的用户,故跳失率为0。 说明该购物网页是足够吸引用户的,可以进一步培养用户忠诚度,鼓励用户进行更高频次的消费。

四、用户行为分析 1 用户行为转化及流失分析 ① 流量行为转化漏斗分析

操作说明:将创建的视图“用户行为数据”导出,建立行为转化漏斗模型如下

淘宝直通车在哪里分析数据_淘宝网数据分析_淘宝如何分析同行数据

分析说明:

② 用户行为转化漏斗分析

操作说明:将各个行为的用户计数,语句如下

select sum(case when 点击>0 then 1 else 0 end)as 点击用户数,
       sum(case when 收藏>0 then 1 else 0 end) as 收藏用户数,
       sum(case when 加购>0 then 1 else 0 end) as 加购用户数,
       sum(case when 购买>0 then 1 else 0 end) as 购买用户数
from 用户行为数据;

运算结果如下

淘宝网数据分析_淘宝如何分析同行数据_淘宝直通车在哪里分析数据

将数据导出,作为用户转化漏斗模型如下

淘宝直通车在哪里分析数据_淘宝网数据分析_淘宝如何分析同行数据

分析说明:

问题推进:

为什么收藏用户数远小于加购的用户数?

推测是因为加入购物车和收藏商品并没有行为的先后性,即购物可以有以下多条行为路径到达:

点击——购买点击——加购——购买点击——收藏——购买点击——收藏——加购——购买

由以上4个路径,作对应的4个漏斗模型,分别对比不同路径下的用户流失情况,语句如下

注:以下语句都需外联结(点击用户数),如直接在语句加上(点击用户数),则点击用户数量会被where过滤,得到的不是(点击用户数)的总和

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);0

注:用power bi可不用反复导入4次数据,点击power bi 的开始 - 输入数据,即可直接手动输入,后续可以 复制表 →开始- 输入数据 →粘贴 →调整表的数据

由上述语句分别运行,所得4个漏斗模型如下:

淘宝如何分析同行数据_淘宝直通车在哪里分析数据_淘宝网数据分析

分析说明:

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);1

语句运算结果如下

淘宝如何分析同行数据_淘宝直通车在哪里分析数据_淘宝网数据分析

作图如下

淘宝直通车在哪里分析数据_淘宝网数据分析_淘宝如何分析同行数据

分析说明:

五、用户类目偏好分析

统计所有商品的购买次数,同时找到购买次数、浏览次数、收藏次数和加入购物车次数最多的商品

1 商品销售情况分析

需统计商品的销售情况,语句如下

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);2

注:mysql的视图不支持子查询,会出现报错 View’s SELECTcontainsa subquery in the FROM clause

所以需要先将子查询写为一个视图并别名,再引用该别名,语句如下:

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);3

语句结果输出如下

淘宝网数据分析_淘宝如何分析同行数据_淘宝直通车在哪里分析数据

再统计商品销售整体情况,语句如下

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

结果输出如下

淘宝直通车在哪里分析数据_淘宝网数据分析_淘宝如何分析同行数据

将商品销售情况数据导出,作图如下

淘宝直通车在哪里分析数据_淘宝网数据分析_淘宝如何分析同行数据

分析说明:

2商品品类top20分析 ① 商品品类浏览top20

语句如下:

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);5

作图如下

淘宝网数据分析_淘宝直通车在哪里分析数据_淘宝如何分析同行数据

分析说明:

来源【大数据】,更多内容/合作请关注「运营见识」公众号,赠文案训练手册电子书。

版权声明:本文内容来源互联网整理,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 jkhui22@126.com举报,一经查实,本站将立刻删除。

相关推荐

评论 1

  • 昵称 (必填)
  • 邮箱
  • 网址
  1. #1

    东西太多不知道从哪里入手

    金华亚马逊 2022-07-13 04:51:48 回复
二维码
评论