Google BigQuery Recipe Flattening with Multiple Repeated Fields

February 02, 2015
By Bounteous

Legacy SQL

SELECT
     CONCAT(fullVisitorId, ".", STRING(visitId)) AS Session_ID,
     hits.page.pagePath AS Page,
     customDimensions.index AS CD_Index,
     customDimensions.value AS CD_Value
FROM
     FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits)

Standard SQL

SELECT
     CONCAT(fullVisitorId, ".", STRING(visitId)) AS Session_ID,
     hits.page.pagePath AS Page,
     customDimensions.index AS CD_Index,
     customDimensions.value AS CD_Value
FROM
     FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits)