Game Player's retention rate statistics implementation

Players in a certain period of time to register to start the game, after a period of time, still continue to play is considered to be retained; This part of the user accounted for the new user ratio that is the retention rate, according to every 1 units of time (example days, weeks, months) to statistics. As the name suggests, retention refers to "how many players have stayed." The retention user and retention rates reflect the quality of the application and the ability to retain the user.

Next-day retention rate	Number of registered users of the game user/statistics day on the second day after first landing
3rd retention Rate	Number of registered users/Statistics days after landing on the third day after first landing
7th Retention Rate	Number of registered users who have logged on to the game on the seventh day of the first login/statistics day
Retention of 30th	Number of registered users who have logged on to the game on the 30th day of the first login/statistics day

Retention rate in different games the algorithm is not the same as the retention rate description
New users at a certain time, after a period of time, still continue to log on to the game is considered as a retained user; the percentage of users who were added to the site was the retention rate.
For example:
September 5 New User 200, the 200 people logged on the game on 6th 100 people, 7th signed up to 80 people, 8th login has 50 people;
The retention rate for the following September 5 is 50%, the 3rd retention rate is 40% and the 4th retention rate is 25%.
This is how we calculate in our game.
This statistic is scientifically based.
Like the day you open an ad, you can see the quality of the user.
And it's going to look good with this data.

--Login log
DROP TABLE IF EXISTS log_login;
CREATE TABLE log_login (
  ID int (one) UNSIGNED not null auto_increment,
  player_id INT (one) UNSIGNED not NULL,
  Las T_login_time timestamp NOT null default ' 2000-01-01 00:00:00 ',
  register_time timestamp not null default ' 2000-01-01 00 : 00:00 ', 
  PRIMARY KEY (ID)
) engine=myisam DEFAULT Charset=utf8;

The Log_login data creates a single login for each player, redundancy for each player's registration time, in order to perform efficiency considerations for the next stored procedure.

--Statistic retention rate
DROP TABLE IF EXISTS stat_remain;
CREATE TABLE stat_remain (
  ID int (one) UNSIGNED not NULL auto_increment,
  Dru INT (one) not null--new registered user per day
  secon D_day Int (one) default NULL,
  Third_day int (one) default NULL,
  Seventh_day int (one) default NULL,
  Thirtieth_day INT (one) default NULL,  
  stat_time timestamp not null default ' 2000-01-01 00:00:00 ',
  add_time timestamp NOT null DEFA ULT ' 2000-01-01 00:00:00 ', 
  PRIMARY KEY (ID)
Engine=myisam DEFAULT Charset=utf8;

DELIMITER $$
--Statistical retention rate
DROP PROCEDURE IF EXISTS stat_remain_player$$
CREATE PROCEDURE Stat_remain_player ()
BEGIN
--Today's date
Declare today date default curdate ();
Declare yesterday date default date_sub (today, Interval 1 day);
DECLARE days_ago_2 date default date_sub (today, Interval 2 day);
DECLARE days_ago_3 date default date_sub (today, Interval 3 day);
DECLARE days_ago_4 date default date_sub (today, Interval 4 day);


DECLARE days_ago_6 date default date_sub (today, Interval 6 day);
DECLARE days_ago_7 date default date_sub (today, Interval 7 day);


DECLARE days_ago_13 date default date_sub (today, Interval day);
DECLARE days_ago_14 date default date_sub (today, Interval day);


DECLARE days_ago_29 date default date_sub (today, Interval day);
DECLARE days_ago_30 date default date_sub (today, Interval day);


--Statistics of yesterday's Dru (the number of registered people yesterday)
INSERT into Stat_remain (Dru, Stat_time, Add_time) select count (IDs), Yesterday, now () from user where role_num>0 and ro Ll=false and Last_login_time> ' 2000-01-01 ' and add_time between yesterday and today;


--Modified 2nd of the day before yesterday
Update Stat_remain Set second_day = (
Select
(select COUNT (distinct player_id) from Log_login where (Register_time between Days_ago_2 and yesterday) and (Last_login_ti Me between yesterday and today)
/
(select COUNT (distinct player_id) from Log_login where (Register_time between Days_ago_2 and yesterday))
)
where stat_time = Days_ago_2;


--Modified 3rd the day before yesterday's retention
Update Stat_remain Set third_day = (
Select
(select COUNT (distinct player_id) from Log_login where (Register_time between Days_ago_3 and Days_ago_2) and (last_login_t IME between yesterday and today)
/
(select COUNT (distinct player_id) from Log_login where (Register_time between Days_ago_3 and Days_ago_2))
)
where stat_time = Days_ago_3;


--7th retained
Update Stat_remain Set seventh_day = (
Select
(select COUNT (distinct player_id) from Log_login where (Register_time between Days_ago_7 and Days_ago_6) and (last_login_t IME between yesterday and today)
/
(select COUNT (distinct player_id) from Log_login where (Register_time between Days_ago_7 and Days_ago_6))
)
where stat_time = days_ago_7;


--14th retained
Update Stat_remain Set fourteen_day = (
Select
(select COUNT (distinct player_id) from Log_login where (Register_time between Days_ago_14 and Days_ago_13) and (Last_login _time between yesterday and today)
/
(select COUNT (distinct player_id) from Log_login where (Register_time between Days_ago_14 and Days_ago_13))
)
where stat_time = days_ago_14;


--30th retained
Update Stat_remain Set thirtieth_day = (
Select
(select COUNT (distinct player_id) from Log_login where (Register_time between days_ago_30 and days_ago_29) and (Last_login _time between yesterday and today)
/
(select COUNT (distinct player_id) from Log_login where (Register_time between days_ago_30 and days_ago_29))
)
where stat_time = days_ago_30;
End
$$


DELIMITER;

The stat_remain_player stored procedures are executed at 0 points, 0 minutes, and 1 seconds of each new day, generating stat_remain data and updating the records from the Stat_time gap of 1 days, 2 days, 6 days and 29 days.

Stream son

qq:41157121