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

      Google Analytics Extractor Documentation (V1)

      KEY TOPICS

      INTEGRATION AVAILABILITY

      This V1 integration is available for:

      • Pro V1

      *You are considered V1 if you have your own Looker instance and signed up for Daasity BEFORE July 15, 2020. If unsure, please reach out to Support@Daasity.com for assistance.

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

      The API has limitations on the level of granularity that can be returned as follows:

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

      ERD

      ENDPOINT: Core Reporting API

      Documentation on the Google Analytics APIs can be found here: https://developers.google.com/analytics/devguides/reporting/core/v3/ ​

      Traffic TABLE

      Data Mapping.

      JSON Element  Database Column Datatype
      MD5 Hash of all the Dimensions id VARCHAR
      View ID selected for the integration view_id VARCHAR
      ga:Date created_on TIMESTAMP
      ga:source source VARCHAR
      ga:medium medium VARCHAR
      ga:campaign campaign VARCHAR
      ga:channelGrouping channel_grouping VARCHAR
      ga:userType user_type VARCHAR
      ga:deviceCategory device_category VARCHAR
      ga:keyword keyword VARCHAR
      ga:pageviews pageviews REAL
      ga:sessions sessions REAL
      ga:bounces bounces REAL
      ga:transactions transactions REAL
      ga:sessionDuration session_duration REAL
      ga:timeOnPage time_on_page REAL
      ga:transactionRevenue transaction_revenue REAL
      Daasity: from Channel Mapping Google Sheet channel VARCHAR
      Daasity: from Channel Mapping Google Sheet vendor VARCHAR
      Daasity: from Channel Mapping Google Sheet sub_channel VARCHAR
      Daasity: from Channel Mapping Google Sheet media_type VARCHAR
      Daasity: from Channel Mapping Google Sheet attribution_channel VARCHAR


      Business Rules.

       
      Business Rule Value
      Load Type Upsert
      Extraction Frequency Daily
      Sync Key All dimensions

      Traffic Country TABLE

      Data Mapping.

      JSON Element  Database Column Datatype
      MD5 Hash of all the Dimensions id VARCHAR
      View ID selected for the integration view_id VARCHAR
      ga:Date created_on TIMESTAMP
      ga:source source VARCHAR
      ga:medium medium VARCHAR
      ga:campaign campaign VARCHAR
      ga:channelGrouping channel_grouping VARCHAR
      ga:userType user_type VARCHAR
      ga:deviceCategory device_category VARCHAR
      ga:countryIsoCode country_iso_code VARCHAR
      Daasity: lookup IsoCode from Google country_name VARCHAR
      ga:pageviews pageviews REAL
      ga:sessions sessions REAL
      ga:bounces bounces REAL
      ga:transactions transactions REAL
      ga:sessionDuration session_duration REAL
      ga:timeOnPage time_on_page REAL
      ga:transactionRevenue transaction_revenue REAL
      Daasity: from Channel Mapping Google Sheet channel VARCHAR
      Daasity: from Channel Mapping Google Sheet vendor VARCHAR
      Daasity: from Channel Mapping Google Sheet sub_channel VARCHAR
      Daasity: from Channel Mapping Google Sheet media_type VARCHAR
      Daasity: from Channel Mapping Google Sheet attribution_channel VARCHAR


      Business Rules.

      Business Rule Value
      Load Type Upsert
      Extraction Frequency Daily
      Sync Key All dimensions

      Shopping Stage TABLE

      Data Mapping.

      JSON Element  Database Column  Datatype
      MD5 Hash of all the Dimensions id VARCHAR
      View ID selected for the integration view_id VARCHAR
      ga:Date created_on TIMESTAMP
      ga:source source VARCHAR
      ga:medium medium VARCHAR
      ga:campaign campaign VARCHAR
      ga:shoppingStage shopping_stage VARCHAR
      ga:channelGrouping channel_grouping VARCHAR
      ga:userType user_type VARCHAR
      ga:deviceCategory device_category VARCHAR
      ga:pageviews pageviews REAL
      ga:sessions sessions REAL
      ga:bounces bounces REAL
      ga:transactions transactions REAL
      ga:sessionDuration session_duration REAL
      ga:timeOnPage time_on_page REAL
      ga:transactionRevenue transaction_revenue REAL
      Daasity: from Channel Mapping Google Sheet channel VARCHAR
      Daasity: from Channel Mapping Google Sheet vendor VARCHAR
      Daasity: from Channel Mapping Google Sheet sub_channel VARCHAR
      Daasity: from Channel Mapping Google Sheet media_type VARCHAR
      Daasity: from Channel Mapping Google Sheet attribution_channel VARCHAR


      Business Rules.

      Business Rule Value
      Load Type Upsert
      Extraction Frequency Daily
      Sync Key All dimensions

      Transactions TABLE

      Data Mapping.

      JSON Element  Database Column  Datatype
      MD5 Hash of all the Dimensions id VARCHAR
      ga:transactionId transaction_id VARCHAR
      View ID selected for the integration view_id VARCHAR
      ga:Date created_on ​
      ga:source source VARCHAR
      ga:medium medium VARCHAR
      ga:campaign campaign VARCHAR
      ga:channelGrouping channel_grouping VARCHAR
      ga:userType user_type VARCHAR
      ga:countryIsoCode country_iso_code VARCHAR
      ga:deviceCategory device_category VARCHAR
      ga:referralPath referral_path VARCHAR
      ga:fullReferrer full_referrer VARCHAR
      ga:country country VARCHAR
      ga:city city VARCHAR
      ga:region region VARCHAR
      ga:latitude latitude FLOAT
      ga:longitude longitude FLOAT
      ga:browser browser VARCHAR
      ga:operatingSystem operating_system VARCHAR
      ga:keyword keyword VARCHAR
      ga:metro metro VARCHAR
      ga:transactionRevenue transaction_revenue REAL
      Daasity: from Channel Mapping Google Sheet channel VARCHAR
      Daasity: from Channel Mapping Google Sheet vendor VARCHAR
      Daasity: from Channel Mapping Google Sheet sub_channel VARCHAR
      Daasity: from Channel Mapping Google Sheet media_type VARCHAR


      Business Rules.

      Business Rule Value
      Load Type Upsert
      Extraction Frequency Daily
      Sync Key All dimensions

       EXTRACTION EXAMPLE

      Although the GA Core Reporting data extract is API based the best way to sample data is via the Query Explore supplied by Google: