日活百万以内，统计用户新增活跃留存方案

# 背景

产品上线后，出于运营的需要，我们要对用户进行跟踪，分析用户数据。本文要介绍的是如何统计用户新增数、活跃数和留存率，时间跨度是天，即统计每日新增（DNU），日活（DAU）和某日新增的一批用户在接下来的一段时间内每天活跃的百分比。

# 使用范围

本方案适用于用户量不太大（日活在百万以内，日活百万以上不是不能用，只是在统计数据时耗时太长不太合适），尤其适合小团队或个人开发者（比如你公司服务端接口开发是你，运维也是你，现在老板又来叫你做数据报表）。如果你的产品的日活有几百万甚至几千万或过亿，这样的产品当然是完全可以养一个大数据部门的，本方案并不适用这种情况。

# 涉及到的工具和技术点

shell脚本

本方案需要你懂一点儿shell，起码能看懂，也要求你知道怎么写crontab定时任务。

MySQL

本方案需要你熟练使用sql，知道怎么定义存储过程，知道分区表的概念和用法。

# 实现过程

1.目标

由于数据量是不断增加的，所以我们的目标是要把原始数据聚合成一张可以直接用一条select语句就可以查看每日新增、日活和留存率的表，并且只能做单表查询，否则当数据量增大时，联表查询的速度会大大下降。而且为了防止出错，我们的数据还需要可以重跑但是不会影响到已存在的数据。

最终呈现给运营人员看到的数据是这样的：

新增-活跃表

用户留存表

简单解释一下上面两个表的结构：因为我们是按天统计的，所以日期都是以天为单位，用户可能有不同的国家或地区，不同版本，不同手机型号等等，所以就有了各个维度。用户留存表的数据要注意，比如今天是2022年2月9日，那么就只能统计到9号的新增，9号新增用户的日次留存是10号才能统计到的，但是8号新增用户的次日留存在今天（也就是9号）就统计出来了，所以留存的数据是一个阶梯形状的。

2.收集数据

为了方便介绍本方案，这里假设只有日期、国家、版本号三个维度。

收集数据的下一步是数据入库，为了方便，需要把数据格式进行转换。因为服务端接口现在一般都是使用json格式的数据进行通信，如果直接把json格式的数据输出到日志文件，处理起来会非常麻烦，所以需要在服务端接收到统计日志时，把数据输出到单独的日志文件中，还要按照MySQL的load命令可以识别的数据格式。

在输出日志之前，先确定好都需要哪些数据，这里需要的数据如下：

• ts：timestamp，时间戳。服务端接收到日志的时间，格式是yyyy-MM-DD HH:mm:ss。

• device_id：设备id，这里是用来唯一标识用户的一个字符串，比如在android设备上可以用android id，总之这个字段是用来确定一台设备的，要保证不同的设备设备id不同。

• country：用户所在的国家。如果你是只做一个国家的，比如只做国内市场，也可以把这个字段换成省份或者城市，总之根据运营需求去改变。

• version：应用版本号，一般是一个整数。

于是就可以确定日志的格式如下：

2022-02-09 13:14:15||aaaaaa||CN||100
2022-02-09 13:14:16||bbbbbb||US||100
2022-02-09 13:14:17||cccccc||NL||100

也就是一条数据占一行，字段之间使用双竖线分隔，当然这里不一定是双竖线分隔，也可以换成其它的，原则是字符数少而且不能被字段的值包含，不然在数据入库时会出现字段不对应的问题。

再考虑两个方面：

• 如果数据量较大要怎么处理？

• 可能有的字段的长度没法一下子确定怎么处理？

• 保留数据的策略应该怎样设置？

第1个问题，当数据量大时，可以考虑把日志文件切割成更小的时间段，比如每小时一个日志文件，然后下一小时就把上一个小时的数据入库。

第2个问题，原始数据表的字段长度定义得大一些，做到即使以后字段有变化，也可以适应。

