精华内容
下载资源
问答
  • 如何高效计算用户留存率
    千次阅读
    2019-10-24 17:54:53

    如何高效计算用户留存率

    简单介绍留存率的概念,说明数仓建设中对留存率计算的优化思路

    什么是留存率

    在互联网行业中,用户在某段时间内开始使用应用,经过一段时间后,仍然继续使用该应用的用户,被认作是留存用户。

    留存率就是留存用户与全部用户的比值,计算公式

    留存率 = 留存用户数 / 用户数 * 100%

    比如昨天来了100个人,今天这100个人里面的60个人又来了,那么留存率就是60%。

    留存率反应了一个产品的用户黏性,留存越高说明用户在使用这个产品之后,继续使用的概率越大。
    在用户运营越来越重要的今天,留存率作为公司的重要指标,也越来越被重视起来。

    留存率的口径

    留存率有很多计算口径,适用于不同的分析场景。但都是要确定两个时间窗口,第一个时间段用来圈人,第二个时间段用来观察被圈的人有没有再次访问。一般来说看的比较多的有如下几种口径

    口径名称前一时间段下一时间段
    次日留存1天1天
    次三日留存1天3天
    次七日留存1天7天
    次30日留存1天30天
    周留存7天7天
    月留存30天30天
    自然月留存上个自然月下个自然月

    此外游戏产品还很看重用户注册之后的第一日留存、第二日留存…一直到第7日留存,含义是第0天来的人,在第1天、第2天…第7天的留存,是一个不断下降的曲线,游戏策划的一大目标就是让这条曲线下降变慢一点。

    可以看到留存率的计算口径众多,时间跨度广,如果再摊上用户数量巨大,计算起来就是一件费时费力的工作了。

    留存率计算方式零: 在用到的地方计算

    简单来说就是没有统一计算的底层留存表,数据开发者在需要计算留存的时候,自己把两块用户数据left join起来,然后计算留存。
    这样好处是简单快速;缺点是有数据口径不统一的隐患,逻辑很难复用。只适用于初级的数仓报表建设。因为这种没有任何设计思路,这里就不再展开。

    留存率计算方式一: 全量用户访问情况表

    思路是很简单的,如果我们知道了每一个用户,在前一段时间的访问天数M,在后一段时间的访问天数N,那么留存率就是 sum(M>0 and N>0) / sum(M > 0) * 100%
    而我们一般用到的时间段是可以枚举的:1、2、3、4、5、6、7、14、28、29、30、31。那么,如果我们有一张表包含了12 * 2 = 24个字段(前后要*2),就能方便计算留存率了。

    但是,这种方法会遇到一个问题,就是未来是不知道的,在可以计算次日留存的时候,月留存还无法得到结果,总不能等到一个月之后才拿到次日留存吧。所以就遇到需要回刷的问题。在每一个时间点都需要回刷一次,假设关注12个时间点,一张表就相当于12张表的计算量。虽然看起来计算量大了,但是如果很多地方都需要用到,比起粗放的前一种方式还是提升了很多的。

    留存率计算方式二: 用户访问情况字段

    这是本篇文章主要想讲述的内容,它继承了上面保存用户访问情况的思想,计算量不会太大,口径也可以更加灵活。

    假设我们可以把用户两个月的访问情况都保存在表里,1就是来了,0就是没来,两个月算62天,一天一个字段,就是62列。那么这份数据就可以完全覆盖上面从次日留存到月留存,不需要回刷。为什么呢,因为在最新日期的数据里面,可以看到用户在过去60天,每一天的访问情况,假设用户在60~30这30个日期来访是M,30~1这30个日期来访是N,那么依然可以通过公式sum(M>0 and N>0) / sum(M > 0) * 100%计算留存。

    有人觉得62个字段太多了,放在一个表里不值得,这个完全是可以优化的,一个bigint有64位,64 - 62 = 2 还能剩余两位。所以我们只需要一个bigint,理论上就可以计算绝大多数的留存口径。

    在这里先做一个约定,0代表没来,1代表来,从右到左第1到第62位,分别代表距今天0到61天,用户是否来访。

    那么我们就可以定义一个UDF来计算任意口径用户来访天数,假设叫get_range, 这个函数需要4个信息

    • 用户访问情况: bigint
    • 制作用户访问情况的日期: string
    • 计算留存目标日期: string
    • 计算时间范围: int

    简单示例如下

    public int evaluate(final bigint info, final String baseDate, final String targetDate, int range) {
        int r = 0;
        int diff = datediff(baseDate, targetDate);
        if (diff >= 1 && diff <= 62  && range > 0){  // 保证没有越界
        	for (int i = 0; i < range && i <= diff; i++){
    			r += ((info >> (diff - i)) & 1);
        	}
        }
        return r;
    }
    

    以计算次日留存为例, 就可以

    get_range(info, '20190901', '20190831', 1) as M, 
    get_range(info, '20190901', '20190901', 1) as N
    

    然后 sum(M>0 and N>0) / sum(M > 0) * 100% 来计算留存了

    总结一下,通过一个bigint来存储用户的访问情况,可以非常方便的计算用户的留存,但是也需要很好的工具链来保障这个bigint生成的正确性,对软实力要求很高。
    如果不追求极致的性能,使用回刷的方式也可以达到方便计算的目的。最不推荐的就是在各个用到地方算一次留存,虽然看起来简单,但是最后维护最麻烦,使用的资源也随着时间越来越多。

    更多相关内容
  • 今天我们来分享一个常见案例,用SQL来计算用户留存率。目录:1. 案例数据2. 思路分析3. 完整代码1. 案例数据这里我们一共两份日志数据,分别是用户账号创建的日志以及用户登录的日志。账号创建日志账号创建日志用户...

    今天我们来分享一个常见案例,用SQL来计算用户留存率。

    目录:

    • 1. 案例数据

    • 2. 思路分析

    • 3. 完整代码

    1. 案例数据

    这里我们一共两份日志数据,分别是用户账号创建的日志以及用户登录的日志。

    账号创建日志

    ecc476420bcb74a00bd17267af121614.png
    账号创建日志

    用户登录日志

    f2ea8600ad8f56b9e02bf40f6ddc2016.png
    登录日志

    2. 思路分析

    所谓留存,就是指某日创建的账号在后续自然日登录的比例,比如3月1日新增账号创建数为100,在3月2日这部分用户登录数为51,那么3月1日新增用户的次日留存率为51/100=51%。

    注意:我这里用的MYSQL环境

    基于上述的理解,我们大概就有了以下思路:

    • 考虑到用户每天登录的次数不一定只有一次,为了方面后续的数据处理,可以先对登录数据按照日期和用户id进行去重DISTINCT处理

    SELECT DISTINCT
     STR_TO_DATE( $part_date, '%Y-%m-%d' ) login_date,
     role_id 
    FROM
     role_login
    • 为了计算某条登录日志是该用户创建账号后的第几天登录,我们可以用用户登录日志和账号创建日志进行inner join(这里考虑到不在统计周期内的创建账号的用户数据也会记录在用户登录日志里,所以去掉)

    SELECT
     login_log.role_id,
     create_date,
     login_date
    FROM
     ((
      SELECT DISTINCT
       STR_TO_DATE( $part_date, '%Y-%m-%d' ) login_date,
       role_id 
      FROM
       role_login 
      ) login_log
     INNER JOIN ( SELECT DISTINCT STR_TO_DATE( $part_date, '%Y-%m-%d' ) create_date, role_id FROM role_create ) create_log ON ( login_log.role_id = create_log.role_id ))
    f39a41c3ec387e3ff5a912ee309d9de3.png
    • 然后用登录日期字段和创建账户字段进行差值DATEDIFF获取第几天登录

    SELECT
     login_log.role_id,
     create_date,
     DATEDIFF( login_date, create_date ) day_diff 
    FROM
    ...
    2deebe58d7f2b2dfc96c001555296769.png
    • 对于第0天登录的数据则可以理解为新增用户数,第N(≥1)天登录的数据则为这批新增用户后续有登录的用户数

    SELECT
     create_date 
    , count((CASE WHEN (day_diff = 0) THEN role_id END)) 新增用户数
    , count((CASE WHEN (day_diff = 1) THEN role_id END)) 次日留存
    , count((CASE WHEN (day_diff = 2) THEN role_id END)) 3日留存
    , count((CASE WHEN (day_diff = 7) THEN role_id END)) 7日留存
    FROM
    temp_1
    GROUP BY
     create_date
    4dd324fc37b721c280aae57bd058d084.png
    • 用第N天登录的数据 / 新增用户数  就是对应第N天留存率

    3. 完整代码

    SELECT
      create_date
    , 新增用户数
    , concat(CAST(ROUND((100 * 次日留存) / 新增用户数,2) AS char), '%') 次日留存率
    , concat(CAST(ROUND((100 * 3日留存) / 新增用户数,2) AS char), '%') 3日留存率
    , concat(CAST(ROUND((100 * 7日留存) / 新增用户数,2) AS char), '%') 7日留存率
    FROM
      (
       SELECT
         create_date
       , count((CASE WHEN (day_diff = 0) THEN role_id END)) 新增用户数
       , count((CASE WHEN (day_diff = 1) THEN role_id END)) 次日留存
       , count((CASE WHEN (day_diff = 2) THEN role_id END)) 3日留存
       , count((CASE WHEN (day_diff = 7) THEN role_id END)) 7日留存
       FROM
         (
          SELECT
            login_log.role_id
          , create_date
          , DATEDIFF(login_date, create_date) day_diff
          FROM
            ((
             SELECT DISTINCT
               STR_TO_DATE($part_date, '%Y-%m-%d') login_date
             , role_id
             FROM
               role_login
          )  login_log
          INNER JOIN (
             SELECT DISTINCT
               STR_TO_DATE($part_date, '%Y-%m-%d') create_date
             , role_id
             FROM
               role_create
          )  create_log ON (login_log.role_id = create_log.role_id))
       )  temp_1
       GROUP BY create_date
    )  temp_2
    ORDER BY create_date ASC
    5c49111cf13c86b9793e038db195a9e4.png

    以上就是本次全部内容,由于不同的sql环境语法存在些许差异,大家视情况而处理吧。

    END -

    
     
    对比Excel系列图书累积销量达15w册,让你轻松掌握数据分析技能,可以在全网搜索书名进行了解选购:
    展开全文
  • SQL计算留存率

    2021-10-13 15:01:44
    需要计算用户留存率(公式见下): 留存率=(昨天活跃用户∩今天活跃用户)/昨天活跃用户。 示例数据 问题分析 留存率的计算主要涉及到一个时间参数,而与之对比的时间则是昨天,简单的说即取昨天的用户里...

    用SQL计算用户的留存率

    需求描述

    有用户活跃表dws_user_active_t,它的字段有:

    ds '操作日期'

    device  '设备类型' 1 手机 2 PAD 3 浏览器 

    user_id '用户id'

    active_score  '活跃度'

    计算出用户留存率(公式见下):

    留存率=(昨天活跃用户∩今天活跃用户)/昨天活跃用户。

    示例数据

    问题分析

    留存率的计算主要涉及到一个时间参数,而与之对比的时间则是昨天,简单的说即取昨天的用户里仍然在今天活跃的比率。

    SQL代码

    -- 提供个mysql版的供参考,ORACLE时间函数需做相应替换。
    WITH dws_user_active_t AS
    (SELECT '20211012' ds,1 device,100 user_id,30 active_score UNION
    SELECT '20211012' ds,2 device,101 user_id,30 active_score UNION
    SELECT '20211012' ds,1 device,102 user_id,20 active_score UNION
    SELECT '20211013' ds,1 device,103 user_id,10 active_score UNION
    SELECT '20211013' ds,2 device,102 user_id,60 active_score UNION
    SELECT '20211013' ds,1 device,104 user_id,90 active_score UNION
    SELECT '20211013' ds,3 device,101 user_id,85 active_score 
    ), middle AS(
    	SELECT COUNT(DISTINCT CASE WHEN flag = 0 THEN  user_id END) yesterday,
    	COUNT(DISTINCT CASE WHEN flag = 1 THEN  user_id END ) today,
    	COUNT(DISTINCT CASE WHEN flag = 0 THEN  user_id END) +
    	COUNT(DISTINCT CASE WHEN flag = 1 THEN  user_id END )-
    	COUNT(DISTINCT user_id ) preserved,
    	COUNT(DISTINCT user_id ) unique_twodays
    	FROM
    	(
    		SELECT * ,
    		CASE WHEN A.ds =DATE_FORMAT(now(),'%Y%m%d') THEN 1 ELSE 0 END AS flag
    		FROM dws_user_active_t A
    		WHERE 
    		A.ds >= DATE_FORMAT(ADDDATE(now(), INTERVAL -1 DAY),'%Y%m%d')
    		AND A.ds <=DATE_FORMAT(now(),'%Y%m%d')
    	)A
    )
    
    -- 1 生成留存率
    SELECT preserved/yesterday preserverate,A.* 
    FROM middle A;

    展开全文
  • 关于问题的背景描述和相关数据的下载见另一个帖子 下载完csv数据后需要先将数据导入到mysql... select *, (次日留存人数*1.0/新注册总人数) as 次日留存率, (7日留存人数*1.0/新注册总人数) as 7日留存率 from temp3;

    关于问题的背景描述和相关数据的下载见另一个帖子

    下载完csv数据后需要先将数据导入到mysql里面再进行查询

    import pandas as pd

    import numpy as np

    #将csv数据变成数据框

    data1=pd.read_csv("D:\\A2021_02_13.csv")

    #-----------------------------------------------------------------

    # 接下来将数据框导出到mydql数据库中

    #---------------------------------------------------------------------------------

    #创建python和mysql的连接通道

    from sqlalchemy import create_engine

    connection1=create_engine("mysql+pymysql://root:12345@localhost:3306/test1?charset=utf8")

    #create_engine的用法可以用help(sqlalchemy.engine)看下。

    #将数据框data1借助通道倒入到mysql test1数据库的biao1

    pd.io.sql.to_sql(data1,"biao1",connection1,schema="test1",if_exists="replace")

    下面我们用mysql代码来实现查询计算

    涉及到的关键词有 mysql 创建临时表 删除临时表 两个整数字段如何相除

    drop temporary table if exists temp0;

    create temporary table temp0

    select 团长ID,STR_TO_DATE(审核通过日期,'%Y-%m-%d' ) as 审核通过日期1,

    datediff(统计日期,审核通过日期) as t,

    sum(销量) as 销量1,

    max(if(销量>0,1,0)) 是否消费

    from biao1

    where (STR_TO_DATE(审核通过日期,'%Y-%m-%d' )>=str_to_date("2021-01-01",'%Y-%m-%d')

    and STR_TO_DATE(审核通过日期,'%Y-%m-%d' )<=str_to_date("2021-01-24",'%Y-%m-%d')

    and datediff(biao1.统计日期,biao1.审核通过日期)>=0

    and datediff(统计日期,审核通过日期)<=7)

    group by biao1.审核通过日期,biao1.团长ID,biao1.统计日期

    order by 审核通过日期,团长ID,t

    ;

    drop temporary table if exists temp1;

    create temporary table temp1

    select 审核通过日期1,团长ID,

    max(case t when 0 then 是否消费 else NULL end) as t0,

    max(case t when 1 then 是否消费 else NULL end) as t1,

    max(case t when 2 then 是否消费 else NULL end) as t2,

    max(case t when 3 then 是否消费 else NULL end) as t3,

    max(case t when 4 then 是否消费 else NULL end) as t4,

    max(case t when 5 then 是否消费 else NULL end) as t5,

    max(case t when 6 then 是否消费 else NULL end) as t6,

    max(case t when 7 then 是否消费 else NULL end) as t7

    from temp0 group by 团长ID;

    drop temporary table if exists temp2;

    create temporary table temp2

    select temp1.*,1 as count1,

    (if((t0+t1)=2,1,0)) as 是否次日留存,

    (if((t0+t1+t2+t3+t4+t5+t6)=7,1,0)) as 是否7日留存

    from temp1;

    drop temporary table if exists temp3;

    create temporary table temp3

    select sum(count1) as 新注册总人数,

    sum(是否次日留存) as 次日留存人数,

    sum(是否7日留存) as 7日留存人数

    from temp2 group by count1;

    select *,

    (次日留存人数*1.0/新注册总人数) as 次日留存率,

    (7日留存人数*1.0/新注册总人数) as 7日留存率

    from temp3;

    796d9f1b84679da7449192cdbd85bca1.png

    展开全文
  • 原始数据:创角日志和登录日志导入需要的库 pandasimport pandas as pd1、获取数据#读取创角日志df_create = pd.read_csv(r'F:\Python\常见统计——留存\创角日志\创角日志.csv')创角日志#读取登录日志df_login = pd...
  • 留存率计算公式 留存率=登陆用户数/新增用户数*100% (一般统计周期为天) 新增用户数:应用在当前时间段新注册登录应用的用户数; 登录用户数:在当前时间段至少登录过一次的用户数; 活跃用户数:登录用户数-新增...
  • 用户留存率 1. 什么是用户留存 在互联网行业中,用户在某段时间内开始使用应用,经过一段时间后,仍然继续使用该应用的用户,被认作是留存用户。 这部分用户占当时新增用户的比例即是留存率,会按照每隔1单位时间...
  • 用户留存率计算.pdf

    2021-09-30 21:59:52
    用户留存率计算.pdf
  • mysql查询用户留存问题(用户留存和用户留存率问题)。 计算某日用户留存率(次日、3日、7日、30日,...)。 计算某日新增用户留存率(次日、3日、7日、30日、...)。
  • 牛客网的一道数据库题:计算用户的平均次日留存率 思路: 1、将同一张表连接起来(这里用 left join ,因为左表保留的是所有的刷题记录,右表保留的是第二天还来刷题的记录) 2、通过datediff()过滤不符合的数据...
  • hive 关于用户留存率计算

    千次阅读 2018-12-18 16:26:07
    最近接到一个需求,需要统计用户留存率,而且要一次统计多个模块,多个日期的留存率,那如何通过一段hive(SQL)来实现该需求呢,在网上看到一篇很精妙的文章,参考这篇文章实现了需求,可以说是目前见过的一次性统计...
  • 我是先学习了这位大神留存率的写法,然后我自己完善了一下。@阿柯@ 首先声明一下留存的概念 次日留存:1月1日注册的新用户,在1月2日登陆了app。即登陆日期 - 注册日期 = 1天。 3日留存:1月1日注册的新用户,在1月...
  • time", "registerNum") //两个表相关联 val reJoinSi: DataFrame = signinNum.join(registerNum,Seq("register_time")) //计算留存率 先将时间戳的毫秒数转换为秒,用from_unixtime函数将时间戳转换为日期格式 //...
  • 对于留存率这个概念,相信对每一位游戏运营和分析人员并不陌生,甚至搜索“留存率”也会发现很多关于留存率的解释,留存率的定义,以及留存率的指标表现等等文章。实在是没有什么新意可言。但是笔者前两天和朋友讨论...
  • 牛客网打卡:SQL29 计算用户的平均次日留存率 解题思路 题目给出的意思: 不考虑用户的答题情况,只考虑是否答题了 也就是选出两张表,第一张是当天的答题情况,第二张是第二天的情况 时刻理解新的虚拟表和使用的...
  • DATE_ADD()函数、COUNT函数计算空值与非空;
  • SQL——计算次日留存率

    千次阅读 2021-05-12 10:25:23
    计算每日次日留存率 原数据表:user_login_table表 表字段:用户、登陆日期 sql查询: 计算用户是否是次留用户 select t1.user_name,max(case when datediff(day,date(newdate),date(logindate)) = 1 then 1 ...
  • 激活用户留存率

    2016-12-19 16:19:53
    用户行为触发的日志上报,已经存放在Hive的外部分区表中. 结构如下: 主要字段内容 dt表示日期,如20160510 platform表示平台,只有两个选项,苹果和安卓 mid是用户机器码,类似于网卡MAC地址什么的 pver是版本 channel是...
  • 名词解释:用户留存率:在互联网行业中,用户在某段时间内开始使用应用,经过一段时间后,仍然继续使用该应用的用户,被认作是留存用户。这部分用户占当时新增用户的比例即是留存率,会按照每隔1单位时间(例日、周、...
  • 用户留存率问题计算详解

    千次阅读 2020-04-30 21:03:34
    数据分析工作中经常会遇到计算用户留存的相关问题,例如我们现在遇到这样一个场景, 某APP,它的新增用户的次日留存、7日留存、30日留存为60%、30%、15%,请模拟出来,每天如果日新增5万用户,那么30天后,它的日活数...
  • SQL用户留存率

    千次阅读 2020-12-15 16:02:20
    现需要计算日活跃用户,以及次日、3日、7日的用户留存率: 解决思路如下: 单个表既有新客户,也有老用户每天的登录时间,所以进行自联结计算留存用户 select a.dt ,count(distinct a.id) as `日活跃用户` ,...
  • Mysql-计算n日留存率

    2022-02-13 10:37:14
    一、创建一张包含每个用户最早登入日期的表 select user_id,min(date) as first_day from a2_userbehavior_csv group by user_id ...三、将两个表按照user_id拼接,并且计算日期时间差 select t1.*,t2.d
  • 1、如果没有自动化分析平台,如何很好的利用pandas进行留存率计算呢?传统的方式可以使用sql语句完成,但是可操作性比较差,过程相对于繁琐,现在小编就带大家进行pandas 方法进行留存率计算留存率计算公式: a =...
  • 用户留存率详解与计算方法案例

    万次阅读 2017-10-19 10:38:13
    留存率,越来越受到大家的关注,从网站用户到客户端产品,游戏产品,无线 APP 产品, 都非常重视这一指标,留存率成为衡量一个产品是否健康成长的重要指标之一。       留存率的“ 40 – 20 – 10 ...
  • SQL 计算用户的平均次日留存率
  • SQL实现次日、三日及七日用户留存率计算

    万次阅读 多人点赞 2020-11-02 17:42:45
    现在要计算用户的次日留存率、三日留存率、七日留存率等。在计算之前,我们先来弄清楚这些留存的定义。 次日留存:即当日登录后,第二天也登录的用户,称为次日留存用户。 三日留存:即当日登录后,第三天
  • 用户xx日留存率计算

    2022-02-26 22:15:33
    1. 计算留存 1.1 首选圈定一批用户 假设以开始日期-结束日期的新注册用户作为新用户,首先找出新用户: select user, login_date from where login_date betweed xxx and yyy。 select user_id, str_to_...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 9,102
精华内容 3,640
关键字:

如何计算用户留存率