Excel 利用同期群分析计算新用户留存率

酸菜鱼土豆大侠

已于 2023-07-27 16:34:55 修改

阅读量2k

点赞数 2

分类专栏： Excel 文章标签： excel 大数据

于 2023-03-23 12:52:48 首次发布

本文链接：https://blog.csdn.net/chengyj0505/article/details/129728457

版权

Excel 专栏收录该内容

8 篇文章 17 订阅

订阅专栏

业务分析方法中常用的有同期群分析，本文使用Excel和MySQL，结合同期群计算新注册用户的留存率，并放在实际的应用场景中进行分析。

一、简单介绍同期群分析

1、同期群分析（cohort analysis）是一种数据分析方法，主要用于研究不同时间段内特定群体的行为和变化。具体来说，同期群分析将特定群体划分为同一时间段内加入、注册或开始使用某个产品或服务的人群，然后对这些人的行为进行跟踪和比较，以了解他们在某个指标上的变化趋势。

2、作用：通过同期群分析，企业可以识别出哪些群体对其业务的发展最有潜力，制定更有效的策略，以提高客户满意度和利润率。

3、应用场景：

电商平台留存率分析：假设一家电商平台想要了解其用户留存情况，可以使用同期群分析来比较不同时间段内加入该平台的用户群体的留存率。例如，将在2020年7月1日注册的用户作为一个同期群，将在2020年7月2日注册的用户作为另一个同期群，…，直到7月9日注册的用户作为第9个同期群，然后对这九个群体的留存率进行比较，以了解用户留存情况的变化趋势。
社交媒体广告效果分析：假设一家社交媒体公司想要评估其广告的效果，可以使用同期群分析来比较不同时间段内点击广告并进行购买的用户群体的转化率。例如，将在2020年第一季度点击广告并进行购买的用户作为一个同期群，将在2020年第二季度点击广告并进行购买的用户作为另一个同期群，然后对这两个群体的转化率进行比较，以了解广告效果的变化趋势。

二、效果图

1、最近31日的用户平均留存率

在这里插入图片描述

2、多个同期群的最近31日的平均用户留存率

在这里插入图片描述

三、业务需求

某APP产品为了获取新用户，在2020/7/1至2020/7/9期间开展推广活动。现希望根据7月份的新用户登录数据，分析本次活动的推广效果。

分析业务需求：

方法：同期群分析；
指标：新用户的留存率；
目标：观察新用户的留存率得出推广结论。
实现过程：

1、活动期间每日的注册用户数（辅助分析）；

2、活动期间每日的登录用户数（辅助分析）；

3、计算最近31日的用户平均留存率。

4、从7月1日到9日，每天注册的用户为一个同期群，获取每个同期群在注册之后的每日登录用户数（辅助分析）；

5、获取每个同期群在注册之后的近31日平均用户留存率。

四、制定操作步骤

MySQL：

1、处理数据：对列名进行重命名，修改为英文。

2、导入数据库：将重命名的数据导入到数据库中，接下来的操作都在数据库中进行。

3、获取图表数据：获取活动期间每日的注册用户数、活动期间每日的登录用户数、计算最近31日的用户平均留存率；获取每个同期群在注册之后的31日用户留存率。

Excel:

1、收集数据：对登录日期进行处理，保留date部分。

2、数据透视：获取活动期间每日的注册用户数、活动期间每日的登录用户数、获取每个同期群在注册之后的每日登录用户数和近31日用户留存率。

五、根据操作步骤计算用户留存率

------------------------------------------------MySQL--------------------------------------------

1、处理数据：

处理《用户登录记录》，对列名进行重命名，修改为英文

修改完成之后，将表重命名为《用户登录记录—SQL》
在这里插入图片描述

2、导入数据库

将重命名的数据表《用户登记记录—SQL》导入到数据库中，接下来的操作都在数据库中进行。
在这里插入图片描述

3、获取图表数据

3.1 获取活动期间每日的注册用户数

考虑到会出现重复记录的情况，一定要对注册日期和用户同时去重。

查询代码

#1、活动期间每日的注册用户数
select
	date(login_time) as 登录日期,
	count(distinct user_id)
from login_info
group by 登录日期

将查询到的数据复制到excel中，并作图
在这里插入图片描述

3.2 活动期间每日的登录用户数

考虑到会出现重复记录的情况，一定要对登录日期和用户同时去重。

#2、活动期间每日的登录用户数
select
	date(login_time) as 登录日期,
	count(distinct user_id)
from login_info
group by 登录日期

将查询到的数据复制到excel中，并作图
在这里插入图片描述

3.3 计算最近31日的用户平均留存率

计算公式：第N日留存率 = （当天活跃的用户第N天又活跃了的用户数）/（当天活跃的用户数）

举个例子：如果用户1在1号、2号、3号活跃；用户2在1号、2号活跃。那么次日留存率是多少？

