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. 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

• GA4 Dimension & Metric Explorer

Integration Availability

This integration is available for:

• Enterprise

• Growth

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)

Click here to view the ERD for the GA4 via API integration illustrating the different tables and keys to join across tables.

Unexpected error with integration lucid: Integration is not installed on this space

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 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: Creating a Report

• 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: Creating a Report

• 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: Creating a Report

• 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

• Endpoint: Creating a Report

• 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.

Base Transactions DDA

• Endpoint: Creating a Report

• 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: Creating a Report

• 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: Creating a Report

• 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: Creating a Report

• 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