Query GA custom dimensions

Question & Answer
,
1

How to query GA Custom dimensions

(SELECT value from hits.customDimensions where index=1) AS CD1,
(SELECT value from hits.customDimensions where index=2) AS CD2,

does not work

2

This is a SQL question. But to answer you here is the solution.

SELECT
CASE WHEN index=1 THEN value END AS CD1,
CASE WHEN index=2 THEN value END AS CD2
FROM hits.customDimensions

3

Hi @Max ! Thanks a lot for your reply.

I have tried to integrate it in my query, but it did not work out.

SELECT
date,
device.deviceCategory,
COUNT(DISTINCT fullvisitorid) as users,
MAX(CD1) as CustomDimension1,
MAX(CD2) as CustomDimension2
FROM
XXX,
UNNEST(hits) as t(hit),
unnest(hit.customdimensions) as t(cd),
(SELECT
CASE WHEN index=1 THEN value END AS CD1,
CASE WHEN index=2 THEN value END AS CD2
FROM hits.customDimensions) AS customdims
WHERE
1=1
AND year >= 2023
AND viewid in (‘130705852’)
GROUP BY 1,2

The error is:
SCHEMA_NOT_FOUND: line 14:8: Schema ‘hits’ does not exist

4

It looks like you don’t have a schema named hits. You need to find the correct schema.

5

Hi @Liudmila

Did you figure out this issue?

6
7