How to read GA4 Data API using ODBC JSON Driver

In this section, we will see how to read GA4 Data API using ODBC JSON Driver.

GA4 has no concept of views(profiles) introduced in Universal Analytics. So, there is no viewId parameter anywhere in the Data API v1 reporting requests. To get the data we need to specify a numeric Google Analytics 4 property id in a request URL path when calling the Data API v1 methods.

To get the data first we need to get the Property ID. Please refer to this link for the same: 

GA4 Reporting methods

The Reporting API v4 offered a single method batchGet to access its Core reporting functionality. The Data API v1 provides several Core reporting methods to choose from:

• runReport This method returns a customized report of your Google Analytics event data. It does not support pivot functionality and is a preferred method for simple report queries.
• runPivotReport This method returns a customized pivot report of you Google Analytics event data. Similar to pivots in the Reporting API v4, each pivot describes the visible dimension columns and rows in the report response.
• batchRunReports This is a batch version of the runReport method which allows generating multiple reports using a single API call.
• batchRunPivotReports This is a batch version of the runPivotReport method which allows generating multiple reports using a single API call.

Now let's configure the runReport method with pagination in the ZappySys ODBC JSON Driver

• Configure the below URL and body in the JSON driver as described in the image. 
• Select  POST HTTP Method
• Content Type as JSON (application/json)

URL:

https://analyticsdata.googleapis.com/v1beta/properties/GA4_PROPERTY_ID:runReport

Body:

{
    "metrics": [{ "name": "activeUsers" }, { "name": "newUsers" } ],
    "dateRanges": [{ "startDate": "2023-01-01", "endDate": "2023-03-01"],
    "dimensions": [{ "name": "date" },{ "name": "country" }],
    "limit": 10000,
    "offset": <%offset%>
}

Now let's configure the OAuth Authentication Connection, Select the Connection Type as OAuth and Click to configure link.

• Select the Google Analytics OAuth Provider
• Use your desired OAuth App you want to use
• Enter/Select the below Scopes (Note: you need to pass the required scopes based on your API requirements)

  https://www.googleapis.com/auth/analytics
  https://www.googleapis.com/auth/analytics.readonly

• Generate the Token and click on OK

Now go to the Pagination tab and configure the pagination as below. Refer to this link for more details on pagination configuration.

• Select Pagination POST data Mode - Page Number is passed inside the POST Data
• Pass <%offset%> as Page Num Indicator, same placeholder which we pass in the body.
• Set 10000 as Increment By, same as we which we pass in the body
• Set Max Rows Expression as $.rowCount
• Set Page Data Expression $.rows[*]

Select the Filter as $.rows[*] and click on Test Connection

That's it, click on OK button and Go To the Preview Tab To Preview the Data and you will get your desired data based on your filter.

Below is the Connection String you can directly load it in the JSON Driver to GA4 Data API call the configuration. You need to pass your desired GA4_PROPERTY_ID and need to generate the Token again in the OAuth Connection.

DRIVER={ZappySys JSON Driver};DataPath='https://analyticsdata.googleapis.com/v1beta/properties/GA4_PROPERTY_ID:runReport';DataConnectionType='OAuth';ScopeSeparator='{space}';Scope='https://www.googleapis.com/auth/analytics https://www.googleapis.com/auth/analytics.readonly';ServiceProvider='GoogleAnalytics';AccessToken='ya29.xxxx163';RefreshToken='1//051OGxxxxtL7Fo';Filter='$.rows[*]';ArrayTransColumnNameFilter='$.columnHeaders[*].name';RequestData='{
    "dateRanges": [{ "startDate": "2020-09-01", "endDate": "today" }],
    "dimensions": [{ "name": "date" },{ "name": "country" }],
    "metrics": [{ "name": "activeUsers" }, { "name": "newUsers" } ],
    "limit": 10000,
    "offset": <%offset%>
}';RequestContentTypeCode='ApplicationJson';RequestMethod='POST';PagingMode='ByPostData';PagingByUrlCurrentPage=0;NextUrlAttributeOrExpr='$.nextLink';PagingByUrlAttributeName='<%offset%>';IncrementBy=10000;PagingMaxRowsExpr='$.rowCount';PagingMaxRowsDataPathExpr='$.rows[*]';EnableArrayFlattening=1

Contact Us

If you have more question(s) feel free to contact us via Live chat or email at [e-mail]

https://zappysys.com/support/