第3个问题，因为我们的目标是跑出最后的报表，所以不可能一直保存着所有的原始日志数据，为了防止出错，可能只是保留最近几天的，一个简单的策略是在每次日志数据入库前用delete语句把前几天的数据删除了，但是直接使用delete有两个问题：一是MySQL要扫描全表删除数据，比较耗时；二是MySQL的delete + where删除可能只是假删除，磁盘不会立即释放。

所以这里使用分区表来实现，每天的数据作为一个分区，删除数据时直接删除分区，数据入库时先创建分区。

于是得到原始数据表的DDL如下：

CREATE TABLE `st_base` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `dd` int(11) NOT NULL DEFAULT '0' COMMENT '天数，格式是yyyyMMddHH',
  `ts` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '时间戳',
 `device_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '设备id',
 `country` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '国家',
 `version` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '版本号',
 PRIMARY KEY (`id`,`dd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='原始数据表'
/*!50100 PARTITION BY LIST (dd)
(PARTITION p20220209 VALUES IN (20220209) ENGINE = InnoDB) */

3.数据入库

有了格式化的日志文件和数据表，就可以通过shell脚本把数据入库了。步骤如下：

• 删除历史日志的分区

• 删除执行日期的分区（这一步在重跑数据很有用）

• 创建执行日期的分区

• 使用MySQL的load命令把数据从日志文件加载到数据库中

这里只说一下重要的命令：

1,删除和创建分区可以分别使用下面两个命令：

drop_sql="alter table st_base drop partition pxxxxxxxx" # 这里的xxxxxxxx要根据执行日期转换一下
add_sql="alter table st_base add partition (partition pxxxxxxxx values in (xxxxxxxx) engine=innodb)"

mysql -u${username} -p${password} -D${database} -e "${drop_sql}"

mysql -u${username} -p${password} -D${database} -e "${add_sql}"

上面使用mysql命令指定了用户名、密码、数据库名和sql语句（-e参数）

2.从文件加载数据入库

log_file=xxxx #日志文件名
dd=xxxxxxxx #执行日期
load_sql="load data local infile '${log_file}' ignore into table st_base fields terminated by '||' lines terminated by '\n' (ts,device_id,country,version) set dd='${dd}'"
mysql -u${username} -p${password} -D${database} -e "${load_sql}"

3.定时任务

因为我们是每天入库一次，所以可以在每天的0时10分去跑上面的脚本任务。假设上面的脚本文件保存为st_base.sh

可以通过crontab -e编辑定时任务：

10 0 * * * /path/to/job/st_base.sh

当然最好的做法是把执行日期当做脚本的参数传入，这样可以实现重跑某天的数据了。

4.清洗数据

在上一步得到了原始数据之后，接下来的工作都可以在MySQL中完成，首先要清洗数据。

这一步的目的有两个：

• 确定好数据类型

• 数据去重

先创建一个临时表tmp_base，这个表用来转换数据类型，如果有一些字段的值需要转换的也可以在这里做（举个例子：假如客户端获取到的国家有几种途径，分别是获取了sim卡国家，网络国家，手机国家，到了服务端后服务器根据客户端的ip也解析出了一个国家，但是运营的时候可能只需要一个最接近用户的真实国家，那么就可以按照优先级来确定，当然本文没有多个国家的问题），DDL如下：

CREATE TABLE `tmp_base` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `dt` date NOT NULL COMMENT '日期',
    `device_id` VARCHAR ( 32 ) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '设备id',
    `country` VARCHAR ( 8 ) COLLATE utf8_bin DEFAULT NULL,
    `version` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '版本号',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin COMMENT = '用户基础临时表'

再创建一个用户总表total_base，这个表用来存放所有用户的数据，每个用户只有一条数据，DDL如下：

CREATE TABLE `total_base` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `dt` date NOT NULL COMMENT '新增日期',
  `device_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '设备id',
 `country` varchar(8) COLLATE utf8_bin DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `device` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户总表';

创建一个流水表flow_base，同样以日期作为分区字段，DDL如下：

CREATE TABLE `flow_base` (
    `id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `dt` date NOT NULL DEFAULT '2022-01-01' COMMENT '日期',
    `device_id` VARCHAR ( 32 ) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '设备id',
    `country` VARCHAR ( 8 ) COLLATE utf8_bin DEFAULT NULL,
    `version` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '版本号',
    `rdt` date NOT NULL DEFAULT '2022-01-01' COMMENT '用户注册日期',
    `dd` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '日期(yyyyMMdd)，用来做分区',
    PRIMARY KEY ( `id`, `dd` ),
UNIQUE KEY `unique` ( `dt`, `device_id`, `dd` ) 
) ENGINE = INNODB CHARSET = utf8 COLLATE = utf8_bin COMMENT = '用户基础流水表' 
/*!50100 PARTITION BY LIST ( dd ) ( PARTITION p20220209 VALUES IN ( 20220209 ) ENGINE = InnoDB ) */

注意到流水表flow_base中有一个rdt的字段，这字段是用来存放这个用户的注册日期，方便后面统计留存使用的。

准备好表结构之后，开始清洗数据。清洗数据使用MySQL的存储过程功能，创建一个存储过程sp_data_cleaning，在这个存储过程中，需要做以下几件事：

• 把原始数据表st_base中的数据清洗到临时表tmp_base，如果有字段的值需要转换也在这一步做。

• 把临时表tmp_base中的用户添加到用户总表total_base中。

• 把临时表tmp_base中的数据添加到流水表中，并且联合用户总表，把用户的注册日期也填充好。

于是可以得到存储过程sp_data_cleaning的DDL如下：

CREATE PROCEDURE `sp_data_cleaning`(IN v_dt VARCHAR(10))
BEGIN
 # 变量
 declare pname varchar(10);
 declare v_is_pname_exists int;
 
 # 清除tmp_base表数据
    truncate table tmp_base;


 # 清洗数据
  insert into tmp_base(
   `dt`,
    `device_id`,
   `country`,
    `version`
   )
  select
   v_dt,
   `device_id`,
    `country`,
    `version`
   from `st_base`
   where `dd` = replace(v_dt,'-','');
   
 # 数据加入用户总表
  insert ignore into total_base(
   `dt`,
   `device_id`,
   `country`,
   `version`
   )
  select
   `dt`,
   `device_id`,
   `country`,
   `version`
  from tmp_base;
  
 # 给流水表创建分区
 select concat('p', replace(v_dt, '-', '')) into pname;
 
 # 查找是否已经存在执行日期的分区
 select max(a) into v_is_pname_exists
 from (
  select 1 as a from information_schema.PARTITIONS 
  where `TABLE_SCHEMA` = 'your_database_name' 
   and `TABLE_NAME` = 'flow_base'
   and `PARTITION_NAME`=pname
  union all
  select 0 
  ) t;
 
 # 如果已经存在先删除
 if v_is_pname_exists=1 then
 set @drop_sql=concat('alter table flow_base drop partition ', pname);
 prepare stmt from @drop_sql;
 execute stmt;
 deallocate prepare stmt;
 end if;


 # 创建分区
 set @add_sql=concat('alter table flow_base add partition (partition ', pname, ' values in (', v_date, ') ENGINE = InnoDB)');
 prepare stmt from @add_sql;
 execute stmt;
 deallocate prepare stmt;  


 # 数据加入流水表
  insert ignore into flow_base(
   `dt`,
   `device_id`,
   `country`,
   `version`,
   `rdt`,
   `dd`
   )
  select
   v_dt,
   t1.`device_id`,
   t1.`country`,
   t1.`version`,
   t2.`dt`,
   replace(v_dt, '-', '')
  from tmp_base t1
  left outer join total_base t2
  on (t1.`device_id`=t2.`device_id`);
