BigQuery - Exporting different Events from Google Analytics

Question

Probably a beginner question, but I just couldn't find a soultion here.

I want to export different events (only the total events metric) in BigQuery from my google analytics account

So f.e. I have a download event and a search event, and I want to have each in one column, showing me the total events metric for each event. I filtered both events for different conditions, to make sure I have the correct event.

I'm using LegacySQL here and want to export all data from the same table. Problem is, that there is no subselect allowed in LegacySQL, any thoughts on how I can solve this?

SELECT  clientId, totals.timeOnSite, trafficSource.source,
(SELECT hits.eventInfo.eventAction FROM (TABLE_DATE_RANGE([xxxxxx.ga_sessions_], TIMESTAMP('2018-11-25'), TIMESTAMP('2018-11-25')))   WHERE hits.eventInfo.eventCategory = 'Download' AND hits.type = 'EVENT' AND hits.eventInfo.eventAction = 'pdf') AS DownloadEvent,
(SELECT hits.eventInfo.eventLabel FROM (TABLE_DATE_RANGE([xxxxxx.ga_sessions_], TIMESTAMP('2018-11-25'), TIMESTAMP('2018-11-25')))   WHERE hits.eventInfo.eventLabel  = 'search-header' OR 'search-mainpage' AND hits.type = 'EVENT') AS SearchEvent,

COUNT(1) eventHits FROM (TABLE_DATE_RANGE([xxxxxx.ga_sessions_], TIMESTAMP('2018-11-25'), TIMESTAMP('2018-11-25')))

Answer 1

Sub-selects are allowed in BQ Legacy SQL. The below should give you the results you are looking for.

You should not need to use a subquery here. Using a simple CASE statement should do the trick. The case statement will filter for rows that match the condition you are looking for.

Query

SELECT 
clientId,
totals.timeOnSite, 
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 DownloadEvent,
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 SearchEvent
FROM TABLE_DATE_RANGE([xxxxxx.ga_sessions_], TIMESTAMP('2018-11-25'), TIMESTAMP('2018-11-25'))
GROUP BY 1,2,3

Now, if you want to use a subquery, here is how it would look like

With subquery

SELECT  
b.clientId, 
b.totals.timeOnSite, 
b.trafficSource.source,
SUM(a.DownloadEvent),
SUM(a.SearchEvent)
FROM
(TABLE_DATE_RANGE([xxxxxx.ga_sessions_], TIMESTAMP('2018-11-25'), TIMESTAMP('2018-11-25'))) AS b 
INNER JOIN (
SELECT 
clientId,
totals.timeOnSite, 
trafficSource.source,
COUNT(CASE WHEN (hits.eventInfo.eventCategory = 'Download' AND hits.type = 'EVENT' AND hits.eventInfo.eventAction = 'pdf') THEN hits.eventInfo.eventAction END) AS DownloadEvent,
COUNT(CASE WHEN (hits.eventInfo.eventLabel  = 'search-header' OR hits.eventInfo.eventLabel  = 'search-mainpage') AND hits.type = 'EVENT' THEN hits.eventInfo.eventLabel END) AS SearchEvent
FROM TABLE_DATE_RANGE([xxxxxx.ga_sessions_], TIMESTAMP('2018-11-25'), TIMESTAMP('2018-11-25'))
GROUP BY 1
) AS a ON b.clientId = a.clientId
GROUP BY 1,2,3