Google Analytics 4 (GA4 BigQuery Export) data model
Kimball warehouse for the GA4 BigQuery export: a transaction-grain event fact, a derived session fact, a purchase fact, and an item-event fact (from the flattened items array), over conformed user/device/geo/traffic-source/page/event-type/item dimensions. Turns the nested GA4 export into query-ready stars for behavior and ecommerce reporting.
Google Analytics 4 modeled from its native BigQuery export: the nested events_* tables (repeated event_params and items records) flattened into conformed star schemas — event, session, purchase, and item-event facts over date, user, device, geo, traffic-source, page, event-type, and item dimensions for acquisition, engagement, conversion, and ecommerce analytics. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 12
- tables
- 112
- columns
- 20
- relationships
- 1
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Flatten the nested export into conformed stars
The GA4 export stores event_params, user_properties, and items as repeated RECORDs that require UNNEST to query; flattening into an event fact plus an item-event fact makes the data BI-tool-ready without per-query array gymnastics.
Derived session fact alongside the event fact
GA4 has no session table — sessions are reconstructed from the ga_session_id event parameter; a dedicated session fact precomputes engaged-session, bounce, and duration metrics that are expensive to derive ad hoc.
Separate purchase and item-event facts for ecommerce
Purchase headers (revenue, tax, shipping) and the repeated items array live at different grains; modelling them as two facts supports both order-level revenue and product-level performance.
user_id hashed under consent regimes
GA4 carries a pseudonymous user_pseudo_id plus an optional logged-in user_id; hashing user_id and surfacing consent-aware dimensions keeps the model GDPR/CCPA-compliant in the Consent Mode era.
Tables in the Google Analytics 4 (GA4 BigQuery Export) model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (4)
fct_event
Grain: one row per GA4 event (the flattened events_* export)
Transaction-grain event fact flattened from the nested events_* export (one row per event).
| Column | Type | Keys |
|---|---|---|
| event_key | bigint | PK |
| date_key | bigint | |
| user_key | bigint | |
| device_key | bigint | |
| geo_key | bigint | |
| traffic_source_key | bigint | |
| page_key | bigint | |
| event_type_key | bigint | |
| event_timestamp_micros | bigint | |
| session_id | varchar | |
| engagement_time_msec | integer | |
| event_value_usd | decimal | |
| is_conversion | boolean | |
| event_count | smallint |
fct_session
Grain: one row per GA4 session (derived from events)
Session-grain fact for acquisition, engagement-rate, and landing-page analytics.
| Column | Type | Keys |
|---|---|---|
| session_key | bigint | PK |
| date_key | bigint | |
| user_key | bigint | |
| traffic_source_key | bigint | |
| device_key | bigint | |
| geo_key | bigint | |
| landing_page_key | bigint | |
| session_id | varchar | |
| session_start_micros | bigint | |
| session_number | smallint | |
| event_count | integer | |
| pageview_count | integer | |
| session_duration_sec | integer | |
| conversions | smallint | |
| purchase_revenue_usd | decimal | |
| is_engaged_session | boolean | |
| is_bounce | boolean |
fct_purchase
Grain: one row per GA4 purchase/transaction event
Transaction-grain purchase fact for revenue, AOV, and channel-revenue analytics.
| Column | Type | Keys |
|---|---|---|
| purchase_key | bigint | PK |
| date_key | bigint | |
| user_key | bigint | |
| traffic_source_key | bigint | |
| transaction_id | varchar | |
| session_id | varchar | |
| purchase_revenue_usd | decimal | |
| tax_value_usd | decimal | |
| shipping_value_usd | decimal | |
| refund_value_usd | decimal | |
| total_item_quantity | smallint | |
| unique_items | smallint | |
| is_refunded | boolean | |
| purchase_count | smallint |
fct_item_event
Grain: one row per item per ecommerce event (the flattened items array)
Item-grain ecommerce fact flattened from the repeated items record for product analytics.
| Column | Type | Keys |
|---|---|---|
| item_event_key | bigint | PK |
| date_key | bigint | |
| user_key | bigint | |
| item_key | bigint | |
| event_type_key | bigint | |
| transaction_id | varchar | |
| quantity | decimal | |
| item_revenue_usd | decimal | |
| price_usd | decimal | |
| item_event_count | smallint |
Dimension tables (3)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| day_of_week | smallint | |
| is_weekend | boolean |
dim_user
Grain: one row per GA4 user (user_pseudo_id)
| Column | Type | Keys |
|---|---|---|
| user_key | bigint | PK |
| user_pseudo_id | varchar | BK |
| user_id | varchar | PII |
| first_touch_date_key | bigint | |
| first_traffic_source | varchar | |
| first_traffic_medium | varchar | |
| first_traffic_campaign | varchar | |
| first_device_category | varchar | |
| first_country | varchar | |
| is_logged_in | boolean |
dim_item
Grain: one row per ecommerce item (product)
| Column | Type | Keys |
|---|---|---|
| item_key | bigint | PK |
| item_id | varchar | BK |
| item_name | varchar | |
| item_brand | varchar | |
| item_category | varchar | |
| item_category2 | varchar | |
| item_variant | varchar | |
| price_usd | decimal |
Lookup tables (5)
dim_device
Grain: one row per device/platform combination
| Column | Type | Keys |
|---|---|---|
| device_key | bigint | PK |
| device_category | varchar | BK |
| operating_system | varchar | |
| operating_system_version | varchar | |
| browser | varchar | |
| mobile_brand_name | varchar | |
| language | varchar | |
| stream_platform | varchar |
dim_geo
Grain: one row per geographic location
| Column | Type | Keys |
|---|---|---|
| geo_key | bigint | PK |
| continent | varchar | |
| sub_continent | varchar | |
| country | varchar | BK |
| region | varchar | |
| city | varchar | |
| metro | varchar |
dim_traffic_source
Grain: one row per source/medium/campaign
| Column | Type | Keys |
|---|---|---|
| traffic_source_key | bigint | PK |
| source | varchar | BK |
| medium | varchar | |
| campaign | varchar | |
| source_medium | varchar | |
| default_channel_grouping | varchar | |
| is_paid | boolean |
dim_page
Grain: one row per page/screen
| Column | Type | Keys |
|---|---|---|
| page_key | bigint | PK |
| page_location | varchar | BK |
| page_path | varchar | |
| page_title | varchar | |
| hostname | varchar | |
| content_group | varchar |
dim_event_type
Grain: one row per GA4 event name
| Column | Type | Keys |
|---|---|---|
| event_type_key | bigint | PK |
| event_name | varchar | BK |
| event_category | varchar | |
| is_conversion | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_event.date_key → dim_date (many-to-one)
- fct_event.user_key → dim_user (many-to-one)
- fct_event.device_key → dim_device (many-to-one)
- fct_event.geo_key → dim_geo (many-to-one)
- fct_event.traffic_source_key → dim_traffic_source (many-to-one)
- fct_event.page_key → dim_page (many-to-one)
- fct_event.event_type_key → dim_event_type (many-to-one)
- fct_session.date_key → dim_date (many-to-one)
- fct_session.user_key → dim_user (many-to-one)
- fct_session.traffic_source_key → dim_traffic_source (many-to-one)
- fct_session.device_key → dim_device (many-to-one)
- fct_session.geo_key → dim_geo (many-to-one)
- fct_session.landing_page_key → dim_page (many-to-one)
- fct_purchase.date_key → dim_date (many-to-one)
- fct_purchase.user_key → dim_user (many-to-one)
- fct_purchase.traffic_source_key → dim_traffic_source (many-to-one)
- fct_item_event.date_key → dim_date (many-to-one)
- fct_item_event.user_key → dim_user (many-to-one)
- fct_item_event.item_key → dim_item (many-to-one)
- fct_item_event.event_type_key → dim_event_type (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.