Google BigQuery Recipes

SELECT
  cur_hit.fullVisitorId AS fullVisitorId,
  cur_hit.visitId AS visitID,
  cur_hit.hits.hitNumber AS cur_hitnumber,
  cur_hit.hits.page.pagePath as cur_pagePath,
  cur_hit.hits.time AS cur_time,
  MAX(prev_hit.hits.hitNumber) AS prev_hitNumber,
FROM
  FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits) AS prev_hit
INNER JOIN
  FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits) AS cur_hit
ON
  prev_hit.fullvisitorid = cur_hit.fullvisitorid
  AND prev_hit.visitid = cur_hit.visitid
WHERE
  prev_hit.hits.hitNumber < cur_hit.hits.hitNumber
  AND prev_hit.hits.type = "PAGE"
  AND cur_hit.hits.type = "PAGE"
GROUP BY fullVisitorId, visitID, cur_hitnumber, cur_pagePath, cur_time

SELECT COUNT(totals.bounces)/COUNT(totals.visits) AS bounceRate
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]

SELECT COUNT(totals.bounces)/COUNT(totals.visits) AS Bounce_Rate
FROM   'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910'

SELECT HOUR(SEC_TO_TIMESTAMP(visitStartTime)) AS sessionHour,
       COUNT(totals.bounces)/COUNT(totals.visits) AS bounceRate
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
GROUP BY sessionHour

SELECT
     hits.page.pagePath AS Landing Page,
     COUNT(totals.bounces)/COUNT(totals.hits) AS Bounce_Rate, 
     COUNT(totals.bounces) AS Bounces, 
     COUNT(totals.hits) AS Entrances 
FROM 
     [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]  
WHERE 
     hits.type="PAGE" AND hits.hitNumber=1
GROUP BY 
     Landing Page
ORDER BY 
     Entrances DESC

SELECT
     hits.page.pagePath AS Landing_Page,
     COUNT(totals.bounces)/COUNT(totals.hits) AS Bounce_Rate, 
     COUNT(totals.bounces) AS Bounces, 
     COUNT(totals.hits) AS Entrances 
FROM 
     'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910' AS GA,
     UNNEST(GA.hits) AS hits
WHERE 
     hits.type="PAGE" AND hits.hitNumber=1
GROUP BY 
     Landing_Page
ORDER BY 
     Entrances DESC

SELECT trafficSource.medium AS medium,
       HOUR(SEC_TO_TIMESTAMP(visitStartTime)) AS sessionHour,
       COUNT(totals.bounces)/COUNT(totals.visits) AS bounceRate
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
GROUP BY medium, sessionHour
ORDER BY sessionHour, medium

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

SELECT
  pagePathLevel1,
  ROUND(SUM(totals.bounces) / SUM(totals.visits), 2) AS bounceRate
FROM (
  SELECT
    totals.visits,
    -- If no session bounced, then SUM of nulls is null, not 0
    IF(totals.bounces IS NULL, 0, 1) AS totals.bounces,
    NTH(1, SPLIT(hits.page.pagePath, '/')) AS pagePathLevel1,
  FROM
    -- We need to explicitly FLATTEN otherwise SPLIT won't behave as expected
    FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits.page.pagePath))
GROUP BY
  pagePathLevel1

SELECT
  CONCAT(trafficSource.source,"/",trafficSource.medium) AS sourceMedium,
  newSessions / sessions AS percentNewSessions,
  bounces / sessions AS bounceRate
