select min(dates), max(dates) from temp_user;
双十二电商促销活动堪称购物狂欢，淘宝的表现尤其引人注目。深入挖掘淘宝用户的行为数据，可以揭示用户和商品之间的众多奥秘。这些奥秘又可能带来哪些意想不到的发现或启示？
# 基于天
select dates,
count(user_id) pv,
count(distinct user_id) uv,
round(count(user_id) / count(distinct user_id),2) 'pv/uv'
from temp_user
group by dates;
跳失率反映用户粘性
# 基于小时
select hours,
count(user_id) pv,
count(distinct user_id) uv,
round(count(user_id) / count(distinct user_id),2) 'pv/uv'
from temp_user
group by hours;
# 特定日期（12-12）
select hours,
count(user_id) pv,
count(distinct user_id) uv,
round(count(user_id) / count(distinct user_id),2) 'pv/uv'
from temp_user
where dates = '2014-12-12'
group by hours;
衡量APP或网站吸引用户程度的关键指标是跳失率。淘宝的日跳失率相当低，大约只占当天访问者的9%。这一数据说明，淘宝用户对网站的粘性极高，商品对他们的吸引力很强，使得他们愿意在平台上停留并详尽地浏览各种商品。
# 将表关联，筛选出b表的日期大于a表的日期
select * from
(select user_id, dates from temp_user group by user_id, dates) a
left join
(select user_id, dates from temp_user group by user_id, dates) b
on a.user_id = b.user_id
where a.dates <= b.dates;
select a.dates,
count(distinct a.user_id) theday, -- 当天计数
count(if(datediff(b.dates,a.dates)=1,b.user_id,null)) / count(distinct a.user_id) remain1, -- 次日留存率
count(if(datediff(b.dates,a.dates)=7,b.user_id,null)) / count(distinct a.user_id) remain7, -- 七日留存率
count(if(datediff(b.dates,a.dates)=30,b.user_id,null)) / count(distinct a.user_id) remain30 -- 月留存率
from
(select user_id, dates from temp_user group by user_id, dates) a
left join
(select user_id, dates from temp_user group by user_id, dates) b
on a.user_id = b.user_id
where a.dates <= b.dates
group by a.dates;
双十二那天，许多人涌入淘宝。低跳失率表明，淘宝的商品展示和页面布局得到了用户的认可。他们愿意在平台上深入浏览，而非草草离去。这一点从侧面彰显了淘宝平台运营的卓越能力。
漏斗模型分析用户行为
select dates, count(*) '每日跳失数'
from (
select dates, user_id
from temp_user
where behavior_type = 1
group by dates, user_id
having count(behavior_type) = 1
) a
group by dates; -- 存入daily_jump表
数据集记录了用户浏览、收藏、加入购物车和最终购买等行为轨迹，这些流程可以用漏斗模型来剖析。本研究将数据简化为浏览、收藏、加购到购买的漏斗模型，因为收藏和加购是购买前的重要步骤。
# 将表daily_jump与day_pv_uv联结，计算流失率
select dj.dates, 每日跳失数, uv
,round(每日跳失数 / uv,4) '跳失率'
from daily_jump dj join day_pv_uv dpu
on dj.dates = dpu.dates;
双十二这天，浏览后收藏或加购的比例是11.46%，而收藏或加购后再购买的比例是4.65%，这个数字比整个月的平均水平有所增加，但还是显得偏低。这表明从浏览到最终成交，中间有很多步骤需要改进，以便提高用户的购买转化率。
评估用户价值
# 基于日期-小时的用户行为
select dates, hours,
count(if(behavior_type=1,1,null)) pv,
count(if(behavior_type=2,1,null)) fav,
count(if(behavior_type=3,1,null)) cart,
count(if(behavior_type=4,1,null)) buy
from temp_user
group by dates, hours
order by dates, hours;
通常，我们可以根据用户的消费额度、购物频率以及最近一次的购物时间来辨别其价值高低。然而，在这个数据集中，我们并没有消费额度的信息。因此，研究团队采取了用户的最近一次消费记录（记作R）和购买频率（记作F）作为依据，对用户价值进行了一个大致的判断。
根据这两个标准，用户可以被划分为不同的类别。对这些不同类别的用户进行深入分析后，平台能够有针对性地制定营销方案，旨在提升各类用户的购买欲望和购买力。
select behavior_type, count(*) num from temp_user
group by behavior_type;
# +-------------+--------+
# behavior_type |num |
# +-------------+--------+
# |1 |5535879|
# |2 |239472 |
# |3 |331350 |
# |4 |106678 |
# +-------------+--------+
select (239472+331350) / 5535879, 106678 / 5535879;
-- 0.1031;0.0193
# 双十二当天的漏斗模型
select behavior_type, count(behavior_type) num from temp_user
where dates = '2014-12-12'
group by behavior_type;
# +-------------+--------+
# behavior_type |num |
# +-------------+--------+
# |1 |298410 |
# |2 |10309 |
# |3 |23879 |
# |4 |13866 |
# +-------------+--------+
select (23879+10309) / 298410, 13866 / 298410;
-- 0.1146;0.0465
不同用户群体特点及策略
用户近期有过消费，但消费频率并不频繁，大约占了总数的半数。对于这样的用户群体，平台可以实施促销提醒和发放优惠券等策略，以提升他们的消费频率。比如，定期向他们发送专属优惠券，促使他们再次进行购物。
# 用户行为
create view user_behavior_view as
select user_id
,item_id
,count(if(behavior_type = 1,1,null)) pv
,count(if(behavior_type = 2,1,null)) fav
,count(if(behavior_type = 3,1,null)) cart
,count(if(behavior_type = 4,1,null)) buy
from temp_user
group by user_id, item_id;
# 用户行为标准化
create view user_behavior_standard as
select user_id
,item_id
,(case when pv>0 then 1 else 0 end) '浏览了'
,(case when fav>0 then 1 else 0 end) '收藏了'
,(case when cart>0 then 1 else 0 end) '加购了'
,(case when buy>0 then 1 else 0 end) '购买了'
from user_behavior_view
group by user_id, item_id;
# 购买路径类型
# 将行为连接，以此作为字段计数分析：
create view user_behavior_path as
select *
, concat(浏览了,收藏了,加购了,购买了) '购买路径类型'
from user_behavior_standard ubs
where ubs.购买了>0;
# 统计各购买路径数量
select 购买路径类型
, count(*) '数量'
from user_behavior_path
group by 购买路径类型
order by 数量 desc;
近期这些用户消费不多，访问频率也较低，若不尽快采取措施，他们很可能就会离开。因此，平台需要采取措施，比如增加曝光度，不断推送活动信息和优惠内容。可以发送限时抢购的提醒，告知用户有划算的商品在等待购买。
复购率体现用户忠诚度
用户忠诚度可以通过复购率来评判。在淘宝，这个比率超过了90%，这一数据直接反映出消费者的忠诚度极高，同时也表明了淘宝的商品对消费者的吸引力相当强。
# 计算最近购买日期和购买次数
select user_id
, max(dates) '最近购买日期'
, count(user_id) '购买次数'
from temp_user
where behavior_type = 4
group by user_id
order by 2 desc, 3 desc;
## 建立rfm_model表，并添加f_score列
update rfm_model
set f_score= case
when(购买次数 between 0 and 10) then 1
when(购买次数 between 10 and 50) then 2
when(购买次数 between 50 and 100) then 3
when(购买次数 between 100 and 200) then 4
else 5 end;
# 添加r_score列
alter table rfm_model add column r_score int;
update rfm_model
set r_score= case
when 最近购买日期 in ('2014-11-18','2014-11-24') then 1
when 最近购买日期 in ('2014-11-24','2014-11-30') then 2
when 最近购买日期 in ('2014-11-30','2014-12-06') then 3
when 最近购买日期 in ('2014-12-06','2014-12-12') then 4
else 5 end;
淘宝双十二活动的高复购率证明了其成功举办，同时也为活动的运营改进提供了指导。淘宝应把握这一优势，推出更多符合消费者偏好的商品，增强用户忠诚度，激发用户不断购买。
# 对用户分层
set @f_avg = null;
set @r_avg = null; -- 定义用户变量存储平均值，加@以区分列名
select avg(f_score) into @f_avg from rfm_model;
select avg(r_score) into @r_avg from rfm_model;
alter table rfm_model add column class char(4);
update rfm_model set class = case
when f_score > @f_avg and r_score > @r_avg then '价值客户'
when f_score > @f_avg and r_score < @r_avg then '保持客户'
when f_score < @f_avg and r_score > @r_avg then '发展客户'
when f_score < @f_avg and r_score < @r_avg then '挽留客户'
end;
商品四象限分析及优化建议
商品浏览与销量数据难以全面反映商品状况，因此需从浏览和销量两方面对商品进行四象限分类。这种分类方法与RF模型相似，有助于更深入地洞察商品表现。
研究发现，商品销量与浏览量之间的联系并不紧密，多数商品即便浏览量低，销量也偏低，具有较高潜力的商品也不例外。这或许与流量入口和商品自身的问题有关，因此建议首先加大宣传力度，提升商品的曝光度。如果效果仍然不明显，可能是因为商品本身缺乏竞争力，这时可以考虑对商品进行优化或淘汰。而对于有改进空间的商品，我们可以从目标消费者定位、价格调整、商品描述页面的优化、客户服务以及评论区管理等环节入手进行改进。同时，平台也应优化推荐和购买流程，以减少资源浪费，提升用户与商品的互动机会。
select count(if(购买总数>1,a.user_id,null)) 复购人数
, count(if(购买总数>0,a.user_id,null)) 购买总人数
, concat(round(count(if(购买总数>1,1,null)) * 100 / count(if(购买总数>0,1,null)),2),'%') 复购率
from(
select user_id
, count(if(behavior_type = 4,1,null)) 购买总数
from temp_user
group by user_id) a; -- 8117,8886,91.35%
你觉得在淘宝接下来的促销里，哪项改进能够最显著提升消费者的购买意愿？欢迎留言交流你的观点，给文章点赞和转发，帮助更多朋友知晓淘宝双十二的数据内幕！
发表评论