Custom dimension in Bigquery export
I have a Google Analytics export setup for Bigquery activation.
This is the request for the previous page path, page:
SELECT
LAG(hits.page.pagePath, 1) OVER (PARTITION BY fullVisitorId, visitId ORDER BY hits.hitNumber ASC) AS Previous,
hits.page.pagePath AS Page
FROM
[xxxxxxxx.table]
WHERE
hits.type="PAGE"
LIMIT
100
I am also trying to get a custom size for a previous page request, but I am stuck.
Basically I want to get a custom size (which is a nested value) from a LAG.
This works, but it also generates a lot of extra lines:
LAG ( IF (hits.customDimensions.index = 10, hits.customDimensions.value, NULL)) ,1) OVER (PARTITION BY fullVisitorId, visitId ORDER BY hits.hitNumber ASC) AS Previous_PT
If I use max ( https://support.google.com/analytics/answer/4419694?hl=en#query7_MultipleCDs ) it throws an error.
Any help would be much appreciated.
Thank.
+3
source to share
2 answers
For future reference and seekers, I managed to solve this:
Max is an analytic function and you cannot use analytic functions in LAG.
The only way I have been able to get the custom X size for the previous query is by joining the same ON hitnumber table:
SELECT
hits.page.pagePath AS Page,
fullVisitorId,
visitId,
LAG(hits.hitNumber, 1) OVER (PARTITION BY fullVisitorId, visitId ORDER BY hits.hitNumber ASC) AS Previous_Hit,
LAG(hits.page.pagePath, 1) OVER (PARTITION BY fullVisitorId, visitId ORDER BY hits.hitNumber ASC) AS Previous,
MAX(IF (hits.customDimensions.index = 6, hits.customDimensions.value, NULL)) WITHIN RECORD AS BLABLA1,
MAX(IF (hits.customDimensions.index = 8, hits.customDimensions.value, NULL)) WITHIN RECORD AS BLABLA2,
MAX(IF (hits.customDimensions.index = 10, hits.customDimensions.value, NULL)) WITHIN RECORD AS BLABLA3,
hits.hitNumber AS hitNumber
FROM
FLATTEN([xxxxxxxxx], hits)
WHERE
hits.type="PAGE" ) AS T1
LEFT JOIN
FLATTEN(xxxxxxxxxx], hits) AS T2
ON
T2.hits.hitNumber = T1.Previous_Hit
AND T1.fullVisitorId = T2.fullVisitorId
AND T1.visitId = T2.visitId
0
source to share