BigQuery Workbook

This workbook contains all the SQL concepts required to effectively query GA4 data in Google BigQuery.

Analytic functions

-- How the data is aligned with "PARTITION BY user_pseudo_id ORDER BY event_timestamp"

+----------------+------------+-----------------+
| user_pseudo_id | event_name | event_timestamp |
+----------------+------------+-----------------+
| user123        | page_view  | 12345           |
| user123        | page_view  | 12346           |
| user123        | page_view  | 12348           |
| user234        | page_view  | 12349           |
| user234        | page_view  | 12350           |
+----------------+------------+-----------------+

ARRAY_AGG

-- Gets the first source for all user_pseudo_id

select
    user_pseudo_id,
    array_agg(
        source order by date
    )[offset(0)] as first_source
from
    source_table
group by
    user_pseudo_id

AS

-- Rename the event_timestamp_micros column as event_timestamp
select
    event_timestamp_micros as event_timestamp

BETWEEN … AND

CASE WHEN

-- Returns "h1" as the current date is within the first half of the year

select
    case
        when extract(quarter from current_date()) in (1, 2) then 'h1'
        else 'h2' end

Code comments

CONCAT

-- The CONCAT joins source and medium together, resulting in e.g. "google / cpc"

select
    concat(traffic_source.source, " / ", traffic_source.medium) as source_medium,
    count(distinct user_pseudo_id) as users
from
    `simoahava-com.analytics_206575074.events_20210601`
group by
    source_medium
order by
    users desc

COUNT

-- Count all rows where event name is "session_start"
select
    count(*) as sessions
from
    <table>
where
    event_name = 'session_start'

COUNTIF

CROSS JOIN

-- These two queries do exactly the same thing, first:

select
    props.key
from
    table
    cross join unnest(user_properties) as props

-- And second:

select
    props.key
from
    table,
    unnest(user_properties) as props

CURRENT_DATE

DATE_DIFF

-- Returns 1, as that's the difference in YEAR. date_diff returns the number of date part *boundaries* between the two dates. There's a year boundary between 1st Jan, 2021 and 31st Dec, 2020.

select
date_diff(date '2021-01-01', date '2020-12-31', year

DATE_SUB

select
    date_sub(current_date(), interval 4 week) as four_weeks_ago

DISTINCT

EXCEPT

EXTRACT

-- Returns the quarter (1, 2, 3, or 4) for the current date

select
    extract(quarter from current_date())

FORMAT

FORMAT_DATE

-- Outputs "Monday, 25 Jun, 2021" if current_date is June 25th, 2021.
select
    format_date('%A, %d %b, %Y', current_date())

FROM

GROUP BY

select
    user_pseudo_id,
    event_name,
    count(*) as number_of_events
from
    table
group by
    user_pseudo_id,
    event_name

HAVING

-- Would not work:

select
    user_pseudo_id as user
from
    table
where
    user is not null

-- Would work:

select
    user_pseudo_id as user,
    count(*) as hits
from
    table
group by
    user
having
    user is not null

IFNULL

IN

-- Returns true

select
    2021 in (2019, 2020, 2021)

LAG and LEAD

LEFT JOIN

-- Table_1                     Table_2

+------+----------------+   +------+-------------+
| user | transaction_id |   | user | year_active |
+------+----------------+   +------+-------------+
| simo | t12345         |   | simo | 2021        |
| mari | t23456         |   | mari | 2021        |
| john | t34567         |   | anne | 2018        |
+------+----------------+   +------+-------------+

-- Query

select
    table_1.user,
    table_1.transaction_id,
    table_2.year_active
from
    Table_1 as table_1
    left join Table_2 as table_2
    on table_1.user = table_2.user

-- Result

+------+----------------+-------------+
| user | transaction_id | year_active |
+------+----------------+-------------+
| simo | t12345         | 2021        |
| mari | t23456         | 2021        |
| john | t34567         | null        |
+------+----------------+-------------+

LIMIT

MAX

NULLIF

-- Returns null if user_id is 'admin'

select
    nullif(user_id, 'admin') as user
from
    table

ORDER BY

select
    user_pseudo_id,
    event_name
    count(*) as number_of_events
from
    table
group by
    user_pseudo_id,
    event_name
order by
    number_of_events desc

PARSE_DATE

PIVOT

-- Main query that selects everything in the subquery
select
    *
from(
-- Subquery that selects user_pseudo_id and event_name from the source_table
    select
        user_pseudo_id,
        event_name
    from
        source_table)
-- Pivot
    pivot(
-- Aggregation that counts all instances of each event name
        count(*)
-- Specify that event_name is what you want to count and pivot with
        for
            event_name
-- Specify the values of event_name you want to count and pivot with
        in
            ('page_view', 'session_start', 'user_engagement'))
-- Only include users that had page views
where
    page_view != 0
-- Sort by page views descending
order by
    page_view desc

QUALIFY

REGEXP_EXTRACT

-- Returns "example.com" due to the capturing group in the regex

select
    regexp_extract('email@example.com', '@(.*)')

ROW_NUMBER

SAFE_DIVIDE

-- This would throw a "division by zero" error when running the query

select
    5/0 as division

-- This would return null and not throw an error

select
    safe_divide(5, 0) as division

Scalar subquery

-- Select the "age" column as well as the average of all "age" values in the table

select
    age,
    (select avg(age) from user_stats) as avg_age
from
    table

SELECT

SUM

_TABLE_SUFFIX

TIMESTAMP_MICROS

UNION

-- source_table_1              source_table_2

+------+----------------+   +------+----------------+
| user | transaction_id |   | user | transaction_id |
+------+----------------+   +------+----------------+
| simo | t12345         |   | simo | t34567         |
| mari | t23456         |   | mari | t23456         |
+------+----------------+   +------+----------------+

-- Query

select
    *
from
    source_table_1
union distinct
select
    *
from
    source_table_2

-- Result

+------+----------------+
| user | transaction_id |
+------+----------------+
| simo | t12345         |
| mari | t23456         |
| simo | t34567         |
+------+----------------+

UNNEST

+-----+----------------+---------+                            +-----+---------+
| ROW | user_pseudo_id | devices |                            | ROW | devices |
+-----+----------------+---------+      After UNNESTing       +-----+---------+
|  1  | user123        | iphone  |          devices           |  1  | iphone  |
|     |                | android |                            |  2  | android |
+-----+----------------+---------+                            +-----+---------+

WHERE

-- Select all columns for rows where the event_name is "session_start"
select
    *
from
    <table>
where
    event_name = 'session_start'

WITH…AS

-- my_query is the named subquery, and it's referenced in the main query with FROM

with my_query as (
select
    *
from
    my_source_table
order by
    kpi desc)

select
    kpi_id
from
    my_query
limit
    1