数据分析实战项目——电商用户行为分析及可视化（基于SQL/Tableau 含代码）

本项目以阿里巴巴移动电商平台的真实用户-商品行为数据为基础，使用SQL进行数据清洗，以AARRR模型、RFM模型为基础展开分析，再用Tableau做可视化，最后从提升用户活跃度、促进商品成交、差异化用户营销三个方面提出建议。

首先理清思路，这是我的思维导图：

一、数据源

数据来自天池竞赛：
tianchi.aliyun.com/dataset/dat…

数据包含两部分，第一部分是用户在商品全集上的移动端行为数据，第二部分是商品子集。我只用到了第一部分。对应文件是tianchi_mobile_recommend_train_user.zip，包含如下字段：

注意：这个文件解压后有560MB，数据有130万条多，很大，Excel打不开，还把我电脑卡死机了。

二、数据清洗

2.1 导入数据到数据库

我是买了一个阿里云的云端数据库，用起来比较方便，我买的2核2G，有点带不动，处理起来有点慢，但也能用。 导入数据时，所有字段都按varchar(255)导入，尤其是time字段，不要改成datetime，导入后会显示不出来，很麻烦。

2.2 异常值

查看time范围，behavior_type的类型。代码如下：

select min(time),max(time)
from data;
select distinct behavior_type
from data;

2.3 缺失值

查看各字段数量，全部一样，没有缺失值。代码如下：

select count(user_id),count(item_id),
count(behavior_type),count(time)
from data;      #13309161

2.4 数据一致化

将time字段分成 日期date和时段hours两个字段，把behavior_type的数字改为英文简写，方便理解。代码如下：

# 将time分成date和hours两个字段，然后删除time
alter table data add column date date;
alter table data add column hours varchar(255);
update data set date = left(time,10);
update data set hours = substring(time,12,2);

# 把behavior_type 分别改为 pv,col,cart,buy
update data
set behavior_type = (
	case
	when behavior_type = 1 then 'pv'
	when behavior_type = 2 then 'col'
	when behavior_type = 3 then 'cart'
	when behavior_type = 4 then 'buy'
	else '其他'
	end
);

# 删除time这一列
alter table data drop time;

三、数据分析

理解业务，写代码，建视图，可视化

3.1 用户获取(Acquisition)

代码参考后面的留存率day_0

3.2 用户激活(Activation)

PV,UV

本项目数据源共13309161条，PV有12540816条，UV有9994条，人均页面访问数为1254.83。代码如下：

#pv 页面浏览量 uv 独立访客数
create view whole_index as  
select sum(user_pv) PV,
count(user_id) UV,
# 人均页面访问数 PV/UV
format(sum(user_pv)/count(user_id),2) 人均页面访问数
from 
(
	select user_id,count(behavior_type) user_pv
	from data
	where behavior_type = 'pv'
	group by user_id
) a;

跳失率

浏览页跳失率为5.06%，关键页跳失率64.98%。

跳失分析：商品不符合用户需求，竞争力不够。可对比竞品网站，在商品的定价、商品陈列故事、商品详情描述等方面做出改进。 代码如下：

# 浏览页跳失率
create view view_bounce_rate as 
select
(
	select count(distinct user_id)
	from data
) 总用户,
count(distinct user_id) 仅pv用户,
concat(format(count(distinct user_id)/(select count(distinct user_id) from data)*100,2),'%') 浏览页跳失率
from data
where user_id not in 
(
	select distinct user_id
	from data
	where behavior_type = 'col'
) 
and user_id not in 
(
	select distinct user_id
	from data
	where behavior_type = 'cart'
)
and user_id not in 
(
	select distinct user_id
	from data
	where behavior_type = 'buy'
);
select * from view_bounce_rate;