END

五、数据聚合

经过上面几个步骤的处理，现在已经得到了半成品的数据，可以进行聚合了。根据第一步的目标报表，可以确定两个表的结构：一个是用户的新增-活跃表，另一个是用户的留存表，DDL如下：

新增-活跃表：

CREATE TABLE `rpt_base_active` (
    `id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `dt` date NOT NULL DEFAULT '2022-01-01' COMMENT '日期',
    `country` VARCHAR ( 8 ) COLLATE utf8_bin DEFAULT NULL,
    `version` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '版本号',
    `new_users` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '新增数',
    `active_users` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '活跃数',
    PRIMARY KEY ( `id` ),
KEY `index1` ( `dt` ),
KEY `index3` ( `country`, `version` )) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin COMMENT = '用户新增活跃表'

用户留存表（这里假设只看7天的留存情况，如果需要看更多留存天数，可以自行修改）：

CREATE TABLE `rpt_base` (
    `id` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `dt` date NOT NULL DEFAULT '2022-01-01' COMMENT '日期',
    `country` VARCHAR ( 8 ) COLLATE utf8_bin DEFAULT NULL,
    `version` INT ( 11 ) NOT NULL DEFAULT '0' COMMENT '版本号',
    `d0` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '新增数',
    `d1` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '次日留存数',
    `d2` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '2日留存数',
    `d3` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '3日留存数',
    `d4` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '4日留存数',
    `d5` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '5日留存数',
    `d6` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '6日留存数',
    `d7` SMALLINT ( 4 ) NOT NULL DEFAULT '0' COMMENT '7日留存数',
    PRIMARY KEY ( `id` ),
    KEY `index1` ( `dt` ),
KEY `index3` ( `country`, `version` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin COMMENT = '用户留存表'

注意，以上两个表的索引创建并不是固定的，需要根据运营的实际情况去创建相关的索引。

在跑数据之前，先聚合一下执行日期的数据，创建一个临时表a_flow_base，这个表用来初步聚合数据，DDL如下：

CREATE TABLE `a_flow_base` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `dt` date NOT NULL DEFAULT '2022-01-01' COMMENT '日期',
 `country` varchar(8) COLLATE utf8_bin DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '应用版本号',
  `rdt` date NOT NULL DEFAULT '2022-01-01' COMMENT '用户注册日期',
  `rdays` smallint(4) NOT NULL DEFAULT '0' COMMENT '留存天数',
  `users` smallint(4) NOT NULL DEFAULT '0' COMMENT '用户数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户基础数据聚合表'

首先初步聚合用户数据，创建一个存储过程sp_a_flow_base，DDL如下：

CREATE PROCEDURE `sp_a_flow_base`(in v_dt char(10))
BEGIN
 declare d0 date;
 declare d1 date;
 declare d2 date;
 declare d3 date;
 declare d4 date;
 declare d5 date;
 declare d6 date;
 declare d7 date;
 
 select date_sub(v_dt, interval 0 day) into d0;
 select date_sub(v_dt, interval 1 day) into d1;
 select date_sub(v_dt, interval 2 day) into d2;
 select date_sub(v_dt, interval 3 day) into d3;
 select date_sub(v_dt, interval 4 day) into d4;
 select date_sub(v_dt, interval 5 day) into d5;
 select date_sub(v_dt, interval 6 day) into d6;
 select date_sub(v_dt, interval 7 day) into d7;
 
 # 清除a_flow_base表数据
 truncate table a_flow_base;
 
 insert into a_flow_base(
  `dt`,
  `country`,
   `version_code`,
   `rdt`,
   `rdays`,
   `users`
  )
 select 
  t1.`dt`,
   t1.`country`,
   t1.`version`,
   t1.`rdt`,
   datediff(t1.`dt`, t1.`rdt`) as rdays,
   count(*) as users
 from flow_base t1
 where t1.`dt` in (d0,d1,d2,d3,d4,d5,d6,d7)
 group by 
  t1.`dt`,
   t1.`country`,
   t1.`version`,
   t1.`rdt`;
END

初步聚合了数据后，开始正式聚合数据，创建一个存储过程sp_rpt_base，DDL如下：

CREATE PROCEDURE `sp_rpt_base`(in v_dt char(10))
BEGIN
 declare d0 date;
 declare d1 date;
 declare d2 date;
 declare d3 date;
 declare d4 date;
 declare d5 date;
 declare d6 date;
 declare d7 date;
 
 select date_sub(v_dt, interval 0 day) into d0;
 select date_sub(v_dt, interval 1 day) into d1;
 select date_sub(v_dt, interval 2 day) into d2;
 select date_sub(v_dt, interval 3 day) into d3;
 select date_sub(v_dt, interval 4 day) into d4;
 select date_sub(v_dt, interval 5 day) into d5;
 select date_sub(v_dt, interval 6 day) into d6;
 select date_sub(v_dt, interval 7 day) into d7;
 
 # 删除数据
 delete from rpt_base_active where `dt` = v_dt;
 
 insert into rpt_base_active (
  `dt`,
  `country`,
   `version`,
   `new_users`,
   `active_users`
  )
 select
  `dt`,
  `country`,
  `version`,
  sum(if(`dt`=`rdt`, 1, 0)) as `new_users`,
  sum(1) as `active_users`
 from flow_base
 where dt=v_dt
 group by 
  `dt`,
  `country`,
  `version`
  ;


 # 删除数据
 delete from rpt_base where `dt` in (d0,d1,d2,d3,d4,d5,d6,d7);
 
 insert into rpt_base(
  `dt`,
  `country`,
   `version`,
   `d0`,
   `d1`,
   `d2`,
   `d3`,
   `d4`,
   `d5`,
   `d6`,
   `d7`
  )
 select
  t1.`rdt`,
  t1.`country`,
  t1.`version`,
  sum(case when t1.`rdays`=0 then t1.`users` else 0 end) as d0,
  sum(case when t1.`rdays`=1 then t1.`users` else 0 end) as d1,
  sum(case when t1.`rdays`=2 then t1.`users` else 0 end) as d2,
  sum(case when t1.`rdays`=3 then t1.`users` else 0 end) as d3,
  sum(case when t1.`rdays`=4 then t1.`users` else 0 end) as d4,
  sum(case when t1.`rdays`=5 then t1.`users` else 0 end) as d5,
  sum(case when t1.`rdays`=6 then t1.`users` else 0 end) as d6,
  sum(case when t1.`rdays`=7 then t1.`users` else 0 end) as d7
  from a_flow_base t1
  group by
   t1.`rdt`,
   t1.`country`,
    t1.`version`
   ;
   
END

为了方便调用整个过程，可以再创建一个存储过程，把全过程写在一起，一次执行。创建一个存储过程sp_user，DDL如下：

CREATE PROCEDURE `sp_user`(in v_dt char(10))
BEGIN
 call sp_tmp_base(v_dt);
 call sp_data_cleaning(v_dt);
 call sp_a_flow_base(v_dt);
 call sp_rpt_base(v_dt);
 
END

这样，就可以添加定时任务每天定时跑前一天的数据了。

# 写在最后

流水表flow_base应该保留几天的数据？

这个看你的用户留存表需要看多少天留存数据，如果你要看7日留存，那么保留最近8天的数据，如果是想看30天留存，就保留最近31天的数据，依次类推。

如果运营人员或老板9点半上班，每天凌晨的0点开始跑前一天的数据，你将有9个半小时来跑前一天的数据。当然如果一天的数据要跑2个小时以上，还是考虑用Hadoop来做吧。