r语言月度消费频次_MySQL分析用户消费行为

青旅半醒 2023-01-03 14:12 67阅读 0赞

MySQL是关系型数据库，SQL语言可以很好地操作数据库并进行数据分析。现对用户消费数据进行初步分析，数据来源于某网站的销售记录，包含2张表：订单信息表和用户信息表。

下面内容为使用SQL进行的实际案例分析，创建数据库、对数据的处理实现实际业务分析，包含4部分：创建数据库，创建表，导入数据、业务分析。

一、创建数据库

create database 数据库名;

二、创建表

create table 表名( 字段名  类型  约束 );

三、导入数据

a，可通过workbench操作导入，导入时间过长。

b，通过命令行窗口执行语句导入数据，速度快。

![09452537d551a8d1b710cbf835796560.png][]

订单信息表：

select * from orderinfo limit 10;

![d784006e5f366e8aad7fe8bd7c3609be.png][]

用户信息表：

select * from userinfo limit 10;

![4d7880fa07b9c7c735a233921a062dff.png][]

四、业务分析

1、统计不同月份的下单人数。（用户人数）

首先观察业务数据，类似每月消费人数，每月消费金额等较为关注的指标数据，这些指标一定程度上可以反映用户的留存情况或者是运营效果。

select month(paidtime) as 月份,count( distinct userid) as 人数, 
    round(sum(price))/10000 as 消费金额万元 
    from orderinfo 
    where ispaid='已支付' 
    group by month(paidtime) ;

![44a2db5ce85609f6dfd66859e0851300.png][]

2、统计男女用户的消费频次是否有差异。（用户属性差异）

select userinfo.sex 性别,date_format(paidtime,'%Y-%m') 月份, 
    round(sum(price)/count(distinct orderinfo.userid)) as 平均每人消费金额,
    count(price)/count(distinct orderinfo.userid) as 平均每人购买次数  
    from orderinfo inner join userinfo on orderinfo.userid=userinfo.userid
    where ispaid='已支付'  and sex <>" " 
    group by userinfo.sex,date_format(paidtime,'%Y-%m')
    order by 月份;

![dc474fb1bfa44eb32563e3e0668c7bce.png][]

对不同属性（性别）的用户消费进行分析，可以看出男女用户的消费频次和消费金额差异不大，说明基本需求差异也不大。

3、统计用户三月份的回购率和复购率。（消费频次）

对复购率统计，可以看出用户消费的时间频次，用于针对性地推广、以尽可能少的成本实现有效的运营效果。

对回购率统计，可以看出用户的留存情况，通过挖掘背后的原因，可以反映运营的效果、产品功能质量，也可能发现新的增长点，实现营收。

#三月份，用户的回购率
    select count(m1) as 3月份消费人数 ,count(m2) 3、4月份都消费的人数,
    concat(round((count(m2)/count(m1))*100,2),"%") as 回购率 
    from
    (select userid, date_format(paidtime,'%Y-%m-01') as m1
    from orderinfo
    where ispaid='已支付'  and month(paidtime)=3 
    group by  userid,date_format(paidtime,'%Y-%m-01')) t1
    left join 
    (select userid, date_format(paidtime,'%Y-%m-01') as m2
    from orderinfo
    where ispaid='已支付'  
    group by  userid,date_format(paidtime,'%Y-%m-01') ) t2  
    on t1.userid=t2.userid  and t1.m1=date_sub(t2.m2,interval 1 month);

![5447198934bbab12f21749fd5523a455.png][]

#三月份，用户的复购率
    select  count(ct) as 总人数, count(if(ct>1,1,null)) as 购买2次及以上的人数,
    concat(round((count(if(ct=1,null,1))/count(*))*100,2),"%") as 复购率
    from
    (select userid, count(price) as ct
    from orderinfo
    where ispaid='已支付'  and month(paidtime)=3 
    group by  userid ) t;

![88c40a59c664d017789505aa63c732df.png][]

4、统计多次消费的用户，首购和最后一次消费的间隔。

函数：date\_diff( )，时间函数，可用于计算两个日期相隔天数。

select userid, datediff(max(Paidtime),min(Paidtime)) as 间隔天数
    from orderinfo
    where isPaid="已支付" and Paidtime>0
    group by userid
    having count(orderid)>1 ;

![7116745b0df588e949a95aecd00904d6.png][]

对用户分组，查看其消费时间，间隔天数长的用户算是忠诚用户了，可以调研询问他们喜欢的功能或者是哪一点，反馈的结果有可能是当初开发或设计时并不太关注的某个点。对间隔天数短的用户做问卷调查，可以了解引起高流失率的原因，以便做出改善，提高营收。

拓：假定这份数据集记录了某段时间新增用户在后续时间的消费情况，想要查看所采取的营销策略效果如何，运行下面代码可知，用户流失率为71.99%，说明当前运营效果不好或者是产品质量功能有缺陷等，因此需要根据结果采取新策略或改善产品功能。

#生命周期为0的用户数占比
    select count(t1.userid) as 总人数,count(t2.userid) 生命周期为0的人数,
    concat(round((count(t2.userid)/count(t1.userid))*100,2),"%") as 生命周期为0的用户占比
    from 
    
    ((select distinct userid  from orderinfo
    where isPaid="已支付" and Paidtime>0)  t1   #总消费人数
    left join 
    (select userid
    from orderinfo
    where isPaid="已支付" and Paidtime>0
    group by userid
    having datediff(max(Paidtime),min(Paidtime))=0 ) t2  #生命周期为0的人数，包括同一天消费一次或多次
    on t1.userid=t2.userid)

![687d36e0e072108cad0312ca7199ce11.png][]

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

对不同年龄段的用户消费金额进行统计，筛选出重点客户，可用于用户的分层与画像中，为用户运营提供依据。

select concat(floor((year(now())-year(birth))/10)*10,"~",ceil((year(now())-year(birth))/10)*10,"岁") as 年龄段,
    round(sum(price)/10000) as 消费金额万元
    from orderinfo  inner join userinfo on orderinfo.userid=userinfo.userid
    where isPaid="已支付" and Paidtime>0 and birth>0  and ceil((year(now())-year(userinfo.birth))/10)<10
    group by ceil((year(now())-year(birth))/10)
    order by 消费金额万元 desc;

![f0ba97d8bce3422741e67b4013f0ae36.png][]

6、统计消费的二八法则，消费的top20%用户，贡献了多少额度。

为了增加收入，根据客户的消费金额，标记出重点客户，实行精细化运营。

# 20%的消费人数是多少
    select count(distinct userid) as 总消费人数,round(count(distinct userid)*0.2) as 百分之二十的人数
    from orderinfo
    where isPaid="已支付" ;

![1730e79745c12cba70c770c8c0439619.png][]

select count(t1.userid) as 总人数, count(t2.userid) as 百分之20的人数,
    round(sum(total1)) as 总消费, round(sum(total2)) as 百分之20的消费金额, round(sum(total2)/sum(total1),2) as 消费占比
    from 
    (select userid,sum(price) as total1
    from orderinfo
    where  ispaid="已支付"
    group by userid) t1
    left join 
    (select userid,sum(price) as total2
    from orderinfo
    where  ispaid="已支付"
    group by userid
    order by  total2 desc
    limit 17130) t2 on t1.userid=t2.userid;

![2bff9dc05748b85d970d320daa3e8dd4.png][]

可知，20%的客户贡献了85%的消费额，验证了二八定律，这也是运营时要重点关注这20%的客户，进一步对这部分客户进行精细化运营，提高收入。

[09452537d551a8d1b710cbf835796560.png]: /images/20221119/71008dff03bc4e4bba84ea899ed2c0d2.png
[d784006e5f366e8aad7fe8bd7c3609be.png]: /images/20221119/a3e04f462cb14328aecd4bfc6e450e2f.png
[4d7880fa07b9c7c735a233921a062dff.png]: /images/20221119/8b89dd8f4e4b4e358269f9a151bcae22.png
[44a2db5ce85609f6dfd66859e0851300.png]: /images/20221119/4475c9ae65ab4813a8a0767c9ace9834.png
[dc474fb1bfa44eb32563e3e0668c7bce.png]: /images/20221119/fb5481a4ed5a4d4bae6885ed66efda1d.png
[5447198934bbab12f21749fd5523a455.png]: /images/20221119/34f9610dbd154f23928a91c36f7e76de.png
[88c40a59c664d017789505aa63c732df.png]: /images/20221119/1e340864d58545cba15363c93d6cdde2.png
[7116745b0df588e949a95aecd00904d6.png]: /images/20221119/03e5460f38cf467b89f0fe0ac792e9bc.png
[687d36e0e072108cad0312ca7199ce11.png]: /images/20221119/744b439021cb412a9d011648fcd8813c.png
[f0ba97d8bce3422741e67b4013f0ae36.png]: /images/20221119/327117eb4a1d4178aeaaf8264d969024.png
[1730e79745c12cba70c770c8c0439619.png]: /images/20221119/b6050c81ed0b4577a2a16c4f83e62c7f.png
[2bff9dc05748b85d970d320daa3e8dd4.png]: /images/20221119/b680211b53d640a58353633cc08a7ea9.png