用户行为分析

一、数据集介绍

数据来源：阿里云天池淘宝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.流量与活跃度分析

1.1各行为类型点击量

在tableau中绘制不同行为类型随时间变化趋势图：

分析显示：

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

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

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

1.2 PV、UV、PV/UV

计算商品点击量（PV）、独立访客数（UV）

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.转化分析与用户行为转化漏斗图

创建用户行为视图，将各行转化为列，方便后续操作：

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销量前十的商品类目

5.3热销商品复购率

首先统计购买次数在十次及以上的商品：

SELECT item_id,count(DISTINCT user_id) num1
from ub
where behavior_name = 'payment'
GROUP BY item_id
HAVING num1 > 9
ORDER BY num1 desc;

计算各商品复购人数：

SELECT item_id,COUNT(user_id) num
from (
SELECT item_id,user_id,count(*) num2
from ub
where behavior_name = 'payment' and item_id in (SELECT item_id
from ub
where behavior_name = 'payment'
GROUP BY item_id
HAVING COUNT(DISTINCT user_id) > 9)
GROUP BY item_id,user_id
HAVING num2 > 1
ORDER BY num2 desc) t2
GROUP BY item_id;

计算商品复购率：

SELECT a.item_id,ifnull(b.num,0)*1.0/a.num1 as item_repurchase
from (SELECT item_id,count(DISTINCT user_id) num1
from ub
where behavior_name = 'payment'
GROUP BY item_id
HAVING num1 > 9
ORDER BY num1 desc) a
LEFT JOIN
(SELECT item_id,COUNT(user_id) num
from (
SELECT item_id,user_id,count(*) num2
from ub
where behavior_name = 'payment' and item_id in (SELECT item_id
from ub
where behavior_name = 'payment'
GROUP BY item_id
HAVING COUNT(DISTINCT user_id) > 9)
GROUP BY item_id,user_id
HAVING num2 > 1
ORDER BY num2 desc) t2
GROUP BY item_id) b
on a.item_id = b.item_id;

将商品复购率数据导入tableau，制作商品复购率条形图：

分析显示：

购买人数超过10人的商品中，复购率超过40%的商品只有3个。商品复购率最高为85%，表现优异。平均商品复购率为13%，整体复购率较低。

五、项目总结

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

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

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

posted @ 2021-06-23 00:25 蓝山Lens 阅读(546) 评论(0) 收藏举报

刷新页面返回顶部

蓝山Lens

用户行为分析

公告