This article will help you learn about how Daasity replicates data from Google Analytics 4 via the API, limitations to the data we can extract and where the data is stored in the GA4 API schema.
Key Topics
Click on the links below to take you to the section where you can learn more about this Integration
- Integration Overview
- Integration Availability
- API Endpoints
- Entity Relationship Diagram
- Google Analytics Schema
- Related Resources
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. 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:
API dimension limitations
The API limits extractions to 9 dimensions per report.
Discrepancies in session counts
Google has documented that GA4 session calculations are estimations and will not be exact counts, even when there is no sampling. So when you sum up the total number of sessions for a given day in the data extracted by Daasity, they likely will not match exactly the top-level number you see in the GA4 reporting interface. This is because we are pulling the data into your extracted tables at a higher level of granularity, which will return a different estimation than what you'll see when looking at the top-level session totals.
Here is an example of real data pulled directly from the GA4 Data API. Even though there is no sampling, the session totals when segmented by Device Category (49,249) are 1.6% higher than the unsegmented session total (48,477). This is expected.
Integration Availability
This integration is available for:
- Growth
- Enterprise
API Endpoints
The Daasity GA4 extractor is built based on the Analytics Data API. We use the endpoint noted in the Creating a Report section to extract data from GA4.
Entity Relationship Diagram (ERD)
The embedded diagram houses the ERD for the GA4 via API integration illustrating the different tables and keys to join across tables. Hover over the embedded diagram to reveal controls to zoom and scroll.
Google Analytics Schema
The Daasity GA4 via API extractor creates these tables using the endpoints and replication methods listed. The data is mapped from source API endpoint to the table based on the mapping logic outlined in each table.
- Base Traffic
- Base Shopping Stage
- Base PDP Performance
- Base Transactions
- Base Transactions DDA
- Base Transactions STG 2
- Base Transactions STG 3
- Base Transactions STG 4
Base Traffic
- Endpoint:
- Update Method: UPSERT
- Table Name: ga4_api.base_traffic
- Sync Key: streamId + date + sessionSource + sessionMedium + sessionCampaignName + sessionDefaultChannelGrouping + newVsReturning + deviceCategory + countryId
| JSON Element | Database Column |
| Daasity: Property entered into the UI | PROPERTY_ID |
| 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 |
Base Shopping Stage
- Endpoint:
- Update Method: UPSERT
- Table Name: ga4_api.base_shopping_stage
- Sync Key: streamId + date + sessionSource + sessionMedium + sessionCampaignName + eventName + sessionDefaultChannelGrouping + newVsReturning + deviceCategory
| JSON Element | Database Column |
| Daasity: Property entered into the UI | PROPERTY_ID |
| 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 |
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
Base PDP Performance
- Endpoint:
- Update Method: UPSERT
- Table Name: ga4_api.base_pdp_performance
- Sync Key: streamId + date + itemName + itemID
| JSON Element | Database Column |
| Daasity: Property entered into the UI | PROPERTY_ID |
| 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 DDA
- Endpoint:
- Update Method: UPSERT
- Table Name: ga4_api.base_transactions_dda
- Sync Key: streamId + date + transactionId + source + medium + campaignName + defaultChannelGrouping + manualAdContent + manualTerm
| JSON Element | Database Column |
| Daasity: Property entered into the UI | PROPERTY_ID |
| 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:
- Update Method: UPSERT
- Table Name: ga4_api.base_transactions_stg_2
- Sync Key: streamId + date + transactionId
| JSON Element | Database Column |
| Daasity: Property entered into the UI | PROPERTY_ID |
| 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:
- Update Method: UPSERT
- Table Name: ga4_api.base_transactions_stg_3
- Sync Key: streamId + date + transactionId
| JSON Element | Database Column |
| Daasity: Property entered into the UI | PROPERTY_ID |
| 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:
- Update Method: UPSERT
- Table Name: ga4_api.base_transactions_stg_4
- Sync Key: streamId + date + transactionId
| JSON Element | Database Column |
| Daasity: Property entered into the UI | PROPERTY_ID |
| 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 |
Base Transactions
- Endpoint:
- Update Method: UPSERT
- Table Name: ga4_api.base_transactions
- Sync Key: streamId + date + transactionId
| JSON Element | Database Column |
| Daasity: Property entered into the UI | PROPERTY_ID |
| streamId | STREAM_ID |
| date | CREATED_ON |
| transactionId | TRANSACTION_ID |
| sessionSource | SESSION_SOURCE |
| sessionMedium | SESSION_MEDIUM |
| sessionCampaignName | SESSION_CAMPAIGN_NAME |
| sessionDefaultChannelGrouping | SESSION_DEFAULT_CHANNEL_GROUPING |
| sessionManualTerm | SESSION_MANUAL_TERM |
| sessionManualAdContent | SESSION_MANUAL_AD_CONTENT |
| N/A | SESSION_SOURCE_PLATFORM |
| N/A | FIRST_USER_SOURCE |
| N/A | FIRST_USER_MEDIUM |
| N/A | FIRST_USER_CAMPAIGN_NAME |
| N/A | FIRST_USER_DEFAULT_CHANNEL_GROUPING |
| N/A | FIRST_USER_MANUAL_TERM |
| N/A | FIRST_USER_MANUAL_AD_CONTENT |
| N/A | FIRST_USER_SOURCE_PLATFORM |
| N/A | NEW_VS_RETURNING |
| N/A | DEVICE_CATEGORY |
| N/A | COUNTRY |
| N/A | COUNTRY_ID |
| N/A | CITY |
| N/A | REGION |
| N/A | BROWSER |
| N/A | OPERATING_SYSTEM |
| 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 |
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
Related Resources
- None