电商商品数据分析
时间：2023-03-15 21:26:01 | 来源：电子商务
时间：2023-03-15 21:26:01 来源：电子商务
项目背景：一个店铺销售额的高低会有多方面因素的影响，如用户复购，用户流失，会员层次占比。利用sql把这些每日生成数据进行科学的统计，从数据的角度发现问题了解基本情况。
源数据介绍：
orderinfo 订单详情表
userinfo 用户信息表
1、统计不同月份的下单人数
select
year(paidTime),
month(paidTime),
count(distinct userid) as cons
from orderinfo
where isPaid="已支付" and paidTime<>'0000-00-00 00:00:00'
group by year(paidTime),month(paidTime);
2、统计用户三月份的回购率和复购率
复购率：当月购买了多次的用户占当月用户的比例
回购率：本月购买用户中有多少用户下个月又再次购买
a、先筛选出3月份的消费情况
select
*
from orderinfo
where isPaid="已支付" and month(paidTime)="03";
b、统计一下每个用户在3月份消费了多少次
select
userid,
count(1) as cons
from orderinfo
where isPaid="已支付" and month(paidTime)="03"
group by userid;
c、对购买次数做一个判断，统计出来那些消费了多次（大于1次）的用户数
select
count(1) as userid_cons,
sum(if(cons>1,1,0)) as fugou_cons,
sum(if(cons>1,1,0))/count(1) as fugou_rate
from (select
userid,
count(1) as cons
from orderinfo
where isPaid="已支付" and month(paidTime)="03"
group by userid
) a;
3月份的回购率 = 3月用户中4月又再次购买的人数 / 3月的用户总数
a、统计每年每月的一个用户消费情况
select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01');
b、相邻月份进行关联，能关联上的用户说明就是回购
select
*
from (select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) a
left join (select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) b
on a.userid=b.userid and date_sub(b.month_dt,interval 1 month)=a.month_dt;
c、统计每个月份的消费人数情况及格得到回购率
select
a.month_dt,
count(a.userid) ,
count(b.userid) ,
count(b.userid) / count(a.userid)
from (select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) a
left join (select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt,
count(1) as cons
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) b
on a.userid=b.userid
and date_sub(b.month_dt,interval 1 month)=a.month_dt
group by a.month_dt;
3、统计男女用户消费频次是否有差异
3.1、统计每个用户的消费次数，注意要带性别
select
a.userid,
sex,
count(1) as cons
from orderinfo a
inner join (select * from userinfo where sex<>' ') b
on a.userid=b.userid
group by a.userid,sex;
3.2、对性别做一个消费次数平均计算
select
sex,
avg(cons) as avg_cons
from (select
a.userid,
sex,
count(1) as cons
from orderinfo a
inner join (select * from userinfo where sex<>'') b
on a.userid=b.userid
group by a.userid,sex) a
group by sex;
4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
4.1、取出多次消费的用户
select
userid
from orderinfo
where isPaid="已支付"
group by userid
having count(1)>1;
4.2、取出第一次和最后一次的时间
select
userid,
min(paidTime),
max(paidTime),
datediff(max(paidTime), min(paidTime))
from orderinfo
where isPaid="已支付"
group by userid
having count(1)>1;
5、统计不同年龄段，用户的消费金额是否有差异
a、step1：计算每个用户的年龄
select
userid,
birth,
now(),
timestampdiff(year,birth,now()) as age
from userinfo
where birth>'1900-00-00';
step2:对年龄进行分层:0-10:1,11-20:2,21-30:3
select
userid,
birth,
now(),
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-00';
b、关联订单信息，获取不同年龄段的一个消费频次和消费金额
select
a.userid,
age,
count(1) as cons,
sum(price) as prices
from orderinfo a
inner join (select
userid,
birth,
now(),
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-00') b
on a.userid=b.userid
group by a.userid,age;
c、再对年龄分层进行聚合，得到不同年龄层的消费情况
select
age,
avg(cons),
avg(prices)
from (select
a.userid,
age,
count(1) as cons,
sum(price) as prices
from orderinfo a
inner join (select
userid,
birth,
now(),
ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1901-00-00') b
on a.userid=b.userid
group by a.userid,age) a
group by age;
6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额
6.1、统计每个用户的消费金额，并进行一个降序排序
select
userid,
sum(price) as total_price
from orderinfo a
where isPaid="已支付"
group by userid;
6.2、统计一下一共有多少用户，以及总消费金额是多少
select
count(1) as cons,
sum(total_price) as all_price
from (select
userid,
sum(price) as total_price
from orderinfo a
where isPaid="已支付"
group by userid) a;
从85649中取前百分之二十6.3、取出前20%的用户进行金额统计
select
count(1) as cons,
sum(total_price) as all_price
from (
select
userid,
sum(price) as total_price
from orderinfo a
where isPaid="已支付"
group by userid
order by total_price desc
limit 17000) b ;
占总体消费金额的84.906%说明前百分之二十的用户贡献了百分之八十五的消费金额，因此要抓住此部分的用户，其流失造成的影响会很大
在线咨询