日期活跃用户次日留存用户数第3日留存用户数
1号用户1、用户2 2 1
2号用户1、用户2 1 0
3号用户1 0 0
总计 5 3 1

平均次日留存率 = 3/5；平均第三日次日留存率=1/5

日期	活跃用户	次日留存用户数	第3日留存用户数
1号	用户1、用户2	2	1
2号	用户1、用户2	1	0
3号	用户1	0	0
总计	5	3	1

以下代码是计算第31日的用户平均留存率：

#3、第31日的用户平均留存率
select 
	count(次日) / count(当日) as 第31日留存率
from (
  select distinct
    a.user_id,
    date(a.login_time) as 当日,
    date(b.login_time) as 次日
  from login_info a
  left join(
    select distinct
      user_id,
      date(login_time) as login_time
    from login_info
  ) b on a.user_id = b.user_id and datediff(b.login_time,date(a.login_time))= 30
) c

需要逐个修改两个日期相减得到的天数1、2、…、30，既可得到第2日、第3日、…、第31日的平均用户留存率。

将查询到的数据复制到excel中作图，最终得到最近31日的用户平均留存率的表和图，如下：
在这里插入图片描述

3.4 获取每个同期群在注册之后的近31日用户留存率

同期群分析：按照不同的注册日期分为9个同期群。先计算每个同期群的每日登录用户数，再计算每个同期群近一个月的留存率。

计算公式：某天注册的用户留存率=该天注册用户第N天的登录人数/某天注册用户数。

其中，某天注册用户数是一个固定值。

举个例子：如果用户1在1号、2号、3号活跃；用户2在1号、2号活跃，两个用户都是在1号注册；用户4和5在2号注册，用户4号在3号活跃，用户5号在4号活跃。那么留存率是多少？

活跃日期注册用户次日留存用户第3日留存用户数
1号 2 2 1
2号 2 1 1
总计 4 3 2

下面计算留存率

注册日期第1日次日第3日
1号 2/2=1 2/2=1 1/2=0.5
2号 2/2=1 1/2=0.5 1/2=0.5
求平均 1 0.75 0.5

活跃日期	注册用户	次日留存用户	第3日留存用户数
1号	2	2	1
2号	2	1	1
总计	4	3	2

注册日期	第1日	次日	第3日
1号	2/2=1	2/2=1	1/2=0.5
2号	2/2=1	1/2=0.5	1/2=0.5
求平均	1	0.75	0.5

利用SQL分别计算每个注册日期用户的近31日留存率

代码如下：

#5、2020-07-01注册用户的第N日用户留存率
select 
	count(user_id)/(select count(distinct user_id) from login_info where date(register_date) = '2020-07-01') as 用户留存率
from (
  select distinct
    a.user_id,
    date(b.login_time) as 登录日期
  from login_info a
  left join(
    select distinct
      user_id,
      date(login_time) as login_time
    from login_info
  ) b on a.user_id = b.user_id 
  where date(a.register_date) = '2020-07-01'
) c
group by 登录日期
order by 登录日期

需要逐个修改日期’2020-07-01’、‘2020-07-02’、…、‘2020-07-09’，既可得到2020-07-01、2020-07-02、…、2020-07-09注册用户的第N日用户留存率。

将查询到的数据复制到excel中作图，最终得到每个注册日期用户的近31日留存率：
在这里插入图片描述

------------------------------------------------Excel-----------------------------------------------

1、收集数据：

对登录时间进行处理，保留date部分。使用int()函数，选择数据中的格式“短日期”。
在这里插入图片描述

2、数据透视：

2.1 获取活动期间每日的注册用户数

考虑到会出现重复记录的情况，一定要对注册日期和用户同时去重。点击数据——数据工具（删除重复值）即可去重。使用去重后的数据插入数据透视表，拖动“注册日期”为行，“用户编号”为列。
最后再制作一个活动期间的注册用户图

2.2 活动期间每日的登录用户数

考虑到会出现重复记录的情况，一定要对登录日期和用户同时去重。操作步骤和2.1一样。
绘图时点击登录日期可以选择展示的横坐标的范围。

2.3 获取每个同期群在注册之后的每日登录用户数

考虑到会出现重复记录的情况，一定要对登录日期、注册日期和用户同时去重。
拖动“注册日期”为行，“登录日期”为列，“用户编号”为值，最终得到每日登录用户数。

2.4 获取每个同期群在注册之后的近31日用户留存率

对登录日期、注册日期和用户同时去重之后，用登录日期减去注册日期得到第几日登录。使用去重后的数据插入数据透视表，拖动“注册日期”为行，“第几日登录”为列，“用户编号”为值。
右键选择值显示方式—百分比；基本字段：第几日登录，基本项：0。即可得到每个同期群第N天的用户留存率。
画出多个同期群的最近31日的平均用户留存率