Как анализировать столбцы GA4 BigQuery RECORD в SQL Server?
Мы используем скрипт Python для подключения к BigQuery, чтобы загружать файлы ежедневных событий GA4 и загружать их в хранилище данных SQL Server. Большинство столбцов представляют собой целые числа или строки, с которыми легко работать. Есть также 11 полей типа RECORD.
Согласно документации Google по RECORDS они
К RECORD можно получить доступ как к типу STRUCT в GoogleSQL. СТРУКТУРА – это контейнер упорядоченных полей. Столбец RECORD может иметь режим REPEATED, который представлен в виде массива типов STRUCT. Кроме того, поле внутри запись может быть повторена, что представлено как СТРУКТУРА, которая содержит МАССИВ.
Вот пример данных event_params, который имеет массив типов STRUCT:
[{'key': 'engaged_session_event', 'value': {'string_value': None, 'int_value': 1, 'float_value': None, 'double_value': None}}
{'key': 'ga_session_number', 'value': {'string_value': None, 'int_value': 2, 'float_value': None, 'double_value': None}}
{'key': 'ga_session_id', 'value': {'string_value': None, 'int_value': 1679860220, 'float_value': None, 'double_value': None}}
{'key': 'session_engaged', 'value': {'string_value': None, 'int_value': 1, 'float_value': None, 'double_value': None}}
{'key': 'page_title', 'value': {'string_value': 'Heavy Duty Truck Parts Online, Medium Duty, Light Duty - FinditParts', 'int_value': None, 'float_value': None, 'double_value': None}}]
Вот пример данных device, который является СТРУКТОМ:
{'category': 'mobile', 'mobile_brand_name': 'Apple', 'mobile_model_name': 'iPhone', 'mobile_marketing_name': None, 'mobile_os_hardware_model': None, 'operating_system': 'iOS', 'operating_system_version': 'iOS 14.8', 'vendor_id': None, 'advertising_id': None, 'language': 'en-us', 'is_limited_ad_tracking': 'No', 'time_zone_offset_seconds': None, 'browser': None, 'browser_version': None, 'web_info': {'browser': 'Safari', 'browser_version': '14.1.2'}}
Есть ли функция SQL, которую можно использовать для анализа STRUCTS или массивов STRUCTS? Можно ли каким-то образом преобразовать эти данные в формат JSON, чтобы использовать встроенные функции JSON для запроса?
Пробовали ли вы искать готовые решения для выравнивания таблицы GA4 BQ? Лучше всего не трогать вложенный JSon, а сначала сгладить его, чтобы затем можно было легко запрашивать данные.
Привет, Дейл! Мы анализируем результаты данных BigQuery в SQL Server.
Ответы 1
Используйте UNNEST. Пример:
SELECT
EP.key AS event_param_key,
COUNT(*) AS occurrences
FROM
-- Replace table name.
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
-- Replace date range.
_TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
event_param_key
ORDER BY
event_param_key ASC;
Также в этой статье есть подробная информация о том, как использовать UNNEST: Как использовать функцию UNNEST в BigQuery для анализа параметров событий в Analytics
Спасибо, Минхаз, мы рассмотрели возможность использования функции UNNEST, но существует 102 различных значения event_param_key. Мы подумали, что будет проще проанализировать все поле event_params в DW, чем писать массивный запрос, который UNNEST объединяет эти 102 столбца.
Если вы не пытаетесь изменить event_params (т. е. каждый параметр события отображается как новое поле/столбец), вам не нужно писать массивный запрос. Запрос в моем ответе должен создавать отдельные строки для каждого параметра события.
Если вы используете BigQuery, почему вы пометили SQL-сервер?