In google spreadsheets I'm trying to get the average session per user from Google Analytics, so I tried ga:avgSessionDuration on metrics, ga:date in dimensions, it returned me the time in seconds, so I used =TEXT(P2/86400;CHOOSE(MATCH(P2;{0\60\3600};1);"s ""s""";"m ""m"" s ""s""";"[h] ""h"" m ""m"" s ""s""")) to transform the value into hours, minutes and seconds, like this 1 h 49 m 29 s, but this is getting the full time session of all users. Is there any way to get the average session PER user?

EDIT? Tried to divide the session duration by the number of users as suggested:

Session duration

  =TEXT(P2/86400/duracaosessao!E16;CHOOSE(MATCH(P2;{0\60\3600};1);"s ""s""";"m ""m"" s ""s""";"[h] ""h"" m ""m"" s ""s"""))

Time on page

 =TEXT(Q2/duracaosessao!E16/86400;CHOOSE(MATCH(Q2;{0\60\3600};1);"s ""s""";"m ""m"" s ""s""";"[h] ""h"" m ""m"" s ""s"""))

But for both I'm getting the same result: 0 h 1 m 26 s

  • =TEXT(P2/86400;"[h]\h mm\m ss\s") What's in P2? Average session or full session time? Can't you just divide the number by the number of users?
    – TheMaster
    Commented Mar 6, 2018 at 7:54
  • P2 is session duration. How do I do that?
    – 023023
    Commented Mar 6, 2018 at 15:14
  • And, What's the expected result?
    – TheMaster
    Commented Mar 6, 2018 at 23:08


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.