CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Silvia Pina
Silvia Pina

Reputation: 413

Calculating number of sessions with a certain pagePath

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

Answers (1)

Cihan Fethi Hızar
Cihan Fethi Hızar

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

Related Questions