# 关键页跳失率：用户有收藏或加购，但无购买
create view key_bounce_rate as 
select
(
	select count(distinct user_id)
	from data
) 总用户,
count(distinct user_id) col_cart用户,
concat(format(count(distinct user_id)
/(
	select count(distinct user_id) 
	from data
)*100,2),'%') 关键页跳失率
from data
where user_id in 
(
	select distinct user_id
	from data
	where behavior_type = 'col'
) 
or user_id in 
(
	select distinct user_id
	from data
	where behavior_type = 'cart'
)
and user_id not in 
(
	select distinct user_id
	from data
	where behavior_type = 'buy'
);
select * from key_bounce_rate;

时间维度用户行为

从时间维度的日期、时段、星期，来观察用户行为。代码如下：

# 每日、小时用户行为
create view dayhour_behavior_times as 
select date,hours, 
sum(case when behavior_type = 'pv' then 1 else 0 end) pv,
sum(case when behavior_type = 'col' then 1 else 0 end) col,
sum(case when behavior_type = 'cart' then 1 else 0 end) cart,
sum(case when behavior_type = 'buy' then 1 else 0 end) buy,
count(behavior_type) all_click,
count(distinct user_id) all_users
from data
group by date,hours
order by date,hours;
select * from dayhour_behavior_times;

从11月30日开始，pv、cart值增大，12月5日、8日出现小范围减小；12月12日当天pv、cart、buy值激增，达到高点，次日骤降,回归平均水平。

从日时段来看，用户在10点到24点较为活跃。从17点开始逐渐增高，晚上21点—22点达到最高值。

