Google Analytics Data Connector

The Google Analytics Data API gives you programmatic access to Google Analytics 4 (GA4) report data.

Pre-requisites

You need the privateKeyId, privateKey, authType, timeout, clientEmail, clientId parameters.

How to Connect

To connect the Google Analytics Data connector, you need to deploy the connector (if not already deployed) and configure the connection.

To check if the connector is already deployed, run this command:

SQL

SELECT "deployed" FROM "SYSADMIN.ModularConnectors" WHERE "name" = 'google-analytics-data';;

Deploy the connector using the SYSADMIN.deployModularConnector procedure:

SQL

CALL "SYSADMIN.deployModularConnector"(
    "name" => 'google-analytics-data',
    "deploy" => true
);;

Configure the connection using the SYSADMIN.createConnection() procedure.

The connection can be created in several ways:

via client email and private key:

SQL

CALL SYSADMIN.createConnection (    name => 'analytics_data_ds'    ,jbossCLITemplateName => 'ga4'    ,connectionOrResourceAdapterProperties => 'timeout=<timeout>,authType=<authType>,privateKeyId=<privateKeyId>,privateKey=<privateKey>,clientEmail=<clientEmail>,clientId=<clientId>');; 
CALL SYSADMIN.createDataSource (    name => 'analytics_data_ds'    ,translator => 'analytics_data'    ,modelProperties => ''    ,translatorProperties => '');;

via OAuth2 providing a refreshToken:

SQL

CALL SYSADMIN.createConnection("name" => 'analytics_data_ds', "jbossCLITemplateName" => 'ga4', "connectionOrResourceAdapterProperties" =>  'authType="authorized_user",clientSecret=<client secret>,refreshToken=<refresh token>,clientId=<client Id>,timeout=100') ;; 
CALL SYSADMIN.createDataSource("name" => 'analytics_data_ds', "translator" => 'ga4', "modelProperties" => '', "translatorProperties" => '', "encryptedModelProperties" => '', "encryptedTranslatorProperties" => '') ;;

via OAuth2 providing an authCode and redirectUri:

SQL

CALL SYSADMIN.createConnection("name" => 'analytics_data_ds', "jbossCLITemplateName" => 'ga4', "connectionOrResourceAdapterProperties" =>  'authType="authorized_user",clientSecret=<client secret>,clientId=<client Id>,authCode=<auth code>,redirectUri=<redirect URI>,timeout=100') ;; 
CALL SYSADMIN.createDataSource("name" => 'analytics_data_ds', "translator" => 'ga4', "modelProperties" => '', "translatorProperties" => '', "encryptedModelProperties" => '', "encryptedTranslatorProperties" => '') ;;

'No translator found with the specified name' Error

If the modular connector has not been deployed before running the SYSADMIN.createDataSource connection, you will see the following error message:

CODE

Message from SYSADMIN.createDataSource: No translator found with the specified name: ...

In this case, deploy the connector as described in Step 1 above.

Usage

Select the metrics and dimensions you need. Use the CreateProcedure_ReplicateData procedure to create your own data retrieval procedure.

Available Dimensions

