目录
前言
本项目带领大家对阿里移动电商数据集做了初步的数据分析，通过数据分析我们能对业务做出更好的洞察并进一步采取Action。
数据名称：UserBehavior.csv
分析工具：MySQL、Tableau、Nactive
一、项目概述
1、数据介绍
本项目数据来源于阿里云天池《淘宝用户购物行为数据可视化分析》进行研究。
数据链接：淘2宝用户购物行为数据可视化分析_数据集-阿里云天池 (aliyun.com)
本数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：
用户行为详细描述如下：
数据集大小如下：
2、思维导图
二、数据预处理
1、观察数据
将数据文件导入nactive查看数据（连接数据库→新建表→导入数据）。
2、数据导入
由于数据文件中没有表头则用nactive修改数据列名，并将导入nactive的表头的数据重新添加。
由于原数据集记录量过大，选取前10万条数据进行研究
代码如下（示例）：
create table if not exists user_behavior as
(select*from userbehavior limit 100000);
3.数据处理
对数据进行去重处理
代码如下（示例）：
select distinct *from user_behavior
group by customer_id,order_id,category_id,behavoir_type,happening_time;
查看数据缺失值
select count(customer_id),count(order_id),count(category_id),count(behavoir_type),
count(happening_time)from user_behavior;结果记录均为100000条则数据集中并无缺失值
添加新列，对时间戳数据进行转换
#添加 order_dt日期时间列
alter table user_behavior add column order_dt datetime;
update user_behavior set order_dt= from_unixtime(happening_time);
#添加 order_date日期列
alter table user_behavior add order_date date;
update user_behavior set order_date= from_unixtime(happening_time);
#添加 order_dtime日期时间列
alter table user_behavior add order_dtime time;
update user_behavior set order_dtime= from_unixtime(happening_time);异常值处理：将不在2017年11月25号至2017年12月3日的用户行为记录进行剔除。
delete from user_behavior where order_dt not between'2017-11-25'and'2017-12-03';
select count(customer_id),count(order_id),count(category_id),count(behavoir_type),
count(happening_time)from user_behavior;
剔除了13259条，剔除后的数据为86741条。
相关数据进行校对
select max(order_dt),min(order_dt)from user_behavior;相关时间段为
| 2017-12-02 23:59:59 | 2017-11-25 00:00:17 |
三、用户行为分析
1、计算日新增用户
SELECT first_date,COUNT(DISTINCT customer_id) new_uv
FROM (SELECT customer_id,MIN(order_date) first_date FROM user_behavior
GROUP BY customer_id having MIN(order_date) between '2017-11-25' and '2017-12-02') t GROUP BY first_date;| first_date | new_uv |
| 2017/11/25 | 695 |
| 2017/11/26 | 170 |
| 2017/11/27 | 59 |
| 2017/11/28 | 32 |
| 2017/11/29 | 19 |
| 2017/11/30 | 8 |
分析：
从11月25日到12月1日随着时间的增加，日新增人数正在逐渐的减少 ，12月2日没有新增人数。
其中，25日至28日的日新增人数增长比较明显，28日到12月1日的新增人数较少，后续考虑新增人数减少的原因，是否与双12等促销活动有关。
2、计算uv、pv、人均浏览次数反映用户情况
总体uv、pv、人均浏览次数
select count(distinct customer_id) as uv,
(select count(*)from user_behavior where behavoir_type='pv')as pv,
(select count(*) from user_behavior where behavoir_type='pv')/
count(distinct customer_id) as '人均浏览次数'
from user_behavior;
本项目数据源uv为983，pv为77888，人均浏览次数为79.235次
日均UV,PV,人均浏览次数
select t1.order_date,t1.uv,t2.pv,t2.pv/t1.uv '人均浏览次数'from
(select order_date,count(distinct customer_id)as uv from user_behavior group by order_date) t1
join
(select order_date,count(*)as pv from user_behavior where behavoir_type='pv'group by order_date ) t2
on t1.order_date=t2.order_date;
分析：
日均uv、pv均是呈现增长的趋势，从11月25日至11月30日，日均uv、pv增长的波动起伏不明显，从11月30日到12月2日，用户的日均uv、pv出现剧烈的增长，
每日的人均浏览量稳定在13附近波动，说明用户与网站的粘性较好，网站没有出现较大的问题。
12月1日用户的日均uv、pv与人均浏览量突然出现猛增的现象可能由于当天是周五，并且马上将会举行双12的活动，各个品牌方推出了一系列的宣传活动，导致12月初用户的行为情况较为活跃，后面的情况就没有太大的波动。
3、计算客户留存率
首先计算出每个用户首次使用的时间，与使用时间之间的间隔天数。根据间隔天数进行分段，计算出间隔一周各天的留存人数。
#计算出每个用户首次使用时间与使用时间并计算出这两个时间点间隔的天数
select t1.customer_id,t2.first_day,t1.order_date,datediff(order_date,first_day) order_first from (
(select distinct customer_id,order_date from user_behavior) as t1 left join
(select distinct customer_id,min(order_date) first_day from user_behavior group by customer_id) as t2
on t1.customer_id=t2.customer_id);
#根据间隔天数进行分段
select first_day,
sum(case when order_first=0 then 1 else 0 end) as day_0,
sum(case when order_first=1 then 1 else 0 end) as day_1,
sum(case when order_first=2 then 1 else 0 end) as day_2,
sum(case when order_first=3 then 1 else 0 end) as day_3,
sum(case when order_first=4 then 1 else 0 end) as day_4,
sum(case when order_first=5 then 1 else 0 end) as day_5,
sum(case when order_first=6 then 1 else 0 end) as day_6,
sum(case when order_first=7 then 1 else 0 end) as day_7
from
(select customer_id,first_day,order_date,datediff(order_date,first_day) order_first from
(select t1.customer_id,t2.first_day,t1.order_date from
(select distinct customer_id,order_date from user_behavior) as t1
inner join
(select distinct customer_id,min(order_date) first_day from user_behavior group by customer_id) as t2
on t1.customer_id=t2.customer_id)t3)t4
group by first_day order by first_day;
根据留存人数计算出间隔一个周各天留存率。
select first_day,
day_0 as '当日新增人数',
concat(cast((day_1/day_0)*100 as decimal(18,2)),'%') as '次日留存',
concat(cast((day_2/day_0)*100 as decimal(18,2)),'%') as '2日留存',
concat(cast((day_3/day_0)*100 as decimal(18,2)),'%') as '3日留存',
concat(cast((day_4/day_0)*100 as decimal(18,2)),'%') as '4日留存',
concat(cast((day_5/day_0)*100 as decimal(18,2)),'%') as '5日留存',
concat(cast((day_6/day_0)*100 as decimal(18,2)),'%') as '6日留存',
concat(cast((day_7/day_0)*100 as decimal(18,2)),'%') as '7日留存'
from(
select first_day,
sum(case when order_first=0 then 1 else 0 end) as day_0,
sum(case when order_first=1 then 1 else 0 end) as day_1,
sum(case when order_first=2 then 1 else 0 end) as day_2,
sum(case when order_first=3 then 1 else 0 end) as day_3,
sum(case when order_first=4 then 1 else 0 end) as day_4,
sum(case when order_first=5 then 1 else 0 end) as day_5,
sum(case when order_first=6 then 1 else 0 end) as day_6,
sum(case when order_first=7 then 1 else 0 end) as day_7
from
(select customer_id,first_day,order_date,datediff(order_date,first_day) order_first from
(select t1.customer_id,t2.first_day,t1.order_date from
(select distinct customer_id,order_date from user_behavior) as t1
inner join
(select distinct customer_id,min(order_date) first_day from user_behavior group by customer_id) as t2
on t1.customer_id=t2.customer_id)t3)t4
group by first_day)t5 order by first_day;
分析：
11月25日与26日的留存率较为稳定，而从27日开始，大部分天数的留存率变化起伏比较大，可能是因为双十二促销活动带来的游离的用户。
从11月30日以后当日新增人数出现断崖的下降，可能是用户期待着双十二的活动优惠力度增大。
4、计算成交量与复购率
计算出每日的成交数量与复购率
#成交量:
select order_date,count(behavoir_type) buy_count from user_behavior
where behavoir_type='buy'group by order_date order by order_date;
#复购量:
#计算消费次数不小于1的用户:
select distinct customer_id,count(behavoir_type) '消费次数' from user_behavior
where behavoir_type='buy' group by customer_id having 消费次数>=2;
#计算购买用户的总人数:
select count(distinct customer_id)from user_behavior where behavoir_type='buy';
#计算购买次数人数的分布:
select 消费次数,count(customer_id) buyer from (select distinct customer_id,count(behavoir_type) '消费次数' from user_behavior
where behavoir_type='buy' group by customer_id having 消费次数>=2) t
group by 消费次数 order by 消费次数;
#计算复购率:
select
count(distinct customer_id)'复购人数',count(distinct 购买人数) '总购买人数',
(count(distinct customer_id)/count(distinct 购买人数))'复购率'from
(select distinct customer_id,count(behavoir_type) '消费次数'
from user_behavior where behavoir_type='buy' group by customer_id having 消费次数>=2) t,
(select distinct customer_id '购买人数' from user_behavior where behavoir_type='buy')t1 ;
分析：
成交量11月25日至12月2日的成交量平均是在220单左右，并且从11月29日起的成交量相对于前几天是明显的增长，可能是双12 的活动预热，刺激了用户消费。
用户的复购率为61.88%，说明用户的粘性较好，用户的购物体验是不错的，可以满足用户的基本要求。
大部分的用户购买次数是2次和3次，其次购买次数是4次与6次，其中购买次数为2次或者3次的用户约占了全部用户的60.31%。购买次数超过8次的仅约为5.16%
需要对购买次数超过8次的用户进行重点留意，他们有可能变为重要价值客户，对其进行重要的维护。
5、转化率（漏斗模型）
#创建视图
create view ub as
select distinct customer_id,
sum(case when behavoir_type='pv' then 1 else 0 end) if_pv,
sum(case when behavoir_type='fav' then 1 else 0 end) if_fav,
sum(case when behavoir_type='cart' then 1 else 0 end) if_cart,
sum(case when behavoir_type='buy' then 1 else 0 end) if_buy
from user_behavior group by customer_id;
#计算点击人数:
select*from
(select count(customer_id) '点击人数' ,
#计算浏览后加购人数:
sum(case when if_pv>0 and if_cart>0 then 1 else 0 end) as'浏览后加购人数',
#计算加购收藏人数:
sum(case when if_pv>0 and if_cart>0 and if_fav>0 then 1 else 0 end)as'加购收藏人数',
#计算加购收藏购买人数:
sum(case when if_pv>0 and if_cart>0 and if_fav>0 and if_buy>0 then 1 else 0 end)as'加购收藏购买人数'
from ub)t1;
#基于用户购买路径:
#路径:浏览-购买人数
select sum(case when if_buy>0 then 1 else 0 end)'浏览-购买用户数' from ub where if_cart=0 and if_fav=0;
#路径:浏览-收藏-购买人数
select
sum(case when if_fav>0 then 1 else 0 end)'浏览-收藏用户数' ,
sum(case when if_buy>0 and if_fav>0 then 1 else 0 end)'浏览-收藏-购买用户数' from ub where if_cart=0 ;
#路径:浏览-加购-购买人数
select
sum(case when if_cart>0 then 1 else 0 end)'浏览-加购用户数' ,
sum(case when if_buy>0 and if_cart>0 then 1 else 0 end)'浏览-加购-购买用户数' from ub where if_fav=0 ;
#路径:浏览-收藏、加购-购买人数
select
sum(case when if_cart>0 and if_fav>0 then 1 else 0 end)'浏览-加购用户数' ,
sum(case when if_buy>0 and if_cart>0 and if_fav>0 then 1 else 0 end)'浏览-收藏、加购-购买用户数' from ub;
#路径:浏览-收藏或加购-购买人数
select
sum(case when if_cart>0 or if_fav>0 then 1 else 0 end)'浏览-加购用户数' ,
sum(case when if_buy>0 and (if_cart>0 or if_fav>0) then 1 else 0 end)'浏览-收藏、加购-购买用户数' from ub;
分析：
用户添加购物车的转化率约为70.91%，加购后收藏的转化率约为22.89%，说明大多用户都习惯于浏览后加入购物车，而加购后收藏的用户很少，后续应对用户进行相应的画像来促进用户加购后收藏。
用户浏览后直接购买的转化率约为8.44%，说明大部分用户购买商品会基于多方面的考略。可举办一定的活动来刺激用户消费，减少中间复杂的操作。
四、用户喜好分析
1、统计用户活跃时间段
#24小时个时间段的用户行为习惯分布:
#创建视图统计用户使用时段:
create view ut as
select customer_id,category_id,behavoir_type,order_date,order_dtime,hour(order_dtime) dtime
from user_behavior;
#统计各时间段使用用户的人数:
select dtime,count(behavoir_type)'用户行为总数',
sum(case when behavoir_type='pv' then 1 else 0 end) 'pv',
sum(case when behavoir_type='buy' then 1 else 0 end) 'buy',
sum(case when behavoir_type='fav' then 1 else 0 end) 'fav',
sum(case when behavoir_type='cart' then 1 else 0 end) 'cart'
from ut group by dtime order by dtime;
分析：
从19:00开始到22:00，用户行为总数明显增加。在21:00时，用户行为总数达到巅峰，一天之中19:00至23:00时用户最活跃的时间，则可以再这个时间段进行活动推广刺激用户消费。
大部分用户都是点击商品，但对商品的购买、收藏、加入购物车行为的意识不强。
2、统计用户点击、收藏、加购、购买的商品前10种类
#商品种类总数:
select count( distinct category_id)from user_behavior;
#计算点击次数排行前十位商品种类:
select category_id,count(category_id) '点击次数' from user_behavior
where behavoir_type='pv'group by category_id order by count(category_id) desc limit 10 ;
#计算收藏次数排行前十位商品种类:
select category_id,count(category_id) '收藏次数' from user_behavior
where behavoir_type='fav' group by category_id order by count(category_id) desc limit 10 ;
#计算加购次数排行前十位商品种类:
select category_id,count(category_id) '加购次数' from user_behavior
where behavoir_type='cart' group by category_id order by count(category_id) desc limit 10 ;
#计算购买次数排行前十位商品种类:
select category_id,count(category_id) '购买次数' from user_behavior
where behavoir_type='buy' group by category_id order by count(category_id) desc limit 10 ;
流失分许从需求品类与购买品类进行分析
#计算前十商品需求频数以PV指标来衡量:
select t.category_id,t.需求品类频数,
(select count(category_id) from user_behavior
where behavoir_type='buy'and category_id=t.category_id) '购买品类频数',
concat(((select count(category_id) from user_behavior
where behavoir_type='buy'and category_id=t.category_id)*100/t.需求品类频数),'%')'ROI'
from (select category_id,count(category_id) '需求品类频数' from user_behavior
where behavoir_type='pv' group by category_id order by 需求品类频数 desc limit 10) t;
#计算前十商品需求频数以buy指标来衡量:
select a.category_id,a.购买品类频数,
(select count(category_id) from user_behavior
where behavoir_type='pv'and category_id=a.category_id) '需求品类频数',
concat((a.购买品类频数*100/
(select count(category_id) from user_behavior
where behavoir_type='pv'and category_id=a.category_id)),'%')'ROI'
from (select category_id,count(category_id) '购买品类频数' from user_behavior
where behavoir_type='buy' group by category_id order by 购买品类频数 desc limit 10) a;
分析：
需求品类购买最多的category_id为4756105，ROI最高的category_id为3002561，购买品类购买最多的category_id为2735466，ROI最高的category_id为965809。
ROI=用户贡献消费额/花费金额，ROI越高说明转化效果越好，创造的价值也就越高。排名前几的需求品类与购买品类的ROI都不是很高。广告投放的推广效果并不是很好，没有有效的让用户搜索到心仪的物品，使用户的流失严重，转化率不高。
五、用户价值分析
因为数据集中没有消费金额，所以从最近消费间隔时间与消费频率两方面进行分析，只分析RF两个值。R为最近消费间隔时间，F为消费频率，根据这两方面来挖掘更多用户价值。
| R值的要求 | F值的要求 | 得分 |
|---|---|---|
| 2>=R值>=0 | F值>14 | 4 |
| 4>=R值>2 | 14>=F值>10 | 3 |
| 6>=R值>4 | 10>=F值>6 | 2 |
| R值>6 | 6>=F值 | 1 |
#用户价值分析（RFM模型）:
#创建近期消费时间视图，将近期购买时间提取导视图中:
create view lct as
select customer_id,max(order_date) '近期购买时间'from user_behavior
where behavoir_type='buy' group by customer_id;
#建立R等级划分视图，以用户近期购买时间进行等级划分，越接近‘2017-12-03’越大:
create view R等级划分图 as
select customer_id,近期购买时间,datediff('2017-12-03',近期购买时间) 'R' from lct ;
#构建R数值和R字符串型，前者用于后续计算全体用户的平均值，后者用于计算每个等级的数量:
select customer_id,近期购买时间,
case when R <=2 then '4'
when R <=4 then '3'
when R <=6 then '2'
else '1' end 'R得分' from R等级划分图;
# 创建消费频数视图:
create view cf as
select distinct customer_id,count(behavoir_type)'近期消费频数'from user_behavior
where behavoir_type='buy' group by customer_id having count(behavoir_type)>=2;
#建立F等级划分视图，以用户近期购买频率进行等级划分:
create view F等级划分图 as
select customer_id,近期消费频数,
case when 近期消费频数<=6 then '1'
when 近期消费频数<=10 then '2'
when 近期消费频数<=14 then '3'
else '4' end'F得分' from cf ;
#构建RFM模型:
#计算R平均值与F平均值:
select avg(R) 'R得分平均值'from R等级划分图; #2.2552
select avg(F得分) 'F得分平均值'from F等级划分图; #1.1727
#根据用户个人R，F得分与整体R,F得分的平均值进行对比，进行用户分层:
create view RFM as
select
a.customer_id,a.近期购买时间,b.近期消费频数,a.R 'R得分',b.F得分,
(case when a.R >=2.2552 and b.F得分>=1.1727 then '重要高价值客户'
when a.R <2.2552 and b.F得分>=1.1727 then '重要保持客户'
when a.R >=2.2552 and b.F得分<1.1727 then '重要发展客户'
else '重要挽留客户' end)'客户分类'
from R等级划分图 a,F等级划分图 b where a.customer_id=b.customer_id;
#客户分类频数分布:
select 客户分类,count(客户分类)'频数'from rfm group by 客户分类;
分析：
大部分用户是位于重要挽留客户，重要挽留客户占比为59.54%。重要挽留客户主要是已经或即将流失的的用户，针对这种情况可以进行问卷调查来找到流失用户的原因。或者，后续根据用户画像来为用户提供相对应的服务，增加用户粘性。
其次，占比最多的是重要发展客户，重要发展客户占比28.61%。重要发展客户具有极大的发展潜能，这部分用户可以根据其的消费习惯制定方案，使其转化为重要保持客户。
占比最少的是重要高质量客户，其占比为1.55%。重要高质量客户具有很高的粘性，应根据重要保持客户的特点实施方案，将更多重要保持客户转换为重要高质量客户。
六、总结分析
1、实时监控活动的数据，及时调整活动营销策略
根据uv,pv,ren人均浏览量，可知双十二活动将近用户的行为逐渐活跃。在活动的预热，如凑单满减，组队赢取奖金等预热活动中，要实时管控数据趋势，观测出用户反馈情况，根据数据及时制订销售的策略，刺激用户的消费心态。
2、根据用户行为习惯，做出优惠活动
根据用户活跃时间19：00-23:00，为用户推送画像出用户感兴趣的商品，并说明商品的折扣情况，吸引用户的兴趣。根据用户点击、收藏、加入购物车、购买前10的商品，进行适当的折扣，如买多少，有部分折扣，促进用户的消费。
3、根据用户价值，制定合适的营销
对重要流失客户提供评价好，价格低的商品，来使用户发展成重要发展客户。对重要发展用户，进行针对性的营销，将其发展成重要保持客户。对重要保持客户，提供签到领取现金的手段使其发展成重要高质量客户。
8308
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
