Reputation: 413
I am trying to calculate number of sessions having one specific page path as at least one of the hits within the session ("my-path"). I need to assure that it does not match for example "my-path-2" but match "my-path/something", so I am using the following expression:
NTH(1,SPLIT(NTH(1,SPLIT(hits.page.pagePath,'/')),'?'))
The complete query however yields incorrect results and it is the following:
SELECT
date,
hostname,
pagePath,
EXACT_COUNT_DISTINCT(id) AS sumVisits
FROM (
SELECT
date,
hits.page.hostname AS hostname,
id,
pagePath
FROM (
SELECT
(NTH(1,SPLIT(NTH(1,SPLIT(hits.page.pagePath,'/')),'?'))) WITHIN RECORD AS pagePath,
date,
hits.page.hostname,
hits.type,
CONCAT(fullVisitorId, STRING(visitId)) AS id
FROM [my-table]
HAVING
pagePath ="my-path" ) )
GROUP BY
date,
pagePath,
hostname
What am I doing wrong? This query works using CONTAINS (selecting too much) but not using NTH and SPLIT functions.
Upvotes: 0
Views: 180
Reputation: 155
Did you try:
NTH(2,SPLIT(NTH(2,SPLIT(hits.page.pagePath,'/')),'?'))
Sometimes the saved paths start by "/" as "/my-path/p/a" instead of "my-path/p/a"; and in that case when you take the 1st element from "split" instead of the 2nd one; the query will return you an empty string.
Upvotes: 1