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

NEW documentation: This article covers setup for OWOX BI. For the latest guides on setting up FREE connectors and Data Marts, visit the new OWOX Data Marts documentation.

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