美文网首页python数据库
用MYSQL分析网络销售案例

用MYSQL分析网络销售案例

作者: 雅_2f4f | 来源:发表于2019-09-30 16:47 被阅读0次

数据来源于某网站的销售统计,主要分为两部分:
1、网络订单数据;
2、用户信息。
数据来源:链接:https://pan.baidu.com/s/1urm8NR2hvlCsKK2Ip9uoTw
提取码:qj4b

阅读路线:

0、数据导入
1、不同月份的下单人数
2、用户三月份的回购率和复购率
3、统计男女用户的消费频次
4、统计多次消费用户,分析第一次和最后一次的消费间隔
5、统计不同年龄段用户的消费金额差异
6、统计消费的二八法则:消费top20%的用户贡献了多少消费额度

0、数据导入

  • userID为主键,且不能为空;
  • Birth生日设置为DATE类型。

导入后各个表数据如下所示:


image.png image.png

1、不同月份的下单人数

思路:对orderinfo按月分组并对userID去重、计数。

select date_format(paidtime,'%Y-%c')as dtmonth,
count(distinct userID) as count_users
from orderinfo
where ispaid='已支付'
group by date_format(paidtime,'%Y-%c')

运算结果:


image.png

2、用户三月份的复购率和回购率

复购率:自然月内,购买多次的用户占比
回购率:曾经购买过的用户在某一时期内再次购买的占比

2.1 用户复购率计算:

首先,计算三月份每个用户的购买次数:

select userID,count(userID)as ct
from orderinfo
where month(paidtime)=3 and IsPaid='已支付'
group by userID

再嵌套个select函数,计算复购率:购买次数大于1的人数/购买总人数。

select count(if(t.ct>1,1,null)) /count(1)as 三月复购率
from(
select userID,count(userID)as ct
from orderinfo
where month(paidtime)=3 and IsPaid='已支付'
group by userID
) as t
image.png

三月份用户的复购率为30.87%

2.2 用户回购率计算

首先对数据进行过滤,将已支付的用户userID,支付日期月份筛选出来:

select userID,date_format(paidtime,'%Y-%m-01')as m
from orderinfo
where ispaid='已支付'
group by userID,date_format(paidtime,'%Y-%m-01')
image.png

采用date_sub函数,将上表与下月消费的userID进行表联结,即可筛选出本月消费的userID和下月回购的userID,即可计算每月的回购率:

select t1.m,
count(t1.m) as 消费总人数,
count(t2.m) as 回购人数,
count(t2.m)/count(t1.m) as 回购率
from
    (select userID,date_format(paidtime,'%Y-%m-01')as m
    from orderinfo
    where ispaid='已支付'
    group by userID,date_format(paidtime,'%Y-%m-01'))as t1
    left join
    (select userID,date_format(paidtime,'%Y-%m-01')as m
    from orderinfo
    where ispaid='已支付'
    group by userID,date_format(paidtime,'%Y-%m-01'))as t2
on t1.userID=t2.userID
and t1.m=date_sub(t2.m,interval 1 month)
group by t1.m

image.png
  • 三月用户的回购率为23.94%

3、统计男女用户的消费频次

分析思路:将orderinfo和BVV
+userinfo进行表联结,并统计每个人的消费频次。

select userinfo.UserID,
userinfo.sex,
count(1)
from orderinfo
left join userinfo
on userinfo.userID=orderinfo.userID
where IsPaid='已支付'
and userinfo.sex <>''
group by userinfo.UserID

image.png

再对以上数据进行分组,对频次求均值即可求出男女用户的消费频次:

select t.sex,avg(t.ct) as 平均消费频次
from(
select userinfo.UserID,
userinfo.sex,
count(1) as ct
from orderinfo
left join userinfo
on userinfo.userID=orderinfo.userID
where IsPaid='已支付'
and userinfo.sex <>''
group by userinfo.UserID
) t
group by t.sex
image.png

4、统计多次消费用户,分析第一次和最后一次的消费间隔

通过datediff函数计算每个用户消费日期的最大值和最小值的间隔天数,过滤掉最大值和最小值相等的值,或者间隔天数为0的用户,即为多次消费用户第一次和最后一次的消费间隔:

select userID,datediff(max(paidtime),min(paidtime))as 消费间隔
from orderinfo
where orderinfo.ispaid='已支付'
group by userID
having max(paidtime)!=min(paidtime)
#or
having datediff(max(paidtime),min(paidtime))!=0
image.png