FROM (
  SELECT
    trafficSource.source,
    trafficSource.medium,
    SUM(totals.visits) AS sessions,
    SUM(totals.newVisits) AS newSessions,
    SUM(totals.bounces) AS bounces,
  FROM
    [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
  GROUP BY
    trafficSource.source,
    trafficSource.medium)

SELECT
  trafficSource.medium,
  SUM(totals.visits) AS sessions,
  -- 5 will give the min, 25%, 50%, 75%, max with 20% error
  -- the more buckets, the better the approximation (error = 1/number of buckets)
  -- at the cost of more computation
  -- QUANTILES returns all of the buckets, use NTH to extract the bucket you want
  NTH(2, QUANTILES(totals.timeOnSite, 5)) AS firstQuartile,
  NTH(3, QUANTILES(totals.timeOnSite, 5)) AS mean,
  NTH(3, QUANTILES(totals.timeOnSite, 5)) AS thirdQuartile
FROM (
  SELECT
    trafficSource.medium,
    totals.visits,
    -- Sessions with a single page view will have no time on site reported
    IF(totals.timeOnSite IS NULL, 0, totals.timeOnSite) AS totals.timeOnSite
  FROM
    [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910])
GROUP BY
  trafficSource.medium

SELECT
  hits.page.pagePath,
  CASE 
    WHEN pagePathLevel1 = 'helmets' THEN "Products" 
    WHEN pagePathLevel1 = 'vests' THEN "Products"
    WHEN pagePathLevel1 IN ('basket.html', 'shipping.html', 'billing.html', 'confirm.html') THEN "Checkout"
    WHEN pagePathLevel1 IS NULL OR pagePathLevel1 = 'login.html' THEN "RestOfSite"
    ELSE 'Unknown'
  END AS contentGroup1,
  CASE 
    WHEN pagePathLevel2 = 'foldable.html' THEN "Foldable" 
    WHEN pagePathLevel2 IN ('orange.html', 'yellow.html') THEN "Color"
  END AS contentGroup2
FROM (
  SELECT
    fullVisitorId,
    visitID,
    hits.page.pagePath,
    NTH(1, SPLIT(hits.page.pagePath, '/')) AS pagePathLevel1,
    NTH(2, SPLIT(hits.page.pagePath, '/')) AS pagePathLevel2,
  FROM
    -- We need to explicitly FLATTEN otherwise SPLIT won't behave as expected
    FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits.page.pagePath))
GROUP BY 
    hits.page.pagePath, 
    contentGroup1, 
    contentGroup2

SELECT
  fullvisitorid,
  visitId,
  DATEDIFF(SEC_TO_TIMESTAMP(visitStartTime), SEC_TO_TIMESTAMP(prevVisitStartTime)) AS daysSinceLastSession,
  FLOOR((visitStartTime - prevVisitStartTime)/60) as minutesSinceLastSession
FROM (
  SELECT
    fullvisitorid,
    visitId,
    visitStartTime,
    LAG(visitStartTime) OVER (PARTITION BY fullvisitorid ORDER BY visitStartTime ASC) AS prevVisitStartTime
  FROM
    [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910])

SELECT date, 
       SUM(totals.visits) AS sessions
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
GROUP BY date
HAVING sessions > 70

SELECT
  num,
  AVG(num) OVER (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as moving_avg,
  AVG(num) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as moving_avg_middled,
  LAG(num) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as lag_mid,
  LEAD(num) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as lead_mid,
  MAX(num) OVER (ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING ) as max_here_3_more,
  MAX(num) OVER (ORDER BY num DESC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING ) as max_here_3_more_desc,
  MIN(num) OVER (ORDER BY num ASC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING ) as max_here_3_more_asc,
FROM 
  ( SELECT 2 AS num),
  ( SELECT 4 AS num),
  ( SELECT 3 AS num),
  ( SELECT 1 AS num),

SELECT
  hits.page.pagePath,
  NTH(1, SPLIT(hits.page.pagePath, '/')) AS pagePathLevel1,
  NTH(2, SPLIT(hits.page.pagePath, '/')) AS pagePathLevel2
FROM
  -- We need to explicitly FLATTEN otherwise SPLIT won't behave as expected
  FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits.page.pagePath)

SELECT
     CONCAT(fullVisitorId, ".", STRING(visitId)) AS Session_ID,
     totals.pageviews AS Pageviews
FROM   
     [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE
     trafficSource.medium="organic"

SELECT
     CONCAT(fullVisitorId, ".", CAST(visitId AS STRING)) AS Session_ID,
     totals.pageviews AS Pageviews
FROM   
     'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910'
WHERE
     trafficSource.medium="organic"

SELECT 
     hits.type, 
     count(hits.hitNumber)
FROM 
     [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
GROUP BY 
     hits.type

SELECT 
     hits.type, 
     COUNT(hits.hitNumber)
FROM 
     'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910' AS GA,
     UNNEST(GA.hits) AS hits
GROUP BY 
     hits.type

SELECT 
     CONCAT(fullVisitorId, ".", STRING(visitId)) AS Session_ID,
     hits.page.pagePath AS Page
FROM
     FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits)

SELECT 
     CONCAT(fullVisitorId, ".", CAST(visitId AS STRING)) AS Session_ID,
     hits.page.pagePath AS Page
FROM   
  'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910' AS GA,
  UNNEST(GA.hits) AS hits

SELECT
     CONCAT(fullVisitorId, ".", STRING(visitId)) AS Session_ID,
     hits.page.pagePath AS Page,
     customDimensions.index AS CD_Index,
     customDimensions.value AS CD_Value
FROM
     FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits)