API Name	UI Name
`achievementId`	Achievement ID
`adFormat`	Ad format
`adSourceName`	Ad source
`adUnitName`	Ad unit
`appVersion`	App version
`audienceId`	Audience ID
`audienceName`	Audience name
`brandingInterest`	Interests
`browser`	Browser
`campaignId`	Campaign ID
`campaignName`	Campaign
`"character"`	Character
`city`	City
`cityId`	City ID
`cohort`	Cohort
`cohortNthDay`	Daily cohort
`cohortNthMonth`	Monthly cohort
`cohortNthWeek`	Weekly cohort
`contentGroup`	Content group
`contentId`	Content ID
`contentType`	Content type
`continent`	Continent
`continentId`	Continent ID
`country`	Country
`countryId`	Country ID
`"date"`	Date
`dateHour`	Date + hour (YYYYMMDDHH)
`dateHourMinute`	Date hour and minute
`"day"`	Day
`"dayOfWeek"`	Day of week
`dayOfWeekName`	Day of week name
`defaultChannelGroup`	Default channel group
`deviceCategory`	Device category
`deviceModel`	Device model
`eventName`	Event name
`fileExtension`	File extension
`fileName`	File name
`firstSessionDate`	First session date
`firstUserCampaignId`	First user campaign ID
`firstUserCampaignName`	First user campaign
`firstUserDefaultChannelGroup`	First user default channel group
`firstUserGoogleAdsAccountName`	First user Google Ads account name
`firstUserGoogleAdsAdGroupId`	First user Google Ads ad group ID
`firstUserGoogleAdsAdGroupName`	First user Google Ads ad group name
`firstUserGoogleAdsAdNetworkType`	First user Google Ads ad network type
`firstUserGoogleAdsCampaignId`	First user Google Ads campaign ID
`firstUserGoogleAdsCampaignName`	First user Google Ads campaign
`firstUserGoogleAdsCampaignType`	First user Google Ads campaign type
`firstUserGoogleAdsCreativeId`	First user Google Ads creative ID
`firstUserGoogleAdsCustomerId`	First user Google Ads customer ID
`firstUserGoogleAdsKeyword`	First user Google Ads keyword text
`firstUserGoogleAdsQuery`	First user Google Ads query
`firstUserManualAdContent`	First user manual ad content
`firstUserManualTerm`	First user manual term
`firstUserMedium`	First user medium
`firstUserSource`	First user source
`firstUserSourceMedium`	First user source / medium
`firstUserSourcePlatform`	First user source platform
`fullPageUrl`	Full page URL
`googleAdsAccountName`	Google Ads account name
`googleAdsAdGroupId`	Google Ads ad group ID
`googleAdsAdGroupName`	Google Ads ad group name
`googleAdsAdNetworkType`	Google Ads ad network type
`googleAdsCampaignId`	Google Ads campaign ID
`googleAdsCampaignName`	Google Ads campaign
`googleAdsCampaignType`	Google Ads campaign type
`googleAdsCreativeId`	Google Ads creative ID
`googleAdsCustomerId`	Google Ads customer ID
`googleAdsKeyword`	Google Ads keyword text
`googleAdsQuery`	Google Ads query
`groupId`	Group ID
`hostName`	Hostname
`"hour"`	Hour
`isConversionEvent`	Is conversion event
`isoWeek`	ISO week of the year
`isoYear`	ISO year
`isoYearIsoWeek`	ISO week of ISO year
`itemAffiliation`	Item affiliation
`itemBrand`	Item brand
`itemCategory`	Item category
`itemCategory2`	Item category 2
`itemCategory3`	Item category 3
`itemCategory4`	Item category 4
`itemCategory5`	Item category 5
`itemId`	Item ID
`itemListId`	Item list ID
`itemListName`	Item list name
`itemListPosition`	Item list position
`itemLocationID`	Item location ID
`itemName`	Item name
`itemPromotionCreativeName`	Item promotion creative name
`itemPromotionCreativeSlot`	Item promotion creative slot
`itemPromotionId`	Item promotion ID
`itemPromotionName`	Item promotion name
`itemVariant`	Item variant
`landingPagePlusQueryString`	Landing page + query string
`"language"`	Language
`languageCode`	Language code
`level`	Level
`linkClasses`	Link classes
`linkDomain`	Link domain
`linkId`	Link ID
`linkText`	Link text
`linkUrl`	Link URL
`manualAdContent`	Manual ad content
`manualTerm`	Manual term
`medium`	Medium
`"method"`	Method
`"minute"`	Minute
`mobileDeviceBranding`	Device brand
`mobileDeviceMarketingName`	Device
`mobileDeviceModel`	Mobile model
`"month"`	Month
`newVsReturning`	New / returning
`nthDay`	Nth day
`nthHour`	Nth hour
`nthMinute`	Nth minute
`nthMonth`	Nth month
`nthWeek`	Nth week
`nthYear`	Nth year
`operatingSystem`	Operating system
`operatingSystemVersion`	OS version
`operatingSystemWithVersion`	Operating system with version
`orderCoupon`	Order coupon
`outbound`	Outbound
`pageLocation`	Page location
`pagePath`	Page path
`pagePathPlusQueryString`	Page path + query string
`pageReferrer`	Page referrer
`pageTitle`	Page title
`percentScrolled`	Percent scrolled
`platform`	Platform
`platformDeviceCategory`	Platform / device category
`region`	Region
`screenResolution`	Screen resolution
`searchTerm`	Search term
`sessionCampaignId`	Session campaign ID
`sessionCampaignName`	Session campaign
`sessionDefaultChannelGroup`	Session default channel group
`sessionGoogleAdsAccountName`	Session Google Ads account name
`sessionGoogleAdsAdGroupId`	Session Google Ads ad group ID
`sessionGoogleAdsAdGroupName`	Session Google Ads ad group name
`sessionGoogleAdsAdNetworkType`	Session Google Ads ad network type
`sessionGoogleAdsCampaignId`	Session Google Ads campaign ID
`sessionGoogleAdsCampaignName`	Session Google Ads campaign
`sessionGoogleAdsCampaignType`	Session Google Ads campaign type
`sessionGoogleAdsCreativeId`	Session Google Ads creative ID
`sessionGoogleAdsCustomerId`	Session Google Ads customer ID
`sessionGoogleAdsKeyword`	Session Google Ads keyword text
`sessionGoogleAdsQuery`	Session Google Ads query
`sessionManualAdContent`	Session manual ad content
`sessionManualTerm`	Session manual term
`sessionMedium`	Session medium
`sessionSa360AdGroupName`	Session SA360 ad group name
`sessionSa360CampaignId`	Session SA360 campaign ID
`sessionSa360CampaignName`	Session SA360 campaign
`sessionSa360CreativeFormat`	Session SA360 creative format
`sessionSa360EngineAccountId`	Session SA360 engine account ID
`sessionSa360EngineAccountName`	Session SA360 engine account name
`sessionSa360EngineAccountType`	Session SA360 engine account type
`sessionSa360Keyword`	Session SA360 keyword text
`sessionSa360Medium`	Session SA360 medium
`sessionSa360Query`	Session SA360 query
`sessionSa360Source`	Session SA360 source
`sessionSource`	Session source
`sessionSourceMedium`	Session source / medium
`sessionSourcePlatform`	Session source platform
`shippingTier`	Shipping tier
`signedInWithUserId`	Signed in with user ID
`source`	Source
`sourceMedium`	Source / medium
`sourcePlatform`	Source platform
`streamId`	Stream ID
`streamName`	Stream name
`testDataFilterId`	Test data filter ID
`testDataFilterName`	Test data filter name
`transactionId`	Transaction ID
`unifiedPagePathScreen`	Page path and screen class
`unifiedPageScreen`	Page path + query string and screen class
`unifiedScreenClass`	Page title and screen class
`unifiedScreenName`	Page title and screen name
`userAgeBracket`	Age
`userGender`	Gender
`videoProvider`	Video provider
`videoTitle`	Video title
`videoUrl`	Video URL
`virtualCurrencyName`	Virtual currency name
`visible`	Visible
`"week"`	Week
`"year"`	Year
`yearMonth`	Year month
`yearWeek`	Year week

