public List<TrackedUserOccurance> GetReactsfrom8to14DaysAgoRange(DateTime ProcessDate) { List<TrackedUserOccurance> userList = new List<TrackedUserOccurance>(); //have to add a day to the end of the range we're looking at if we're using the 00:00:00 midnight beginning of the range dates //so we can get the whole date, either we can use INTERVAL 9 DAY in the SQL statement or ADD DAY in C# DateTime land. -- PJ string query = String.Format(@"SELECT DISTINCT(UserId) as UserId, LoginTimestamp FROM {0} WHERE LoginTimestamp BETWEEN SUBDATE('{1}', INTERVAL 14 DAY) AND SUBDATE('{2}', INTERVAL 8 DAY) AND RetentionCohortType = 2 ORDER BY LoginTimestamp desc;", USER_SESSION_META_TABLE, ProcessDate.ToString("yyyy-MM-dd 00:00:00"), ProcessDate.AddDays(1).ToString("yyyy-MM-dd 00:00:00")); DataTable UsersTable = DBManager.Instance.Query(Datastore.Monitoring, query); if (UsersTable.Rows.Count > 0) { foreach (DataRow UserRecord in UsersTable.Rows) { TrackedUserOccurance user = new TrackedUserOccurance() { Date = DateTime.Parse(UserRecord["LoginTimestamp"].ToString()), UserId = UserRecord["UserId"].ToString(), CohortType = RetentionCohortType.ReactivatedUser }; userList.Add(user); } } return userList; }
public List<TrackedUserOccurance> GetRetentionCohorts(DateTime ProcessDate) { List<TrackedUserOccurance> userList = new List<TrackedUserOccurance>(); //have to add a day to the end of the range we're looking at if we're using the 00:00:00 midnight beginning of the range dates //so we can get the whole date, either we can use INTERVAL 9 DAY in the SQL statement or ADD DAY in C# DateTime land. -- PJ string query = String.Format(@" SELECT UserId, min(LoginTimestamp) as LoginTimestamp, RetentionCohortType FROM {0} WHERE LoginTimestamp >= SUBDATE('{1}', INTERVAL 13 DAY) AND LoginTimestamp <= SUBDATE('{1}', INTERVAL 7 DAY) GROUP BY UserId ORDER BY RetentionCohortType, UserId;", USER_SESSION_META_TABLE, ProcessDate.ToString("yyyy-MM-dd 00:00:00")); DataTable UsersTable = DBManager.Instance.Query(Datastore.Monitoring, query); if (UsersTable.Rows.Count > 0) { foreach (DataRow UserRecord in UsersTable.Rows) { TrackedUserOccurance user = new TrackedUserOccurance() { Date = DateTime.Parse(UserRecord["LoginTimestamp"].ToString()), UserId = UserRecord["UserId"].ToString(), CohortType = (RetentionCohortType)Convert.ToInt32(UserRecord["RetentionCohortType"].ToString()) }; userList.Add(user); } } return userList; }
public TrackedUserOccurance DetermineUserType(Login User) { TrackedUserOccurance occurence = new TrackedUserOccurance(); occurence.Date = User.LoginTimestamp; occurence.UserId = User.UserId; if (User.InstallDateRecord == 1) { // todo: if you install, quit and then play on the same day... should this be marked as n, and c? occurence.CohortType = RetentionCohortType.NewUser; } else if (IsReactivatedUser(User)) { occurence.CohortType = RetentionCohortType.ReactivatedUser; } else { occurence.CohortType = RetentionCohortType.ContinuingUser; } return occurence; }