Product Analytics - Filtering sessions by date range throws exception
Problem to solve
While working on Filters for Dashboards - Daterange (#377817 - closed) I noticed that the Audience dashboard queries for “Average Sessions per User” and “Sessions per Browser” return exceptions when a date range is added. The other queries work as expected.
Code: 62. DB::Exception: Syntax error: failed at position 1632 ('WHERE') (line 20, col 128): WHERE (`main__sessions`.next_session_start_at >= parseDateTimeBestEffort('2023-01-01T00:00:00.000Z') AND `main__sessions`.next_session_start_at <= parseDateTime. Expected one of: OR, AND, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, IS NULL, IS NOT NULL, alias, AS, GROUP BY, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, SETTINGS, UNION, EXCEPT, INTERSECT. (SYNTAX_ERROR) (version 22.12.1.1752 (official build))
Steps to reproduce
Build the following query in Cube playground http://localhost:4000/:
Click to expand
{
"timeDimensions": [
{
"dimension": "Sessions.startAt",
"granularity": "day",
"dateRange": [
"2022-11-01",
"2022-12-31"
]
}
],
"order": {
"Sessions.startAt": "asc"
},
"dimensions": [
"Jitsu.parsedUaUaFamily",
"Jitsu.parsedUaUaVersion"
],
"measures": [
"Sessions.count"
]
}
or
Click to expand
{
"measures": [
"Sessions.count"
],
"order": {
"Sessions.count": "desc"
},
"filters": [
{
"member": "Sessions.startAt",
"operator": "inDateRange",
"values": [
"2022-10-01",
"2022-12-01"
]
}
],
"timeDimensions": [
{
"dimension": "Sessions.startAt"
}
],
"dimensions": [
"Jitsu.parsedUaUaFamily",
"Jitsu.parsedUaUaVersion"
]
}
Additional information
The two measurements that result in errors are Sessions.averagePerUser
and Sessions.count
(with dimension Jitsu.parsedUaUaVersion
).
Potentially related problem
While debugging this I also discovered that when Sessions.averagePerUser
is selected in cube it does not show inDateRange
as a filter option.
Implementation plan
Replace all queries that measure Sessions
and have TrackedEvents
dimensions with Sessions
alternatives:
-
Sessions per browser - use
Sessions.parsedUaUaFamily
andSessions.parsedUaUaVersion
dimensions. -
Sessions over time - use
Sessions.startAt
instead ofTrackedEvents.utcTime
.
Update the documentation to note that when measuring Sessions
or TrackedEvents
that only the matching dimensions and filters can be used, for example a Sessions
measurement cannot be combined with TrackedEvents
dimensions.