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 Mar 6 '18 at 7:54
  • P2 is session duration. How do I do that? – 023023 Mar 6 '18 at 15:14
  • And, What's the expected result? – TheMaster Mar 6 '18 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.