Big Query - Google Analytics - 首次访问和购买之间的时差 [英] Big Query - Google Analytics - Time diff between first visit and purchase

问题描述

试图获得一个列表：

visitorid，首次访问时间，事件发生时间。

我写的只是抓取有交易收入的行。我也试图通过日期（visitStartTime）将visitStartTime（一个unix日期）转换为常规日期，但是由于输出的日期而导致该组失败。

任何方向都很有帮助。

  SELECT 
 fullvisitorID，
 visitNumber，
 visitStartTime，
 hits.transaction .transactionRevenue 
 
从
 [75718103.ga_sessions_20150310]，
 [75718103.ga_sessions_20150309]，
 [75718103.ga_sessions_20150308]，
 [75718103.ga_sessions_20150307] ，
 [75718103.ga_sessions_20150306]，
 [75718103.ga_sessions_20150305]，
 [75718103.ga_sessions_20150304]，
 [75718103.ga_sessions_20150303]，
 [75718103.ga_sessions_20150302] ，
 
 
 WHERE totals.transactions> = 1 
 
 GROUP BY 
 fullvisitorID，visitNumber，visitStartTime，hits.transaction.transactionRevenue; 
  

解决方案

visitStartTime在Google Analytics架构中定义为POSIX时间，这意味着自时代以来的秒数。 BigQuery TIMESTAMP被编码为自纪元以来的 micro 秒数。因此，为了得到TIMESTAMP的开始时间，我使用了 TIMESTAMP（INTEGERvisitStartTime * 1000000））。 hits.time包含自第一次命中以来的 milli 秒数，因此为了获得事务处理时间，它们需要乘以1000才能达到微秒粒度，因此 TIMESTAMP（INTEGER（ visitStartTime * 1000000 + hits.time * 1000））。由于命中是重复的RECORD，所以不需要GROUP BY，数据模型已经将所有命中组合在一起。
把它放在一起：

$ p $ SELECT
fullVisitorId，
timestamp（integer（visitStartTime * 1000000））作为start_time，
timestamp（integer（visitStartTime * 1000000 + hits.time * 1000））as transaction_time
FROM
[google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE hits.transaction.transactionRevenue> 0


Trying to get a list:

visitorid, time first visit, time of hit where transaction occurred.

What've I've written is only grabbing rows that have transaction revenue. I am also trying to convert visitStartTime which is a unix date, to a regular date via Date(visitStartTime) but that's failing in the group by because of the outputted date.

Any direction super helpful.

SELECT 
  fullvisitorID, 
  visitNumber,
  visitStartTime,
  hits.transaction.transactionRevenue

FROM 
  [75718103.ga_sessions_20150310],
  [75718103.ga_sessions_20150309],
  [75718103.ga_sessions_20150308],
  [75718103.ga_sessions_20150307],
  [75718103.ga_sessions_20150306],
  [75718103.ga_sessions_20150305],
  [75718103.ga_sessions_20150304],
  [75718103.ga_sessions_20150303],
  [75718103.ga_sessions_20150302],


WHERE totals.transactions >=1

GROUP BY    
  fullvisitorID, visitNumber, visitStartTime, hits.transaction.transactionRevenue;

解决方案

visitStartTime is defined as POSIX time in Google Analytics schema, which means number of seconds since epoch. BigQuery TIMESTAMP is encoded as number of microseconds since epoch. Therefore, to get start time as TIMESTAMP, I used TIMESTAMP(INTEGERvisitStartTime*1000000)). hits.time contains number of milliseconds since first hit, therefore to get time of transactions, they needed to be multiplied by 1000 to get to microsecond granularity, hence TIMESTAMP(INTEGER(visitStartTime*1000000 + hits.time*1000)). Since hits is repeated RECORD, no GROUP BY is necessary, the data model already has all the hits grouped together. Putting it all together:

SELECT
  fullVisitorId,
  timestamp(integer(visitStartTime*1000000)) as start_time, 
  timestamp(integer(visitStartTime*1000000 + hits.time*1000)) as transaction_time
FROM 
 [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910] 
WHERE hits.transaction.transactionRevenue > 0

这篇关于Big Query - Google Analytics - 首次访问和购买之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！