2.3人均页面访问数
人均页面访问数=pv/uv
总体的人均页面访问数=987911/8477=116.54即每个用户平均访问了116.54个页面
2.4跳失率计算：
跳失率：只有点击行为的用户/总用户数
只有点击的用户数为1588
(select distinct user_id from user where behavior_type=2)
and user_id not in
(select distinct user_id from user where behavior_type=3)
and user_id not in
(select distinct user_id from user where behavior_type=4);
跳失率 =1588/8477=18.7%跳失率不高，说明店铺的商品详情页还是能吸引到用户的进行下一步行为。
2.5每日用户pv,uv
(select DATE(time) date,count(behavior_type) pv,count(distinct user_id) from user
where behavior_type=1 group by DATE(time)) a
inner join
(select DATE(time) date,count(distinct user_id) uv from user group by DATE(time))b
on a.date=b.date;
image.png
image.png
3.商品购买情况分析
1.成交量
用户行为是4支付的数量
select DATE(time) date,count(behavior_type)'成交量' from user
where behavior_type=4 group by DATE(time);
image.png
从图中可以看见双十二附近几天成交量较其它日期多。
2.人均购买次数
select count(behavior_type)/count(distinct user_id) '人均购买量' from user
where behavior_type=4;
结果为2.365次
3.复购率
购买两次及以上的用户/总用户数
#总购买用户数
select count(distinct user_id) '用户数'from user
where behavior_type=4;
结果为4330
#购买两次及以上的用户数
select count(*) from
(select count(user_id) '重复购买用户数' from user
where behavior_type=4 group by user_id)a
where a.重复购买用户数>1;
结果为2295
那么复购率=2295/4330=53%
#购买次数最多的10件商品
select item_id,count(behavior_type)'购买次数'
from user where behavior_type=4 group by item_id order by count(behavior_type) desc limit 10;
image.png
#购买次数最多的10位客户
select user_id,count(user_id)'购买次数'from user where behavior_type=4
group by user_id order by count(user_id) desc limit 10;
image.png
4.用户行为转化漏斗分析
select behavior_type,count(behavior_type) from user
group by behavior_type order by count(behavior_type) desc;
image.png
image.png
可见只有不到1%的购物，客户在浏览商品之后出现大量的流失。
image.png
实际购物路线可以有两条：
第一条线路：浏览—加入购物车—购买 或者是
第二条线路：浏览—添加收藏—购买
按照线路一来购买的话，从浏览到加入购物车到购买之间的转化率是多少呢？
购物车转化率=加入购物车后购买的用户数/加入购物车的用户数
select count(distinct a.user_id)as 加入购物车用户数,count(distinct b.user_id)as 加入后购买用户数 from
(select distinct user_id,item_id,item_category,time from user where behavior_type=3)a
left join
(select distinct user_id,item_id,item_category,time from user where behavior_type=4)b
on a.user_id=b.user_id and a.item_id=b.item_id and a.item_category=b.item_category and a.time<b.time;
image.png
购物车转换率=199/5204=3.8%。
进行过点击的用户数为8474
select count(distinct user_id)from user where behavior_type=1;
image.png
从中可以看到用户浏览到加入购物车的转化率是很高的，达到了61.41%，但之后进行支付的转化率却只有3.82%。
推测可能的原因
1.当前商品价格处于高位
2.当前商家活动力度不足
3.为了凑单，进行满减活动。
分析第二条线路。
收藏转化率=添加收藏后购买的用户数/添加收藏的用户数。
select count(distinct a.user_id)as 添加收藏的用户数,count(distinct b.user_id)as 添加收藏后购物的用户数 from
(select distinct user_id,item_id,item_category,time from user where behavior_type=2)a
left join
(select distinct user_id,item_id,item_category,time from user where behavior_type=4)b
on a.user_id=b.user_id and a.item_id=b.item_id and a.item_category=b.item_category and a.time<b.time;
image.png
image.png
对比两条线路，分析出用户更加倾向于购物车购买。
5.RFM模型按照客户价值分类
RFM模型是衡量客户价值和客户创利能力的重要工具。该模型通过客户的最近交易行为（Recency）、交易频率（Frequency）以及交易金额（Monetary）三项指标来描述该客户的价值状况。一般来说，会将这三项指标分成几个区间进行评分，通过计算评分找到有价值的用户，并对用户进行分类。
R最近一次消费(Recency)：是指最近一次消费距离上一次消费之间的时间长短。它反映了客户对产品的态度以及对品牌价值的信任度，它关乎消费者的存留状况。
F消费频率(Frequency)：是指某个特定时间内消费的次数。它直接反映了客户的忠诚度，消费频率越高，忠诚度就越高；忠诚度越高的客户数量越多，公司的竞争优势越强，市场份额越大。
M消费金额(Monetary)：是指一定时间内消费某产品金额。它反映的是客户的价值，价值越高，给公司创造的利益就更大。
因为数据源里没有金额相关的信息，所以只通过R和F来对客户价值进行评分。
目标日期设定为2014-12-18.
R(Recency)最近的购买行为：
数据集都是2014-11-18到12-18的数据，距离目标日期最大为30天。以距离目标的天数划分区间，其中0-10天，11-20天，21-30天，分别对应为1-3分.
#创建R视图
create view R as
select user_id,
(case when 购买天数 between 0 and 10 then 1
when 购买天数 between 11 and 20 then 2
when 购买天数 between 21 and 30 then 3 else 0 end
)as 购买得分
from
(select user_id,datediff('2014-12-18',MAX(DATE(time)))as 购买天数
from user
where behavior_type=4
group by user_id)as a
order by 购买得分;
select* from R;
F(Frequency）购买频率
从复购分析可知客户最大购买次数为82次，将1-82进行分类，1-4次为1,5-8为2,9-12为3,13-82为4.
#创建F视图：
create view F as
select user_id,
(case when 购买次数 between 1 and 4 then 1
when 购买次数 between 5 and 8 then 2
when 购买次数 between 9 and 12 then 3
when 购买次数 between 13 and 82 then 4 else 0 end
)as 购买频率得分
from
(select user_id,count(behavior_type)as 购买次数
from user
where behavior_type=4
group by user_id)b
order by 购买频率得分 desc;
select* from F;
select r.user_id,r.购买得分,f.购买频率得分 from R r left join F f on r.user_id=f.user_id;
image.png
通过上面两个步骤，从两个维度：最近购买时间及购买频率，分别给用户进行了评分。接下来用这两项的每一项平均值作为判断高于还是低于。
image.png
求两项评分的均值：
R平均分
select avg(购买得分) from R;
image.png
F平均分
#F平均得分
select avg(购买频率得分) from F;
image.png
客户分类
#对用户进行分类：
create view users_classify as
select user_id,
(case when R>2 and F>1 then '重要价值用户'
when R>2 and F<=1 then '重要保持用户'
when R<=2 and F>1 then '重要发展用户'
when R<=2 and F<=1 then '一般价值用户' else 0 end
)as 用户类型
from
(select a.user_id,a.购买得分 as R,b.购买频率得分 as F
from R as a inner join F as b
on a.user_id=b.user_id)c;
SELECT * FROM users_classify;
image.png
统计各类用户的数量
#统计各类用户的数量
select 用户类型,count(用户类型) 数量 from users_classify group by 用户类型;
image.png
对于重要价值客户要重点关注并保持，提高留存
对于重要保持用户，他们最近有购买，但购买频率不高，可以通过活动等提高其购买频率；
对于重要发展用户，他们虽然最近没有购买，但以往购买频率高，可以做触达，以防止流失；
对于一般价值用户，他们最近没有购买，以往购买频率也不高，特别容易流失，所以应该赠送优惠券或推送活动信息，唤醒购买意愿。
6.用户留存分析
创建活跃用户表，提取10000条记录分析
create view active_user as
select a.user_id,DATE(a.time) a_dates,DATE(b.time) b_dates
from user a left join user b on a.user_id=b.user_id where DATE(b.time)>DATE(a.time);
create view active_users as
select
* from active_user limit 10000;
select * from active_users;
求出基准日用户数，次日用户数，四日用户数，第八日用户数。
select a_dates,
count(distinct user_id)'基准日用户数',
count(distinct case when datediff(b_dates,a_dates)=1 then user_id else null end)'次日用户数',
count(distinct case when datediff(b_dates,a_dates)=3 then user_id else null end)'四日用户数',
count(distinct case when datediff(b_dates,a_dates)=7 then user_id else null end)'第八日用户数'
from active_users group by a_dates;
image.png
image.png
image.png
7结论
1.双12几天成交量最大，双12活动获客效应明显。
2.复购率达到53%，店铺产品对用户吸引力比较大。
3.交易次数排名前十的客户，对于这些忠实用户，建议要更全面地了解，开发用户信息库，建立详实的用户资料数据库，追踪记录顾客的交易情况。
4.用户购买转化率只有1%左右，造成了很大的用户流失。用户在浏览商品详情页后出现了大量的流失。建议通过活动、优惠券、产品详情页的改进等提高转化。
5.客户从浏览到加入购物车转化率有61.41%，有超过一半的客户有购买意愿，从加入购物车到实际购买转化率却只有3.82%。
建议在用户加入购物车后能有促进用户下单的利益“诱导”，如赠送优惠券或采用倒计时购物车增加客户购买紧近感。
6.客户从浏览到收藏转化率有38.73%，最终购物的转化率为1.25%，低于加入购物车的转化率，多数客户更加倾向于购物车购物方式。
7.从RFM结果看，重要价值客户只有零星几个，对这些客户要重点关注，做好留存，绝大多数客户属于一般价值客户，根据用户类型，进行有针对性的精准营销。
8.从留存分析，2014-12-10日到2014-12-17日这段时间用户的次日留存较高，要抓住双十二活动的机会，搞好获客与留存，对于留存率低的日期，商家要多做一些打折促销，以旧换新等等活动，来吸引客户。
网友评论