CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Cansu
Cansu

Reputation: 167

Google BigQuery returns same value for visiId and visitStartTime

I run this very simple query on BigQuery and it returned the visitId value as visit start time. Code is below and you can also see the screenshot here: visitStartTime and visitId same value 

SELECT visitStartTime, visitId FROM [83606741.ga_sessions_intraday_20160606] group by visitStartTime, visitId order by visitStartTime DESC;

How can I get the session start time to be shown like 11:45 and what is the reason these two columns have the same value?

Thank you for the replies!

Upvotes: 3

Views: 1150

Answers (2)

Suhkjin Hur
Suhkjin Hur

Reputation: 103

If you specifically need hours and minutes, one way to do it is using FORMAT_UTC_USEC, HOUR, and MINUTE functions as follows.

SELECT 
FORMAT_UTC_USEC(visitStartTime * 1000000) AS time, 
HOUR(FORMAT_UTC_USEC(visitStartTime * 1000000)) AS hour, 
MINUTE(FORMAT_UTC_USEC(visitStartTime * 1000000)) AS minute
FROM ...

Visit https://cloud.google.com/bigquery/query-reference#datetimefunctions for more details.

Upvotes: 0

NathanC
NathanC

Reputation: 193

The following function will return the time of the visit.

SELECT time(SEC_TO_TIMESTAMP(visitid)) as visit_time FROM [83606741.ga_sessions_intraday_20160606]

Like you say, visitID is the same as visitstarttime. For this reason visitid is not a unique reference for a session. To create a unique sessionid you need to concatenate it with the visitorid:

concat(fullvisitorid, string(visitid)) as sessionid

Upvotes: 2

Related Questions