Query for time spent on site by user

I would like to be able to see how long a user spent on the site or on a specific page.

Do I need to create a custom attribute for this?

1 Like

Hi @maltman!

You can use the session attribute to track a specific user across their session. The query would look something like this:

SELECT duration, pageUrl FROM PageView WHERE session ='enter specific session id here'

I believe that pageUrl and session are both attributes which are in Insights out of the box.

Hope this helps!

1 Like

Hey Aaron,

Thanks for the reply. This does give me some data but I did the query over 1 month and it only returned 6 sessions.

Hey @maltman,

If you’re not an Insights Pro subscriber, NR only keeps Insights data for the current day.

Does this describe your situation?

Hey Aaron,

No, we are a subscriber. We have approx. 30 weeks worth of data to query against.

Hi @maltman,

It occurs to me that I mis-read your initial question, I’m very sorry for creating confusion. You’re asking about time on page/site, per user, I was giving you an example for load time by page, per user (assuming you knew the users session ID).

I didn’t have a pre-written example of this, but I’ve spent some time on it, and it appears this query will get you total session length, in minutes, by session ID:

SELECT 
((MAX(timestamp) - MIN(timestamp))/1000/60) as 'Total Session Length In Minutes' 
FROM PageView 
SINCE 1 day ago 
FACET session 
LIMIT 100

Does this get you closer to what you are looking for?

2 Likes

Hey Aaron,

I think this is exactly what I was looking for.

I do have some sessions that the session length is in the thousands but I would have to track these down and see if it is something running on our side.

Thank you very much for your help,

Matt

1 Like

New to Insights, etc… Is there a way to then get an average of these session durations? The query above provides a list, which is nice, but I’d like to see one big number.

Hey @therealgarrett - try just removing facet session from the query and see how that goes.

Oh, perfect! Thanks so much!

So, minus the FACET, that will produce an average of all sessions? Or just max - min from all sessions.

Hey @therealgarrett - sorry, that instruction doesn’t make a lot of sense, I just realized. You’re right, that’s just going to give the longest possible session in the timeframe. I’m not sure if there is a way to get an average here since we’re already doing some math to derive the attribute.

Let me give some thought on how this could be done with custom attributes - I don’t see a way to get the average with the default attributes.

@therealgarrett - sorry for the delay in my update. We weren’t able to figure out a good way to do this using existing events, even with custom attributes, if you want an overall average. The session facet gives a way to group PageView events and determine which ones are associated with a given session in order to do the computation. With the way the datastore is structured, we don’t have a separate session duration to operate on into order to get an average.

I’ve passed your interest in this on to our product team to be evaluated as a possibility for future development - thank you for the great question!

Hi I have tried this query, seems it will come out 0 minutes if user only open one webpage which result in Max value = Min value. Any thought?