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
Comments
0 comments
Article is closed for comments.