用户行为分析

一、数据集介绍

数据来源:阿里云天池淘宝APP用户行为数据集 https://tianchi.aliyun.com/dataset/dataDetail?dataId=46&userId=1 tianchi_mobile_recommend_train_user.csv

数据含义:

列说明 数据类型
user_id 用户ID int
item_id 商品ID int
behavior_type 行为类型 int
user_geohash 用户位置 text
item_category 商品类目ID int
time 时间 text

behavior_type(行为类型)包括:

  click(点击)  add-to-cart(加入购物车)  collect(收藏)  payment(购买)

 

二、数据概览及明确分析目的

SELECT COUNT(user_id) as user_id,COUNT(item_id) as item_id,COUNT(behavior_type) as behavior_type,COUNT(user_geohash) as user_geohash,COUNT(item_category) as item_category,COUNT(time) as time
FROM ub;

   用户行为分析

 

 数据集共含有12256906行数据,user_geohash列有缺失,其余列无缺失。

分析目的:了解淘宝APP用户行为变化情况,行为转化率,建立电商业务指标,对业务结果进行评价,提出优化建议

 

三、数据处理

1.添加辅助字段

1.1添加行为名称

alter table ub
add COLUMN behavior_name text; 

UPDATE ub
set behavior_name = click
where behavior_type = 1;
UPDATE ub
set behavior_name = collect
where behavior_type = 2;
UPDATE ub
set behavior_name = add-to-cart
where behavior_type = 3;
UPDATE ub
set behavior_name = payment
where behavior_type = 4;

1.2提取年月日

alter TABLE ub
add COLUMN dates char(10);
UPDATE ub
set dates = substr(time,1,10);

1.3提取小时

alter TABLE ub
add COLUMN hours char(10);
UPDATE ub
set hours = substr(time,12,2);

2.数据清洗

2.1重复值

CREATE TABLE ub1(
    user_id INTEGER NOT NULL,
    item_id INTEGER NOT NULL,
    behavior_type INTEGER NOT NULL,
    user_geohash TEXT NULL,
    item_category INTEGER NOT NULL,
    `time` TEXT NOT NULL,
    behavior_name TEXT NOT NULL,
    dates TEXT NOT NULL,
    hours text NOT NULL
);                    --创建去重后的新表

INSERT into ub1(
    user_id,
    item_id,
    behavior_type,
    user_geohash,
    item_category,
    `time`,
    behavior_name,
    dates,
    hours)
SELECT DISTINCT * 
FROM ub;            --插入去重数据到新表

2.2缺失值

SELECT count(user_id),count(item_id),count(behavior_type),count(user_geohash),count(item_category),count(time)
from ub;  --缺少位置信息记录数

CREATE TABLE ub_lacation as
SELECT *
from ub
where user_geohash is not null;   --创建位置信息不为空的记录视图

2.3日期范围

SELECT min(dates),max(dates) 
from ub;                      --数据集日期范围从2014-11-18至2014-12-18

SELECT dates,count(*) 
from ub
GROUP by dates
order by count(*) desc;        --每日数据记录数均大于30万条

 

四、数据分析

1.活跃度分析

SELECT u1.dates,u1.UV,u2.PV
from (SELECT dates,count(DISTINCT user_id) as UV FROM ub group by dates) u1
join (SELECT dates,count(*) as PV from ub WHERE behavior_name = click GROUP BY dates) u2
on u1.dates = u2.dates;   --计算每日PV,UV

SELECT u1.hours,u1.UV,u2.PV
from (SELECT hours,count(DISTINCT user_id) as UV FROM ub group by hours) u1
join (SELECT hours,count(*) as PV from ub WHERE behavior_name = click GROUP BY hours) u2
on u1.hours = u2.hours;   --计算每时PV、UV

将查询结果数据导入tableau,制作独立访客数(UV)、商品点击量(UV)、平均点击量(PV/UV)每日变化趋势图及每时变化趋势图:

用户行为分析

 分析显示:

①UV、PV、PV/UV三者变化趋势基本一致;

②12月11日~12月12日UV、PV、PV/UV增长迅速,且商品点击量涨幅明显大于访客数涨幅。12月13日三个指标回落。

用户行为分析

 

 用户行为分析

 

 

分析得出: 

①UV、PV、PV/UV三者变化趋势基本一致;

②12月11日~12月12日UV、PV、PV/UV增长迅速,且商品点击量涨幅明显大于访客数涨幅。12月13日三个指标回落。

2.跳失率分析

2.1用户跳失率

--用户跳失率:只点击的用户数量/所有用户数量

SELECT sum(case t.num when 0 then 1 else 0 end)*1.0/COUNT(user_id) as Bounce_Rate
from (SELECT user_id,
             sum(case behavior_name 
             when add-to-cart then 1
             when collect then 1
             when payment then 1
             ELSE 0 end) num 
FROM ub
GROUP by user_id) t;

用户行为分析

 

 2.2商品跳失率

