欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

复购客户sql

程序员文章站 2022-05-01 16:54:31
复购客户用户分析是电商数据分析中重要的模块,在对用户特征深度理解和用户需求充分挖掘基础上,进行全生命周期的运营管理(拉新—>活跃—>留存—>价值提升—>忠诚),请尝试回答以下3个问题:① 现在数据库中有一张用户交易表order,其中有userid(用户ID)、orderid(订单ID)、amount(订单金额)、paytime(支付时间),请写出对应的SQL语句,查出每个月的新客数(新客指在严选首次支付的用户),当月有复购的新客数,新客当月复购率(公式=当月有复购的新客数/月总...

复购客户

用户分析是电商数据分析中重要的模块,在对用户特征深度理解和用户需求充分挖掘基础上,进行全生命周期的运营管理(拉新—>活跃—>留存—>价值提升—>忠诚),请尝试回答以下3个问题:
① 现在数据库中有一张用户交易表order,其中有userid(用户ID)、orderid(订单ID)、amount(订单金额)、paytime(支付时间),请写出对应的SQL语句,查出每个月的新客数(新客指在严选首次支付的用户),当月有复购的新客数,新客当月复购率(公式=当月有复购的新客数/月总新客数)。
② 当你发现最近一周APP端新访用户当天转化率(公式=新访当天支付人数/新访用户数,新访是指首次访问严选APP的设备)环比最近4周日均转化率大幅下跌(超30%),你会如何去探查背后的原因?请描述你的思路和其中涉及的关键指标
③ 在进行用户运营之前,我们通常会对用户进行分层,针对不同类型用户实施差异化的运营策略和资源投入,请你帮助设计严选用户分层的方案,包括关键特征的选择,分层的方法,如涉及模型/算法,请说明选择的算法类型、基本原理和步骤

(1)

Alter table order add column ym  varchar(255) not null
Update order set ym=date_format(paytime,’%Y-%M’)
##data_format()函数自定义时间格式为"年-月“

#0.找出每个用户第一次下单的时间和年月,此次是以用户分组进行group by,输出为用户id,第一次下单的时间,第一次下单的年月
select user_id,min(paytime),ym
from order
group by user_id;

#1.找出每月的新客户数目,此次以年月进行分组,找出某年某月第一次下单用户数
select ym,count(user_id) as '新客户数'
from 
(select user_id,min(paytime),ym
	from order 
	group by user_id
) as o1 #这是用户第一次
group by ym;


#2.找出当月有复购的新客户数目,分两步,第一步找出用户在第一次下单的那个月的购买次数,第二步是将购买次数大于2
select ym,o3.user_id,count(o3.user_id) as '有复购的新客户数目'
from
(select o2.user_id,count(o2.user_id) as '次数',o2.ym
from
(select user_id,min(paytime),ym
	from order 
	group by use_id) o1,order o2 
where o1.user_id=o2.user_id and o1.ym=o2.ym 
group by 02.user_id)as o3
where 次数>2
group by ym;

##3.将他们连接起来

select order1.年月,order1.新客户数 as 每月新客户数,if null(order2.有复购的新客户数,0) as 当月有复购的新客数,
(if null(order2.有复购的新客户数,0)/order1.新客户数) as 新客当月复购率
from 
(select 年月,count(userid) as 新客户数
from(select userid,min(paytime),年月
from `order`
group by userid) as o1
group by 年月) as order1 left join 
(
select 年月,count(userid) as `有复购的新客户数`
from
(
select o2.userid,count(o2.userid) as 次数,o2.年月
from
(select userid,min(paytime),年月
from `order`
group by userid) as o1,`order` o2
where o2.userid=o1.userid and o2.年月=o1.年月
group by o2.userid) as o3 
where 次数>=2
group by 年月) as order2
on order1.年月=order2.年月
order by order1.年月;

本文地址:https://blog.csdn.net/weixin_42224523/article/details/107313167