google-analyticsgoogle-bigqueryenhanced-ecommerce

Calculate nested field without loosing export schema in BigQuery

Calculation on a field leads to a loss of the original export schema in BigQuery.

I have a standard enhanced e-commerce schema and want to change the transactionRevenue to a different currency. I want to keep the general export schema structure. The calculated field "transactionRevenueNewCurrency" should be in hits.transaction.transactionRevenueNewCurrency.

#standardSQL
SELECT
s.*,
ARRAY(SELECT  COALESCE( x.transaction.transactionRevenue*1.17,0)
        FROM UNNEST(hits) AS x) AS transactionRevenueNewCurrency
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` as s , UNNEST(hits) as h
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20160831'
AND transaction.transactionRevenue >0
LIMIT 10000

The new field is attached to the session instead each hit.

Solution

  • Below is for BigQuery Standard SQL 

    #standardSQL
SELECT * REPLACE(
  ARRAY(
    SELECT AS STRUCT * REPLACE(
      (SELECT AS STRUCT * REPLACE(
        COALESCE(CAST(transactionRevenue * 1.17 AS INT64), 0
        ) AS transactionRevenue)
       FROM UNNEST([transaction])
      ) AS transaction)
    FROM UNNEST(hits) hit
  ) AS hits)
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` 
WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20160831'