淘宝用户行为分析（SQL）

一、数据说明

数据集是阿里巴巴提供的一个淘宝用户行为数据集，数据集包含了2017年11月18日至2017年12月18日之间，用户行为数据约80多万条记录。

user_id：用户ID，item_id：商品ID，item_category：商品类目ID

behavior_type：用户行为，分别以1，2，3，4代表点击，收藏，加入购物车，支付

user_geohash:用户地理位置，time：时间戳

二、分析目标

通过对上述数据集中用户行为数据分析，找出用户群体在整个购物过程中的转化率，找出影响用户下单的因素，并针对此提出一些改进的建议。

分析思路

1、分析用户不同的日期，时段的pv，uv，以及成交量

2、分析用户在不同的日期，时段的用户行为习惯

3、从业务流程去考虑，各流程的转化率和用户最终下单之间的影响因素（漏斗分析）

4、分析用户支付率、复购率和购买次数，提高用户粘性

5、进行用户分层（RFM模型）找出最具有价值的核心用户群体，并采取相应的措施

6、分析商品的销售情况，哪些商品最畅销，优化商品营销策略

三、数据清洗

-全字段去重

create table taobao1 as SELECT * FROM taobao GROUP BY User_id,Item_id,item_category,behavior_type,time HAVING COUNT(*)=1;

-缺失值查询

SELECT count(User_ID),count(Item_ID),count(item_Category),count(Behavior_type),count(time) FROM taobao1;

-修改时间格式

ALTER TABLE taobao1 ADD COLUMN Date varchar(10) NULL;

UPDATE taobao1 SET Date = DATE_FORMAT(time,'%Y-%m-%d');

ALTER TABLE taobao1 ADD COLUMN hour varchar(10) null;

UPDATE taobao1 SET hour = date_format(time,'%H');

-查询异常值

SELECT MAX(Date),MIN(Date) FROM taobao1;

四、数据分析

1、pv、uv、成交量

------整体pv/uv/成交量概览

SELECT sum( CASE WHEN Behavior_type='1' THEN 1 ELSE 0 END) AS 'pv',COUNT(DISTINCT User_ID) AS 'uv',sum(CASE WHEN Behavior_type ='4' THEN 1 ELSE 0 END) AS '成交量' FROM taobao1;

------每日pv/uv/成交量

SELECT date,sum( CASE WHEN Behavior_type='1' THEN 1 ELSE 0 END) AS 'pv',COUNT(DISTINCT User_ID) AS 'uv',sum(CASE WHEN Behavior_type ='4' THEN 1 ELSE 0 END) AS '成交量' FROM taobao1 group by date order by date;

结果显示如上图所示，在双十二期间，pv和uv访问量达到峰值，并且可以发现，uv和pv两个访问量数值差距比较大。

-------每小时pv/uv/成交量

SELECT hour,sum( CASE WHEN Behavior_type='1' THEN 1 ELSE 0 END) AS 'pv',COUNT(DISTINCT User_ID) AS 'uv',sum(CASE WHEN Behavior_type ='4' THEN 1 ELSE 0 END) AS '成交量' FROM taobao1 group by hour order by hour;

结论：

pv和uv在凌晨0-5点期间因为用户大多都在休息，所以波动情况相同，都呈下降趋势，访问量都比较小，同时在晚上18：00左右，pv波动情况比较剧烈，相比来看uv不太明显，因此晚上18：00以后是淘宝用户访问app的活跃时间段

-----每日人均访问深度（pv/uv）

select date,sum( CASE WHEN Behavior_type='1' THEN 1 ELSE 0 END) AS 'pv',COUNT(DISTINCT User_ID) AS 'uv',concat(round(count(distinct user_id)/sum( CASE WHEN Behavior_type='1' THEN 1 ELSE 0 END)*100,2),"%")'访问深度' from taobao1 group by date;

2、用户行为习惯

-------用户行为习惯（按天）

select date,sum(case when Behavior_type='1' THEN 1 ELSE 0 END) AS '点击',sum(case when Behavior_type='2' THEN 1 ELSE 0 END) AS '收藏',sum(case when Behavior_type='3' THEN 1 ELSE 0 END) AS '加购',sum(case when Behavior_type='4' THEN 1 ELSE 0 END) AS '支付' from taobao1 group by date order by date;

结论：

除了双十二活动，吸引了大量客户去参与。点击，收藏，加购在1双十二前几天显著增长，这可能是双十二预热活动，可能由于双十二活动导致用户开始大量浏览商品。购买并未有明显增幅，是双十二批量购买时的前置动作。

-------用户行为习惯（按时段）

select hour,sum(case when Behavior_type='1' THEN 1 ELSE 0 END) AS '点击',sum(case when Behavior_type='2' THEN 1 ELSE 0 END) AS '收藏',sum(case when Behavior_type='3' THEN 1 ELSE 0 END) AS '加购',sum(case when Behavior_type='4' THEN 1 ELSE 0 END) AS '支付' from taobao1 group by hour order by hour;

结论：

各时段指标走势大致一致。5:00-10:00及18:00-21:00两段时间是用户活跃度迅速增长的时段，10:00-18:00这个时间段的用户活跃度上下浮动，但基本保持着同一水平。

13:00，15:00这两个时间段是用户活跃度稳定阶段的两个小高峰，我们可以猜测13:00是因为用户工作午休结束了拿起手机再摸一会儿鱼造成的，15:00是因为工作了一两个小时又想摸鱼。17:00开始一部分用户下班了或马上下班，可以开始正大光明摸鱼。21:00达到用户操作量峰值，一些广告推广或者活动营销可以在18:00-21:00这段时间加大力度；

