一、项目背景

本项目以淘宝的真实用户的商品行为数据为基础，通过sql分析和tableau可视化，探索用户行为规律，寻找高价值用户；分析商品特征，寻找高贡献商品；分析产品功能，优化产品路径。

注：数据包含了抽样出来的1W用户在一个月时间（11.18~12.18）之内的移动端行为约一千两百万条数据。

二、分析流程

主要从以下四个维度对用户行为进行分析和建议:
用户行为间的转化情况分析
利用漏斗模型分析用户从商品浏览到购买整个过程中，常见的电商分析指标，确定各个环节流失率，提出改善转化率的建议。
用户的行为习惯分析
利用pv、uv等指标，找出用户活跃的日期以及每天的活跃时间段。
用户类目偏好分析
根据商品的点击、收藏、加购、购买频率，探索用户对商品的购买偏好，找到针对不同商品的营销策略(购买率较高的类目和产品，优化产品推荐)。
用户价值分析
找出最具有价值的核心用户群，针对这个群体推送个性化推送，优惠券或者活动。

分析流程

确认问题

本次分析的目的是想通过对用户行为数据进行分析，为以下问题提供解释和改进建议:

基于漏斗模型的用户购买流程各环节的分析指标，分析各环节的转化率，便于找到改进的环节。
商品分析：找出热销商品，研究热销商品的特点。
基于RFM模型找出核心付费用户群，对每部分群体做精准营销。

三、数据读取

表结构

use user_tb;
create table user_action
(
user_id int (9),
item_id int (9),
behavior_type int (1),
item_category int (5),
time  varchar (13)
);

数据导入

数据来源：阿里云-天池-淘宝用户购物行为数据可视化分析

四、数据预处理

数据预处理包括数据重复值、空缺值、异常值处理以及保持数据一致性。

数据重复值

查看数据重复值

select *,count(1) 
from user_action
group by user_id,item_id,behavior_type,item_category,time
having count(1)>1
order by count(1) desc;

数据重复值

处理数据重复值

虽然可以看到有大量的重复数据。

但是通过实际分析，time的精确到小时，而一个小时内可以发生多个用户购买行为，所以忽略掉这些重复的数据。

数据空缺值

查看数据空缺值

1 2	`select count(user_id),count(item_id),count(behavior_type),count(item_category),count(time) from user_action;`

数据空缺数值

数据异常值

从范围数值内入手或者是明显不符合实际逻辑的数据。

查看behavio type的类型
1
select distinct behavior_type from user_action;
四种行为，没有异常。
查看time的范围
1
select max(time),min(time) from user_action;
没有问题，符合数据集中的描述，时间是从2014年11月18号到2014年12月18号共一个月的时间。

数据一致性

对time字段做处理：
- 新建日期列，命名为date；新建小时列，命名为hour。
- 更新新增列内容后删去原time列。

alter table user_action add date date;
update user_action set date=left(time,10);

alter table user_action add hour char(255);
update user_action set hour=right(time,8);

alter table user_action drop time;

对behavior_type字段改为英文命名

update user_action
set behavior_type=(
	case
	when behavior_type=1 then 'look'
	when behavior_type=2 then 'collect'
	when behavior_type=3 then 'cart'
	when behavior_type=4 then 'buy'
	else 'other' end
	);

五、数据分析

（一）整体情况概述

1.月维度-总体情况

11月18日至12月18日总体uv、浏览总次数、人均浏览次数、收藏量、加购量、成交数

-- 整体
create view month_all_cnt as -- 创建月维度-指标视图
select 
	count(distinct user_id) as uv,-- 独立访客数
	sum(behavior_type='look')/count(distinct user_id) as per_view, -- 人均页面访问数
	sum(behavior_type='look') as cnt_all_look,-- 浏览量
	sum(behavior_type='collect') as cnt_all_collect,-- 收藏量
	sum(behavior_type='cart') as cnt_all_cart,-- 加购量
	sum(behavior_type='buy') as cnt_all_buy-- 成交量
from user_action;

查询结果如下

month_all_cnt

可以看出分析的总用户数为1万人，一个月的订单量约为12万单，并且比起收藏，人们更倾向于加购。

2.DNU指标

create view day_dnu as -- 创建每日新用户数视图
select date,count(distinct user_id)
from (select user_id,date,rank() over(partition by user_id order by date) r
			from user_action)t 
where r=1
group by date;

查询结果如下

DNU指标

使用tableau将其可视化结果如下
可以看出DNU指标从首日便开始骤减，是由于数据是从11月18日开始，没有之前的数据源。故导致在11月18日中的“新增用户”极大部分是来自于此前活跃用户，并非当日新增的用户。
而次日到三日的DNU数据也出现一定程度的骤减情况，原因为这些“新增用户”也有部分来自于此前活跃用户。
越到后面越呈现稳定状态，也更能代表DNU真实情况。

3.整体行为数据

（1）用户行为数据频次统计

with view user_cnt_all as -- 用户维度整体情况
select 	user_id,
	count(1) as all_cnt,-- 用户总点击量
	sum(behavior_type='look') as cnt_all_look,-- 用户浏览量
	sum(behavior_type='collect') as cnt_all_collect,-- 用户收藏量
	sum(behavior_type='cart') as cnt_all_cart,-- 用户加购量
	sum(behavior_type='buy') as cnt_all_buy-- 用户成交数
from user_action
group by user_id
order by cnt_all_buy;

查询结果如下

user_cnt_all

统计出每个用户的总点击数、浏览数、收藏数、加购数和购买数，方便后续分析出用户的复购情况和浏览页面跳出情况。

（2）用户整体复购情况

with view user_again as -- 用户复购视图
select sum(cnt_all_buy>0) as buy,-- 用户月买了多少次
	sum(cnt_all_bu>1) as again_buy,-- 用户月复购多少次
	concat(round((sum(cnt_all_bu>1)/sum(cnt_all_bu>0))*100,2),'%') as again_buy_rate-- 用户月复购率
from user_cnt_all

查询结果如下

user_again

用户复购率高达91.69%，说明淘宝对用户有较大的吸引力使用户停留且用户的忠诚度非常高。可以进一步提高复购率，鼓励用户更高频次的购物。
猜测其最大的原因是双十二当天的购买人数非常多，拉高了复购率。后续还要和别的月份相比较。

（3）页面跳出情况

浏览页面跳出

create view user_lost as -- 用户跳出情况
select count(distinct user_id) as user_lost,
	concat(
		round(
			(count(distinct user_id)/(select count(user_id) from user_cnt_all))*100
			,2)
		,'%') as user_lost_rate
from user_cnt_all
where cnt_all_look>0 
	and cnt_all_collect=0 
	and cnt_all_cart=0 
	and cnt_all_cart=0 
	and cnt_all_buy=0

查询结果如下

user_lost

关键页面跳出

指的是用户加购或者收藏却没有购买的行为

create view user_keylost as -- 用户关键跳出情况
select 
 (select count(user_id) from user_cnt_all) as '总用户',
	count(distinct user_id) as '跳出用户',
	concat(
		round(
			(count(distinct user_id)/(select count(user_id) from user_cnt_all))*100
			,2)
		,'%') as 关键跳出率
from user_cnt_all
where cnt_all_collect>0 or cnt_all_cart>0 
	and cnt_all_buy=0;

查询结果如下

user_keylost

跳出情况解读：
- 浏览页面跳出率为42.61%，关键页面跳出率我额70.29%。
- 分析：可能商品不符合用户需求，竞争力不够。
- 解决方法：可对比竞品网站，在商品的定价、商品陈列故事、商品详情描述等方面做出改进

4.用户留存情况

create view user_remain as -- 创建用户留存表
with temp_remain as (
	select t1.date,
		sum(datediff(t2.date,t1.date)=0) remain_0,-- 当天用户数
		sum(datediff(t2.date,t1.date)=1) remain_1,-- 第二天剩下来的用户数
		sum(datediff(t2.date,t1.date)=2) remain_2,-- 第三天剩下来的用户数
		sum(datediff(t2.date,t1.date)=6) remain_6-- 第七天剩下来的用户数
	from 
	(select user_id,date from user_action group by user_id,date)t1
	left join (select user_id,date from user_action group by user_id,date)t2
	on t1.user_id=t2.user_id and t1.date<=t2.date 
	group by date
	order by date
)
select date,remain_0 as today_cnt,
	concat(round((remain_1/remain_0)*100,2),'%') day_2_rate,-- 第2天的留存率
	concat(round((remain_2/remain_0)*100,2),'%') day_3_rate,-- 第3天的留存率
	concat(round((remain_6/remain_0)*100,2),'%') day_7_rate-- 第7天的留存率
from temp_remain;

查询结果如下
因为时间跨度只有一个月，所以直接分析次日、三日、一周留存率。
- 可以看出次日留存率、三日留存率、一周留存率在“双十二”前留存率呈下降趋势，而“双十二”活动左右的一周时间内各项指标都出现比较大的波动而且总体是提高的趋势，可以看出活动对用户的吸引力是很强的，在12.13号当天次日留存率达到顶峰值，可能为客户在购买完商品后对商品状态的关心而进行再次访问。

（二）用户转化情况分析

1.漏斗分析

（1）流量转化分析

对浏览量-收藏量-加购量-购买量的对比查询结果如下

没有图
使用tableau将其可视化结果如下
从浏览量-加购量-收藏量-购买量来看，只有1%的购买。并且无论是加购还是收藏的转化率都十分低，说明用户花费了大量时间来寻找合适产品，故我们可以从UI设计，关键词搜索优化，推荐算法等方面进行提升客户体验。
进一步用户是否也是在点击后产生了大量的流失

（2）各环节用户转化分析

基于user_cnt_all表对用户各个行为的统计，使用漏斗转化模型进行了处理，建立了用户转化漏斗图。

create view user_transfor as 
select 
	sum(cnt_all_look),sum(cnt_all_collect),sum(cnt_all_cart),sum(cnt_all_buy)
from user_cnt_all;

查询结果如下
使用tableau将其可视化结果如下
- 可以看出，用户并未点击后就大量流失，并且最后付费用户比例达到了88.86%。说明用户的购买意愿较为理想。结合用户行为数统计推断用户的点击行为远远超过了收藏和加购物车的行为，说明用户有较大的可能在购物时有“货比三家”的习惯。
- 所以针对大部分点击后转化到加购物车和收藏的概率较低有较大的提升空间，APP可以通过优化推荐商品的功能和筛选商品的功能，让用户不用浏览较多的网页获得心仪的商品。

（3）路径分析

那么再回到用户转化漏斗图，从浏览到购买每个环节的转化率又是多少?为什么收藏比加入购物车少?
从用户点击后到最后购买的四条不同的路径展开分析

点击-购买

使用tableau将其可视化结果如下

浏览-购买

点击-收藏-购买

浏览-收藏-购买

点击-加购-购买

浏览-加购-购买

点击-收藏或加购-购买

浏览-收藏或加购-购买

从四种购买行为的留存，可以看出用户点击后，若存在后续行为则主要是加入购物车，其次是收藏和加购物车，再接下来是收藏；除此之外还与一部分用户是点击后直接购买的。
可以着重研究一下各类路径用户购买的什么产品以及用户的标签，通过优化商品推荐和精准运营加强这部分的转化。
推断在收藏的用户转换率较低的原因在于用户在点击时较为喜欢此商品或者说活动时间还未开始，通过提醒用户等方式精准运营提高这部分用户的购买率。
最后，对用户从收藏到购买，加购物车到购买和收藏和加购物车到购买的转化分析来看，加购物车到购买的转化率最高达到了 34.79%，说明直接加入到购物车最后购买的概率较高，故引导客户加入购物车及时购买可以提高购买转化率。

2.行为习惯分析

行为习惯分布（周）

create view day_all_cnt as -- 创建周的指标视图
select date_format(date,'%W') as weeks,
	count(distinct user_id) as uv,-- 独立访客数
	round(sum(behavior_type='look')/count(distinct user_id),2) as per_view, -- 人均页面访问数
	sum(behavior_type='look') as cnt_all_look,-- 浏览量
	sum(behavior_type='collect') as cnt_all_collect,-- 收藏量
	sum(behavior_type='cart') as cnt_all_cart,-- 加购量
	sum(behavior_type='buy') as cnt_all_buy-- 成交量
from user_action
group by weeks
order by field(weeks,'Monday','Tuesday','Wednesday','Thurday','Friday','Saturday','Sunday');

查询结果如下

周维度数据

使用tableau将其可视化结果如下
从星期来看，周四、周五、周六形成一个客流小高峰，在周五完成购买。

