Google BigQuery Recipe Flattening with Multiple Repeated Fields

February 2, 2015 | Bounteous x Accolite

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)