Блог

довольно младший аналитик данных здесь

Я пытаюсь превратить денормализованные данные о событиях из GA4 в более удобный для BI вложенный формат. Схема необработанных данных GA4: схема GA4

Отправной точкой являются данные на уровне событий, но поскольку я пытаюсь создать панель мониторинга пользователей с глубоким погружением на основе user_pseudo_id, я хочу создать три уровня абстракции:

1. Уровень пользователя, содержащий информацию об устройстве, общую статистику и сеансы в виде вложенной ПОВТОРЯЮЩЕЙСЯ ЗАПИСИ
2. Уровень сеанса, содержащий географические данные, продолжительность сеанса, количество посещенных страниц и все события сеанса в виде вложенной ПОВТОРЯЮЩЕЙСЯ ЗАПИСИ
3. Уровень события, содержащий метку времени, тип события и информацию о конкретном событии.

Мой код до сих пор:

 ...events_joined_with_transactions AS (
    SELECT
        ue.*,
        t.transaction_id,
        t.currency,
        t.shipping,
        t.tax,
        t.revenue,
        t.unique_items,
        t.total_items,
        t.items
    FROM user_events AS ue
    LEFT JOIN transactions AS t
    ON ue.event_name = "purchase"
    AND ue.user_pseudo_id = t.user_pseudo_id
    AND t.timestamp = ue.timestamp
),

sessions AS (
    SELECT
        user_pseudo_id,
        session_id,
        source_medium,
        campaign_name,
        ARRAY_AGG(
            STRUCT(
                date, 
                timestamp,
                event_name,
                event_specific_info
            )
        ) AS events
    FROM events_joined_with_transactions
    GROUP BY 1, 2, 3, 4
),

users AS (
    SELECT
        user_pseudo_id,
        SUM(IF(event_name != "user_engagement", 1, 0)) AS total_events,
        SUM(IF(event_name = "session_start", 1, 0)) AS sessions,
        SUM(IF(event_name = "page_view", 1, 0)) AS view_page,
        SUM(IF(event_name = "view_item", 1, 0)) AS view_item,
        SUM(IF(event_name = "add_to_cart", 1, 0)) AS add_to_cart,
        SUM(IF(event_name = "remove_from_cart", 1, 0)) AS remove_from_cart,
        SUM(IF(event_name = "add_payment_info", 1, 0)) AS add_payment_info,
        SUM(IF(event_name = "add_shipping_info", 1, 0)) AS add_shipping_info,
        SUM(IF(event_name = "begin_checkout", 1, 0)) AS begin_checkout,
        SUM(IF(event_name = "purchase", 1, 0)) AS transactions,
        SUM(shipping) AS total_shipping,
        SUM(tax) AS total_tax,
        SUM(revenue) AS total_revenue,
        SUM(total_items) AS total_items,
    FROM events_joined_with_transactions
    GROUP BY 1
),

final AS (
    SELECT
        u.user_pseudo_id,
        u.total_events,
        u.sessions,
        u.view_page,
        u.view_item,
        u.add_to_cart,
        u.remove_from_cart,
        u.add_payment_info,
        u.add_shipping_info,
        u.begin_checkout,
        u.transactions,
        u.total_shipping,
        u.total_tax,
        u.total_revenue,
        u.total_items,
        ARRAY_AGG(
            session_id,
            source_medium,
            campaign_name,
            events
        ) AS sessions
    FROM users u
    LEFT JOIN sessions s
    USING(user_pseudo_id)
)

SELECT *
FROM final
 

Однако я получаю следующее сообщение об ошибке:

Аргумент для ARRAY_AGG не должен быть типом массива, но должен быть МАССИВ<СТРУКТУРА<дата ДАТА, метка времени, СТРОКА имя_события, массив_специфик_инфо<СТРУКТУРА<строка ключа, структура значения<строка_вал, int_val INT64, float_val DOUBLE><строка_вал, int_val INT64, float_val DOUBLE>><строка_вал, int_val INT64, float_val DOUBLE>><строка_вал, int_val INT64, float_val DOUBLE>><строка_вал, int_val INT64, float_val DOUBLE>> < строка_вал, int_val INT64, float_val DOUBLE>> < строка_вал, int_val INT64, float_val DOUBLE>>>> < string_val СТРОКА, int_val INT64, float_val ДВОЙНОЙ > > > > >

Почему этот тип массива недопустим?

согласно вашему коду, поле события из таблицы сеансов уже является типом массива, поэтому его нельзя использовать в последнем array_agg.

Из официального документа

 Supported Argument Types
All data types except ARRAY.