# 周weeks用户行为,'%W' W必须大写，才会返回星期名。
create view weeks_behavior_times as 
select date_format(date,'%W') weeks,
sum(case when behavior_type = 'pv' then 1 else 0 end) pv,
sum(case when behavior_type = 'col' then 1 else 0 end) col,
sum(case when behavior_type = 'cart' then 1 else 0 end) cart,
sum(case when behavior_type = 'buy' then 1 else 0 end) buy,
count(behavior_type) all_click,
count(distinct user_id) all_users
from data
where date between '2014-11-23' and '2014-12-13'
group by weeks 
order by field(weeks,'Monday','Tuesday','Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

从星期来看，周四、周五、周六形成一个客流小高峰，在周五完成购买。

3.3 用户留存(Retention)

留存率

为了排除11月18日前，未统计数据的影响。选择近一个月的中间日期，从12月3日开始看用户留存率。代码如下：

create view time_inter as 
select a.*,b.firstday,datediff(a.date,b.firstday) day_diff 
from 
(
	select user_id,date
	from data
	group by user_id,date
) a,
(
	select user_id,min(date) firstday
	from data
	group by user_id
) b 
where a.user_id = b.user_id
order by user_id,date;

# 搭建留存天数模型retention_day，第一次活跃用户会持续活跃几天
create view retention_day as 
select firstday,
sum(case when day_diff = 0 then 1 else 0 end) day_0,
sum(case when day_diff = 1 then 1 else 0 end) day_1,
sum(case when day_diff = 2 then 1 else 0 end) day_2,
sum(case when day_diff = 3 then 1 else 0 end) day_3,
sum(case when day_diff = 4 then 1 else 0 end) day_4,
sum(case when day_diff = 5 then 1 else 0 end) day_5,
sum(case when day_diff = 6 then 1 else 0 end) day_6,
sum(case when day_diff = 7 then 1 else 0 end) day_7,
sum(case when day_diff = 15 then 1 else 0 end) day_15,
sum(case when day_diff = 30 then 1 else 0 end) day_30
from time_inter
group by firstday
order by firstday;

# 搭建留存率模型retention_rate
create view retention_rate as 
select firstday,day_0, 
concat(format(day_1/day_0*100,2),'%') day_1,
concat(format(day_2/day_0*100,2),'%') day_2,
concat(format(day_3/day_0*100,2),'%') day_3,
concat(format(day_4/day_0*100,2),'%') day_4,
concat(format(day_5/day_0*100,2),'%') day_5,
concat(format(day_6/day_0*100,2),'%') day_6,
concat(format(day_7/day_0*100,2),'%') day_7,
concat(format(day_15/day_0*100,2),'%') day_15,
concat(format(day_30/day_0*100,2),'%') day_30
from retention_day;

12·12活动前后的用户留存率高于同期水平。从留存率看，预计12月18日后近期会有另外的促销活动开展。

3.4 用户推荐(Referral)

本项目中的收藏col与加购cart的行为不是相互独立的，都是确定购买buy意向的行为，没有先后之分。因此，从浏览pv、加购cart、购买buy这一路径来简要分析。

从浏览到加购的转化率为2.98%，用户花了大量时间寻找合适的产品。可优化网站筛选、关键词搜索功能，推荐更适合用户的商品，方便用户迅速获取信息。

从加购到购买的转化率约为35%。可针对加购未购买产品细分，采用提供优惠券、推荐同款商品等促销方法。

转化率

代码如下：

# 计算转化率conversion_rate
create view conversion_rate as 
select 
concat(format(
(
	select count(behavior_type)
	from data
	where behavior_type = 'col'
)/
(
	select count(behavior_type)
	from data
	where behavior_type = 'pv'
)*100,2),'%') 收藏转换率,
concat(format(
(
	select count(behavior_type)
	from data
	where behavior_type = 'cart'
)/
(
	select count(behavior_type)
	from data
	where behavior_type = 'pv'
)*100,2),'%') 购物车转换率,
concat(format(
(
	select count(behavior_type)
	from data
	where behavior_type = 'buy'
)/
(
	select count(behavior_type)
	from data
	where behavior_type = 'pv'
)*100,2),'%') 购买转换率

漏斗模型

用户行为漏斗behavior_times，uv独立访客漏斗user_times。代码如下：

# 漏斗模型
create view funnel_times as 
select behavior_type,count(behavior_type) behavior_times,
count(distinct user_id) user_times
from data
group by behavior_type
order by field(behavior_type,'pv','col','cart','buy')

3.5 用户收益(Revenue)

购买率

代码如下：

# 创建视图user_behavior_times,用户-行为分类明细表
create view user_behavior_times as 
select user_id,
sum(case when behavior_type = 'pv' then 1 else 0 end) pv_times,
sum(case when behavior_type = 'col' then 1 else 0 end) col_times,
sum(case when behavior_type = 'cart' then 1 else 0 end) cart_times,
sum(case when behavior_type = 'buy' then 1 else 0 end) buy_times,
concat(format(sum(case when behavior_type = 'buy' then 1 else 0 end)
/sum(case when behavior_type = 'pv' then 1 else 0 end)*100,2),'%') 购买率,
sum(case when behavior_type = 'buy' then 1 else 0 end)
/sum(case when behavior_type = 'pv' then 1 else 0 end) sort 
from data
group by user_id
order by sort desc;

复购率

代码如下：

# 复购率
create view repurchase_rate as 
select concat(format(
(
	select count(user_id)
	from user_behavior_times
	where buy_times>1
)/
(
	select count(user_id)
	from user_behavior_times
	where buy_times>0
)*100,2),'%') 复购率;

2.商品销售情况

查看各品类及商品被购买次数，包括0次即没有成交记录，在可视化时可以，计算商品购买率，并按照需要筛选相应指标。代码如下：

create view hot_item as 
select item_category,item_id, 
sum(case when behavior_type = 'pv' then 1 else 0 end) pv_times,
sum(case when behavior_type = 'buy' then 1 else 0 end) buy_times
from data
group by item_category,item_id 
order by item_category,item_id,buy_times;

可视化：

3.用户价值（基于RFM模型）

仅在会员（购买过商品的用户）范围内，进行价值划分,R/F等级为1-5，由于本项目的数据不涉及M(Monetary)，所以只做4类划分。

通过RFM模型，了解每位顾客的特性，实现差异化营销。

• 重要价值用户，R高F高。经常在网站购物，并形成习惯，做好维护即可。
• 重要深耕用户，R高F低。这类用户粘性不强，近期有消费行为，可能是针对需要的商品直接下单，不会过于频繁地进行其他商品的浏览、购买。可推荐更符合用户喜好、性价比高商品，吸引其进行购买，逐渐建立消费习惯。
• 重要唤回用户，R低F高。最近没有购买行为，可以通过短信等场外提醒，引导其入场参与优惠活动，尽快完成下次购买行为。
• 重要挽留用户，R低F低。该类用户容易流失，占比40.02%，可提升空间大，运营活动可重点针对这部分用户，通过拼团打折、积分兑换、捆绑销售等活动唤起用户注意力，提升用户兴趣。

代码如下：

# 计算R/F/M,本项目无M，省略
create view rfm as 
select user_id,
max(date) as 'lasttime',datediff('2014-12-19',max(date)) recency,
count(behavior_type) frequency,
(
	case 
		when datediff('2014-12-19',max(date))<=3 then 5
		when datediff('2014-12-19',max(date))<=6 then 4
		when datediff('2014-12-19',max(date))<=9 then 3
		when datediff('2014-12-19',max(date))<=12 then 2
		else 1 end
) R,
(
	case 
		when count(behavior_type)<=2 then 1
		when count(behavior_type)<=4 then 2
		when count(behavior_type)<=6 then 3
		when count(behavior_type)<=8 then 4
		else 5 end 
) F 
from data
where behavior_type = 'buy'
group by user_id;

# 计算平均值
select avg(F) F平均值
from rfm;  #3.7771
select avg(R) R平均值
from rfm;  #3.3687

# 没有M，建立在购买量M大的前提下，对用户做出以下4类分类
create view rfm用户分类 as 
select *,
(
	case 
		when R>3.3687 and F>3.7771 then '重要价值用户'
		when R>3.3687 and F<3.7771 then '重要深耕用户'
		when R<3.3687 and F>3.7771 then '重要唤回用户'
		when R<3.3687 and F<3.7771 then '重要挽留用户'
		end 
) 用户分类 
from rfm;

可视化：

4.导出数据

右键视图，选择将数据导出到文件，导出到合适的文件夹。大概半小时左右即可导出成功。一定要选择添加列标题，方便可视化。

四、可视化看板

主要分为三部分，第一部分是整体用户行为分析，可行建议有：对比竞品网站，在商品的定价、商品陈列故事、商品详情描述等方面做出改进，减少用户跳失。 大型活动至少提前1—2周进行预热，可活跃用户，提升用户留存率；网站维护、客服尤其要关注晚上20点—23点时段；日常的推新、促销可选择在周四至周六这一时间段进行。 优化网站筛选、关键词搜索功能，推荐更适合用户的商品，方便用户迅速获取信息；针对加购未购买产品细分，采用提供优惠券、推荐同款商品等促销方法，提升用户行为转化率，促进更快、更顺利完成购买行为。

第二部分是对商品品类的分析，可行建议有：在成交品类较高的6977、5232、13500等大类后，可设置引流通道。对成交量高的品类，提炼可复用的营销方法。 对pv低buy低的商品，可增加其客流量，以期提高成交量；在活动中把pv低buy高的商品作为主打，提高复购率，打开市场、吸引新用户，借其增强品类口碑；针对pv高buy低的商品，在商品详情、价格等方面做出改变，提升商品竞争力；提炼pv高buy高的商品卖点、精简描述，引导用户更快做出购买决策，提升购买体验。

第三部分是对用户特性的分析，细分每一类用户，做好差异化营销。可行建议有：做好 重要价值用户 的维护工作，推荐更符合 重要深耕用户 喜好、性价比高商品，吸引其进行购买，逐渐建立消费习惯。通过发送短信等场外提醒给 重要唤回用户 的方式，引导其入场参与优惠活动，尽快完成下次购买行为。运营活动可重点关注容易流失的 重要挽留用户 ，通过拼团打折、积分兑换、捆绑销售等活动唤起用户注意力，帮助用户建立对平台的兴趣。