行为习惯分布（天）

create view day_all_cnt as -- 创建每天指标视图
select 
	count(distinct user_id) as uv,-- 每日独立访客数
	sum(behavior_type='look')/count(distinct user_id) as per_view, -- 每日人均页面访问数
	sum(behavior_type='look') as cnt_all_look,-- 浏览量
	sum(behavior_type='collect') as cnt_all_collect,-- 每日收藏量
	sum(behavior_type='cart') as cnt_all_cart,-- 每日加购量
	sum(behavior_type='buy') as cnt_all_buy-- 每日成交量
from user_action
group by date;

查询结果如下

day_all_cnt

使用tableau将其可视化结果如下
- 在研究日期范围内，用户活跃度均比较平稳，但是在12月2日和12月3日出现小幅度的增长，其中点击量和收藏，尤其是加购物车增幅增大，但是购买没有较大增幅。
- 分析其原因是由于周末空余时间较多，用户流量增大;再加上双12很多预热活动，用户提前添加收藏和加购物车，是双12批量购买的前置动作，符合常规预期。

一天内用户行为习惯分布

create view hours_all_cnt as -- 创建每小时指标视图
select hours,
	count(distinct user_id) as uv,-- 每小时独立访客数
	round(sum(behavior_type='look')/count(distinct user_id),2) as per_view, -- 每日人均页面访问数
	sum(behavior_type='look') as cnt_all_look,-- 浏览量
	sum(behavior_type='collect') as cnt_all_collect,-- 每小时收藏量
	sum(behavior_type='cart') as cnt_all_cart,-- 每小时加购量
	sum(behavior_type='buy') as cnt_all_buy-- 每小时成交量
from user_action
group by hours;

查询结果如下
使用tableau将其可视化结果如下
- 用户在10点到24点较为活跃。
- 从17点开始逐渐增高，晚上21点—22点达到最高值。原因可能为18点后处于下班高峰期，人们喜欢下班后购物，在9点左右忙完琐事后购物。

3.类目偏好分析

统计所有商品的购买次数，同时找到购买次数、浏览次数、收藏次数和加入购物车次数最多的商品。

create view item_action_cnt as --创建商品各种动作数据视图
select item_id,
	sum(behavior_type='look') item_look_cnt,
	sum(behavior_type='collect') item_collect_cnt,
	sum(behavior_type='cart') item_cart_cnt,
	sum(behavior_type='buy') item_buy_cnt
from user_action 
group by item_id

查询结果如下

item_action_cnt

商品销售情况分析

-- 统计商品购买次数情况
create view item_buy as 
select item_buy_cnt,count(1) as item_cnt
from item_action_cnt 
group by item_buy_cnt
order by item_buy_cnt;

查询结果如下

item_buy

用户购买的商品总共有4001种，购买次数在1次的商品占绝大部分，其次是购买次数为2商品，未出现购买次数集中的商品，说明商品的售卖主要是依靠商品的长尾效应，而非爆款商品。

商品品类浏览的top20

create view look_20 as 
select item_id,item_look_cnt
from item_action_cnt
order by item_look_cnt desc
limit 20;

查询结果如下
使用tableau将其可视化结果如下
可以看出商品112921337浏览次数最多，占所有浏览次数的9.5%。

商品品类收藏的top20

create view collect_20 as 
select item_id,item_collect_cnt
from item_action_cnt
order by item_collect_cnt desc
limit 20;

查询结果如下
使用tableau将其可视化结果如下
可以看出商品277922302浏览次数最多，占所有浏览次数的7.6%。

商品品类加购的top20

create view cart_20 as 
select item_id,item_cart_cnt
from item_action_cnt
order by item_cart_cnt desc 
limit 20;

查询结果如下
使用tableau将其可视化结果如下
可以看出商品83098075浏览次数最多，占所有浏览次数的9.8%。

商品品类购买的top20

create view buy_20 as 
select item_id,item_buy_cnt
from item_action_cnt
order by item_buy_cnt desc 
limit 20;

查询结果如下
使用tableau将其可视化结果如下
可以看出商品303205878浏览次数最多，占所有浏览次数的710.2%。

商品品类分析