3、行为转化率（漏斗分析：点击-加购-收藏-购买）

数据集收藏比加购少，漏斗路径不明确，故按加购在收藏前开展分析

create table pv_buy as select (case Behavior_type when '1' then '点击' when '3' then "加购" when '2' then '收藏' else '支付' end)'用户行为' ,count(*)'数量' from taobao1 group by Behavior_type;

结论：

用户点击后，收藏或者加购物车的概率在3.4%左右，而最后真正的购买概率在1.2%,也就是说用户的行为在浏览商品详情页后出现了大量的流失，建议优化广告，进行算法优化，推荐用户感兴趣产品。

4、用户支付率、复购率和购买次数

-------支付率

select round((select count(distinct user_id) from taobao1 where Behavior_type ='4')/(select count(distinct user_id) from taobao1),1)'支付率';

-------复购率

CREATE VIEW behavior AS

SELECT User_ID,COUNT(Behavior_type) AS '用户行为数',

sum(CASE WHEN Behavior_type ='1' THEN 1 ELSE 0 END ) AS '点击',

sum(CASE WHEN Behavior_type ='2' THEN 1 ELSE 0 END) AS '收藏',

sum(CASE WHEN Behavior_type ='3'THEN 1 ELSE 0 END) AS '加购',

sum(CASE WHEN Behavior_type ='4' THEN 1 ELSE 0 END) AS '购买' from taobao1 GROUP BY User_ID;

SELECT sum(CASE WHEN 购买>1 THEN 1 ELSE 0 END ) AS 复购次数,sum(CASE WHEN 购买>0 THEN 1 ELSE 0 END ) AS 购买次数,CONCAT(ROUND(sum(CASE WHEN 购买>1 THEN 1 ELSE 0 END)*100/ sum(CASE WHEN 购买>0 THEN 1 ELSE 0 END),2),'%')'复购率'FROM behavior;

-------appru(人均购买次数：次数代替金额)

select round(count(*)/(select count(distinct user_id) from taobao1 where Behavior_type ='4'),1)'人均购买次数' from taobao1 where Behavior_type ='4';

-------用户购买次数分布

create view buy as select user_id,count(*)buy_num from taobao1 where Behavior_type='4' group by user_id;

select buy_num,count(*) from buy group by buy_num order by count(*) desc;

结论：

淘宝用户复购率达到52%，但是进一步查看人均购买次数，发现绝大多数用户复购次数很少，淘宝的付费用户忠诚度较低。

• 在当前的电商平台中，淘宝已有很大的市场份额，能够增加的新用户有限，目前的重心应该放在用户黏性和营收的增长上。购买一次可能是被吸睛的标题、精美的图片或是诱人的营销活动吸引，而复购就要求产品质量过关、服务到位，消费者对第一次的购物体验很满意才会进行第二次,所以淘宝可以重点在这些方面帮助商家。

5、用户分层（RFM模型）

-------用户分层（RFM模型；本数据无金额M，只考虑RF俩维度）

# 1-留存，0-流失；1-高频，0-低频

create view a as select user_id,timestampdiff(day,date_format(max(date),'%y-%m-%d'),date_format('2014-12-20','%y-%m-%d')) 'recent',count(*) 'freq' from taobao1 where behavior_type='4' group by user_id;

create view rf as select a.*,(case when recent>(select avg(recent) from a) then 1 else 0 end)recent_value,(case when freq>(select avg(freq) from a) then 1 else 0 end)freq_value from a;

create view aa as select rf.*,concat(recent_value,freq_value)RF from rf;

select rf, (case when RF='10' then '重要发展客户' when RF='01' then '重要保持客户' when RF='00' then '重要挽留客户' else '重要价值客户' end)'用户类型',count(*)'用户数' from aa group by rf;

结论：

重要价值用户是重点关注的对象，所以可以有针对性地给这类客户提供 VIP服务，比如现在的淘宝VIP会员卡等等。

重要挽留客户占比最大，对于这类客户，最近消费时间间隔较远，并且消费频次低，我们需要主动联系客户，调查清楚哪里出现了问题，比如通过APP推送等唤醒客户。

重要发展客户，消费频次低，我们需要提升他的消费频率，可以通过优惠券叠加或捆绑销售等活动来刺激消费；

重要保持客户，消费时间间隔较远，但是消费频次高，对于这类客户，需要主动联系，了解客户的需求，及时满足这类用户的需求。

6、商品销售情况

------品类销量TOP10（考虑点击购买转化率）

 select item_category,sum(CASE WHEN Behavior_type ='1' THEN 1 ELSE 0 END ) AS '点击',

sum(CASE WHEN Behavior_type ='2' THEN 1 ELSE 0 END) AS 收藏,

sum(CASE WHEN Behavior_type ='3'THEN 1 ELSE 0 END) AS 加购,

sum(CASE WHEN Behavior_type ='4' THEN 1 ELSE 0 END) AS 购买

from taobao1 GROUP BY item_category order by 购买 desc limit 10;

结论：

在购买率前十的商品类目中，分布比较集中，基本上转化率集中在(0,0.2]这个区间，而绝大多数转化率在0.1以内。

这类商品转化率低可能因为同一类商品包含很多种商品，用户存在“货比三家”的心理，用户对于需要的该类商品可能不会购买多种商品，而是在该类商品中购买具体的一个商品。