Integration Specifications
This article will help you learn about how Daasity replicates data from Google Analytics (Universal. limitations to the data we can extract and where the data is stored in the Google Analytics schema.
Integration Overview
Google Analytics 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 V2 Base Google Analytics extractor pulls a standard set of data via the Core Reporting API. The Core Reporting API is a report generation API and requires parameters to be passed into the API. The request generates a report that is a summary roll-up of session level data that is stored within GA and returned as an aggregate summary.
NOTE: The API has limitations on the level of granularity that can be returned:
Dimensions are limited to 7 per report
Metrics are limited to 10 per report
Daasity queries each report for each day which thus removes the need to include the ga:Date as a parameter and thus allowing the full 7 dimensions to be used.
Integration Availability
This integration is available for:
Growth
Enterprise
API Endpoints
The Google Analytics extractor is built based on this Google Analytics Reporting API documentation. The following endpoint is used by Daasity to replicate data from Google Analytics:
Entity Relationship Diagram (ERD)
Click here to view the ERD for the Google Analytics (Universal Analytics) integration illustrating the different tables and keys to join across tables.
Google Analytics Schema
The Daasity V2 Google Analytics 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
Endpoint: Reports
Update Method: UPSERT
Table Name:
ga.base_traffic
MD5(ga:date + ga:channelGrouping + ga::source + ga:medium + ga:campaign + ga:userType + ga:deviceCategory + ga:countryIsoCode) | id |
View ID selected for the integration | view_id |
ga:date | created_on |
ga:channelGrouping | channel_grouping |
ga:source | source |
ga:medium | medium |
ga:campaign | campaign |
ga:userType | user_type |
ga:countryIsoCode | country_iso_code |
ga:pageviews | pageviews |
ga:sessions | sessions |
ga:bounces | bounces |
ga:transactions | transactions |
ga:sessionduration | session_duration |
ga:timeonpage | time_on_page |
ga:transactionRevenue | transaction_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 |
MD5(ga:date + ga:channelGrouping + ga::source + ga:medium + ga:campaign + ga:userType + ga:deviceCategory + ga:countryIsoCode) | __sync_key |
account_id | _account_id |
Daasity: timestamp when loaded into DB | __synced_at |
Daasity: from Channel Mapping Google Sheet | attribution_channel |
Base Shopping Stage
Endpoint: Reports
Update Method: UPSERT
Table Name:
ga.base_shopping_stage
| JSON Element | Database Column |
|---|---|
MD5(ga:date + ga:source + ga:medium + ga:campaign + ga:shoppingStage + ga:channelGrouping + ga:userType + ga:deviceCategory) | id |
View ID selected for the integration | view_id |
ga:date | created_on |
ga:source | source |
ga:medium | medium |
ga:campaign | campaign |
ga:channelGrouping | channel_grouping |
ga:deviceCategory | deivce_category |
ga:shoppingStage | shopping_stage |
ga:userType | user_type |
ga:pageviews | pageviews |
ga:sessions | sessions |
ga:bounces | bounces |
ga:transactions | transactions |
ga:sessionduration | session_duration |
ga:timeonpage | time_on_page |
ga:transactionRevenue | transaction_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 |
MD5(ga:date + ga:source + ga:medium + ga:campaign + ga:shoppingStage + ga:channelGrouping + ga:userType + ga:deviceCategory) | __sync_key |
account_id | _account_id |
Daasity: timestamp when loaded into DB | __synced_at |
Daasity: from Channel Mapping Google Sheet | attribution_channel |
Base PDP Performance
Endpoint: Reports
Update Method: UPSERT
Table Name:
ga.base_pdp_performance
| JSON Element | Database Column |
|---|---|
MD5(ga:date + ga:productName + ga:productSku) | id |
View ID selected for the integration | view_id |
ga:date | created_on |
ga:productName | product_name |
ga:productSku | product_sku |
ga:productDetailView | product_detail_views |
ga:productAddsToCart | product_adds_to_cart |
ga:productCheckouts | product_checkout |
ga:itemRevenue | product_sales |
MD5(ga:date + ga:productName + ga:productSku) | __sync_key |
account_id | _account_id |
Daasity: timestamp when loaded into DB | __synced_at |
Base Transactions
Endpoint: Reports
Update Method: UPSERT
Table Name:
ga.base_transactions
| JSON Element | Database Column |
|---|---|
MD5(view_id + ga:transactionid) | id |
ga:transactionid | transaction_id |
View ID selected for the integration | view_id |
ga:date | created_on |
ga:source | source |
ga:medium | medium |
ga:campaign | campaign |
ga:channelGrouping | channel_grouping |
ga:countryIsoCode | country_iso_code |
ga:userType | user_type |
ga:deviceCategory | device_category |
ga:referralPath | referral_path |
ga:fullReferrer | full_referrer |
ga:country | country |
ga:city | city |
ga:region | region |
ga:latitude | latitude |
ga:longitude | longitude |
ga:browser | browser |
ga:operatingSystem | operating_system |
ga:keyword | keyword |
ga:metro | metro |
ga:transactionRevenue | transaction_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 |
MD5(view_id + ga:transactionid) | __sync_key |
account_id | _account_id |
Daasity: timestamp when loaded into DB | __synced_at |
NOTE: There are three extractions being made for this report, due to dimension limits within an extraction. The primary table (ga.base_transactions) will contain the full set of columns while their stage tables (ga.base_transactions_stg2 and ga.base_transactions_stg3) will have a subset of metrics. Stage tables are stitched together to complete the primary table, updating rows with matching sync keys.
Base Transactions Stage 2
Endpoint: Reports
Update Method: UPSERT
Table Name:
ga.base_transactions_stg2
| JSON Element | Database Column |
|---|---|
MD5(view_id + ga:transactionid) | id |
ga:transactionId | transaction_id |
View ID selected for the integration | view_id |
ga:Date | created_on |
ga:countryIsoCode | country_iso_code |
ga:country | country |
ga:region | region |
ga:latitude | latitude |
ga:longitude | longitude |
ga:metro | metro |
ga:transactionRevenue | transaction_revenue |
MD5(view_id + ga:transactionid) | __sync_key |
account_id | _account_id |
Daasity: timestamp when loaded into DB | __synced_at |
NOTE: There are three extractions being made for this report, due to dimension limits within an extraction. The primary table (ga.base_transactions) will contain the full set of columns while their stage tables (ga.base_transactions_stg2 and ga.base_transactions_stg3) will have a subset of metrics. Stage tables are stitched together to complete the primary table, updating rows with matching sync keys.
Base Transactions Stage 3
Endpoint: Reports
Update Method: UPSERT
Table Name:
ga.base_transactions_stg3
| JSON Element | Database Column |
|---|---|
MD5(view_id + ga:transactionid) | id |
ga:transactionId | transaction_id |
View ID selected for the integration | view_id |
ga:Date | created_on |
ga:deviceCategory | device_category |
ga:referralPath | referral_path |
ga:fullReferrer | full_referrer |
ga:browser | browser |
ga:operatingSystem | operating_system |
ga:transactionRevenue | transaction_revenue |
MD5(view_id + ga:transactionid) | __sync_key |
account_id | _account_id |
Daasity: timestamp when loaded into DB | __synced_at |
NOTE: There are three extractions being made for this report, due to dimension limits within an extraction. The primary table (ga.base_transactions) will contain the full set of columns while their stage tables (ga.base_transactions_stg2 and ga.base_transactions_stg3) will have a subset of metrics. Stage tables are stitched together to complete the primary table, updating rows with matching sync keys.
Related Resources:
Although the GA Core Reporting data extract is API based the best way to sample data is via the Query Explore supplied by Google:
GA Query Explorer: https://ga-dev-tools.appspot.com/query-explorer/
GA Dimensions & Metrics Explorer: https://ga-dev-tools.appspot.com/dimensions-metrics-explorer/
Last updated