JDBC Driver for Google Analytics

Build 22.0.8509

GoogleAnalytics4 Data Model

The CData JDBC Driver for Google Analytics models the Google Analytics 4 API as relational tables, views, and stored procedures. The provided tables give you an overview of your account information and the profiles available for Google Analytics queries. Google Analytics supports the queryting of Dimensions and Metrics in a large number of arrangements. Some sample views are provided based on common Google Analytics reports.

You can also create your own custom views based on any combination of Dimensions and Metrics you need. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations. However, there is a strict limit of nine dimensions and ten metrics per query, unless otherwise stated in the specfic table or view pages.

To use Google Analytics 4 Data Model, set Schema to GoogleAnalytics4.

Tables

Tables describes the available tables, such as Accounts and Properties.

Views

Views are tables that cannot be modified, such as Events, Acquisitions, and Engagement. Typically, data that are read-only and cannot be updated are shown as views.

Date Ranges and Aggregation

Date Ranges You can specify date ranges in the WHERE clause using the "Date" field with these operators: =, <,>

Additional predefined date fields are available:

  • week
  • month
  • quarter
When filtering on these time intervals, you can use the = operator, specifying the date of first day of the time period. If you specify a different date, an error is returned. For example, to specify the month of May in the year 2022, use the following, specifying the first day of that month:
month = '2022-05-01' 
NOTE: The default time interval for views is 30 days.

Aggregation

Aggregation is the process of reducing and summarizing data. You can apply aggregation at multiple levels:

The following examples show the syntax of aggregation queries:


SELECT Date,NewUsers, TotalUsers From Tech where StartDate = '2022-01-01' and EndDate = '2023-05-18'

SELECT Hour,NewUsers, TotalUsers From Tech where StartDate = '2022-01-01' and EndDate = '2023-05-18'

SELECT Day,NewUsers, TotalUsers From Tech where StartDate = '2022-01-01' and EndDate = '2023-05-18'

SELECT Week,NewUsers, TotalUsers From Tech where StartDate = '2022-01-01' and EndDate = '2023-05-18'

SELECT Month,NewUsers, TotalUsers From Tech where StartDate = '2022-01-01' and EndDate = '2023-05-18'

SELECT Year,NewUsers, TotalUsers From Tech where StartDate = '2022-01-01' and EndDate = '2023-05-18' 
Some comments about the aggregation code examples:

  • Date: returns daily data in the query results.
  • Hour: returns data aggregated by hour across the specified date range. For example, querying two years worth of data and selecting "Hour" returns 24 rows of data (one for each hour) with two years data aggregated for each hour.
  • Day: returns data aggregated by da across the specified date range. For example, querying two years worth of data and selecting "Day" returns seven rows of data (one for each day) with two years data aggregated for each day
  • Week: returns data aggregated by week across the specified date range. For example, querying two years worth of data and selecting "Week" returns 104 rows of data (one for each week) with data aggregated for each week
  • Month: returns data aggregated by month across the specified date range. For example, querying two years worth of data and selecting "Month" returns 24 rows of data (one for each month) with data aggregated for each month.
  • Year: returns data aggregated by year across the specified date range. For example, querying two years worth of data and selecing "Year" returns two rows of data (one for each year) with data aggregated by year.

NOTE: choosing "Date" as a metric overrides any other date metric you select. Only daily data is returned.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. You can use these to search, update, and modify information in the data source.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8509