Integration Specifications

This article will help you learn about how Daasity replicates data from GA4 BigQuery, limitations to the data we can extract and where the data is stored in the GA4 BigQuery schema.

Integration Overview

Google Analytics V4 is the newest version of Google Analytics, wherein raw event data from Google Analytics V4 properties can be exported to BigQuery and then fetched through a series of SQL queries.

This document provides context on what kind of data is being gathered through this extractor, which endpoints that data is coming from, and how the extracted tables relate to each other.

Integration Availability

This integration is available for:

• Enterprise

• Growth

BigQuery Export

The Daasity GA4 BigQuery extractor is built based on this GA4 BigQuery Export Documentation. Staging tables are extracted from BigQuery Exports via SQL and are then stitched together during the transformation process to create the standard GA report tables. The Daasity GA4 BigQuery extractor fetches reports in the following time periods:

• Full day (tables ending in _stg1)

• Intraday (tables ending in _stg2)

• Hourly

NOTE:

• Daasity includes a cutover date for merchants that have both a GA3 and a GA4 integration. This allows merchants to link the integrations and set a date after which the UTS tables will be populated with data from GA4 instead of GA3.

• Intraday reports are currently unavailable but will be optional for merchants soon.

Entity Relationship Diagram (ERD)

Click here to view the Daasity GA4 BigQuery integration illustrating the different tables and keys to join across tables.

GA4 BigQuery Schema

The Daasity GA4 BigQuery extractor creates these tables using SQL and replication methods listed. The data is mapped from BigQuery to the table based on the mapping logic outlined in each table.

• Base Landing Page
• Base PDP Performance
• Base Shopping Stage
• Base Transactions
• Base Traffic
• Base Traffic Hourly History

Base Landing Page

• Endpoint: BigQuery Export

• Update Method: UPSERT

• Table Names:

  • [ga4_bq_stg.base_landing_page_stg1]

  • [ga4_bq_stg.base_landing_page_stg2]

SQL	Database Column
MD5('<%= integration.account.id %>'||':'||stream_id||':'||created_on||':'||IFNULL(REGEXP_REPLACE(landing_page,r'\?.*',''),''))	id
stream_id	stream_id
PARSE_TIMESTAMP('%Y%m%d', event_date)	created_on
REGEXP_REPLACE(landing_page,r'\?.*','')	landing_page_path
SUM(pageviews)	pageviews
COUNT(DISTINCT ga_session_id)	sessions
COUNT(DISTINCT CASE WHEN is_engaged = 1 THEN ga_session_id END)	engaged_sessions
SUM(transactions)	transactions
SUM(session_duration)	session_duration
SUM(transaction_revenue)	transaction_revenue
'events'	__source_table
PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)	__file_date
MD5('<%= integration.account.id %>'||':'||stream_id||':'||created_on||':'||IFNULL(REGEXP_REPLACE(landing_page,r'\?.*',''),''))	__sync_key
'<%= integration.account.id %>'	_account_id
CURRENT_TIMESTAMP()	__synced_at

Base PDP Performance

• Endpoint: BigQuery Export

• Update Method: UPSERT

• Table Names:

  • [ga4_bq_stg.base_pdp_performance_stg1]

  • [ga4_bq_stg.base_pdp_performance_stg2]

SQL	Database Column
MD5('<%= integration.account.id %>'||':'||stream_id||':'||event_date||':'||item_name||':'||item_id||':'||IFNULL(currency,''))	id
stream_id	stream_id
PARSE_TIMESTAMP('%Y%m%d', event_date)	created_on
item_name	product_name
item_id	product_sku
currency	currency
COUNT(CASE WHEN event_name = 'view_item' THEN CONCAT(user_pseudo_id,event_timestamp) END)	product_detail_views
COUNT(CASE WHEN event_name = 'add_to_cart' THEN CONCAT(user_pseudo_id,event_timestamp) END)	product_adds_to_cart
COUNT(CASE WHEN event_name = 'begin_checkout' THEN CONCAT(user_pseudo_id,event_timestamp) END)	product_checkouts
SUM(CASE WHEN event_name = 'purchase' THEN item_revenue ELSE 0 END)	product_sales
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN transaction_id END)	product_purchases
'events'	__source_table
PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)	__file_date
MD5('<%= integration.account.id %>'||':'||stream_id||':'||event_date||':'||item_name||':'||item_id||':'||IFNULL(currency,''))	__sync_key
'<%= integration.account.id %>'	_account_id
CURRENT_TIMESTAMP()	__synced_at

