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 表名 
   

来源【首席数据官】，更多内容/合作请关注「辉声辉语」公众号，送10G营销资料！