--商品跳失率:只进行点击的用户数量/所有点击该商品的用户数量

SELECT ub.item_id,
             (t.bounce_num)*1.0/COUNT(DISTINCT ub.user_id) as Bounce_Rate
FROM ub
join (SELECT item_id,count(user_id) as bounce_num
       from (SELECT item_id,user_id,
             sum(case behavior_name 
             when add-to-cart then 1
             when collect then 1
             when payment then 1
             ELSE 0 end) num 
FROM ub
where item_id in (SELECT item_id FROM ub GROUP BY item_id order by COUNT(*) desc LIMIT 300)
GROUP by item_id,user_id) t1
WHERE num = 0
group by item_id) t    --t表统计出只进行了点击操作的用户数量
on ub.item_id = t.item_id
where ub.item_id in (SELECT item_id FROM ub GROUP BY item_id order by COUNT(*) desc                     LIMIT 300)    --只统计记录数前三百的商品   
GROUP by ub.item_id;

将查询结果数据导入tableau,制作记录数前三百的商品跳失率图:

用户行为分析

 

 记录数前300的商品中,跳失率最小值为45%,最大值为98%,平均值86%。其中跳失率小于60%的商品只有三个,显示跳失率普遍较高,说明商品详情页吸引力有待提高。

 

3.转化分析

3.1各行为类型点击量每时变化趋势

用户行为分析

 

 用户行为分析

 

 分析显示:

①晚上19点~23点,商品点击量明显上涨,收藏、加购、购买数量也随之上涨,该时段用户开始频繁访问;

②四种行为的趋势基本一致;

③购买曲线与收藏曲线更为接近,相较于加购,收藏商品转化为购买的比例更大,这在一定程度上说明用户会将更想要购买的商品进行收藏。因此在进行个性化商品推送时,应当更加关注用户收藏的商品。

3.2转化漏斗图

CREATE VIEW action as 
SELECT user_id,sum(case behavior_name when click then 1 else 0 end) as click,
             sum(case behavior_name when add-to-cart then 1 else 0 end) as add-to-cart,
             sum(case behavior_name when collect then 1 else 0 end) as collect,
             sum(case behavior_name when payment then 1 else 0 end) as payment
FROM ub 
GROUP BY user_id;                               --创建用户行为视图,将各行为转化为列,方便后续操作


SELECT sum(click) as 点击量,sum(`add-to-cart`)+sum(collect) as 收藏&加购,sum(payment) as 购买
from action;                 DROP TABLE if EXISTS 月整体转化率;
CREATE table 月整体转化率 (
behavior_name text not null,
num integer not null);                 

INSERT into 月整体转化率 VALUES(点击量 ,11550581);
INSERT into 月整体转化率 VALUES(收藏&加购,586120);
INSERT into 月整体转化率 VALUES(购买,120205);           --创建月整体转化数据表

根据对业务的理解,转化流程为:点击>收藏&加购>购买。将数据导入tableau,制作用户行为转化漏斗图:

用户行为分析

 

 分析显示:

2014年11月18日~2014年12月18日期间,用户收藏&加购的比例为5.07%,最终购买的额比例为1.04%。由收藏转化为购买的比例为20.51%。转化漏斗图表明用户转化率情况较差。

4.留存分析

4.1首日用户留存率

将2014年11月18日登录的用户作为首日用户 ,分析该批用户在后续的留存率变化

SELECT dates,COUNT(DISTINCT ub.user_id)*1.0/
(SELECT count(*) from (SELECT DISTINCT user_id from ub WHERE dates = 2014-11-18) t) as retention 
from ub
join (SELECT DISTINCT user_id from ub WHERE dates = 2014-11-18) t
on ub.user_id = t.user_id
GROUP BY ub.dates; 

将查询结果数据导入tableau,制作留存率变化趋势图:

用户行为分析

 

 分析显示:

①前五日留存率下滑,七日留存率略有回升,留存率随时间具有一定的周期波动规律;

②每日的用户留存率相对稳定,12月12日留存率有明显升高,可能是“双12”购物活动的带动作用。

4.2新增用户次日留存率

SELECT COUNT(DISTINCT u1.user_id)*1.0/(SELECT COUNT(DISTINCT user_id) FROM ub)
from ub u1 join ub u2
on u1.user_id = u2.user_id and u2.dates = DATE(u1.dates, +1 day);

每日新增用户次日成功留存的比率为:

用户行为分析

 

 留存比率为97%,显示出良好的用户黏性 

5.商品销量分析

5.1销售量前二十的商品

用户行为分析

 

 5.2销量前十的商品类目

用户行为分析

 

 五、项目总结

1.本项目利用SQL,对数据进行清洗和加工,结合电商业务理解,提取所需数据,建立电商分析指标;

2.利用tableau可视化工具,将数据指标进行可视化,直观分析数据变化;

3.基于业务指标分析,提出分析结果和业务建议。

 

用户行为分析

上一篇:DOS命令行(11)——更多实用的命令行工具


下一篇:Dos黑窗口