> For the complete documentation index, see [llms.txt](https://help.daasity.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://help.daasity.com/core-concepts/data-integrations/setup-guides/digital-integrations/google-analytics-4/api-integration/integration-specifications.md).

# Integration Specifications

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

## Integration Overview

Google Analytics 4 is Google's free web analytics service that allows you to analyze in-depth detail about the visitors on your website. It provides valuable insights that can help you to shape the success of your business.

The Daasity GA4 API extractor pulls a standard set of data via the [Analytics Data API](https://developers.google.com/analytics/devguides/reporting/data/v1). Similar to Universal Analytics / GA3 this endpoint has certain limitations on both available dimensions and metrics. More information on what can be extracted is available here:

* [Dimension & Metrics in GA4](https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema)
* [GA4 Dimension & Metric Explorer](https://ga-dev-tools.google/ga4/dimensions-metrics-explorer/)

## **Integration Availability**

This integration is available for:

* Enterprise
* Growth

## API Endpoints

The Daasity GA4 extractor is built based on the [Analytics Data API](https://developers.google.com/analytics/devguides/reporting/data/v1). We use the endpoint noted in the [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics) section to extract data from GA4.

## Entity Relationship Diagram (ERD)

[Click here to view the ERD for the GA4 via API integration](https://lucid.app/documents/embedded/36649bea-9930-4fff-abd0-e7f4073cb2d6) illustrating the different tables and keys to join across tables.

{% @lucid/lucid-component url="<https://lucid.app/lucidchart/36649bea-9930-4fff-abd0-e7f4073cb2d6/view?page=0_0>" fullWidth="true" %}

## Google Analytics 4 Schema

The Daasity GA4 via API extractor creates these tables using the endpoints and replication methods listed. The data is mapped from the source API endpoint to the table based on the mapping logic outlined in each table.​

* [Base Traffic](#base-traffic)
* [Base Traffic Partial](#base-traffic-partial)
* [Base Shopping Stage](#base-shopping-stage)
* [Base PDP Performance](#base-pdp-performance)
* [Base Transactions](#base-transactions)
* [Base Transactions DDA](#base-transactions-dda)
* [Base Transactions STG 2](#base-transactions-stage-2)
* [Base Transactions STG 3](#base-transactions-stage-3)
* [Base Transactions STG 4](#base-transactions-stage-4)
* [Custom Reports](#custom-reports)

### **Base Traffic**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: UPSERT
* Table Name: \[`ga4_api.base_traffic`]
* Sync Key: \[`integration_id + property_id + streamId + date + sessionSource + sessionMedium + sessionCampaignName + sessionDefaultChannelGrouping + newVsReturning + deviceCategory + countryId`]

| JSON Element                               | Database Column                     |
| ------------------------------------------ | ----------------------------------- |
| Daasity: Property entered into the UI      | PROPERTY\_ID                        |
| metadata.timeZone (report response)        | TIME\_ZONE                          |
| metadata.currencyCode (report response)    | CURRENCY\_CODE                      |
| streamId                                   | STREAM\_ID                          |
| date                                       | CREATED\_ON                         |
| sessionSource                              | SESSION\_SOURCE                     |
| sessionMedium                              | SESSION\_MEDIUM                     |
| sessionCampaignName                        | SESSION\_CAMPAIGN\_NAME             |
| sessionDefaultChannelGrouping              | SESSION\_DEFAULT\_CHANNEL\_GROUPING |
| newVsReturning                             | NEW\_VS\_RETURNING                  |
| deviceCategory                             | DEVICE\_CATEGORY                    |
| countryId                                  | COUNTRY\_ID                         |
| screenPageviews                            | SCREEN\_PAGEVIEWS                   |
| sessions                                   | SESSIONS                            |
| engagedSessions                            | ENGAGED\_SESSIONS                   |
| transactions                               | TRANSACTIONS                        |
| userEngagementDuration                     | USER\_ENGAGEMENT\_DURATION          |
| purchaseRevenue                            | PURCHASE\_REVENUE                   |
| Daasity: from Channel Mapping Google Sheet | CHANNEL                             |
| Daasity: from Channel Mapping Google Sheet | VENDOR                              |
| Daasity: from Channel Mapping Google Sheet | SUB\_CHANNEL                        |
| Daasity: from Channel Mapping Google Sheet | MEDIA\_TYPE                         |
| Daasity: Unique ID for Integration         | INTEGRATION\_ID                     |

{% hint style="info" %}
The last three days (today and the two days before) are not loaded into `base_traffic`; they load into `base_traffic_partial` instead (see below). A day is loaded into `base_traffic` once it is older than that.
{% endhint %}

### **Base Traffic Partial**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: TRUNCATE and INSERT (the table is emptied and reloaded on every run)
* Table Name: \[`ga4_api.base_traffic_partial`]
* Sync Key: \[`integration_id + property_id + streamId + date + sessionSource + sessionMedium + sessionCampaignName + sessionDefaultChannelGrouping + newVsReturning + deviceCategory + countryId`]

Same report and columns as [Base Traffic](#base-traffic), for the last three days only (today and the two days before). Loaded when the Traffic report is selected on the integration; not loaded by history or chrono runs.

### **Base Shopping Stage**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: UPSERT
* Table Name: \[`ga4_api.base_shopping_stage`]
* Sync Key: \[`integration_id + property_id + streamId + date + sessionSource + sessionMedium + sessionCampaignName + eventName + sessionDefaultChannelGrouping + newVsReturning + deviceCategory`]

| JSON Element                               | Database Column                     |
| ------------------------------------------ | ----------------------------------- |
| Daasity: Property entered into the UI      | PROPERTY\_ID                        |
| metadata.timeZone (report response)        | TIME\_ZONE                          |
| metadata.currencyCode (report response)    | CURRENCY\_CODE                      |
| streamId                                   | STREAM\_ID                          |
| date                                       | CREATED\_ON                         |
| sessionSource                              | SESSION\_SOURCE                     |
| sessionMedium                              | SESSION\_MEDIUM                     |
| sessionCampaignName                        | SESSION\_CAMPAIGN\_NAME             |
| eventName                                  | EVENT\_NAME                         |
| sessionDefaultChannelGrouping              | SESSION\_DEFAULT\_CHANNEL\_GROUPING |
| newVsReturning                             | NEW\_VS\_RETURNING                  |
| deviceCategory                             | DEVICE\_CATEGORY                    |
| sessions                                   | SESSIONS                            |
| Daasity: from Channel Mapping Google Sheet | CHANNEL                             |
| Daasity: from Channel Mapping Google Sheet | VENDOR                              |
| Daasity: from Channel Mapping Google Sheet | SUB\_CHANNEL                        |
| Daasity: from Channel Mapping Google Sheet | MEDIA\_TYPE                         |
| Daasity: Unique ID for Integration         | INTEGRATION\_ID                     |

{% hint style="info" %}
This extraction applies a number of filters to the report:

* Event Name on the following
  * session\_start
  * view\_item
  * add\_to\_cart
  * view\_cart
  * begin\_checkout
  * purchase
  * add\_payment\_info
  * add\_shipping\_info
  * view\_item\_list
  * select\_item
    {% endhint %}

### **Base PDP Performance**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: UPSERT
* Table Name: \[`ga4_api.base_pdp_performance`]
* Sync Key: \[`integration_id + property_id + streamId + date + itemName + itemId`]

| JSON Element                            | Database Column      |
| --------------------------------------- | -------------------- |
| Daasity: Property entered into the UI   | PROPERTY\_ID         |
| metadata.timeZone (report response)     | TIME\_ZONE           |
| metadata.currencyCode (report response) | CURRENCY\_CODE       |
| streamId                                | STREAM\_ID           |
| date                                    | CREATED\_ON          |
| itemName                                | ITEM\_NAME           |
| itemId                                  | ITEM\_ID             |
| itemsViewed                             | ITEM\_VIEW\_EVENTS   |
| itemsAddedToCart                        | ADD\_TO\_CARTS       |
| itemsCheckedOut                         | CHECKOUTS            |
| itemRevenue                             | ITEM\_REVENUE        |
| itemsPurchased                          | ECOMMERCE\_PURCHASES |
| Daasity: Unique ID for Integration      | INTEGRATION\_ID      |

### **Base Transactions**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: UPSERT
* Table Name: \[`ga4_api.base_transactions`]
* Sync Key: \[`integration_id + property_id + streamId + date + transactionId`]

<table><thead><tr><th width="360">JSON Element</th><th>Database Column</th></tr></thead><tbody><tr><td>Daasity: Property entered into the UI</td><td>PROPERTY_ID</td></tr><tr><td>metadata.timeZone (report response)</td><td>TIME_ZONE</td></tr><tr><td>metadata.currencyCode (report response)</td><td>CURRENCY_CODE</td></tr><tr><td>streamId</td><td>STREAM_ID</td></tr><tr><td>date</td><td>CREATED_ON</td></tr><tr><td>transactionId</td><td>TRANSACTION_ID</td></tr><tr><td>sessionSource</td><td>SESSION_SOURCE</td></tr><tr><td>sessionMedium</td><td>SESSION_MEDIUM</td></tr><tr><td>sessionCampaignName</td><td>SESSION_CAMPAIGN_NAME</td></tr><tr><td>sessionDefaultChannelGrouping</td><td>SESSION_DEFAULT_CHANNEL_GROUPING</td></tr><tr><td>sessionManualTerm</td><td>SESSION_MANUAL_TERM</td></tr><tr><td>sessionManualAdContent</td><td>SESSION_MANUAL_AD_CONTENT</td></tr><tr><td>N/A</td><td>SESSION_SOURCE_PLATFORM</td></tr><tr><td>N/A</td><td>FIRST_USER_SOURCE</td></tr><tr><td>N/A</td><td>FIRST_USER_MEDIUM</td></tr><tr><td>N/A</td><td>FIRST_USER_CAMPAIGN_NAME</td></tr><tr><td>N/A</td><td>FIRST_USER_DEFAULT_CHANNEL_GROUPING</td></tr><tr><td>N/A</td><td>FIRST_USER_MANUAL_TERM</td></tr><tr><td>N/A</td><td>FIRST_USER_MANUAL_AD_CONTENT</td></tr><tr><td>N/A</td><td>FIRST_USER_SOURCE_PLATFORM</td></tr><tr><td>N/A</td><td>NEW_VS_RETURNING</td></tr><tr><td>N/A</td><td>DEVICE_CATEGORY</td></tr><tr><td>N/A</td><td>COUNTRY</td></tr><tr><td>N/A</td><td>COUNTRY_ID</td></tr><tr><td>N/A</td><td>CITY</td></tr><tr><td>N/A</td><td>REGION</td></tr><tr><td>N/A</td><td>BROWSER</td></tr><tr><td>N/A</td><td>OPERATING_SYSTEM</td></tr><tr><td>purchaseRevenue</td><td>PURCHASE_REVENUE</td></tr><tr><td>Daasity: from Channel Mapping Google Sheet</td><td>CHANNEL</td></tr><tr><td>Daasity: from Channel Mapping Google Sheet</td><td>VENDOR</td></tr><tr><td>Daasity: from Channel Mapping Google Sheet</td><td>SUB_CHANNEL</td></tr><tr><td>Daasity: from Channel Mapping Google Sheet</td><td>MEDIA_TYPE</td></tr><tr><td>Daasity: Unique ID for Integration</td><td>INTEGRATION_ID</td></tr></tbody></table>

{% hint style="info" %}
**NOTE:** The \[`base_transactions`] table has all the fields across all transactions tables so that a single table can be created. To fully populate this table SQL code needs to be run to update the \[`base_transactions`] table from the staging and DDA transactions table.
{% endhint %}

### **Base Transactions DDA**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: UPSERT
* Table Name: \[`ga4_api.base_transactions_dda`]
* Sync Key: \[`integration_id + property_id + streamId + date + transactionId + source + medium + campaignName + defaultChannelGrouping + manualAdContent + manualTerm`]

| JSON Element                            | Database Column            |
| --------------------------------------- | -------------------------- |
| Daasity: Property entered into the UI   | PROPERTY\_ID               |
| metadata.timeZone (report response)     | TIME\_ZONE                 |
| metadata.currencyCode (report response) | CURRENCY\_CODE             |
| streamId                                | STREAM\_ID                 |
| date                                    | CREATED\_ON                |
| transactionId                           | TRANSACTION\_ID            |
| source                                  | SOURCE                     |
| medium                                  | MEDIUM                     |
| campaignName                            | CAMPAIGN\_NAME             |
| defaultChannelGrouping                  | DEFAULT\_CHANNEL\_GROUPING |
| manualAdContent                         | MANUAL\_AD\_CONTENT        |
| manualTerm                              | MANUAL\_TERM               |
| purchaseRevenue                         | PURCHASE\_REVENUE          |
| Daasity: Unique ID for Integration      | INTEGRATION\_ID            |

### **Base Transactions Stage 2**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: UPSERT
* Table Name: \[`ga4_api.base_transactions_stg_2`]
* Sync Key: \[`integration_id + property_id + streamId + date + transactionId`]

| JSON Element                            | Database Column                         |
| --------------------------------------- | --------------------------------------- |
| Daasity: Property entered into the UI   | PROPERTY\_ID                            |
| metadata.timeZone (report response)     | TIME\_ZONE                              |
| metadata.currencyCode (report response) | CURRENCY\_CODE                          |
| streamId                                | STREAM\_ID                              |
| date                                    | CREATED\_ON                             |
| transactionId                           | TRANSACTION\_ID                         |
| sessionSourcePlatform                   | SESSION\_SOURCE\_PLATFORM               |
| firstUserSource                         | FIRST\_USER\_SOURCE                     |
| firstUserMedium                         | FIRST\_USER\_MEDIUM                     |
| firstUserCampaignName                   | FIRST\_USER\_CAMPAIGN\_NAME             |
| firstUserDefaultChannelGrouping         | FIRST\_USER\_DEFAULT\_CHANNEL\_GROUPING |
| firstUserManualTerm                     | FIRST\_USER\_MANUAL\_TERM               |
| purchaseRevenue                         | PURCHASE\_REVENUE                       |
| Daasity: Unique ID for Integration      | INTEGRATION\_ID                         |

### **Base Transactions Stage 3**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: UPSERT
* Table Name: \[`ga4_api.base_transactions_stg_3`]
* Sync Key: \[`integration_id + property_id + streamId + date + transactionId`]

| JSON Element                            | Database Column                  |
| --------------------------------------- | -------------------------------- |
| Daasity: Property entered into the UI   | PROPERTY\_ID                     |
| metadata.timeZone (report response)     | TIME\_ZONE                       |
| metadata.currencyCode (report response) | CURRENCY\_CODE                   |
| streamId                                | STREAM\_ID                       |
| date                                    | CREATED\_ON                      |
| transactionId                           | TRANSACTION\_ID                  |
| firstUserManualAdContent                | FIRST\_USER\_MANUAL\_AD\_CONTENT |
| firstUserSourcePlatform                 | FIRST\_USER\_SOURCE\_PLATFORM    |
| newVsReturning                          | NEW\_VS\_RETURNING               |
| deviceCategory                          | DEVICE\_CATEGORY                 |
| purchaseRevenue                         | PURCHASE\_REVENUE                |
| Daasity: Unique ID for Integration      | INTEGRATION\_ID                  |

### **Base Transactions Stage 4**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: UPSERT
* Table Name: \[`ga4_api.base_transactions_stg_4`]
* Sync Key: \[`integration_id + property_id + streamId + date + transactionId`]

| JSON Element                            | Database Column   |
| --------------------------------------- | ----------------- |
| Daasity: Property entered into the UI   | PROPERTY\_ID      |
| metadata.timeZone (report response)     | TIME\_ZONE        |
| metadata.currencyCode (report response) | CURRENCY\_CODE    |
| streamId                                | STREAM\_ID        |
| date                                    | CREATED\_ON       |
| transactionId                           | TRANSACTION\_ID   |
| country                                 | COUNTRY           |
| countryId                               | COUNTRY\_ID       |
| city                                    | CITY              |
| region                                  | REGION            |
| browser                                 | BROWSER           |
| operatingSystem                         | OPERATING\_SYSTEM |
| purchaseRevenue                         | PURCHASE\_REVENUE |
| Daasity: Unique ID for Integration      | INTEGRATION\_ID   |

### **Custom Reports**

* Endpoint: [Creating a Report](https://developers.google.com/analytics/devguides/reporting/data/v1/basics)
* Update Method: UPSERT (the optional partial table: TRUNCATE and INSERT)
* Table Name: \[`ga4_api.<table name set on the custom report>`]

Custom reports set up on the integration load into their own table in the `ga4_api` schema, one column per dimension and metric chosen on the report, plus PROPERTY\_ID, TIME\_ZONE, CURRENCY\_CODE and INTEGRATION\_ID as above. The sync key is integration\_id + property\_id + the sync key fields chosen on the report. A custom report marked as partial also loads its last three days into `<table name>_partial`, the same way as Base Traffic Partial.