SELECT 
     CONCAT(fullVisitorId, ".", CAST(visitId AS STRING)) AS Session_ID,
     hits.page.pagePath AS Page,
     customDimensions.index AS CD_Index,
     customDimensions.value AS CD_Value
FROM   
  'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910' AS GA,
  UNNEST(GA.hits) AS hits,
  UNNEST(GA.customDimensions) AS customDimensions

SELECT
     fullVisitorId AS Client_ID,
     SUM(totals.pageviews) AS Pageviews,
     COUNT(visitId) AS Sessions
FROM   
     [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
GROUP BY
     Client_ID
ORDER BY
     Pageviews DESC
LIMIT 5

SELECT
     fullVisitorId AS Client_ID,
     SUM(totals.pageviews) AS Pageviews,
     COUNT(visitId) AS Sessions
FROM   
     'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910'
GROUP BY
     Client_ID
ORDER BY
     Pageviews DESC
LIMIT 5

SELECT fullVisitorId AS Client_ID, totals.hits AS Hits
FROM   [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]

SELECT fullVisitorId AS Client_ID, totals.hits AS Hits
FROM   'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910'

SELECT
     hits.transaction.transactionId AS Transaction_ID,
     hits.transaction.transactionRevenue/1000000 AS Revenue,
     Refund_data.RefundAmount AS Refunded,
     hits.transaction.transactionRevenue/1000000- Refund_data.RefundAmount AS Net_Revenue
FROM FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910],hits) AS GA_data
JOIN 
    (SELECT TransactionId, RefundAmount
     FROM [google.com:analytics-bigquery:LondonCycleHelmet.refunds_201309]) AS Refund_data
    ON GA_data.hits.transaction.transactionId = Refund_data.TransactionId
WHERE hits.type = "TRANSACTION"

SELECT
  fullVisitorId,
  visitId,
  -- Uses the knowledge that hits are stored in chronological order
  FIRST(IF(hits.type = "PAGE", hits.page.pagePath, NULL)) WITHIN RECORD AS landingPagePath
FROM
  [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]

SELECT
  COUNT(*) as logins_after_product_view
FROM (
  SELECT
    SUM(IF(hits.eventinfo.eventCategory = "Login", 1, 0)) OVER (PARTITION BY fullvisitorid, visitid ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) AS login_count,
    hits.page.pagePath as pagePath
  FROM
    [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910] )
WHERE
  login_count > 0
  AND (pagePath CONTAINS 'vests' OR pagePath CONTAINS 'helments')

SELECT trafficSource.medium AS medium,
       HOUR(SEC_TO_TIMESTAMP(visitStartTime)) AS sessionHour,
       COUNT(totals.visits) AS sessionCount,
       COUNT(totals.bounces)/COUNT(totals.visits) AS bounceRate
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
GROUP BY medium, sessionHour
ORDER BY sessionHour, sessionCount DESC

SELECT
     Customer_Type, 
     COUNT(fullVisitorId) AS Users, 
     SUM(totals.transactionRevenue)/1000000 AS Revenue,
     SUM(totals.transactionRevenue)/1000000/COUNT(fullVisitorId) AS Revenue_per_User
FROM 
     (SELECT
          fullVisitorId,
          totals.transactionRevenue,
          LAST(IF(
              hits.customDimensions.index=2,
              hits.customDimensions.value, 
              "Regular")) WITHIN RECORD as Customer_Type
     FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910])
GROUP BY Customer_Type

SELECT COUNT(totals.visits) AS visitCount
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE trafficSource.medium = "organic"

SELECT date, 
       SUM(totals.visits) AS sessions
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
GROUP BY date

SELECT
     CONCAT(fullVisitorId, ".", STRING(visitId)) AS Session_ID,
     totals.pageviews AS Pageviews
FROM   
     [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
ORDER BY
     Pageviews DESC
LIMIT 5

SELECT
     CONCAT(fullVisitorId, ".", CAST(visitId AS STRING)) AS Session_ID,
     totals.pageviews AS Pageviews
FROM   
     'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910'
ORDER BY
     Pageviews DESC
LIMIT 5

SELECT
  sp as pageA,
  pp as pageB,
  COUNT(*) AS crossCount
FROM (
  SELECT
    sess.fullvisitorid AS sfvi,
    pages.fullVisitorid AS pfvi,
    sess.hits.page.pagePath AS sp,
    pages.hits.page.pagePath AS pp
  FROM
    FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits) AS sess
  CROSS JOIN
    FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits) AS pages
  WHERE
    -- ensures each combo is only counted a single time
    pages.fullvisitorid < sess.fullvisitorid
    AND pages.hits.page.pagePath < sess.hits.page.pagePath
  GROUP BY sfvi, pfvi, sp, pp )
