How to query Average spent time of users in my website?

How to query Average spent time of users in my website?

Is that possible?

Hi @mateus.rangel - Take a look at theNRQL Library as there are helpful queries such as this listed there.

1 Like

Damn nice thread

But i did not found the query :frowning:

This query helped me:

SELECT
min(timestamp) as ‘Start’,
max(timestamp) as ‘End’,
((MAX(timestamp) - MIN(timestamp)) / 1000) AS ‘Length (seconds)’,
count(*) as ‘Pages’
FROM
PageView
FACET
session

but i cant average this, idkw

Of course, this is the NRQL function in a function limitation. What you would like is to actually use this query:

SELECT
average((MAX(timestamp) - MIN(timestamp)) / 1000) AS ‘ Average Length (seconds)’,
count(*) as ‘Pages’
FROM
PageView
FACET
session

Not supported: BinOp{type=DIVIDE, left=BinOp{type=SUBTRACT, left=Call{target=Id{name=‘MAX’, isQuoted=false}, arguments=CallArguments{positionalArgs=[Id{name=‘timestamp’, isQuoted=false}], kwargs={}}}, right=Call{target=Id{name=‘MIN’, isQuoted=false}, arguments=CallArguments{positionalArgs=[Id{name=‘timestamp’, isQuoted=false}], kwargs={}}}}, right=IntLiteral{value=1000}} (at line 0 column 0)

I believe what Stefan meant by his last response was that you would like to use such a query, but subqueries are not supported in Insights.

1 Like

@babbott @stefan_garnham @mateus.rangel I think a good question to ask here is: what performance data are you looking to investigate by finding out how long users spend on your site? The Browser focuses less on User Analytics and so this is probably why it’s hard to find a query to do this out of the box.

That being said, you can easily add a custom attribute along with a little code to record this value. You’ll want to calculate how long a user has been on the page with the navigation timing API, and then add this to the current event as a custom attribute.

I’ve been working on a personal side project that does this as a by-product, so feel free to use the code below as open-source and as-is. This also requires using the SPA agent:

window.addEventListener("beforeunload", function (event) {
    // Ensure the current interaction is saved 
    newrelic.interaction().save();
    var navStartTime = window.performance.timing.navigationStart;
    var unloadTime = Date.now();
    var timeOnPage = unloadTime - navStartTime;
    var durationTimeoutReached = false;

    // Limit the page session to 30 minutes (1800000 milliseconds). 30-minute timeout is based on analytics standards.
    if (timeOnPage > 1800000) {
      durationTimeoutReached = true;
      timeOnPage = 1800000;
    }

    // Add timeOnPage value as a custom attribute, add timeout identifier if 30 minutes was reached.
    newrelic.setCustomAttribute('timeOnPage', timeOnPage);
    newrelic.setCustomAttribute('durationTimeoutReached', durationTimeoutReached);
 });

After this is in place, you can query average duration in Insights and chart it over time like this:

SELECT average(timeOnPage) from BrowserInteraction where durationTimeoutReached = false since 24 hours ago TIMESERIES

If you’re not using the SPA Agent, you could instead add the attributes to PageAction events - just replace the newrelic.interaction().save() line with newrelic.addPageAction("durationOnPage");, and query on PageAction instead of BrowserInteraction.

You also might be able to facet on session at that point, but not sure if that would work or not. Whether or not it does though, being able to do that should go back to the original question at the top of my post.

Let us know if this works for you or if there’s any questions!

(edit: added missing closing tags from code, updated about PageView/BI/PageAction)

8 Likes

Does this code calculate out of focus time & window tab switch time till the window is closed or session ended? Also i am unable to capture the correct session length using this code. Some sessions are also getting missed.

@Mohd.Salman2 great question! The code here doesn’t account for time where focus is elsewhere or when switching to different window tabs, because it’s a basic calculation comparing the start of page load versus when the beforeunload event occurs.

For both having this extra level of data as well as troubleshooting the errors you’re running through, I recommend taking a look at the code and potentially applying it in a different way in your application. There could be a multitude of things causing the session length to be incorrect or missing, so I recommend experimenting with the code a bit more to get it working for you.