5、不同年龄段的消费差异

首先通过表表联结的方式给不同用户划分年龄段,以10年为间隔进行划分,过滤掉出生日期为1900-00-00的异常值:

select orderinfo.*,ceil(timestampdiff(year,userinfo.birth,now())/10) as age
from orderinfo
left join userinfo
on orderinfo.userID=userinfo.userID
where orderinfo.ispaid='已支付'
and userinfo.birth>'1901-00-00'
image.png
  • 时间差函数:TIMESTAMPDIFF(unit,begin,end); 根据单位返回时间差,对于传入的begin和end不需要相同的数据结构,可以存在一个为Date一个DateTime;unit可等于(year,quarter,week,day,hour,second,microsecond等);
    DATEDIFF(date1,date2) 函数返回两个日期之间的天数;
  • CEIL(X) 返回不小于X的最小整数值。(天花板)
    FLOOR(X) 返回不大于X的最大整数值。(地板)

再对以上年龄段分组求消费均值:

select t.age,avg(t.price)
from(
select orderinfo.*,ceil(timestampdiff(year,userinfo.birth,now())/10) as age
from orderinfo
left join userinfo
on orderinfo.userID=userinfo.userID
where orderinfo.ispaid='已支付'
and userinfo.birth>'1901-00-00'
) as t
group by t.age
order by avg(t.price)
image.png
  • 年龄在90-100之间的人均消费最高,达到了653.96元(不排除乱填信息的情况);
  • 各个年龄段人均消费较为平均,极差为120元。

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

计算每个用户的消费总额并排序:

elect userID,sum(price)as sp
from orderinfo
where ispaid='已支付'
group by userID
order by sum(price) desc
image.png

计算top20%的用户数:

select floor(count(1)*0.2)
from
(select userID,sum(price)as sp
from orderinfo
where ispaid='已支付'
group by userID
order by sum(price) desc )as t 
image.png

在源程序的基础上,计算top20%用户的消费总额:

select sum(t.sp) as sum_top20
from
(select userID,sum(price)as sp
from orderinfo
where ispaid='已支付'
group by userID
order by sum(price) desc limit 17192)as t 
image.png

所有用户的消费总额:

select sum(price)
from orderinfo
image.png

top20%用户的消费总额占比情况:top20%用户的消费总额/所有用户的消费总额=73.93%
top20%的用户贡献了73.93%消费额度。

相关文章

  • 用MYSQL分析网络销售案例

    数据来源于某网站的销售统计,主要分为两部分:1、网络订单数据;2、用户信息。数据来源:链接:https://pan...

  • 用MYSQL分析网络销售案例

    数据来源于某网站的销售统计,主要分为两部分:1、网络订单数据;2、用户信息。数据来源:链接:https://pan...

  • MYSQL的销售数据分析案例

    数据来源于某网站的销售信息统计,主要由订单信息及用户信息两个部分组成,分析过程中可通过对两部分的单独分析或联结分析...

  • 销售案例分析

    一、案例:回程的旅游大巴缓缓驶出九寨沟。天刚放亮,晨曦照在远方的雪山顶上,闪着柔柔的光。清新的空气中含有一丝凛冽,...

  • 销售案例分析

    项目简介 某一产品3,4,5月份的消费信息和用户信息,对这两份数据进行分析来了解这三个月销售情况,并为下个季度销售...

  • 一个课程顾问的销售案例分析

    一个课程顾问的销售案例分析 李吉诃德 昨天,我被问到这样一个课程顾问的销售案例,要我共同探讨一下。案例和我的分析如...

  • 使用MySQL 对淘宝用户进行数据分析 实战学习

    案例来源: 知乎《使用 MySQL 对淘宝用户行为进行分析》byStarLau 总结:MySQL编码问题需要重新学...

  • 十六、Swarm 案例

    Wordpress mysql 案例 一、创建overlay网络 二、查看docker 网络 三、可以查看一下其...

  • 销售---“引导”案例分析

    顾客:我想把头发剪成短发,要类似于手机里图片的感觉 理发师:嗯… 这个发型不是很适合你,如果你想剪短发,我可以给你...

  • 销售案例和分析

    1摊主一 老太太去买菜,路过水果摊,看到卖苹果的摊主,就问道:“苹果怎么样啊?” 摊主回答:“我的苹果特别好吃,又...

网友评论

    本文标题:用MYSQL分析网络销售案例

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