练习用户行为分析

工具:Navicat

数据:order_info_utf, user_info_utf

问题:

1、统计不同月份的下单人数

2、统计用户3月份的复购率和回购率

3、男女用户的消费频次是否有差异?

4、多次消费的用户,第一次和最后一次消费间隔是多少?

5、不同年龄段,用户的消费频次是否有差异?

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


1.统计不同月份的下单人数:

要求下单人数,先过滤掉未支付的人,然后用DATE_FORMAT()函数,按月分组。查询人数用count()计数,注意要加distinct去除重复值,如果不加distinct,计数的人数会偏多因为有重复的userID


2.统计用户3月份的复购率和回购率:

复购率是一个月之内,多次购买的用户占比

先过滤出购买的用户和3月份,按用户ID分组后对用户ID计数,即可以知道每个用户在该月购买了几次。从中选出购买次数大于1 的用户(复购),计数。计数得到的两个数字相除就是复购率

count()里面嵌套了一个if条件语句,表示如果用户购买次数大于1则返回1,否则返回空值,对所有的1 计数,null值不计数。

回购率是当月消费过的,在次月也消费了

题目若改为求所有月份的回购率,对用户和月份分组就可以知道每个用户出现在哪个月

把这个结果作为表,左联结,联结的条件是两个表的用户ID一样而且左表比右表的月份小一月。左联结就是在左边表出现(消费)的用户不一定在右边表出现,若两个表之间能匹配上的用户就是回购的,不能匹配上的自然显示空值。

结果如下,3月有54799人消费,其中有13119人在4月也消费了。


3.男女用户的消费频次是否有差异?

首先过滤掉用户表中的无性别的值,然后和订单表内部联结。按用户分组,计数得到每个用户消费多少次,然后按性别分组

结果如下,比如用户13341是女,消费了1次:

再对上面的表按性别分组,求出男女各自的平均消费次数:


4.多次消费的用户,第一次和最后一次消费间隔是多少?

按用户分组,提取多次消费的用户:

求日期间隔用datediff(),相减的话得到的是秒:


5.不同年龄段,用户的消费频次是否有差异?

我们把年龄10岁分为一段,YEAR(NOW())-YEAR(birth)计算出每个用户多少岁,ceil:向上取整函数,:2号用户处在第五个年龄段


6.统计消费的二八法则,消费的20%用户贡献了多少额度?

首先看一下所有支付了的用户的消费额:

再从这个表中查询所有用户消费的总金额:

得到结果如下,共有85649条消费记录

20%用户就是17129,我们选取前17000条可以了

查询20%的用户消费的总金额:

两者的总额相除就知道消费的20%用户贡献了多少额度

©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。