用MySQL分析网络销售案例

用MySQL分析网络销售案例

数据来源于某网站销售统计

  1. 网络订单数据
  2. 用户信息

点击获取数据 提取码:3k6i

分析步骤

0、数据导入

1、不同月份的下单人数

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

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

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

5、统计不同年龄段用户的消费金额差异

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

0 数据导入

首先需要先创建对应的数据库和相应的表

  1. 创建orderinfo 表

image.png

  1. 创建userinfo表

image.png

  1. 登录mysql导入相应的数据

load data local infile "file" into table dbname.tablename ...

# 登录
mysql --local-infile -uroot -p
# 导入数据orderinfo
load data local  infile 'F:\BaiduNetdiskDownload\SQL\order_info_utf.csv' into table data.orderinfo fields terminated by ',';
# 导入数据userinfo
load data local  infile 'F:\BaiduNetdiskDownload\SQL\user_info_utf.csv' into table data.userinfo fields terminated by ',';
  1. 观察数据,对时间进行处理 ; 更新字符串为日期格式
update orderinfo set paidtime=replace(paidtime,'/','-') where paidtime is not null
update orderinfo set paidtime=str_to_date(paidtime,'%Y-%m-%d %H:%i') where paidtime is not null

5查看数据

image.png

1 不同月份的下单人数

思路 :按月份进行分组,对用户进行去重统计

select month(paidTime) as dtmonth, 
count(distinct userId) as count_users
from orderinfo
where isPaid = '已支付'
group by month(paidTime)

image.png

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

  1. 复购率 : 自然月内,购买多次的用户占比
  • 首先先找出已支付中3月份的用户id和对应次数,按用户分组
  • 然后再嵌套一层,复购率:购买次数大于1/ 总购买次数
select count(ct),count(if(ct>1,1,null)) from(
    select userID,Count(userId) as ct from orderinfo
    where isPaid = '已支付'
    and month(paidTime) = 3
    group by userId
    order by userId) t

image.png

复购率: 16916 / 54799 = 0.308

  1. 回购率: 曾经购买过的用户在某一时期内再次购买的占比

首先先查询已支付userId ,和 支付月份的统计

select userId, date_format(paidTime, '%Y-%m-01') as m from orderinfo
    where isPaid = '已支付'
    group by userId , date_format(paidTime,'%Y-%m-01')

然后使用date_sub函数,将表关联,筛选出本月的消费的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')) 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')) t2
on t1.userId = t2.userId and t1.m = date_sub(t2.m, interval 1 month)
group by t1.m

回购率

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

  • userinfo因为性别有空值,需要筛选出t orderinfo 再和表t连接 统计出用户男女消费次数
select o.userId,sex,count(o.userId)as ct from orderinfo o
    inner join
        (select * from userinfo
        where sex != '') t
    on o.userId = t.userId
    group by userId,sex
    order by userId

image.png

  • 根据上表,在进行子查询,统计出男性消费频次
select sex,avg(ct) from(
    select o.userId,sex,count(o.userId)as ct from orderinfo o
    inner join
        (select * from userinfo
        where sex != '') t
    on o.userId = t.userId
    group by userId,sex
    order by userId)t2
group by sex

消费频次

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

  • 首先把多次消费的用户,和相应第一次最后一次消费时间提取出来
  • 然后使用datediff 计算时间间隔,以天为单位
select userId,max(paidTime),min(paidTime),datediff(max(paidTime),min(paidTime)) from data.orderinfo
where isPaid = '已支付'
group by userId having count(1) > 1
order by userId

image.png

5 统计不同年龄段用户的消费金额差异

通过表联结,给用户划分不同的年龄段,以10年为基准,过滤出生日期为1900-00-00的异常值,筛选出用户消费频次和消费金额

select o.userId,age,price,count(o.userId)as ct from orderinfo o
inner join (
    select userId, ceil((year(now()) - year(birth))/10) as age
            from userinfo
            where birth > 1901-00-00) t
on o.userId = t.userId
where isPaid = '已支付'
group by userId
order by userId

image.png

统计出年龄段的消费频次和消费金额

select t2.age,avg(ct),avg(price) from (
    select o.userId,age,price,count(o.userId)as ct from orderinfo o 
    inner join(
        select userId, ceil((year(now()) - year(birth))/10) as age
        from userinfo
        where birth > 1901-00-00)t
    on o.userId = t.userId
    where ispaid = '已支付'
    group by userId, age) t2
group by age
order by age

image.png

  • ceil : 向上取整

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

按照用户消费总额排序

select userId,sum(price) as total from orderinfo o
where isPaid = '已支付'
group by userId
order by total desc

image.png

查看总用户数和总金额

select count(userId),sum(total) from (
    select userId,sum(price) as total from orderinfo o
    where isPaid = '已支付'
    group by userId
    order by total desc) as t

image.png

查看前20%的用户数量有多少

select count(userId)*0.2,sum(total) from (
    select userId,sum(price) as total from orderinfo o
        where isPaid = '已支付'
        group by userId
        order by total desc)as t

image.png

limit限制前17000用户

select count(userId),sum(total) from (
select userId,sum(price) as total from orderinfo o
where isPaid = '已支付'
group by userId
order by total desc
limit 17129) t

image.png

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

posted @ 2019-05-05 01:38  东西  Views(...)  Comments(... Edit 收藏