Querying Google Analytics events in BigQuery: event error message

0
votes

When attempting to run the query below in the query editor, receive this error: "SELECT list expression references h.eventinfo.eventlabel which is neither grouped nor aggregated at [5:3]." Attempting to get a count of 'fullvisitorid' who performed the event actions listed in the where clause, but have yet to get this query running. 

SELECT
  'iOS' as app_source,
  h.eventInfo.eventCategory,
  h.eventinfo.eventaction,
  h.eventinfo.eventlabel,
  COUNT(1) events,
  COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS string))) uniqueEvents
FROM
   `xxxxxxxx-xxxx.xxxxxxxx.ga_sessions_*` t,
  UNNEST(hits) h
WHERE
  h.type='EVENT'
  and (h.eventInfo.eventCategory = 'Live' and h.eventInfo.eventLabel = 'Team Chat')
  or (h.eventInfo.eventCategory = 'Messages' and h.eventInfo.eventLabel = 'Direct Message')
  and date >= "20190801"
GROUP BY
  1,2,3
ORDER BY
  events DESC
2
google-bigquery
unfortunately, your query is total mess. it is a combination of BigQuery's legacy and standard SQL. has too many conceptual issues and btw not full query presented - so I don't think anyone will waste time here. I recommend you first to revisit and simplify it to the point it makes then sense to ask for help - Mikhail Berlyant
it sure looks much better now. thank you for updating your question :o) - Mikhail Berlyant

2 Answers

0
votes

I receive this error: "SELECT list expression references h.eventinfo.eventlabel which is neither grouped nor aggregated at [5:3]."

You are just missing this column in the GROUP BY, so try below 

SELECT
  'iOS' as app_source,
  h.eventInfo.eventCategory,
  h.eventinfo.eventaction,
  h.eventinfo.eventlabel,
  COUNT(1) events,
  COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS string))) uniqueEvents
FROM
   `xxxxxxxx-xxxx.xxxxxxxx.ga_sessions_*` t,
  UNNEST(hits) h
WHERE
  h.type='EVENT'
  and (h.eventInfo.eventCategory = 'Live' and h.eventInfo.eventLabel = 'Team Chat')
  or (h.eventInfo.eventCategory = 'Messages' and h.eventInfo.eventLabel = 'Direct Message')
  and date >= "20190801"
GROUP BY
  1,2,3,4
ORDER BY
  events DESC

The only change here is in GROUP BY
1,2,3,4 instead of 1,2,3

0
votes

I can't fix your query because of what Mikhail already said. But I guess I can give you a starting point for querying events in general:

SELECT
  h.eventInfo.eventCategory,
  h.eventinfo.eventaction,
  h.eventinfo.eventlabel,
  COUNT(1) events,
  COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS string))) uniqueEvents
FROM
  `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910` t, -- comma means cross join
  UNNEST(hits) h -- unnest turns arrays into table format, meaning we cross join the table with a bunch of 'hits'-arrays here
WHERE
  h.type='EVENT'
GROUP BY
  1,2,3
ORDER BY
  events DESC