The CData ODBC 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 querying of dimensions and metrics in various 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 10 metrics per query, unless otherwise stated in the specific table or view pages.

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

Views

Views are tables that cannot be modified, such as Accounts, Properties, Events, Acquisitions, and Engagement. Typically, data that is read-only and cannot be updated are shown as views. Two types of views are made available:

• Base Views are statically defined to model Engagements, Acquisitions, Active Users, and more. You can use base views to create your custom reports. By default, these views return data from all time aggregated into a single row. All base views are subsets of GlobalAccessObject, a view that contains all data for all available dimensions and metrics.
• Predefined Report Views are a set of standard reports that mimic exactly what you see in the Google Analytics UI. All Predefined Report Views have "report" appended to their name. By default, these views return data from all time with a daily breakdown.

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 day 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 selecting "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.