AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
4.2K members online now
4.2K members online now
Ask questions about Data Studio
Guide Me

Duration Metric Coming into Report as Text Dimension - Solution?

Follower ✭ ✭ ✭
# 1
Follower ✭ ✭ ✭

The 'time' and 'date' dimensions in Data Studio seem to need a lot of work. 


I've managed to fix most of my issues with this via the todate() function, but my 'Avg. Session Duration' and 'Total Time on Site' metrics that I'm pulling in from a Google Sheet are giving me a problem. 


When I pulled the data into the Google Sheet, it was initially pulling in as seconds, but I finally got my data in the correct hh*:mm:ss format in the Google sheet by using this function: int((G2/60)/60)&":"&int(MOD((G2/60),60))&":"&MOD(G2,60), and then changing the format to duration. 


To get the average session duration, I just divided the Total Time on Site column by the number of sessions, and it successfully returned the average in the hh:mm:ss format -- i.e. it was able to recognize it and manipulate it via a numeric operator. (I also confirmed numeric the data type via 'type()' function)


When I connected the sheet to Data Studio, however, both of those columns were recognized as 'Text' and categorized as a dimension rather than a metric. When I switch it to a numeric I get a 'null' response, and keeping it as 'Text' returns the following:


 Screen Shot 2016-09-17 at 2.08.55 PM.png


Does anyone have any idea of how to remedy this? 


My initial thought was the CAST() as function, but I can't find any helpful documentation to get the transformation to work.