SQL 电商用户，商品，平台价值分析

探索用户行为规律，寻找高价值用户

分析商品特征，寻找高贡献商品

分析产品功能，优化产品路径

日期数据处理

1.字符串日期转换

alter table o_retailers_trade_user add column date_time datetime null;

update o_retailers_trade_user
set date_time =str_to_date(time,'%Y-%m-%d %H') ;

2.保留年月日日期

alter table o_retailers_trade_user add column dates char(10) null;

update o_retailers_trade_user
set dates=date(date_time);

人

1.基于漏斗模型的用户购买流程分析各环节指标，确定各环节的转化率，找到需要改进的环节

需求1：计算uv，pv，浏览深度（按日）统计

select dates ,
count(distinct user_id) as uv ,
count(if(behavior_type=1,user_id,null)) as pv,
count(if(behavior_type=1,user_id,null))/count(distinct user_id) as 'pv/uv'
from temp_trade tt group by dates order by dates;

 需求2：计算用户留存，用户留存率

按日期分组每天的不同用户数量进行统计

创建视图计算用户1，2，3，4，5，6，7，15，30日的留存数

create view temp_table_view as(
select a.dates,
count(distinct b.user_id) as device_v,
count(if(datediff(b.dates,a.dates)=0,b.user_id,null)) as device_v_remain0,
count(if(datediff(b.dates,a.dates)=1,b.user_id,null)) as device_v_remain1,
count(if(datediff(b.dates,a.dates)=2,b.user_id,null)) as device_v_remain2,
count(if(datediff(b.dates,a.dates)=3,b.user_id,null)) as device_v_remain3,
count(if(datediff(b.dates,a.dates)=4,b.user_id,null)) as device_v_remain4,
count(if(datediff(b.dates,a.dates)=5,b.user_id,null)) as device_v_remain5,
count(if(datediff(b.dates,a.dates)=6,b.user_id,null)) as device_v_remain6,
count(if(datediff(b.dates,a.dates)=7,b.user_id,null)) as device_v_remain7,
count(if(datediff(b.dates,a.dates)=15,b.user_id,null)) as device_v_remain15,
count(if(datediff(b.dates,a.dates)=30,b.user_id,null)) as device_v_remain30
from (select dates,user_id from temp_trade tt group by user_id,dates order by dates) a left join 
(select dates,user_id from temp_trade tt group by dates,user_id order by dates) b on a.user_id=b.user_id group by a.dates);

根据视图计算1，2，3，4，5，6，7，15，30日的留存率

select dates ,
device_v_remain0,
concat(cast((device_v_remain1/device_v_remain0)*100 as DECIMAL(18,2)),'%') as 'day_1%',
concat(cast((device_v_remain2/device_v_remain0)*100 as DECIMAL(18,2)),'%') as 'day_2%',
concat(cast((device_v_remain3/device_v_remain0)*100 as DECIMAL(18,2)),'%') as 'day_3%',
concat(cast((device_v_remain4/device_v_remain0)*100 as DECIMAL(18,2)),'%') as 'day_4%',
concat(cast((device_v_remain5/device_v_remain0)*100 as DECIMAL(18,2)),'%') as 'day_5%',
concat(cast((device_v_remain6/device_v_remain0)*100 as DECIMAL(18,2)),'%') as 'day_6%',
concat(cast((device_v_remain7/device_v_remain0)*100 as DECIMAL(18,2)),'%') as 'day_7%',
concat(cast((device_v_remain15/device_v_remain0)*100 as DECIMAL(18,2)),'%') as 'day_15%',
concat(cast((device_v_remain30/device_v_remain0)*100 as DECIMAL(18,2)),'%') as 'day_30%'
from temp_table_view;

需求3：建立R视图，计算每个用户的最近一次购买时间

create view user_recency as
select user_id ,
max(dates) as rec_buy_time  
from temp_trade tt where behavior_type =2 group by user_id order by rec_buy_time;

 需求4：建立R等级视图，将用户最近一次购买时间进行等级划分，越靠近12-18R越大

create view r_clevel as
select user_id,
rec_buy_time ,
datediff('2019-12-18',rec_buy_time) as 'rec_num',
case 
	when datediff('2019-12-18',rec_buy_time)<=2 then 5
	when datediff('2019-12-18',rec_buy_time)<=4 then 4
	when datediff('2019-12-18',rec_buy_time)<=6 then 3
	when datediff('2019-12-18',rec_buy_time)<=8 then 2
else 1 end as r_value
from user_recency ur ;

 需求5：建立F视图，计算每个用户消费的次数

