Examples of Google BigQuery SQL features – SegmentStream

How to use Wildcard tables

https://cloud.google.com/bigquery/docs/querying-wildcard-tables

Example: Counting unique users who visited website between 10 October 2019 and 14 October 2019

-- Count users
SELECT
    COUNT(DISTINCT anonymousId) as usersCount
FROM
    `project_id.dataset_name.sessions_*`
WHERE
    _TABLE_SUFFIX BETWEEN "20191010"
    AND"20191014"

How to UNNEST nested values

https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

Example: Get a flat table of all pageviews ordered by time from a session table:

-- Flat table with page views from "20191010" till "20191014"
SELECT
    receivedAt,
    anonymousId,
    sessionId,
    event.page
FROM
    `project_id.dataset_name.sessions_*`,
    UNNEST(hits) AS hits
WHERE
    _TABLE_SUFFIX BETWEEN "20191010"
    AND"20191014"
    AND event.name='Viewed Page'
ORDER BY
    receivedAt

Example: Count the number of JS Errors:

-- Count the number of JS Errors on mobile devices
SELECT
    COUNT(*) AS JS_Errors
FROM
    `project_id.dataset_name.sessions_*`,
    UNNEST(hits) AS hits
WHERE
    _TABLE_SUFFIX BETWEEN "20191010"
    AND"20191014"
    AND event.category ='JS Errors'
    AND context.device.type = 'mobile'

Analytics functions

https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts

Example: Count sessions, 50% scroll events and sessions with 50% scroll events grouped by device type:

-- Sessions, 50% scroll events and sessions with 50% scroll events by device type
WITH countScrolls50 AS (
    SELECT
        context.device.type AS deviceType,
       sessionId,
       SUM(
          IF(
             hits.event.name = "Scrolled Page"
             ANDCAST(hits.event.label AS INT64) >=50,
             1,
             0
           )
       ) OVER (PARTITION BY sessionId) AS scrolls50,
       ROW_NUMBER() OVER (
          PARTITIONBY sessionId
          ORDER BY
             sessionId ASC
       ) AS rowNumber
    FROM
       `project_id.dataset_name.sessions_*`,
       UNNEST(hits) AS hits
    WHERE
       _TABLE_SUFFIX BETWEEN "20191010"
       AND"20191014"
),
countSessionsWithScroll50 AS (
    SELECT
       *,
       IF(scrolls50 >0, 1, 0) AS sessionsWithScroll50
    FROM
       countScrolls50
    WHERE
       rowNumber = 1
)
SELECT
    deviceType,
    COUNT(DISTINCT sessionId) ASsessions,
    SUM(scrolls50) AS scrolls50,
    SUM(sessionsWithScroll50) AS sessionsWithScroll50
FROM
    countSessionsWithScroll50
GROUP BY
    deviceType
ORDER BY
    sessionsDESC