Google Analytics 4 (GA4) Reporting - Knowi Integration

Knowi lets you explore, visualize, transform, warehouse, and automate reporting on your Google Analytics 4 data, and merge it with data from your other sources.

Overview

There are two ways to connect, extract, and transform your Google Analytics 4 data:

  • The Knowi UI: connect directly from the browser.
  • Cloud9Agent: run the Cloud9Agent inside your network to pull the data securely. See Cloud9Agent below.

Once the data is in Knowi, you can visualize it and automate your reporting.

UI-Based Approach

Connecting

  1. Log in to Knowi and select Queries from the left sidebar.

  2. Click New Datasource + and select Google Analytics 4 from the list of datasources.

  3. Authorize Knowi with the Google account connected to your Google Analytics property.

  4. Enter the following details:

    a. Datasource name: A name that identifies this datasource.

    b. Google Analytics Profile ID: The Property ID of the Google Analytics property you want to query. To find it, see the Property ID documentation.

    c. Refresh Token: The token returned by Google when you authorized access. Knowi uses it to connect and pull your GA reports. You can revoke access at any time at https://www.google.com/settings/u/1/security.

  5. Click Save and start querying.

adding-ga4

Query

After you connect, Knowi loads the list of available metrics and dimensions, along with sample values. Use them to build queries in the visual builder without writing code, either by dragging and dropping fields or by choosing from the drop-down lists.

Metrics: The metrics you want to track. Choose from the drop-down or type them in. See the API Schema for the full list of metrics.

Dimensions: The fields used to group the selected metrics. For example, grouping by the "browser" dimension returns the metrics for each browser. For more information, see Dimension Filters.

Filter Dimensions: Conditions applied to the data before it is aggregated. Use these to narrow the dataset without changing how the results are grouped. Filter dimensions are passed to the GA4 API as dimensionFilters only, so they do not appear as columns in the results. For example, you can limit results to one country without adding country as a grouping column.

Start Date and End Date: The period to pull data for. Enter a date in the format yyyy-MM-dd, or a relative date such as today, yesterday, or NdaysAgo, where N is a positive integer. Use either a start and end date or a date range, not both.

Date Range: The period to pull data for, relative to now. Leave this empty if you set a start and end date. Enter a number followed by a unit: y for years, m for months, w for weeks, d for days, h for hours, or min for minutes. For example, 3m is the last 3 months. The following values are supported:

Date Range Op Comments
min Date range of n minutes back from now. Example (10 minutes): 10min
d Date range of n days back from today. Example (up to 120 days): 120d
w Date range of n weeks back from today. Example (up to 10 weeks): 10w
m Date range of n months back from today. Example (up to 3 months): 3m
y Date range of n years back from today. Example (up to 1 year): 1y
today Midnight of today until now
yesterday Midnight of yesterday until now
this hour The start of the current hour until now
this week Midnight of Monday of the current week until now
this month Midnight of the 1st of the current month until now
last hour The previous hour, from 0 minutes and 0 seconds until now
last week Midnight of Monday of the previous week until now
last month Midnight of the 1st of the previous month until now

Max Results: The maximum number of records to pull. Google Analytics allows up to 10,000 rows per data pull.

Sort By Field: The field to sort the returned data by. For example, sessions sorts ascending and -sessions sorts descending.

query-ga4

Note: You can also transform the results with Cloud9QL.

Data Execution Strategy

Choose how the query runs:

  • Direct Execution: Runs the query directly on the original datasource, with nothing stored in between. Each time a widget is displayed, it fetches the data in real time from the underlying datasource.

  • Non-Direct Execution: Stores the results in Knowi's Elastic Store. This is useful for long-running queries and reduces the load on your datasource. Non-direct queries can run once or on a schedule.

For more information, see Defining Data Execution Strategy.

data-strategy

Preview and Create the Dataset

Click Preview to check the results of your query and fine-tune the output if needed.

preview

The result of a query is called a Dataset. When you are happy with the results, name your dataset and click Create & Run.

dataset created

Cloud9Agent

Use Cloud9Agent as an alternative to the UI-based connection above. The agent runs inside your network, extracts the data from Google Analytics 4, and sends the extracted (and optionally transformed) data to your Knowi warehouse. To download the agent, see Cloud9Agent.

