0

I'm trying to export a Google Anlaytics custom dimension on hit level and some events from BigQuery. So far, the custom dimension export isn't working.

I'm using LegacySQL on that. Cause this is part of a bigger Query, I don't want to switch to StandardSQL at this point.

SELECT 
clientId,
totals.timeOnSite,
hits.page.pagePath,
MAX(IF(customDimensions.index=1, customDimensions.value, NULL)) WITHIN 
customDimensions AS cd1,
trafficSource.source,
SUM(COUNT(CASE WHEN (hits.eventInfo.eventCategory = 'Download' AND hits.type 
= 'EVENT' AND hits.eventInfo.eventAction = 'pdf') THEN 
hits.eventInfo.eventAction END)) AS Downloads,
SUM(COUNT(CASE WHEN (hits.eventInfo.eventLabel  = 'search-header' OR 
hits.eventInfo.eventLabel  = 'search-mainpage') AND hits.type = 'EVENT' THEN 
hits.eventInfo.eventLabel END)) AS Search,
FROM TABLE_DATE_RANGE([xxxxxx.ga_sessions_],
TIMESTAMP('2018-11-25'), TIMESTAMP('2018-11-25')) WHERE hits.page.pagePath LIKE '%xyz%'
GROUP BY 1,2,3,4,5
LIMIT 100;

The Query fails and shows me "Error: Invalid mixture of scoped and unscoped aggregation functions" Any thoughts on how to solve this? Kind regards!

Julez2000
  • 1
  • 1
  • 1
  • you should add the table structure and some examples with input/output – hlagos Dec 04 '18 at 15:42
  • My query runs on the whole google analytics table which is linked with my analyitcs view. The Outcome works correct, except when I add the line MAX(IF(customDimensions.index=1, customDimensions.value, NULL)) WITHIN customDimensions AS cd1, so the custom dimension query isn't working correctly. The custom dimensoin cd1 shows the true URL which is typed in the browser. – Julez2000 Dec 05 '18 at 08:13

1 Answers1

0

I use this with standard SQL, not sure if it works on legacy.

max(case when customdimensions.index = 1 then customdimensions.value end) as cd1

Jiminie.Glick
  • 27
  • 1
  • 1
  • 5