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;
}
