google-analytics - Big Query - Google Analytics - 首次访问和购买之间的时间差异

Question

尝试获取列表:

visitorid，首次访问时间，交易发生的命中时间。

我所写的只是抓取具有交易收入的行。我还尝试通过 Date(visitStartTime) 将作为 unix 日期的 visitStartTime 转换为常规日期，但是由于输出日期，这在组中失败了。

任何方向都 super 有用。

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;

Answer 1

visitStartTime 在 Google Analytics 架构中定义为 POSIX 时间，表示自纪元以来的秒数。 BigQuery TIMESTAMP 编码为自纪元以来的微秒数。因此，为了将开始时间设为 TIMESTAMP，我使用了 TIMESTAMP(INTEGERvisitStartTime*1000000))。 hits.time 包含自第一次命中以来的 milli 秒数，因此要获得事务时间，需要将它们乘以 1000 才能达到微秒粒度，因此 TIMESTAMP(INTEGER(visitStartTime*1000000 + hits.time*1000))。由于命中是重复记录，因此不需要 GROUP BY，数据模型已经将所有命中分组在一起。把它们放在一起:

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