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