目录:
一. 建表和导入数据
二. 分析用户的消费行为
统计不同月份的下单人数
统计用户三月份的回购率和复购率
统计多次消费的用户,第一次和最后一次消费间隔
统计男女用户的消费频次是否有差异
统计不同年龄段,用户的消费金额是否有差异
根据二八法则,统计消费金额的前 20% 用户贡献的额度
一. 建表和导入数据
- 数据:order_info_utf ,user_info_utf
- 工具:Navicat 和 MySQL
-
建表和导入数据:直接用Navicat导入向导导入。
建表:
orderinfo (订单)表
![]()
orderId 订单ID(主键)
userId 用户ID
isPaid 是否支付
price 订单价格
paidTime 订单支付时间
userinfo (用户)表
![]()
userId 用户ID(主键)
sex 用户性别
birth 用户出生日期
二. 分析用户的消费行为
1. 统计不同月份的下单人数
在订单表中查询,先筛选出 已支付 的用户,然后以月份( 这里年份同为一年) 来分组,再以非重复的用户 ID 来计数
#统计不同月份的下单人数
select month(paidTime) as month, count(distinct userId) as num from orderinfo
where isPaid= '已支付'
group by month(paidTime)
![]()
num 即为当月的下单人数
2. 统计用户三月份的回购率和复购率
-
复购率
在订单表中查询,三月份中,对有消费行为的用户计数,再用条件筛选复购 (当月消费次数大于一次) 的用户,与总的有消费用户相比,得出比率 rate
#统计用户三月份的复购率
select count(if(ct > 1, 1, null)) / count(ct) as rate from
(
select count(userId) ct from orderinfo
where isPaid= '已支付' and month(paidTime)= 3
group by userId
) t
![]()
三月份的复购率为 30.87%
-
回购率
回购率:当月消费用户中有多少在下一月份有消费
在订单表中查询,在四月份中有消费的并且其在三月份中也有消费的用户的计数,与在三月份中有消费的用户的计数相比
#统计用户三月份的回购率
select ct34/ct3 rate from
(select count(distinct userId) ct34 from orderinfo
where isPaid = '已支付' and month(paidTime) = 4
and userId in (select distinct userId from orderinfo
where isPaid = '已支付' and month(paidTime) = 3))t1,
(select count(distinct userId) ct3 from orderinfo
where isPaid = '已支付' and month(paidTime) = 3)t2
![]()
三月份的回购率为 23.94%
3. 统计男女用户的消费频次是否有差异
由于数据中性别有空值,先进行数据清洗
然后先对每一个用户进行频次计数,再求男女用户的平均频次
#统计男女用户的消费频次是否有差异
select sex,avg(ct) as f from
(
select o.userId,sex,count(1) ct from orderinfo o
join (
select * from userinfo
where sex <> 'null') t
on o.userId = t.userId
group by o.userId) t2
group by sex
![]()
男女用户的消费频次基本无差异
无需针对其一类别用户进行维护和管理
4. 统计多次消费的用户,第一次和最后一次消费间隔
在多次消费的用户中,先分别求出第一次和最后一次的消费时间,再求差
与用户生命周期相关
#统计多次消费的用户,第一次和最后一次消费间隔
select userId, datediff(max(paidTime), min(paidTime)) as diff from orderinfo
where isPaid = '已支付'
group by userId having count(1) > 1
5. 统计不同年龄段,用户的消费金额是否有差异
需要连接订单表和用户表来查询,用 case 语句将用户年龄分段,再以年龄段来分组求平均消费金额
select t.cut_ages, round(avg(t.price), 2) avg_price from(
select o.orderId, o.userId, u.ages, cast(o.price as float) price,
case when u.ages between 10 and 19 then '10-19 岁'
when u.ages between 20 and 29 then '20-29 岁'
when u.ages between 30 and 39 then '30-39 岁'
when u.ages between 40 and 49 then '40-49 岁'
when u.ages between 50 and 59 then '50-59 岁'
when u.ages between 60 and 69 then '60-69 岁'
when u.ages between 70 and 79 then '70-79 岁'
else null end as cut_ages
from
(select * from orderinfo
where isPaid= '已支付') o
join
(select userId, (year(now()) - year(birth)) as ages
from userinfo
where birth <> 'null') u
on o.userId= u.userId) t
group by t.cut_ages
having t.cut_ages <> 'null'
order by t.cut_ages
![]()
青少年用户的消费金额较少
中年人用户的消费金额较高
但差异不是十分的大,可在推广中稍侧重在中年用户方面
6. 根据二八法则,统计消费金额的前 20% 用户贡献的额度
计算每个用户的消费总额,再以总额排名,筛选出前 20%
select sum(total) from
(
select *, row_number()over(order by total desc) rn from
(select userId, round(sum(cast(price as float)),2) total from orderinfo
where isPaid = '已支付'
group by userId) t) t2
where rn < (select round(count(distinct userId)*0.2, 0) from orderinfo where isPaid = '已支付')
所有用户的消费金额
![]()
消费金额的 top 20% 用户约占总金额的 85%
这 20% 高价值的用户要进行有针对的客户管理和维护
网友评论