美文网首页数据分析
SQL:分析用户消费行为

SQL:分析用户消费行为

作者: hylym | 来源:发表于2020-05-06 15:28 被阅读0次

目录:

一. 建表和导入数据
二. 分析用户的消费行为
  1. 统计不同月份的下单人数

  2. 统计用户三月份的回购率和复购率

  3. 统计多次消费的用户,第一次和最后一次消费间隔

  4. 统计男女用户的消费频次是否有差异

  5. 统计不同年龄段,用户的消费金额是否有差异

  6. 根据二八法则,统计消费金额的前 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% 高价值的用户要进行有针对的客户管理和维护

相关文章

  • SQL:分析用户消费行为

    目录:一. 建表和导入数据二. 分析用户的消费行为统计不同月份的下单人数统计用户三月份的回购率和复购率统计多次消费...

  • sql案例用户消费行为分析

    一、数据导入 1、创建数据库和表: 2、导入csv数据表 命令行导入:load data local infile...

  • 用户消费行为和消费趋势分析案例

    一、项目背景: 这是一份单车网站用户消费行为的分析报告,通过对用户消费记录,深入分析用户消费行为,建立 RFM 模...

  • SQL练习2:用户消费行为分析

    所需数据:ORDER_INFO_UTF.CSV、USER_INFO_UTF.CSV 数据链接:https://pa...

  • SQL练习2:消费者行为分析

    目录:一、将数据导入数据库二、SQL--用户消费行为分析1、统计不同月份的下单人数2、统计用户三月份的回购率和复购...

  • 用户消费行为分析项目(python)

    数据来源:CDNOW网站用户消费购买记录通过对消费数据的分析,了解网站总体运营状况,分析用户消费行为,并建立用户分...

  • 用户消费行为分析

    用python分析用户消费行为,数据来源CDNow网站的用户购买明细。一共有用户ID, 购买日期, 购买数量和购买...

  • 用户消费行为分析

    前言:本文数据量来源于网上,是一份CD的消费数据,数据链接会放在文章最后,请需要者自取。本文分析的主要工具为:Py...

  • 用户消费行为分析

    前言:本文数据量来源于网上,是一份CD的消费数据,数据链接会放在文章最后,请需要者自取。本文分析的主要工具为:Py...

  • 用户行为路径漏斗转化计算分析

    一、分析目的: 结合数据集,根据用户行为路径,制作漏斗转化图,分析用户消费行为各结点转化情况,了解业务和用户现状。...

网友评论

    本文标题:SQL:分析用户消费行为

    本文链接:https://www.haomeiwen.com/subject/soopghtx.html