GROUP BY pageA, pageB
ORDER BY pageA, crossCount DESC

SELECT trafficSource.medium, 
       SUM(totals.newVisits) / SUM(totals.visits) AS percentNewSessions
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
GROUP BY trafficSource.medium

SELECT
     Sku,
     SUM(IF(Customer_Type="Bronze", Quantity, 0)) AS Bronze,
     SUM(IF(Customer_Type="Platinum", Quantity, 0)) AS Platinum,
     SUM(IF(Customer_Type IS NULL, Quantity, 0)) AS none
FROM
  (SELECT   
       hits.item.productSku AS Sku,
       hits.item.itemQuantity AS Quantity,
       LAST(IF(
            hits.customDimensions.index=2,
            hits.customDimensions.value, 
            NULL)) WITHIN RECORD AS Customer_Type
   FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910])
WHERE Sku IS NOT NULL
GROUP BY Sku

SELECT
     fullVisitorId AS Client_ID,
     LAST(IF(
          hits.customDimensions.index=2,
          hits.customDimensions.value, 
          "Regular")) WITHIN RECORD AS Customer_Type
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
HAVING Customer_Type = "Platinum"

SELECT 
     fullVisitorId, totals.hits
FROM   
     TABLE_DATE_RANGE(
          [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_],
          TIMESTAMP('2013-09-01'),
          TIMESTAMP('2013-09-30'))

SELECT fullVisitorId, totals.hits
FROM 'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_*'
WHERE _TABLE_SUFFIX BETWEEN '20130901' AND '20130930'

SELECT CONCAT(fullVisitorId, ".", STRING(visitId)) AS Session_ID
FROM   [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]

SELECT CONCAT(fullVisitorId, ".", CAST(visitId AS STRING)) AS Session_ID
FROM   'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910'

SELECT date, 
       SUM(totals.visits) AS sessions
FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
WHERE totals.transactions > 0
GROUP BY date

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'

SELECT
  LAG(num) OVER () AS lag,
  num,
  LEAD(num) OVER () AS lead,
FROM 
  ( SELECT 2 AS num),
  ( SELECT 4 AS num),
  ( SELECT 3 AS num),
  ( SELECT 1 AS num),

SELECT
  LAG(num) OVER (ORDER BY num) AS ordered_lag,
  LAG(num) OVER () AS lag,
  num,
  LEAD(num) OVER () AS lead,
  LEAD(num) OVER (ORDER BY num) AS ordered_lead,
FROM 
  ( SELECT 2 AS num),
  ( SELECT 4 AS num),
  ( SELECT 3 AS num),
  ( SELECT 1 AS num),

SELECT SUM(totals.hits) AS Hits
FROM   [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]

SELECT SUM(totals.hits) AS Hits
FROM   'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910'

SELECT
  fullVisitorId,
  visitId,
  hitNumber,
  pagePath,
  diff AS timeOnPage_ms
FROM
  timePerPage(
  SELECT
    fullVisitorId,
    visitId,
    NEST(hits.page.pagePath) AS pages,
    NEST(hits.type) AS types,
    NEST(hits.time) AS times
  FROM
    [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
  GROUP BY
    fullVisitorId,
    visitId
  LIMIT
    10)

SELECT
  fullVisitorId,
  visitId,
  hits.hitNumber,
  hits.page.pagePath,
  -- get next and previous hit time to be able to work out length of each hit
  hits.time AS hit_time,
  LEAD(hits.time, 1) OVER (PARTITION BY fullVisitorId, visitId ORDER BY hits.time ASC) AS next_hit_time,
  FROM
    [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
  WHERE
    hits.type = "PAGE"

SELECT
  num,
  MAX(num) OVER (ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) as max_here_1_following
FROM 
  ( SELECT 2 AS num),
  ( SELECT 4 AS num),
  ( SELECT 3 AS num),
  ( SELECT 1 AS num),

SELECT
  num,
  COUNT(num) OVER (PARTITION BY num) AS partion_count,
  COUNT(num) OVER () AS plain_count
FROM 
  ( SELECT 2 AS num),
  ( SELECT 4 AS num),
  ( SELECT 3 AS num),
  ( SELECT 1 AS num),
  ( SELECT 1 AS num),