Resources

Deployed Resources

Overview

The following resources are deployed to the destination dataset in all deployment scenarios.

Resource Name	Resource Type	Arguments	Resource Description
GA4_EVENTS	DATE-BOUNDED TABLE FUNCTION	start_date DATE, end_date DATE	Base date-bounded table function (DBTF) containing GA4 event-level data with some additional utility columns, data type conversions and decoder function references to include the custom flat STRUCT columns event_count, event_param and user_property.
GA4_event_names	FUNCTION	event_name STRING	Returns the custom flat STRUCT event_count containing sub-columns representing event counts (1 or NULL at event level) for each event_name (event_count.[event_name]) in addition to total events (event_count.total_events) and conversions (event_count.total_conversions). These sub-columns can be used as metrics, and aggregate functions can be applied directly to them.
GA4_event_parameters	event_params ARRAY<STRUCT>	FUNCTION	Returns the custom flat STRUCT event_param comprising sub-columns which contain type-specific values for each event_params key.
GA4_user_properties	user_properties ARRAY<STRUCT>	FUNCTION	Returns the custom flat STRUCT user_property comprising sub-columns which contain type-specific values for each user_properties key.
EVENTS	DATE-PARTITIONED TABLE	Partitioned by event_date	Output table containing remodelled event data. To connect this table optimally to Looker Studio, use the event_date partitioning column as the report date field in Looker Studio.
RUN_FLOW	PROCEDURE	start_date DATE, end_date DATE	Runs the flow to refresh the output EVENTS table, with the behaviour controlled by the arguments.

Architecture

These resources interoperate in the following architectural configuration:

flowchart TB
subgraph GA4 Dataset

subgraph source data
    analytics.events[analytics_#########.events_*]
end

subgraph data modelling
    analytics.RUN_FLOW((RUN_FLOW))
    subgraph analytics.GA4_EVENTS[GA4_EVENTS]
        analytics.GA4_event_names[GA4_event_names]
        analytics.GA4_event_params[GA4_event_params]
        analytics.GA4_user_properties[GA4_user_properties]
    end
end

subgraph output data
  analytics.EVENTS>EVENTS]
end

analytics.events --> analytics.GA4_EVENTS
analytics.GA4_EVENTS --> analytics.RUN_FLOW --> analytics.EVENTS

end

Usage

BigQuery

The EVENTS date-partitioned table is the output events table to which you connect downstream tools, logic and processes. Note that using the GoogleSQL CURRENT_DATE function enables dynamic ranges to be set in a clear and concise manner.

Query Data

basic query example: EVENTS

SELECT all data for the past 7 days

SELECT * 
FROM [dataset_id].EVENTS
WHERE event_date 
BETWEEN CURRENT_DATE - 7
AND CURRENT_DATE

advanced query example: EVENTS

SELECT all data for the past 7 days, then compute session count per date

WITH 
ga4_events AS (
    SELECT * 
    FROM [dataset_id].EVENTS
    WHERE event_date 
    BETWEEN CURRENT_DATE - 7
    AND CURRENT_DATE),

unique_sessions_per_day AS (
    SELECT 
    event_date,
    COUNT(DISTINCT session_id) AS session_id_count
    FROM ga4_events
    GROUP BY
    event_date)

SELECT *
FROM unique_sessions_per_day