# Integration Specifications

## 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](https://support.google.com/analytics/topic/9359001?hl=en\&ref_topic=9306488). 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](https://www.ga4bigquery.com/how-to-query-an-intraday-table-and-combine-it-with-daily-tables-ua/) (tables ending in \_stg2)
* Hourly

{% hint style="info" %}
**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.
  {% endhint %}

## Entity Relationship Diagram (ERD)

[Click here to view the Daasity GA4 BigQuery integration](https://lucid.app/documents/embedded/25cf569d-b59b-4155-8e8b-534611ee81ea) 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-landing-page)
* [Base PDP Performance](#base-pdp-performance)
* [Base Shopping Stage](#base-shopping-stage)
* [Base Transactions](#base-transactions)
* [Base Traffic](#base-traffic)
* [Base Traffic Hourly History](#base-traffic-hourly)

### **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`]

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

### **Base PDP Performance**

* Endpoint: BigQuery Export
* Update Method: UPSERT
* Table Names:&#x20;
  * \[`ga4_bq_stg.base_pdp_performance_stg1`]
  * \[`ga4_bq_stg.base_pdp_performance_stg2`]

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

### **Base Shopping Stage**

* Endpoint: BigQuery Export
* Update Method: UPSERT
* Table Names:&#x20;
  * \[`ga4_bq_stg.base_shopping_stage_stg1`]
  * \[`ga4_bq_stg.base_shopping_stage_stg2`]

<table><thead><tr><th width="439">SQL</th><th>Database Column</th></tr></thead><tbody><tr><td>MD5('&#x3C;%= 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)</td><td>id</td></tr><tr><td>stream_id</td><td>stream_id</td></tr><tr><td>PARSE_TIMESTAMP('%Y%m%d', event_date)</td><td>created_on</td></tr><tr><td>source</td><td>source</td></tr><tr><td>medium</td><td>medium</td></tr><tr><td>campaign</td><td>campaign</td></tr><tr><td>shopping_stage</td><td>shopping_stage</td></tr><tr><td>NULL</td><td>channel_grouping</td></tr><tr><td>CASE WHEN ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END</td><td>user_type</td></tr><tr><td>device_category</td><td>device_category</td></tr><tr><td>COUNT(DISTINCT ga_session_id)</td><td>sessions</td></tr><tr><td>term</td><td>keyword</td></tr><tr><td>content</td><td>adcontent</td></tr><tr><td>NULL</td><td>channel</td></tr><tr><td>NULL</td><td>vendor</td></tr><tr><td>NULL</td><td>sub_channel</td></tr><tr><td>NULL</td><td>media_type</td></tr><tr><td>NULL</td><td>attribution_channel</td></tr><tr><td>'events'</td><td>__source_table</td></tr><tr><td>PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)</td><td>__file_date</td></tr><tr><td>MD5('&#x3C;%= 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)</td><td>__sync_key</td></tr><tr><td>'&#x3C;%= integration.account.id %>'</td><td>_account_id</td></tr><tr><td>CURRENT_TIMESTAMP()</td><td>__synced_at</td></tr></tbody></table>

### **Base Transactions**

* Endpoint: BigQuery Export
* Update Method: UPSERT
* Table Names:&#x20;
  * \[`ga4_bq_stg.base_transactions_stg1`]
  * \[`ga4_bq_stg.base_transactions_stg2`]

<table><thead><tr><th width="450">SQL</th><th>Database Column</th></tr></thead><tbody><tr><td>MD5('&#x3C;%= integration.account.id %>'||':'||stream_id||':'||transaction_id)</td><td>id</td></tr><tr><td>stream_id</td><td>stream_id</td></tr><tr><td>PARSE_TIMESTAMP('%Y%m%d', event_date)</td><td>created_on</td></tr><tr><td>transaction_id</td><td>transaction_id</td></tr><tr><td>source</td><td>source</td></tr><tr><td>medium</td><td>medium</td></tr><tr><td>campaign</td><td>campaign</td></tr><tr><td>NULL</td><td>channel_grouping</td></tr><tr><td>CASE WHEN ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END</td><td>user_type</td></tr><tr><td>NULL</td><td>country_iso_code</td></tr><tr><td>device_category</td><td>device_category</td></tr><tr><td>NULLIF(country,'')</td><td>country</td></tr><tr><td>city</td><td>city</td></tr><tr><td>full_referrer</td><td>full_referrer</td></tr><tr><td>region</td><td>region</td></tr><tr><td>browser</td><td>browser</td></tr><tr><td>operating_system</td><td>operating_system</td></tr><tr><td>term</td><td>keyword</td></tr><tr><td>content</td><td>adcontent</td></tr><tr><td>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)</td><td>transaction_revenue</td></tr><tr><td>NULL</td><td>channel</td></tr><tr><td>NULL</td><td>vendor</td></tr><tr><td>NULL</td><td>sub_channel</td></tr><tr><td>NULL</td><td>media_type</td></tr><tr><td>NULL</td><td>attribution_channel</td></tr><tr><td>'events'</td><td>__source_table</td></tr><tr><td>PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)</td><td>__file_date</td></tr><tr><td>MD5('&#x3C;%= integration.account.id %>'||':'||stream_id||':'||transaction_id)</td><td>__sync_key</td></tr><tr><td>'&#x3C;%= integration.account.id %>'</td><td>_account_id</td></tr><tr><td>CURRENT_TIMESTAMP()</td><td>__synced_at</td></tr></tbody></table>

### **Base Traffic**

* Endpoint: BigQuery Export
* Update Method: UPSERT
* Table Names:&#x20;
  * \[`ga4_bq_stg.base_traffic_stg1`]
  * \[`ga4_bq_stg.base_traffic_stg2`]

<table><thead><tr><th width="418">SQL</th><th>Database Column</th></tr></thead><tbody><tr><td>MD5('&#x3C;%= 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,''))</td><td>id</td></tr><tr><td>stream_id</td><td>stream_id</td></tr><tr><td>PARSE_TIMESTAMP('%Y%m%d', event_date)</td><td>created_on</td></tr><tr><td>source</td><td>source</td></tr><tr><td>medium</td><td>medium</td></tr><tr><td>campaign</td><td>campaign</td></tr><tr><td>NULL</td><td>channel_grouping</td></tr><tr><td>CASE WHEN ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END</td><td>user_type</td></tr><tr><td>device_category</td><td>device_category</td></tr><tr><td>NULL</td><td>country_iso_code</td></tr><tr><td>NULLIF(country_name,'')</td><td>country_name</td></tr><tr><td>SUM(pageviews)</td><td>pageviews</td></tr><tr><td>COUNT(DISTINCT ga_session_id)</td><td>sessions</td></tr><tr><td>COUNT(DISTINCT CASE WHEN is_engaged = 1 THEN ga_session_id END)</td><td>engaged_sessions</td></tr><tr><td>SUM(transactions)</td><td>transactions</td></tr><tr><td>SUM(session_duration)</td><td>session_duration</td></tr><tr><td>NULL</td><td>time_on_page</td></tr><tr><td>SUM(transaction_revenue)</td><td>transaction_revenue</td></tr><tr><td>term</td><td>keyword</td></tr><tr><td>content</td><td>adcontent</td></tr><tr><td>NULL</td><td>channel</td></tr><tr><td>NULL</td><td>vendor</td></tr><tr><td>NULL</td><td>sub_channel</td></tr><tr><td>NULL</td><td>media_type</td></tr><tr><td>NULL</td><td>attribution_channel</td></tr><tr><td>'events'</td><td>__source_table</td></tr><tr><td>PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)</td><td>__file_date</td></tr><tr><td>MD5('&#x3C;%= 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,''))</td><td>sync_key</td></tr><tr><td>'&#x3C;%= integration.account.id %>'</td><td>_account_id</td></tr><tr><td>CURRENT_TIMESTAMP()</td><td>__synced_at</td></tr></tbody></table>

### **Base Traffic Hourly**

* Endpoint: BigQuery Export
* Update Method: UPSERT
* Table Names:&#x20;
  * \[`ga4_bq_stg.base_traffic_hourly_history_stg1`]
  * \[`ga4_bq_stg.base_traffic_hourly_history_stg2`]

<table><thead><tr><th width="421">SQL</th><th>Database Column</th></tr></thead><tbody><tr><td>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,''))</td><td>id</td></tr><tr><td>stream_id</td><td>stream_id</td></tr><tr><td>PARSE_TIMESTAMP('%Y%m%d', event_date)</td><td>created_on</td></tr><tr><td>DATETIME_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp)),hour) AS date_hour_utc</td><td>date_hour_utc</td></tr><tr><td>source</td><td>source</td></tr><tr><td>medium</td><td>medium</td></tr><tr><td>campaign</td><td>campaign</td></tr><tr><td>NULL</td><td>channel_grouping</td></tr><tr><td>term</td><td>keyword</td></tr><tr><td>content</td><td>adcontent</td></tr><tr><td>full_referrer</td><td>full_referrer</td></tr><tr><td>CASE WHEN ftu.ga_session_id IS NOT NULL THEN 'First-time' ELSE 'Returning' END</td><td>user_type</td></tr><tr><td>device_category</td><td>device_category</td></tr><tr><td>NULL</td><td>country_iso_code</td></tr><tr><td>NULLIF(sm.country_name,'')</td><td>country_name</td></tr><tr><td>SUM(pageviews)</td><td>pageviews</td></tr><tr><td>COUNT(DISTINCT  ga_session_id)</td><td>sessions</td></tr><tr><td>COUNT(DISTINCT CASE WHEN is_engaged = 1 THEN ga_session_id END)</td><td>engaged_sessions</td></tr><tr><td>SUM(transactions)</td><td>transactions</td></tr><tr><td>SUM(transaction_revenue)</td><td>transaction_revenue</td></tr><tr><td>NULL</td><td>channel</td></tr><tr><td>NULL</td><td>vendor</td></tr><tr><td>NULL</td><td>sub_channel</td></tr><tr><td>NULL</td><td>media_type</td></tr><tr><td>NULL</td><td>attribution_channel</td></tr><tr><td>'events'</td><td>__source_table</td></tr><tr><td>PARSE_TIMESTAMP('%Y%m%d', _TABLE_SUFFIX)</td><td>__file_date</td></tr><tr><td>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,''))</td><td>__sync_key</td></tr><tr><td>'&#x3C;%= integration.account.id %>'</td><td>_account_id</td></tr><tr><td>CURRENT_TIMESTAMP()</td><td>__synced_at</td></tr></tbody></table>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://help.daasity.com/core-concepts/data-integrations/setup-guides/digital-integrations/google-analytics-4/bigquery-integration/integration-specifications.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.