Bucket/Bin Data for Histogram

June 23, 2016
By Bounteous
SELECT
  trafficSource.medium,
  SUM(totals.visits) AS sessions,
  SUM(IF(timeOnSite_bucket5sec = 0,1,0)) AS B0_4,
  SUM(IF(timeOnSite_bucket5sec = 1,1,0)) AS B5_9,
  SUM(IF(timeOnSite_bucket5sec = 2,1,0)) AS B10_14,
  SUM(IF(timeOnSite_bucket5sec = 3,1,0)) AS B15_19,
  SUM(IF(timeOnSite_bucket5sec = 4,1,0)) AS B20_24,
  SUM(IF(timeOnSite_bucket5sec = 5,1,0)) AS B25_29,
  SUM(IF(timeOnSite_bucket5sec > 5,1,0)) AS B30_
FROM (
  SELECT
    trafficSource.medium,
    totals.visits,
    -- Sessions with a single page view will have no time on site reported
    FLOOR(IF(totals.timeOnSite IS NULL, 0, totals.timeOnSite) / 5) AS timeOnSite_bucket5sec
  FROM
    [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910])
GROUP BY
  trafficSource.medium