How to track user usage on site?

Question

I am using PHP/MYSQL. Each time a user logs in we insert the time they logged in and the date they logged in. While its easy for us to tell how many users logged in a particular day i am not sure how to calculate how much time they spent on the site.

For instance, just like a user signs in and we insert record into login_tracking table we also update that record when the user hits "logout" with the time they logged out. That way we subtract the time logged in and logged out and we get each persons time spent, but what happens if majority of people don't hit "logout" and instead clear cookies/cache or just close the window/tab the site was open in?

Is there a better way to do this? I am using Google Analytics, which gives me users and visits that showed up everyday but i want something proprietary to track usage?

Any ideas what i can do?

Update: @Paul asked if i was using Sessions. I am and this is how i am using it:

$username = $_SESSION["username"];

Answer 1

You can do this by setting a date time at the point of when they log on.

So when a user log on you update the mysql field which is DATETIME, something like...

 $q = $dbc -> prepare("UPDATE accounts SET loggedOn = NOW() WHERE username = ?");
 $q -> execute(array($_SESSION['username']));

Now all you would have to do is create a few more columns in your database table one called totalTime, and one called active or whatever you like upto you really.

On every page load the update active which is a DATETIME column also with NOW(),

 $q = $dbc -> prepare("UPDATE accounts SET active = NOW() WHERE username = ?");
 $q -> execute(array($_SESSION['username']));

This ensures that you know if the user is still online or not, you can check when they were last active. Now say if after 5 minutes of inactivity you want to find the total time you simply do,

 $q = $dbc -> prepare("UPDATE accounts SET totalTime = ADDDATE(totalTime, DATEDIFF(NOW(), loggedOn)) WHERE active < DATE_SUB(NOW(), INTERVAL 5 MINUTE)");
 $q -> execute();

What the above does is if users inactive for 5 minutes, it will take the loggedOn time minus it from the current time, leaving you with the total time on the site and add it to the DATETIME column totalTime.

Simply put the last query as a cron set too every 5 minutes and you have a quite accurate measure of peoples time on your site.

Answer 2

I suggest you use a "last used" field instead the field that holds the logout date/time, and a "first used" field instead of the field that holds the login date/time.

Whenever the user requests a page, you will check the "last used" value, if it was not long ago (i.e: less than 15 minutes for example), you will update it to the current date/time, else, you will have to create a new record in the database (assume that the user is in a new "session" right now, but don't have to create a new one). And, of course, associate the session with the newly created record.

This is a pseudo code:

Page Requested
if new session
    create session 
    create database record (first used=now, last used = now)
    associate the session with the record
else
    query the record associated with the current session
    if long ago
        create a new record (first used=now, last used = now)
        associate the new record with the current session
    else
        update the existing record (last used = now)

It looks easy, I didn't try to implement it though.