Bucket/Bin Data for Histogram
June 23, 2016
|
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