Data Studio
4K members online now
4K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Create new metric using session duration

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I would like to create  a new metric in data studio to count the number of sessions that were greater than 1 minute in length. For now let's ignore the fact that bounced users with only 1 page view have a session duration of 0, and just look at the users who viewed two or more pages.

 

How do I create a metric that checks if a session is greater than 1 minute? This this post as a guide I learned about equations with CASE statements. However, when I attempt to execute functions with the "Session Duration" dimension I just get an "Invalid formula" error. 

 

Screenshot from 2016-12-14 08_23_04.png

 

I also tried casting the Session Duration to a number, but the error is the same.

 

CASE WHEN CAST(Session Duration, NUMBER) > 1 THEN 1 ELSE 0 END

 

Thanks for your help!

 

Cheers,

Jake

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Jake E
December 2016

Create new metric using session duration

Google Employee
# 4
Google Employee

Hi Jake

 

Sorry, my earlier reply was a bit rushed so I didn't get all the details you'd provided. 

 

I tried this on my own and here's what I found:

 

First question: what is your data source? I used Google Analytics, and for me, Session Duration is a text dimension with numbers (duration in seconds, I believe). It's not formatted as a date string.

 
I can get the count of long sessions in two steps:
 
1) Create a new field Test = CAST ( Session Duration AS "DOUBLE" )
2) Create a new filed Long Duration = SUM(CASE WHEN Test > 60 THEN 1 ELSE 0 END)
(Replace 60 with your threshold value)
 
I can then make a scorecard with Long Duration to show the count of long sessions. 
 
I couldn't make this work in a single step combining CAST with the CASE statement and am investigating why not. (Also, I see the doc on CAST is wrong: the supported types are STRING, DOUBLE, INT64, UINT64. I will correct that.)
 
Hope this helps!
 
Rick

View solution in original post

Create new metric using session duration

Google Employee
# 2
Google Employee

Hi Jake, 

 

The basic trick here is to use the SUM() function to count string values. You can combine that with CASE to get what you want, I think. Let me know if that doesn't make sense.

 

There are some examples here in the Forum I think.

 

Rick

Create new metric using session duration

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks for the reply, Rick! I'm clear with the SUM() function. The issue is with comparing the Session Duration (SD) value with a number or some other value.

 

When the SD is plotted it's units are in HH:MMSmiley FrustratedS format. You'll see in my CASE statement above that I'm testing to see if the SD value is greater than 1. I have also tried CASTing the SD value to a number, and also comparing it to something like this:

 

CASE WHEN Session Duration > 00:01:00 THEN 1 ELSE 0 END

 

I still get an error in all situations. What are some other options to try to find SD's greater than X?

 

Cheers,

Jake

 

Marked as Best Answer.
Solution
Accepted by topic author Jake E
December 2016

Create new metric using session duration

Google Employee
# 4
Google Employee

Hi Jake

 

Sorry, my earlier reply was a bit rushed so I didn't get all the details you'd provided. 

 

I tried this on my own and here's what I found:

 

First question: what is your data source? I used Google Analytics, and for me, Session Duration is a text dimension with numbers (duration in seconds, I believe). It's not formatted as a date string.

 
I can get the count of long sessions in two steps:
 
1) Create a new field Test = CAST ( Session Duration AS "DOUBLE" )
2) Create a new filed Long Duration = SUM(CASE WHEN Test > 60 THEN 1 ELSE 0 END)
(Replace 60 with your threshold value)
 
I can then make a scorecard with Long Duration to show the count of long sessions. 
 
I couldn't make this work in a single step combining CAST with the CASE statement and am investigating why not. (Also, I see the doc on CAST is wrong: the supported types are STRING, DOUBLE, INT64, UINT64. I will correct that.)
 
Hope this helps!
 
Rick

Create new metric using session duration

Google Employee
# 5
Google Employee

p.s. You can also just change the semantic Type of Session Duration to be a Number. Then you wouldn't need to CAST it.

Create new metric using session duration

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

You nailed it, Rick. Thanks!

 

Cheers,

Jake