Adapter Online Help > TDV Google Analytics Adapter > Data Model > Tables
 
Tables
The adapter models the data in Google Analytics into a list of tables that can be queried using standard SQL statements.
Generally, querying Google Analytics tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
Google Analytics Adapter Tables
Name
Description
Accounts
Lists all Accounts to which the user has access.
Goals
A Goals resource describes a goal for one of a user's profiles.
Profiles
Lists all Profiles to which the user has access.
Segments
Lists all Segments to which the user has access.
UserActivity
List information on a user's activity.
WebProperties
Lists Web Properties to which the user has access.
Accounts
Lists all Accounts to which the user has access.
Table Specific Information
Select
The Accounts table exposes every account the user has access to. Filters are not available in the WHERE clause for this table but LIMIT is supported.
Columns
Name
Type
ReadOnly
Description
Id [KEY]
String
True
Account Id.
Kind
String
True
Resource type for Analytics account.
SelfLink
String
True
Link for this account.
Name
String
True
Account name.
Permissions
String
True
All the permissions that the user has for this account. These include any implied permissions.
Created
Datetime
True
Time the account was created.
Updated
Datetime
True
Time the account was last modified.
ChildLink
String
True
Child link for an account entry. Points to the list of Web properties for this account.
Goals
A Goals resource describes a goal for one of a user's profiles.
Columns
Name
Type
ReadOnly
Description
Id [KEY]
String
True
Goal Id.
WebPropertyId [KEY]
String
True
Web property ID to which this goal belongs. The web property ID is of the form UA-XXXXX-YY.
Kind
String
True
Resource type for Analytics profile.
SelfLink
String
True
Link for this goal.
AccountId
String
True
Account ID to which this goal belongs.
InternalWebPropertyId
String
True
Internal ID for the web property to which this goal belongs.
ProfileId
String
True
View (Profile) ID to which this goal belongs.
Name
String
True
Goal name.
Active
Boolean
True
Determines whether this goal is active.
Value
Double
True
Goal value.
Type
String
True
Goal type. Possible values are URL_DESTINATION, VISIT_TIME_ON_SITE, VISIT_NUM_PAGES, and EVENT.
Created
Datetime
True
Time this goal was created.
Updated
Datetime
True
Time this goal was last modified.
ParentLink
String
True
Parent link for a goal. Points to the view (profile) to which this goal belongs.
UrlDestinationDetails
String
True
Details for the goal of the type URL_DESTINATION.
VisitTimeOnSiteDetails
String
True
Details for the goal of the type VISIT_TIME_ON_SITE.
VisitNumPagesDetails
String
True
Value used for this comparison.
EventDetails
String
True
Details for the goal of the type EVENT.
Profiles
Lists all Profiles to which the user has access.
Table Specific Information
Select
The Profiles table exposes all profiles that the user has access to. The AccountId and WebPropertyId fields are available for filtering in the WHERE clause with = operator. For example:
SELECT * FROM Profiles WHERE AccountId='123456'
Columns
Name
Type
ReadOnly
Description
Id [KEY]
String
True
Profile Id.
Kind
String
True
Resource type for Analytics profile.
SelfLink
String
True
Link for this profile.
AccountId
String
True
Account Id to which this profile belongs.
WebPropertyId
String
True
Web property Id of the form UA-XXXXX-YY to which this profile belongs.
InternalWebPropertyId
String
True
Internal Id for the Web property to which this profile belongs.
Name
String
True
Name of this profile.
Currency
String
True
The currency type associated with this profile.
Timezone
String
True
Time zone for which this profile has been configured.
WebsiteURL
String
True
Website URL for this profile.
DefaultPage
String
True
Default page for this profile.
ExcludeQueryParameters
String
True
The query parameters that are excluded from this profile.
SiteSearchQueryParameters
String
True
The site search query parameters for this profile.
SiteSearchCategoryParameters
String
True
Site search category parameters for this profile.
Type
String
True
Profile type.
Permissions
String
True
All the permissions that the user has for this profile. These include any implied permissions.
Created
Datetime
True
Time this profile was created.
Updated
Datetime
True
Time this profile was last modified.
eCommerceTracking
Boolean
True
Indicates whether e-commerce tracking is enabled for this profile.
ParentLink
String
True
Parent link for this profile. Points to the Web property to which this profile belongs.
ChildLink
String
True
Child link for this profile. Points to the list of goals for this profile.
Segments
Lists all Segments to which the user has access.
Table Specific Information
Select
The Segments table exposes all segments that the user has access to. Filters are not available in the WHERE clause for this table but LIMIT is supported.
Columns
Name
Type
ReadOnly
Description
Id [KEY]
String
True
Segment Id.
Kind
String
True
Resource type for Analytics segment.
SelfLink
String
True
Link for this segment.
SegmentID
String
True
Segment Id. Can be used with the segment parameter in Data Feed.
Name
String
True
Segment name.
Definition
String
True
Segment definition.
Type
String
True
Type for a segment. Possible values are BUILT_IN or CUSTOM.
Created
Datetime
True
Time the segment was created.
Updated
Datetime
True
Time the segment was last modified.
UserActivity
List information on a user's activity.
Table Specific Information
Select
This view requires column 'ClientID' to be specified in order to execute a query. Unfortunately, we don't expose a 'Clients' or 'Users' table (or equivalent) to display client IDs. This is an API limitation.
Because of this, the only way to retrieve a ClientID is by following the below steps:
1. Login to Google Analytics in your preferred browser.
2. Expand the 'Audience' report on the left side of the screen.
3. Choose 'User Explorer' option.
Client IDs will be displayed under column 'Client Id'. Use one of those values when building the query in the adapter, e.g.:
SELECT * FROM UserActivity WHERE ClientId = '194067010.1581918826'
Columns
Name
Type
ReadOnly
Description
ClientId
String
True
The Client ID or User ID associated with the user. This can be found in the 'User Explorer' section of Google Analytics' UI.
ActivityTime
String
True
Timestamp of the activity.
Source
String
True
The source of referrals. For manual campaign tracking, it is the value of the utm_source campaign tracking parameter. For AdWords autotagging, it is google. If you use neither, it is the domain of the source (e.g., document.referrer) referring the users. It may also contain a port address. If users arrived without a referrer, its value is (direct).
Medium
String
True
The type of referrals. For manual campaign tracking, it is the value of the utm_medium campaign tracking parameter. For AdWords autotagging, it is cpc. If users came from a search engine detected by Google Analytics, it is organic. If the referrer is not a search engine, it is referral. If users came directly to the property and document.referrer is empty, its value is (none).
ChannelGrouping
String
True
The Channel Group associated with an end user's session for this View (defined by the View's Channel Groupings).
Campaign
String
True
For manual campaign tracking, it is the value of the utm_campaign campaign tracking parameter. For AdWords autotagging, it is the name(s) of the online ad campaign(s) you use for the property. If you use neither, its value is (not set).
Keyword
String
True
For manual campaign tracking, it is the value of the utm_term campaign tracking parameter. For AdWords traffic, it contains the best matching targeting criteria. For the display network, where multiple targeting criteria could have caused the ad to show up, it returns the best matching targeting criteria as selected by Ads. This could be display_keyword, site placement, boomuserlist, user_interest, age, or gender. Otherwise its value is (not set).
Hostname
String
True
The hostname from which the tracking request was made.
LandingPagePath
String
True
The first page in users' sessions, or the landing page.
ActivityType
String
True
Type of this activity.
Event
String
True
This field contains all the details pertaining to an event and will be set if activityType equals EVENT.
PageView
String
True
This will be set if activityType equals PAGEVIEW. This field contains all the details about the visitor and the page that was visited.
AppView
String
True
This will be set if activityType equals SCREEN_VIEW.
Ecommerce
String
True
This will be set if activityType equals ECOMMERCE.
Goals
String
True
This will be set if activityType equals GOAL.
StartDate
String
True
Start date for fetching Analytics data. Either a date string or a relative date (e.g., today, yesterday, or #daysAgo).
EndDate
String
True
End date for fetching Analytics data. Either a date string or a relative date (e.g., today, yesterday, or #daysAgo).
SamplesReadCounts
String
True
Comma separated list of samples read counts when the response data is sampled.
SamplingSpaceSizes
String
True
Comma separated list of samples space sizes counts when the response data is sampled.
WebProperties
Lists Web Properties to which the user has access.
Table Specific Information
Select
The Web Properties table exposes all web properties that the user has access to. The AccountId field is available for filtering in the WHERE clause with = operator. For example:
SELECT * FROM WebProperties WHERE AccountId='123456'
Columns
Name
Type
ReadOnly
Description
Id [KEY]
String
True
Web property Id.
Kind
String
True
Resource type for Analytics web property.
SelfLink
String
True
Link for this web property.
AccountId
String
True
Account Id to which this web property belongs.
InternalWebPropertyId
String
True
Internal Id for the web property.
DefaultProfileId
String
True
Default view (profile) Id.
IndustryVertical
String
True
 
Level
String
True
Level for this web property.
Name
String
True
Name of this web property.
ProfileCount
Integer
True
View (Profile) count for this web property.
WebsiteURL
String
True
Website URL for this web property.
Permissions
String
True
All the permissions that the user has for this web property. These include any implied permissions.
Created
Datetime
True
Time this web property was created.
Updated
Datetime
True
Time this web property was last modified.
ParentLink
String
True
Parent link for this profile. Points to the Account to which this profile belongs.
ChildLink
String
True
Child link for this web property. Points to the list of views (profiles) for this web property.