Base Shopping Stage

• Endpoint: BigQuery Export

• Update Method: UPSERT

• Table Names:

  • [ga4_bq_stg.base_shopping_stage_stg1]

  • [ga4_bq_stg.base_shopping_stage_stg2]

SQL	Database Column
MD5('<%= integration.account.id %>'||':'||stream_id||':'||created_on||':'||IFNULL(source,'')||':'||IFNULL(medium,'')||':'||IFNULL(campaign,'')||':'||IFNULL(term,'')||':'||IFNULL(content,'')||':'||shopping_stage||':'||CASE WHEN ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END||device_category)	id
stream_id	stream_id
PARSE_TIMESTAMP('%Y%m%d', event_date)	created_on
source	source
medium	medium
campaign	campaign
shopping_stage	shopping_stage
NULL	channel_grouping
CASE WHEN ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END	user_type
device_category	device_category
COUNT(DISTINCT ga_session_id)	sessions
term	keyword
content	adcontent
NULL	channel
NULL	vendor
NULL	sub_channel
NULL	media_type
NULL	attribution_channel
'events'	__source_table
PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)	__file_date
MD5('<%= integration.account.id %>'||':'||stream_id||':'||created_on||':'||IFNULL(source,'')||':'||IFNULL(medium,'')||':'||IFNULL(campaign,'')||':'||IFNULL(term,'')||':'||IFNULL(content,'')||':'||shopping_stage||':'||CASE WHEN ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END||device_category)	__sync_key
'<%= integration.account.id %>'	_account_id
CURRENT_TIMESTAMP()	__synced_at

Base Transactions

• Endpoint: BigQuery Export

• Update Method: UPSERT

• Table Names:

  • [ga4_bq_stg.base_transactions_stg1]

  • [ga4_bq_stg.base_transactions_stg2]

SQL	Database Column
MD5('<%= integration.account.id %>'||':'||stream_id||':'||transaction_id)	id
stream_id	stream_id
PARSE_TIMESTAMP('%Y%m%d', event_date)	created_on
transaction_id	transaction_id
source	source
medium	medium
campaign	campaign
NULL	channel_grouping
CASE WHEN ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END	user_type
NULL	country_iso_code
device_category	device_category
NULLIF(country,'')	country
city	city
full_referrer	full_referrer
region	region
browser	browser
operating_system	operating_system
term	keyword
content	adcontent
FIRST_VALUE(purchase_revenue_in_usd IGNORE NULLS) OVER (PARTITION BY transaction_id ORDER BY event_timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)	transaction_revenue
NULL	channel
NULL	vendor
NULL	sub_channel
NULL	media_type
NULL	attribution_channel
'events'	__source_table
PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)	__file_date
MD5('<%= integration.account.id %>'||':'||stream_id||':'||transaction_id)	__sync_key
'<%= integration.account.id %>'	_account_id
CURRENT_TIMESTAMP()	__synced_at

Base Traffic

• Endpoint: BigQuery Export

• Update Method: UPSERT

• Table Names:

  • [ga4_bq_stg.base_traffic_stg1]

  • [ga4_bq_stg.base_traffic_stg2]

