Sessions with Transactions

May 12, 2016
By Bounteous
SELECT
  fullVisitorId,
  visitId,
  SEC_TO_TIMESTAMP(visitStartTime) AS time,
  transacted,
  SUM(transacted) OVER (PARTITION BY fullVisitorId ORDER BY visitStartTime DESC) AS transaction_group
FROM (
  SELECT
    fullVisitorId,
    visitId,
    visitStartTime,
    MAX(IF(hits.item.transactionID IS NOT NULL, 1, 0)) AS transacted
  FROM (
    SELECT
      fullVisitorId,
      visitId,
      visitStartTime,
      hits.item.transactionID
    FROM
      [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910] )
  GROUP BY
    fullVisitorId,
    visitId,
    visitStartTime)
WHERE
  fullVisitorId='5711522334224447562'