For the complete documentation index, see llms.txt. This page is also available as Markdown.

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:

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.

spinner

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

  • 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

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.

Base Traffic Partial

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

  • 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

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

  • 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

  • Update Method: UPSERT

  • Table Name: [ga4_api.base_transactions]

  • 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

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

  • 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

  • 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

  • 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

  • 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

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

Was this helpful?