Available Metrics

API Name	UI Name
`active1DayUsers`	1-day active users
`active28DayUsers`	28-day active users
`active7DayUsers`	7-day active users
`activeUsers`	Active users
`addToCarts`	Add to carts
`adUnitExposure`	Ad unit exposure
`advertiserAdClicks`	Ads clicks
`advertiserAdCost`	Ads cost
`advertiserAdCostPerClick`	Ads cost per click
`advertiserAdCostPerConversion`	Cost per conversion
`advertiserAdImpressions`	Ads impressions
`averagePurchaseRevenue`	Average purchase revenue
`averagePurchaseRevenuePerPayingUser`	ARPPU
`averagePurchaseRevenuePerUser`	Average purchase revenue per user
`averageRevenuePerUser`	ARPU
`averageSessionDuration`	Average session duration
`bounceRate`	Bounce rate
`cartToViewRate`	Cart-to-view rate
`checkouts`	Checkouts
`cohortActiveUsers`	Cohort active users
`cohortTotalUsers`	Cohort total users
`conversions`	Conversions
`crashAffectedUsers`	Crash-affected users
`crashFreeUsersRate`	Crash-free users rate
`dauPerMau`	DAU / MAU
`dauPerWau`	DAU / WAU
`ecommercePurchases`	Ecommerce purchases
`engagedSessions`	Engaged sessions
`engagementRate`	Engagement rate
`eventCount`	Event count
`eventCountPerUser`	Event count per user
`eventsPerSession`	Events per session
`eventValue`	Event value
`firstTimePurchaserConversionRate`	First-time purchaser conversion
`firstTimePurchasers`	First time purchasers
`firstTimePurchasersPerNewUser`	First-time purchasers per new user
`itemListClickEvents`	Item-list click events
`itemListClickThroughRate`	Item list click through rate
`itemListViewEvents`	Item-list view events
`itemViewEvents`	Item view events
`itemsAddedToCart`	Items added to cart
`itemPromotionClickThroughRate`	Item promotion click through rate
`itemRevenue`	Item revenue
`itemsCheckedOut`	Items checked out
`itemsClickedInList`	Items clicked in list
`itemsClickedInPromotion`	Items clicked in promotion
`itemsPurchased`	Items purchased
`itemsViewed`	Items viewed
`itemsViewedInList`	Items viewed in list
`itemsViewedInPromotion`	Items viewed in promotion
`newUsers`	New users
`organicGoogleSearchAveragePosition`	Organic google search average position
`organicGoogleSearchClicks`	Organic google search clicks
`organicGoogleSearchClickThroughRate`	Organic google search click through rate
`organicGoogleSearchImpressions`	Organic google search impressions
`promotionClicks`	Promotion clicks
`promotionViews`	Promotion views
`publisherAdClicks`	Publisher ad clicks
`publisherAdImpressions`	Publisher ad impressions
`purchaserConversionRate`	Purchaser conversion
`purchaseToViewRate`	Purchase-to-view rate
`returnOnAdSpend`	Return on ad spend
`screenPageViews`	Views
`screenPageViewsPerSession`	Views per session
`screenPageViewsPerUser`	Views per user
`scrolledUsers`	Scrolled users
`sessionConversionRate`	Session conversion rate
`sessions`	Sessions
`sessionsPerUser`	Sessions per user
`shippingAmount`	Shipping amount
`taxAmount`	Tax amount
`totalAdRevenue`	Total ad revenue
`totalPurchasers`	Total purchasers
`totalRevenue`	Total revenue
`totalUsers`	Total users
`transactions`	Transactions
`transactionsPerPurchaser`	Transactions per purchaser
`userConversionRate`	User conversion rate
`userEngagementDuration`	User engagement
`wauPerMau`	WAU / MAU

Examples

1. Creating an ActiveUsersInCountry procedure that returns a dataset with three fields: country, userGender, activeUsers:

SQL

CALL analytics_data_ds.CreateProcedure_ReplicateData(    dimensions => 'country,userGender',    metrics => 'activeUsers',    procName => 'ActiveUsersInCountry');;

The procedure makes use of the following parameters:

dimensions: comma-separated list of dimensions
metrics: comma-separated list of metrics
procName: name of the procedure to be created

2. Calling the ActiveUsersInCountry procedure to get data:

CODE

CALL analytics_data_ds.ActiveUsersInCountry(    propertyId => <propertyId>,    startDate => '2023-06-01',    endDate => '2023-06-21',    preview => true);;

For instructions on how to get the property identifier, please refer to the Google Analytics Data API documentation.

A useful tool is Google Query Explorer. You can construct a query there and see if it will return any data or if the result should be empty for the given combination of dimensions/metrics.