電商商品數(shù)據(jù)分析
時(shí)間：2023-03-15 21:26:01 | 來源：電子商務(wù)
時(shí)間：2023-03-15 21:26:01 來源：電子商務(wù)
項(xiàng)目背景：一個(gè)店鋪銷售額的高低會(huì)有多方面因素的影響，如用戶復(fù)購，用戶流失，會(huì)員層次占比。利用sql把這些每日生成數(shù)據(jù)進(jìn)行科學(xué)的統(tǒng)計(jì)，從數(shù)據(jù)的角度發(fā)現(xiàn)問題了解基本情況。
源數(shù)據(jù)介紹：
orderinfo 訂單詳情表
userinfo 用戶信息表
1、統(tǒng)計(jì)不同月份的下單人數(shù)
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、統(tǒng)計(jì)用戶三月份的回購率和復(fù)購率
復(fù)購率：當(dāng)月購買了多次的用戶占當(dāng)月用戶的比例
回購率：本月購買用戶中有多少用戶下個(gè)月又再次購買
a、先篩選出3月份的消費(fèi)情況
select
*
from orderinfo
where isPaid="已支付" and month(paidTime)="03";
b、統(tǒng)計(jì)一下每個(gè)用戶在3月份消費(fèi)了多少次
select
userid,
count(1) as cons
from orderinfo
where isPaid="已支付" and month(paidTime)="03"
group by userid;
c、對(duì)購買次數(shù)做一個(gè)判斷，統(tǒng)計(jì)出來那些消費(fèi)了多次（大于1次）的用戶數(shù)
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月又再次購買的人數(shù) / 3月的用戶總數(shù)
a、統(tǒng)計(jì)每年每月的一個(gè)用戶消費(fèi)情況
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、相鄰月份進(jìn)行關(guān)聯(lián)，能關(guān)聯(lián)上的用戶說明就是回購
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、統(tǒng)計(jì)每個(gè)月份的消費(fèi)人數(shù)情況及格得到回購率
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、統(tǒng)計(jì)男女用戶消費(fèi)頻次是否有差異
3.1、統(tǒng)計(jì)每個(gè)用戶的消費(fèi)次數(shù)，注意要帶性別
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、對(duì)性別做一個(gè)消費(fèi)次數(shù)平均計(jì)算
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、統(tǒng)計(jì)多次消費(fèi)的用戶，第一次和最后一次消費(fèi)間隔是多少天
4.1、取出多次消費(fèi)的用戶
select
userid
from orderinfo
where isPaid="已支付"
group by userid
having count(1)>1;
4.2、取出第一次和最后一次的時(shí)間
select
userid,
min(paidTime),
max(paidTime),
datediff(max(paidTime), min(paidTime))
from orderinfo
where isPaid="已支付"
group by userid
having count(1)>1;
5、統(tǒng)計(jì)不同年齡段，用戶的消費(fèi)金額是否有差異
a、step1：計(jì)算每個(gè)用戶的年齡
select
userid,
birth,
now(),
timestampdiff(year,birth,now()) as age
from userinfo
where birth>'1900-00-00';
step2:對(duì)年齡進(jìn)行分層: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、關(guān)聯(lián)訂單信息，獲取不同年齡段的一個(gè)消費(fèi)頻次和消費(fèi)金額
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、再對(duì)年齡分層進(jìn)行聚合，得到不同年齡層的消費(fèi)情況
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、統(tǒng)計(jì)消費(fèi)的二八法則，消費(fèi)的top20%用戶，貢獻(xiàn)了多少消費(fèi)額
6.1、統(tǒng)計(jì)每個(gè)用戶的消費(fèi)金額，并進(jìn)行一個(gè)降序排序
select
userid,
sum(price) as total_price
from orderinfo a
where isPaid="已支付"
group by userid;
6.2、統(tǒng)計(jì)一下一共有多少用戶，以及總消費(fèi)金額是多少
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%的用戶進(jìn)行金額統(tǒng)計(jì)
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 ;
占總體消費(fèi)金額的84.906%說明前百分之二十的用戶貢獻(xiàn)了百分之八十五的消費(fèi)金額，因此要抓住此部分的用戶，其流失造成的影響會(huì)很大
關(guān)鍵詞：分析,數(shù)據(jù),商品
在線咨詢