Google Analytics Extractor Info (V2)

INTEGRATION OVERVIEW

Google Analytics is Google's free web analytics services 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

*You are considered V2 if..

  • You have your own Looker instance and signed up for Daasity AFTER July 15, 2020
  • You're utilizing our Growth Platform

Not sure? Reach out to Support@Daasity.com 

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.

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

This V2 integration is available for:

  • Growth
  • Pro V2

 

ERD

 

ENDPOINT: Core Reporting API

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

Base 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: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

Base 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

Base PDP Performance 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 TIMESTAMPT
ga:productName product_name VARCHAR
ga:productDetailView product_detail_views REAL
ga:productAddsToCart product_adds_to_cart REAL
ga:productCheckouts product_checkout REAL
ga:itemRevenue product_sales REAL

Business Rules

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

Base Traffic 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:

Was this article helpful?
0 out of 0 found this helpful