留存指的就是"有多少用户留下来了"。留存用户和留存率体现了应用(游戏)的质量和保留用户的能力。

次日留存: 如昨天新增了100个玩家，今天这100个玩家里面登录了54个，那昨天的新增次日留存率即为
(54/100)*100%=54%

3日留存: 如前天新增了100个玩家，今天这100个玩家里面登录了35个，那前天的新增三日留存率即为
(35/100)*100%=35%

 

// 留存统计
class Stat_keep extends MY_Controller {
// 5分钟执行一次
public function run() {
$time = strtotime('today');
$this->stat($time);
}

// 执行一次昨天的数据
public function run_yesterday() {
$time = strtotime('yesterday');
$this->stat($time);
}

private function stat($sTime) {
$eTime = $sTime+86400;

// 统计日登陆的角色id
$sql_login = "SELECT DISTINCT rid FROM role_login WHERE login_time>={$sTime} AND login_time<{$eTime}";

$date = array(
1 => $sTime-86400, //2日留存
2 => $sTime-86400*2, //3日留存
3 => $sTime-86400*3, //4日留存
4 => $sTime-86400*4, //5日留存
5 => $sTime-86400*5, //6日留存
6 => $sTime-86400*6, //7日留存
7 => $sTime-86400*7, //8日留存
14=> $sTime-86400*13, //14日留存
30=> $sTime-86400*29, //30日留存
);

// 获取留存2,3,4,5,6,7,8,14,30日创建的角色id
$where[] = "(time>=" .$date[7]. " AND time<{$sTime})";
$where[] = "(time>=" .$date[14]. " AND time<" .($date[14]+86400).")";
$where[] = "(time>=" .$date[30]. " AND time<" .($date[30]+86400).")";
$sql_register = "SELECT rid,`time`,platform FROM `register_log` WHERE ". implode(" OR ", $where);

foreach ($this->server_list as $key=>$info) {
// 连接服务器
$server_db = $this->connect_server($info['id']);
if (!$server_db) continue;

// 创建帐号rid列表
$create_rids = array();

// 细分渠道创建rid列表
$platform_rids = array();

// 获取留存日创建的账号
$query= $server_db->query($sql_register);
if($query->num_rows() <= 0) continue;
foreach ($query->result_array() as $v) {
$ymd = strtotime(date("Y-m-d", $v['time']));
$k = array_search($ymd, $date);
$create_rids[$k][]=$v['rid'];
$platform_rids[$v['platform']][$k][]=$v['rid'];
}

//获取统计日登陆的账号
$row = $server_db->query($sql_login)->result_array();
$logon_rids = array_column($row, 'rid');

//分渠道统计
foreach ($platform_rids as $platform => $val) {
// 如果不含当前数据则创建
$query = $this->db->select('id')->from('stat_keep')
->where('stat_date', $date[1])
->where('server_id', $info['id'])
->where('platform', $platform)
->get()->row_array();
if (!$query) {
$this->db->insert('stat_keep', array(
"stat_date" => $date[1],
'server_id' => $info['id'],
'platform' => $platform
));
}

foreach ($val as $date_index => $rids) {
// 注册用户数
$new_role_num = count($rids);
// 获取注册用户在这天有多少人登录
$num = count(array_intersect($rids, $logon_rids));

$updat = array("day{$date_index}"=>$num);
if (1==$date_index) {
$updat['new_role'] = $new_role_num;
}
// 更新留存数据
$this->db->where('stat_date', $date[$date_index])
->where('server_id', $info['id'])
->where('platform', $platform)
->update('stat_keep', $updat);
}
}
// 关闭游戏服db
$server_db->close();
// 释放参数
unset($query, $row, $create_rids, $platform_rids);
}
// 释放参数
unset($where, $sql_register);
}