Book the exclusive demo launch to experience the OWOX BI analytics

How to get a session-level scope value for a custom dimension?

OWOX BI collects custom dimensions on the hit-level scope.

The query example below lets you get a session-level scope value for a custom dimension.

Parameters to set in the request

  • project-name — the name of the Google BigQuery project containing user session data uploaded via OWOX BI;
  • dataset-name — the name of the Google BigQuery dataset containing the session data;
  • _TABLE_SUFFIX — the date range you want to view data for;
  • index — custom dimension index. In the example below we used index 8.

SQL query

SELECT sessionIdFromCd FROM (SELECT
  MAX((
    SELECT
      value
    FROM
      UNNEST (hits.customDimensions)
    WHERE
      index = 8
      AND value IS NOT NULL)) OVER(PARTITION BY sessionId) sessionIdfromCd
FROM
  `project-name.dataset-name.owoxbi_sessions_*`,
  UNNEST (hits) hits
 WHERE _TABLE_SUFFIX BETWEEN '20191213' AND '20191214'
)
  GROUP BY 1

 

Row sessionIdFromCd
1 1088888882.188888881_1584892173257
2 1077777772.177777772_1584897362067
3 1066666663.166666667_1584881382775
4 1055555558.155555550_1584829420499
5 1044444448.144444441_1584849461498
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.