other

bigquery.txt

SELECT
     REGEXP_EXTRACT(hits.page.pagePath, r'([^?&#]*)')AS landing_Page,
     trafficSource.medium AS medium,
     trafficSource.source AS source,
     device.deviceCategory AS deviceCategory,
     hits.contentGroup.contentGroup1 as contentGroup1,
     date,
     (SELECT x.value FROM UNNEST(hits.customDimensions) x WHERE x.index = 4) as type,
     COUNT(totals.bounces) AS Bounces, 
     Count(totals.transactions) as transactions,
     COUNT(totals.hits) AS Entrances 
FROM 
     (Select * from `somehost.ga_sessions_201808*` 
Union all
Select * from `somehost.ga_sessions_201809*`) AS GA,
     UNNEST(GA.hits) AS hits
WHERE 
     hits.type="PAGE" AND hits.hitNumber=1 and trafficSource.medium = 'organic'
GROUP BY 
     landing_Page, medium, source, date, deviceCategory, contentGroup1, type
ORDER BY 
     Entrances desc limit 1000