/***************************************************************************************************************************
Created by Václav Ráš (www.dataras.cz)
BigQuery | Unnest GA4 Query:
- Automaticky sestaví UNNESTovací sub-queries pro všechny event parametry v GA4 datasetu za posledních 7 dní
- Parametry, které mají více datových typů (např. int a string) přetypuje na jeden sjednocující (většinou string)
- Řekne vám, které event parametry mají více datových typů a jaké to jsou (může být užitečné i pro kontrolu datové čistoty)
***************************************************************************************************************************/
WITH
parameters AS (
SELECT
event_param.key AS key,
CASE
WHEN event_param.value.string_value IS NOT NULL THEN "string_value"
WHEN event_param.value.int_value IS NOT NULL THEN "int_value"
WHEN event_param.value.double_value IS NOT NULL THEN "double_value"
END AS data_type,
COUNT(*) AS event_count
FROM
`analytics_XXXXXXXXX.events_*`, /* <-- SEM VLOŽTE GA4 DATASET KONČÍCÍ NA "events_*" */
UNNEST(event_params) AS event_param
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY 1, 2
ORDER BY 1),
data_types AS (
SELECT
key,
ARRAY_AGG(DISTINCT data_type IGNORE NULLS) AS data_types_agg,
COUNT(*) AS distinct_data_types,
SUM(event_count) AS event_count
FROM
parameters
GROUP BY key
)
SELECT
key,
CASE
WHEN distinct_data_types=1 AND "string_value" IN UNNEST(data_types_agg) THEN "(SELECT value.string_value FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
WHEN distinct_data_types=1 AND "int_value" IN UNNEST(data_types_agg) THEN "(SELECT value.int_value FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
WHEN distinct_data_types=1 AND "double_value" IN UNNEST(data_types_agg) THEN "(SELECT value.double_value FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
WHEN distinct_data_types=2 AND "string_value" IN UNNEST(data_types_agg) AND "int_value" IN UNNEST(data_types_agg) THEN "(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING)) FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
WHEN distinct_data_types=2 AND "string_value" IN UNNEST(data_types_agg) AND "double_value" IN UNNEST(data_types_agg) THEN "(SELECT COALESCE(value.string_value, CAST(value.double_value AS STRING)) FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
WHEN distinct_data_types=2 AND "int_value" IN UNNEST(data_types_agg) AND "double_value" IN UNNEST(data_types_agg) THEN "(SELECT COALESCE(value.double_value, CAST(value.int_value AS FLOAT64)) FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
ELSE "(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING), CAST(value.double_value AS STRING)) FROM UNNEST(event_params) WHERE key = '"|| key ||"') AS " || key || ","
END AS query,
distinct_data_types,
data_types_agg,
event_count
FROM
data_types
ORDER BY key;