Days Since Last Session - For Users With Session Level Custom Dimension - Bigquery - Google Analytics

I've got the following SQL query, which I got from the Lunametrics blog. Works fine. However, what I want to be able to do is have an additional column showing the value of a sessi

Solution 1:

MAX is an aggregation function - you have to group it somehow, either over the whole table with GROUP BY or within the row, e.g. with WITHIN RECORD or WITHIN hits

To get custom dimension values for each session you need to MAX( IF(hits.customdimensions.index = 30,hits.customdimensions.value,NULL) ) WITHIN RECORD

This line is first producing a list of values for each session (RECORD) and customDimension - the value if index is 30 and NULL else: MAX(NULL, NULL, NULL, ..., <value for hits.cd30>, ... , NULL, NULL, ... <another hits.cd30>, ... , NULL)

Where NULL is the lowest possible value. Strings are sorted alphabetically (actually by code table, but they contain characters sorted alphabetically) - since you're aggregating hit-level customDimensions on session level your list might contains multiple non-null values for sessions with more than one hit, because there might be multiple cd30s. MAX() returns the one that comes latest in the alphabet: "aab" < "aac" < "b"