For sample configurations, see datasource_example_googleanalytics.json and query_example_googleanalytics.json in the examples folder of the Cloud9Agent install directory.

Highlights:

  • Connects to Google Analytics 4 using OAuth tokens. Obtain the refresh token with the connection steps in the UI section above.

  • Pulls data using the GA API, with optional transformations using Cloud9QL.

Datasource Configuration

Parameter Comments
name Unique datasource name.
datasource Set the value to ga4.
authRefreshToken The OAuth offline token generated by Google Analytics.
gaProfileID The Property ID of your Google Analytics 4 property. To find it, see the Property ID documentation.

Query Configuration

Query Config Params Comments
entityName Dataset name identifier.
identifier A unique identifier for the dataset. Either identifier or entityName must be specified.
dsName Required. The name of the datasource, as configured in the datasource_XXX.json file, to run the query against.
gaStartDate The first date to pull data for, in yyyy-MM-dd format. Specify either gaStartDate and gaEndDate, or gaDateRange.
gaEndDate The last date to pull data for, in yyyy-MM-dd format. Specify either gaStartDate and gaEndDate, or gaDateRange.
gaDateRange The date range to pull data for, such as 5d or this week. Takes precedence over gaStartDate and gaEndDate if specified. See the Date Range table above for the supported values.
gaMetrics Required. The metrics to track, separated by commas. Example: sessions,activeUsers. See the API Schema for the supported metrics.
dimensions Optional. The dimensions to group the data by, specified as an array of name/value/operator objects. Use ["*"] as the value to group by every value of the dimension. See the API Schema for the supported dimensions. If dimensionFilters is not specified, the values in dimensions are also applied as filters.
dimensionFilters Optional. Conditions applied to the data before it is aggregated. These are passed to the GA4 API as dimensionFilters only, so they do not appear as grouping columns in the results. Use them to filter the dataset by a dimension without changing how results are grouped. Uses the same array of name/value/operator objects as dimensions. Example: [{"name": "country", "value": ["United States"], "operator": "="}]
gaMaxResults Required. The maximum number of records to pull.
gaSort Optional. The field to sort by. A minus prefix sorts descending. Example: -sessions
c9QLFilter Optional. A Cloud9QL statement used to transform the results returned by the GA API.
frequencyType The scheduling frequency type. One of minutes, hours, days, weeks, or months. If not specified, the query is treated as a one-time query and runs when Cloud9Agent starts (or when the query is first saved).
frequency How often the query runs, in units of frequencyType. For example, a frequency of 10 with a frequencyType of minutes runs the query every 10 minutes.
startTime Optional. When the query should first run. The schedule is calculated from that time onwards. For example, a weekly query set to start at 07/01/2014 13:30 runs first on 07/01 at 13:30, and then at the same time on 07/08/2014. The time is based on the local time of the machine running the agent. Supported formats: MM/dd/yyyy HH:mm, MM/dd/yy HH:mm, MM/dd/yyyy, MM/dd/yy, HH:mm:ss, HH:mm, mm
overrideVals Sets the data storage strategy. If not defined, the query results are added to the existing dataset. To replace all data in the dataset in Knowi, specify {"replaceAll":true}. To upsert, specify "replaceValuesForKey":["fieldA","fieldB"], which replaces existing records that have the same fieldA and fieldB values with the new data, and inserts the records that are not already present.

Datasource Example:

[
  {
   "name":"demoGA",
   "datasource":"ga4",
   "authRefreshToken":"YOUR_REFRESH_TOKEN",
   "gaProfileID":"366465881"
  }
]

Query Example:


[
  {
    "entityName":"Website Visits",
    "dsName":"demoGA",
    "gaMetrics":"active7DayUsers",
    "dimensions" : [ {
        "name" : "date",
        "value" : [ "*" ],
        "operator" : "="
      } ],
    "dimensionFilters" : [ {
        "name" : "browser",
        "value" : [ "Chrome" ],
        "operator" : "="
      } ],
    "gaDateRange":"10d",
    "gaMaxResults":"1000",
    "gaSort":"active7DayUsers",
    "overrideVals":{
      "replaceAll":true
    },
    "frequencyType":"hours",
    "frequency":2
  }
]