SQL	Database Column
MD5('<%= integration.account.id %>'||':'||stream_id||':'||created_on||':'||IFNULL(source,'')||':'||IFNULL(medium,'')||':'||IFNULL(campaign,'')||':'||IFNULL(term,'')||':'||IFNULL(content,'')||':'||CASE WHEN ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END||':'||IFNULL(device_category,'')||':'||IFNULL(country_name,''))	id
stream_id	stream_id
PARSE_TIMESTAMP('%Y%m%d', event_date)	created_on
source	source
medium	medium
campaign	campaign
NULL	channel_grouping
CASE WHEN ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END	user_type
device_category	device_category
NULL	country_iso_code
NULLIF(country_name,'')	country_name
SUM(pageviews)	pageviews
COUNT(DISTINCT ga_session_id)	sessions
COUNT(DISTINCT CASE WHEN is_engaged = 1 THEN ga_session_id END)	engaged_sessions
SUM(transactions)	transactions
SUM(session_duration)	session_duration
NULL	time_on_page
SUM(transaction_revenue)	transaction_revenue
term	keyword
content	adcontent
NULL	channel
NULL	vendor
NULL	sub_channel
NULL	media_type
NULL	attribution_channel
'events'	__source_table
PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)	__file_date
MD5('<%= integration.account.id %>'||':'||sm.stream_id||':'||sm.created_on||':'||IFNULL(ts.source,'')||':'||IFNULL(ts.medium,'')||':'||IFNULL(ts.campaign,'')||':'||IFNULL(ts.term,'')||':'||IFNULL(ts.content,'')||':'||CASE WHEN ftu.ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END||':'||IFNULL(sm.device_category,'')||':'||IFNULL(sm.country_name,''))	sync_key
'<%= integration.account.id %>'	_account_id
CURRENT_TIMESTAMP()	__synced_at

Base Traffic Hourly

• Endpoint: BigQuery Export

• Update Method: UPSERT

• Table Names:

  • [ga4_bq_stg.base_traffic_hourly_history_stg1]

  • [ga4_bq_stg.base_traffic_hourly_history_stg2]

SQL	Database Column
MD5(sm.stream_id||':'||sm.created_on||':'||DATETIME_TRUNC(DATETIME(TIMESTAMP_MICROS(st.event_timestamp)),hour)||':'||IFNULL(ts.source,'')||':'||IFNULL(ts.medium,'')||':'||IFNULL(ts.campaign,'')||':'||IFNULL(ts.term,'')||':'||IFNULL(ts.content,'')||':'||IFNULL(ts.full_referrer,'')||':'||CASE WHEN ftu.ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END||':'||IFNULL(sm.device_category,'')||':'||IFNULL(sm.country_name,''))	id
stream_id	stream_id
PARSE_TIMESTAMP('%Y%m%d', event_date)	created_on
DATETIME_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp)),hour) AS date_hour_utc	date_hour_utc
source	source
medium	medium
campaign	campaign
NULL	channel_grouping
term	keyword
content	adcontent
full_referrer	full_referrer
CASE WHEN ftu.ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END	user_type
device_category	device_category
NULL	country_iso_code
NULLIF(sm.country_name,'')	country_name
SUM(pageviews)	pageviews
COUNT(DISTINCT ga_session_id)	sessions
COUNT(DISTINCT CASE WHEN is_engaged = 1 THEN ga_session_id END)	engaged_sessions
SUM(transactions)	transactions
SUM(transaction_revenue)	transaction_revenue
NULL	channel
NULL	vendor
NULL	sub_channel
NULL	media_type
NULL	attribution_channel
'events'	__source_table
PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)	__file_date
MD5(sm.stream_id||':'||sm.created_on||':'||DATETIME_TRUNC(DATETIME(TIMESTAMP_MICROS(st.event_timestamp)),hour)||':'||IFNULL(ts.source,'')||':'||IFNULL(ts.medium,'')||':'||IFNULL(ts.campaign,'')||':'||IFNULL(ts.term,'')||':'||IFNULL(ts.content,'')||':'||IFNULL(ts.full_referrer,'')||':'||CASE WHEN ftu.ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END||':'||IFNULL(sm.device_category,'')||':'||IFNULL(sm.country_name,''))	__sync_key
'<%= integration.account.id %>'	_account_id
CURRENT_TIMESTAMP()	__synced_at