查询商品在加购和收藏的top20，但不在购买类目的top20
1
2
3
4
select cart_20.item_id from cart_20 where item_id not in (select item_id from buy_20) and item_id in (select item_id from collect_20);
- 查询结果如下
- 发现没有商品查询商品在加购和收藏的top20，但不在购买类目的top20。说明所有top20的商品的转化率都还可以。

4.价值分析—RFM模型

R部分

将客户近期购买时间进行等级划分，越接近2014-12-18号R越大。
- 间隔>15天为1;
  间隔在10-15天为2;
  间隔在7-9天为3;
  间隔在3-6天为4;
  间隔在0-2天为5;

-- 第一步：先将每个用户最近购买时间提取出来
create table recency as -- 筛选出每个用户最近购买的日期
select user_id,max(date) as recent_buy_time
from user_action 
where behavior_type='buy'
group by user_id;

-- 第二步：根据购买等级划分计算出得分
create table r_value as 
select *,
	case 
		when datediff('2014-12-18',recent_buy_time)>15 then 1
		when datediff('2014-12-18',recent_buy_time) between 10 and 15 then 2
		when datediff('2014-12-18',recent_buy_time)between 7 and 9 then 3
		when datediff('2014-12-18',recent_buy_time)between 3 and 6 then 4
		when datediff('2014-12-18',recent_buy_time)between 0 and 2 then 5 
	end as r -- 计算出每个用户的r得分情况
from recency;

查询结果如下

r_value

F部分

将客户购买频次进行等级划分，越接近2014-12-18号R越大。
无购买，0分；
1-49次，1分；
50-99次，2分；
100-299次，3分；
300-499次，4分；
大于500次，5分。

-- 表user_cnt_all已经计算出每个用户购买的次数 
-- 接下来根据购买频次等级计算出得分即可
create table f_value as 
select user_id,cnt_all_buy,
	case 
		when cnt_all_buy>500 then 5
		when cnt_all_buy between 300 and 499 then 4
		when cnt_all_buy between 100 and 299 then 3
		when cnt_all_buy between 50 and 99 then 2
		when cnt_all_buy between 1 and 49 then 1 else 0
	end as f -- 计算出每个用户的r得分情况
from user_cnt_all;

查询结果如下

f_value

用户划分

由于只有R和F两个维度，所以分为以下四种，按照最近一次消费的均值和消费频率的均值定高低界限。
- 重要高价值客户:指最近一次消费较近而且消费频率较高的客户;
  重要唤回客户:指最近一次消费较远且消费频率较高的客户;
  重要深耕客户:指最近一次消费较近且消费频率较低的客户;
  重要挽留客户:指最近一次消费较远且消费频率较低的客户。

计算出r和f的均值

1
2
3

select avg(r) as r_avg,avg(f) as f_avg
from r_value,f_value
where f<>0; -- 因为在划分的时候没有购买过的用户f值给0分，故计算平均得分时去掉这类用户

查询结果如下

avg

r_avg=3.8522;f_avg=1.0396

用户划分成四类

create table user_RF as 
select user_id,
	case 
		when r>3.8522 and f>1.0396 then '重要高价值客户'
		when r<3.8522 and f>1.0396 then '重要唤回客户'
		when r>3.8522 and f<1.0396 then '重要深耕客户'
		when r<3.8522 and f<1.0396 then '重要挽留客户' 
	end as '客户类型'
from r_value
left join f_value
using(user_id);

查询结果如下

user_RF

对各种类型客户计数

查询结果如下

客户类型计数

使用tableau将其可视化结果如下

客户类型条形图

可以看出重要高价值客户在1w人中有294人，约占3.31%。重要唤回客户在1w人中有8人，约占0.09%，比重较低。
可以在后续精细化运营场景中直接使用细分用户，做差异化运营:
- 对高价值客户做VIP服务设计，增加用户粘性同时通过设计优惠券提升客户消费;
- 对深耕客户做广告、推送刺激，提升消费频次;
- 对挽留客户做优惠券、签到送礼策略，增加挽留用户粘性;
- 对唤回客户做定向广告、短信召回策略，尝试召回用户。

学习

#SQL #数据分析 #Tableau

淘宝用户行为分析

https://liaoweiquan.github.io/2023/04/11/淘宝用户行为分析/

作者

泉泉

发布于

2023年4月11日

许可协议

LeetCode数据库题解下一篇