How to calculate session length

Question

0.00/5 (No votes)

See more:

Every time a visitor to my website performs an action, I am logging that action to a database, along with the time the action occurred. I am in the process of building a dashboard to provide a better overview of what users are doing on the site.

One of the metrics I would like to track is session length.

How can I work out how long the user has been active?

Ideally I would like to create an SQL query that pulls out all the users and groups them into sessions, where the gap between any two actions is never greater than a configured timeout, say, 30 minutes. If the gap is greater than the timeout, then it should be counted as two separate sessions.

Any suggestions on how to do this - either in SQL or C#/Linq?

What I have tried:

I haven't tried anything yet - i'm struggling to think how to begin tackling this.

Posted 20-Apr-16 2:41am

Gavin Coates

Updated 20-Apr-16 3:00am

Add a Solution

Comments

Richard Deeming 20-Apr-16 8:51am

Which version of SQL?

Gavin Coates 28-Apr-16 11:53am

SQL Server 2012.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-04-20T03:00:00

Solution 1

I'd be very tempted to add info to the DB - a value which identifies a session, so they are "automatically" grouped.
If you add a GUID field to the DB, then when you record an action you can do a trivial search for the previous session entry: it's the same user, and with a time stamp less than 30 minutes ago. If it exists, use it's GUID value for your new entry. If it doesn't, create a new GUID and insert that.
Then when you want to run your metrics, it's a relatively simple GROUP BY clause, either in SQL or Linq.

The reason I'd do it like that is that it's a tiny bit of extra processing as a result of a human-scale event, making the data processing trivial. If you don't, then first you have to work out the groups, and then process them to get the info - which is going to be a lot more complex a query (though the SQL Server LAG[^] and LEAD[^] statements introduced to SQL 2012 would help).
[edit]Typo: "woudl" for "would" - OriginalGriff[/edit]

Posted 20-Apr-16 3:00am

OriginalGriff

Updated 20-Apr-16 3:16am

v2

Comments

Richard Deeming 20-Apr-16 9:08am

+5
There's a small typo in the last sentence - "woudl" instead of "would". :)

OriginalGriff 20-Apr-16 9:16am

Thanks - fixed.
My keyboard types ahead of my brain from time to time...:laugh:

F-ES Sitecore 20-Apr-16 9:38am

Just to add to that, the Session ID isn't a suitable way of grouping the users (in case the OP was thinking of going that route) as Session IDs are recycled, so create a new GUID for the user and store it in the Session on Session Start and use that to identify subsequent events.

OriginalGriff 20-Apr-16 9:54am

I considered that, but the default Session length is 20 minutes and it's recommended that it not be extended. (Some hosting services enforce a five minute Session and ignore web.config extension values).
So you'd have a new "user session" every half hour anyway!

F-ES Sitecore 20-Apr-16 10:00am

From reading the question that seems to be what he wants anyway. He might get more value from his data if he gave each user a GUID stored in a cookie, that way he gets to know the user is a returning user and be able to show all that user's sessions, that's how most tracking apps work.

Gavin Coates 20-Apr-16 11:55am

I know the user is a returning user anyway as I save the username, but this approach does make a lot of sense. If the Web App is considering the user to be a new user, then it makes sense to do so from the analytics too. Also it means it can be easily configured from the front end.

How often are session IDs recycled? What would be the likelihood of the same user being given the same session ID again within 24 hours, or 30 days?

I'll have a play around with this and see what works best.

F-ES Sitecore 20-Apr-16 12:27pm

The biggest issue with session ids is that renewed sessions tend to always have the same id and you won't know it's a new session. If my session id is 1234 and I time out, then look at the site again I'll probably retain 1234 as my session id, only it's a new session, just with the same id.