⽇活百万以内，统计⽤户新增活跃留存⽅案
# 背景
产品上线后，出于运营的需要，我们要对⽤户进⾏跟踪，分析⽤户数据。本⽂要介绍的是如何统计⽤户新增数、活跃数和留存率，时间跨度是天，即统计每⽇新增（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 p2******* 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编辑定时任务：
100***/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 p2******* 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 (
select1as a from information_schema.PARTITIONS
where `TABLE_SCHEMA` ='your_database_name'
and `TABLE_NAME` ='flow_base'
and `PARTITION_NAME`=pname
union all
select0
) t;
# 如果已经存在先删除
if v_is_pname_exists=1then
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 0day) into d0;
select date_sub(v_dt, interval 1day) into d1;
mysql下载哪个版本好2022select date_sub(v_dt, interval 2day) into d2;
select date_sub(v_dt, interval 3day) into d3;
select date_sub(v_dt, interval 4day) into d4;
select date_sub(v_dt, interval 5day) into d5;
select date_sub(v_dt, interval 6day) into d6;
select date_sub(v_dt, interval 7day) into d7;
# 清除a_flow_base表数据
truncate table a_flow_base;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论