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:

• Reports

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
• Base Shopping Stage
• Base PDP Performance
• Base Transactions
• Base Transactions Stage 2
• Base Transactions Stage 3

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/