1

I'm trying to calculate the time span between two requests/pageViews in Application Insights. The goal is to calculate the time spent on a page. I know that AI has the autoTrackPageVisitTime setting, but this is not turned on, unfortunately.

My initial try was to join the request table with itself

requests | join kind=inner requests on session_id, $left.timestamp < $right.timestamp , but my understanding is that joining with less than is not supported.

Anyone else has an idea of how I can calculate the time between two consecutive requests for each session?

EDIT: In pseudo-terms:

  1. Find first request where page/name = 'page1'
  2. Find the next request where sessionId equals the first one
  3. Calculate the duration between the two requests
  4. Calculate the avg duration across all sessionIds.
AKG
  • 337
  • 3
  • 18
  • Are there always maximum 2 requests per session, or there might be more (in this case, do you want the output to have the diff between every consecutive pair)? It would be best if you could update the question, and provide sample input (in datatable format) and the expected output. – Slavik N Oct 14 '20 at 07:07
  • I added some information now that hopefully gets the point of what I want to achieve – AKG Oct 14 '20 at 15:23
  • It's still not clear enough to me, unfortunately. I'm not familiar with the schema of the table. It would be best if you could update the question, and provide sample input (in datatable format) and the expected output. – Slavik N Oct 14 '20 at 19:22

1 Answers1

2

I'm not entirely sure if you always want to track from a specific page, but if you are looking for averages you shouldn't need to worry about individual page timings.

If you are looking for the average page timings across the site, then you just need the earliest time the session was active, the last time, and the number of pages visited over that time period. Dividing the time difference by page count gives the average. You can then call summarize again to get the overall average.

Note- I'm subtracting 1 from the page count because I don't have the time between the last page was opened and when they left.

Code:

let userTiming = requests | summarize PageViews=count()-1, StartTime=min(timestamp), EndTime=max(timestamp) by session_Id;
userTiming
| extend AverageTime=datetime_diff('millisecond', EndTime, StartTime)/PageViews
//| summarize avg(AverageTime)

If you are looking for the time spent on a specific page based on the user's next pageview, then your initial idea of joining the table to itself wasn't bad, but we can execute it a bit differently to avoid the < join.

  1. Get a table that only contains the views for a given page.
  2. Join that back onto the requests table based on the session Id alone.
  3. Filter that down so we only have requests that came after the ones to our specific page.
  4. Use summarize to only get the minimum timestamp that comes after our first pageview.
  5. Get the average difference by session Id
  6. Just as before, pairing the avg functions with .summarize can give you the overall average.

Code:

let startTime = requests | where name == "Function2" | project session_Id, startingTimestamp=timestamp;
let userTiming = requests 
| join kind=inner (startTime) on $left.session_Id == $right.session_Id
| where timestamp > startingTimestamp
| summarize nextTimestamp=min(timestamp) by startingTimestamp, session_Id;
userTiming
| summarize AverageTime=avg(datetime_diff('millisecond', nextTimestamp, startingTimestamp)) by session_Id
//| summarize avg(AverageTime)
PerfectlyPanda
  • 3,271
  • 1
  • 6
  • 17
  • Great. The second part was the solution I needed. I also liked your first approach. – AKG Oct 20 '20 at 07:58