Integrations
      Back to home
      1. Help Center
      2. ELT+ | Technical User Documentation
      3. Integrations

      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: