Lag custom dimension in Bigquery export

Question

I have Google Analytics export set-up for Bigquery activated.

This is a query for 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 trying to also get a custom dimension for the previous page request but I am stuck.

Basically I want to retrieve a custom dimension (which is a nested value) with LAG.

This works but it also throws a lot of extra null rows:

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.

Thanks.

Answer 1

Does it work if you just move the "hits.customDimensions.index = 10" into WHERE clause?

Answer 2

For future reference & seekers, I managed to solve this:

Max is an analytic function and you cannot use analytical functions in LAG.

The only way I managed to get the custom dimension X for the previous request is by self joining the same table ON hitnumber:

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