create view frenq_value as
select user_id,
count(user_id) as buy_frenq 
from temp_trade tt where behavior_type =2 group by user_id ;

 需求6：建立F等级视图，将购买频次划分等级，频次越高等级越高

create view f_clevel as
select user_id,buy_frenq,
case
	when buy_frenq<=2 then 1
	when buy_frenq<=4 then 2
	when buy_frenq<=6 then 3
	when buy_frenq<=8 then 4
else 5 end as f_values
from frenq_value ;

需求8：整合R,F结果对用户进行分类

重要高价值用户：最近一次消费较近且消费频次高的用户

重要召回用户：最近一次消费较远但消费频次高的用户

重要深耕用户：最近一次消费较近但消费频次低的用户

重要挽留用户：最近一次消费较远且消费频次低的用户

较近较高的比较都是相对于平均值

create view RFM_inall as 
select rc.*,fc.f_values,
case 
	when rc.r_value > 2.7939 and fc.f_values > 2.2606 then '重要高价值客户'
	when rc.r_value < 2.7939 and fc.f_values > 2.2606 then '重要召回客户'
	when rc.r_value > 2.7939 and fc.f_values < 2.2606 then '重要深耕客户'
	when rc.r_value < 2.7939 and fc.f_values < 2.2606 then '重要挽留客户'
end as user_class
from r_clevel rc ,f_clevel fc where rc.user_id =fc.user_id;

 

货

2.找出热销商品，研究热销商品特点

需求9：计算商品的点击量，收藏量，加购量，购买次数，购买转化

select item_id ,
sum(case when behavior_type=1 then 1 else 0 end) as 'pv',
sum(case when behavior_type=4 then 1 else 0 end) as 'fav',
sum(case when behavior_type=3 then 1 else 0 end) as 'cart',
sum(case when behavior_type=2 then 1 else 0 end) as 'buy',
count(distinct if(behavior_type=2,user_id,null))/count(distinct user_id) as 'buy_rate'
from temp_trade 
group by item_id 
order by buy desc;

 对应品类的统计

 场

3.基于RFM模型找出核心付费用户群，对这部分用户进行精准营销

需求10：每天的点击，收藏，加购，购买次数及购买转化率

select dates,count(1) as '每⽇的总数',
sum(case when behavior_type=1 then 1 else 0 end) as 'pv',
sum(case when behavior_type=4 then 1 else 0 end) as 'fav',
sum(case when behavior_type=3 then 1 else 0 end) as 'cart',
sum(case when behavior_type=2 then 1 else 0 end) as 'buy',
count(distinct if(behavior_type=2,user_id,null))/count(distinct user_id) as 'buy_rate'
from temp_trade 
group by dates ;

 需求11：创建最终行为是购买的用户的行为路径视图

create view product_user_way as
select a.* from 
(select user_id ,item_id, 
lag(behavior_type,4) over (partition by user_id,item_id order by date_time) lag_4,
lag(behavior_type,3) over (partition by user_id,item_id order by date_time) lag_3,
lag(behavior_type,2) over (partition by user_id,item_id order by date_time) lag_2,
lag(behavior_type,1) over (partition by user_id,item_id order by date_time) lag_1,
behavior_type ,
rank() over (partition by user_id,item_id order by date_time desc) as 'rank_dn'
from temp_trade ) a where a.rank_dn=1 and a.behavior_type =2;

 需求12：统计每种路径的人数

select a.user_way,count(distinct a.user_id) as user_count from 
(select 
concat(ifnull(lag_4,'空'),'-',ifnull(lag_3,'空'),'-',ifnull(lag_2,'空'),'-',ifnull(lag_1,'空'),'-',behavior_type) as user_way,
user_id
from product_user_way) a group by a.user_way order by user_count desc;

 总结

1.每日uv在12-12日有上升，uv周环比在12-12前后略有下降，系平台活动所致。

2.对不同的用户采用不同的营销策略，如：对⾼价值客户做VIP服务设计，增加⽤户粘性同时通过设计优惠券提升客户消费；对深耕客户做⼴告、推送刺激，提升消费频次；对挽留客户做优惠券、签到送礼策略，增加挽留⽤户粘性；对唤回客户做定向⼴告、短信召回策略，尝试召回⽤户。

3.商品类别5027和5399购买转化率偏低，需要结合更多数据做进一步解读。

4.发现用户多以直接购买为主，加购在主要购买路径中数量较少。后续加购，收藏等功能还需要结合更多数据改进方案。