用户行为分析（SQL+Tableau+AARRR模型）

编号14816

# 一、数据来源及含义

1、数据来源

阿里云天池： https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1 UserBehavior.csv

原始数据有100150806条，数据量过大，不方便使用Mysql进行操作，本例先使用python对数据进行处理，选取其中的999999条数据用于本次用户行为分析。

2、字段含义

user_id：用户id item_id：商品Id category_id：商品类别id behavior_type：用户行为类型 timestamp：时间戳

二、数据清洗

1、添加辅助字段

① 时间戳转日期型

ALTER TABLE userbehavior_random ADD COLUMN datetime TIMESTAMP(0) NULL; 
UPDATE userbehavior_random SET datetime = FROM_UNIXTIME(timestamp);

② 提取年月日

ALTER TABLE userbehavior_random ADD COLUMN dates CHAR(10) NULL;
UPDATE userbehavior_random SET dates =SUBSTR(datetime,1,10);

③ 提取时分秒

ALTER TABLE userbehavior_random ADD COLUMN hours CHAR(10) NULL;
UPDATE userbehavior_random SET hours = SUBSTR(datetime,12,8);

④ 提取小时（Tableau中绘图时使用）

ALTER TABLE userbehavior_random ADD COLUMN time CHAR(10) NULL;
UPDATE userbehavior_random SET time = SUBSTR(datetime,12,2);

进行上述处理后结果如下：

2、异常值

① 查看时间的最大最小值

SELECT MAX(timestamp),
       MIN(timestamp),
       MAX(datetime),
       MIN(datetime)
FROM userbehavior_random;

② 删除异常值本次分析针对2017年11月25日至2017年12月3日数据，对异常值进行删除，共删除508行。

DELETE FROM userbehavior_random
WHERE dates < '2017-11-25' OR dates > '2017-12-03';

③查看数据集

SELECT * FROM userbehavior_random

目前还剩999491条数据

3、重复值

SELECT DISTINCT * FROM userbehavior_random

显示有999491条数据，无重复值。

4、缺失值

SELECT COUNT(user_id),COUNT(item_id),COUNT(category_id),COUNT(behavior_type),COUNT(timestamp)
FROM userbehavior_random

均有999491条数据，无缺失值。

三、模型构建

1、用户流量分析

① 日活跃用户（DAU）人数分布

2017年11月25日-2017年12月1日活跃人数较为平稳，但也有增长趋势，12月1日-12月3日增长相对较迅速，活跃用户人数约增加 26%。

② PV、UV、PV/UV

SELECT COUNT(DISTINCT user_id)AS 'UV', 
(SELECT COUNT(*) FROM userbehavior_random
WHERE behavior_type='pv')AS 'PV',
(SELECT COUNT(*) FROM userbehavior_random
WHERE behavior_type = 'pv')/(COUNT(DISTINCT user_id) ) AS 'PV/UV'
FROM userbehavior_random;

UV：独立访客数

PV：页面流量次数

PV/UV：平均一个独立访问者所浏览的页面访问量

③ PV、UV每日变化趋势

SELECT dates,
SUM(CASE WHEN behavior_type='pv' THEN 1 ELSE 0 END)AS '日均访问量',
COUNT(DISTINCT user_id)AS '日均UV用户'
FROM userbehavior_random
GROUP BY dates
ORDER BY dates;

将上述查询所得表格导入Tableau中，绘制PV、UV每日变化趋势图。发现在2017年11月25日-2017年12月3日期间，PV与UV随日期的变化趋势相似，11月25日-12月1日较平稳，12月2日开始有明显增长，增长率约为27%。

SELECT SUM(IF(t.fav_num=0 AND t.cart_num=0 AND t.buy_num=0,1,0))/COUNT(t.user_id)*100 AS '跳失率'
FROM(
   SELECT user_id,
             SUM(IF(behavior_type='fav',1,0)) fav_num,
             SUM(IF(behavior_type='cart',1,0)) cart_num,
             SUM(IF(behavior_type='buy',1,0)) buy_num
      FROM userbehavior_random
      GROUP BY user_id
     ) t

可得跳失率约为23.98%，说明详情页对用户吸引力扔有待提高。

2、用户购买行为分析

① 日交易用户人数分布

2017年11月25日-2017年12月1日交易人数呈平稳状态，并有少量增加，12月1日-12月3日交易用户人数约增加32%。

② 用户复购

复购率 = 购买次数两次及其以上的人数/有购买行为的用户总数

select concat(round((sum(case when 购买量>=2 then 1 else 0 end)/
sum(case when 购买量>0 then 1 else 0 end))*100,2),'%') as 复购率 
from userbehavior1;

3、建立用户各行为转化漏斗模型

①创建用户行为视图

create view userbehavior1 
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 = 'buy' 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 '加购数'
from userbehavior_random
group by user_id;

②计算用户各行为发生总数

select sum(点击量) as total_pv,
       sum(购买量) as total_buy,
       sum(收藏数) as total_fav,
       sum(加购数) as total_cart
from userbehavior1;

③计算各行为的转化率

select concat(round(sum(点击量)/sum(用户行为总数)*100,2),'%') as total_to_pv, 
       concat(round((sum(收藏数)+sum(加购数))/sum(点击量)*100,2),'%') as pv_to_favCart,
       concat(round(sum(购买量)/sum(点击量)*100,2),'%') as pv_to_buy
from userbehavior1;

使用Tableau绘制用户漏斗模型，在2017年11月25日-2017年12月3日期间，用户的点击率为89.54%，收藏或加购的占比为9.47%，最终购买率为2.22%；

4、用户留存分析

n日后留存率=(注册后的n日后还登录的用户数)/第一天新增总用户数

以2017-11-25作为第一天，计算次日、3日、7日留存率

--第一天新用户数
create table liucun 
as select count(distinct user_id) as 第一天新用户数 
from userbehavior_random
where dates = '2017-11-25';
-- 第二天留存用户数
alter table liucun add column 第二天留存用户 INT;
update liucun set 第二天留存用户=
(select count(distinct user_id) from userbehavior_random
where dates = '2017-11-26' 
and user_id in (SELECT user_id FROM userbehavior_random WHERE dates = '2017-11-25'));
-- 第三天留存用户数
alter table liucun add column 第三天留存用户 INT;
update liucun set 第三天留存用户=
(select count(distinct user_id) from userbehavior_random
where dates = '2017-11-27' 
and user_id in (SELECT user_id FROM userbehavior_random WHERE dates = '2017-11-25'));
-- 第五天留存用户数
alter table liucun add column 第五天留存用户 INT;
update liucun set 第五天留存用户=
(select count(distinct user_id)  from userbehavior_random
where dates = '2017-11-29' 
and user_id in (SELECT user_id FROM userbehavior_random WHERE dates = '2017-11-25'));
alter table liucun add column 第七天留存用户 INT;
update liucun set 第七天留存用户=
(select count(distinct user_id)  from userbehavior_random
where dates = '2017-12-01' 
and user_id in (SELECT user_id FROM userbehavior_random WHERE dates = '2017-11-25'));

SELECT CONCAT(ROUND(100*第二天留存用户/第一天新用户数,2),'%')AS 次日留存率,
CONCAT(ROUND(100*第三天留存用户/第一天新用户数,2),'%')AS 三日留存率,
CONCAT(ROUND(100*第五天留存用户/第一天新用户数,2),'%')AS 五日留存率,
CONCAT(ROUND(100*第七天留存用户/第一天新用户数,2),'%')AS 七日留存率
from liucun;