How to track that a user visited the site each day for X days?

Question

There is a new badge on Stack Overflow. The "woot" badge is awarded to users visited the site each day for 30 days. How can you implement a feature like this? How can you track that a user visited the site each day for X days in the simplest way?

I thought of having two fields--one for the timestamp of the last login, and another to count the days on user continuously visited the site. The logic is to first set the counter to 1, and store the time of login as well. On the very next login, check if since the last login no more than one day past, and increment the counter, or set it back to 1. And then update the timestamp field to the current date.

Can you do it simpler?

Answer 1

Track each visit in your database with a timestamp (which you probably already do anyway). Then create an sql statement and group the result by day, while you count the number of visits that day. In the period of the last 30 days, it's not allowed to have a 0-visit day...

Answer 2

If this is the only thing you want to log, then maybe that's a good solution. However, I like to keep logic and the logging separate, both to increase the amount of raw information at my disposal, as well as allow tweaking of logic without breaking existing data.

In this case, I would log every visit or every action (depending on requirements/space/etc), and then write a sproc or a method somewhere which inspected the data and returned true (matches criteria for badge) or false (doesn't match criteria).

The only case where I would create a specific schema to hold information like that was if the calculations required took far too long, either because of the amount of data or the complexity thereof.

Answer 3

I second ropstah's approach. User statistics like login time etc are usually available in the database. We need to derive certain facts out of the available data. So rather than having a counter for each visit and incrementing stuff, I would prefer a batch job that runs on the user login data and publish the results for that day.

But once a user "woot"ed, you might want to stop computing "woot"ness for that user. Else, there is a chance of the user getting "wooted" every day, till a no-login day is encountered. (but this is a minor issue).

Answer 4

Actually, if the Member's visits are in a SQL database, you can do the whole thing with a single SQL query. This is also probably faster than schlepping all of the data over to a client program to check it anyway:

/*
    Find all members who visited at least once every day
  for 30 or more days.  --RBarryYoung, 2009-05-31
*/
;WITH
  cteVisitDays as (
    Select
      MemberID
    , DATEDIFF(dd,'2008-06-01',VisitTime) as VisitDay
     From tblMembersLog
     Where Not Exists( Select * From tblMemberTags T
 Where T.MemberID = tblMembersLog.MemberID
  And T.TagName = 'WOOT!' )
     Group By MemberID
     , DATEDIFF(dd,'2008-06-01',VisitTime)
    )
, cteVisitRunGroups as (
    Select 
      MemberID
    , VisitDay - Row_Number() Over(
      Partition By MemberID
      Order By VisitDay
     ) as RunGrouping
     From cteVisitDays
     )
SELECT Distinct
  MemberID
 From cteVistRunGroups
 Group By MemberId, RunGrouping
 Having COUNT(*) >= 30

Answer 5

You do need to have a cookie, since people might not log in every day -- for example because they are logged in automatically for 2 weeks, or because they are on your site doing things non-stop without sleep and lunch for 50 hours :) You probably actually want to count when user accesses the site.

Now, one could theoretically record every access and perform database queries, as was suggested above, but you might think (as I do) that it strikes the wrong balance between usefulness and privacy+simplicity.

The algorithm you specified is deficient in an obvious way: since you store only whole number of days, you miss the user who logs in and out every 12 hours (your algorithm would keep the count of days as 1)

Here's the solution that I find to be cleanest with two date fields per user, in a kind of self-explanatory non-object oriented Python:

# user.beginStreak----user.lastStreak is the last interval when 
# user accessed the site continuously without breaks for more than 25h 

def onRegister (user):
    ...
    user.beginStreak = user.endStreak = time() # current time in seconds 
    ...

def onAccess (user): 
    ...
    if user.endStreak + 25*60*60 < time():
        user.beginStreak = time()
    user.endStreak = time()
    user.wootBadge = ( user.endStreak-user.beginStreak > 30*24*60*60 )
    ...

(Please forgive my Pythonic skills, I'm an academic and first-time site user)

You cannot do this task with one variable. I'm sure somebody can write a clean argument proving this fact.