Integration: Google Analytics V2

This article will help you learn about how Daasity replicates data from Google Analytics, limitations to the data we can extract and where the data is stored in the Google Analytics schema

Key Topics

Click on the links below to take you to the section where you can learn more about this Integration

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
  • Pro V2

NOTE: You are considered V2 if...

  • You are a Growth customer
  • Your account says Pro V2 in the Daasity App

Not sure? Reach out to Support@Daasity.com

API Endpoints

The Daasity V2 Base 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)

The embedded diagram houses the ERD for the Daasity V2 Base Google Analytics 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 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
JSON Element  Database Column
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: