Три мелкие колхозные хитрости при работе с Google BigQuery и Google Data StudioТри мелкие колхозные хитрости при работе с Google BigQuery и Google Data Studio

Три мелкие колхозные хитрости при работе с Google BigQuery и Google Data Studio

Иван Никитин и партнеры ➢ Новости ➢ Google ➢ Data Studio ➢ Хитрости BigQuery и Data Studio

При построении BI-отчетов в Google Data Studio мы очень активно используем данные в BigQuery, особенно после выхода Google Analytics 4, который умеет выгружать свои данные в GBQ прямо из коробки. Конечно же, при написании запросов для построения отчета может возникать масса вопросов, и наши аналитики наработали уже ряд решений, которые помогают получать красивые эффектные отчеты. В этой статье я покажу три мелких приема, которые помогают в повседневной работе:

  1. Как использовать данные реального времени в своих отчетах;
  2. Как делать параметризованные запросы и расчетные переменные в запросах Data Studio и не поседеть раньше времени;
  3. Как включить любую JavaScript функцию в SQL BigQuery.

Данные реального времени в отчетах Data Studio и таблицы analytics_intraday_*

Суть проблемы в следующем: когда Google Analytics 4 передает данные в BigQuery, эта передача может включать или не включать в себя данные стриминга, то есть данные реального времени (это настраивается галочкой в настройках связи с BigQuery). Если стриминг выключен, то у нас один набор данных – это таблицы analytics_*. Если галочка включена, то этих наборов два, добавляется набор таблиц analytics_intraday_*.

По идее BigQuery накапливает данные за один день в таблицах analytics_intraday_* и ночью (на самом деле рано утром по Москве) он переносит эти данные за сутки в основной набор таблиц analytics_*.

Но это только в теории, а на самом деле по совершенно неведомым причинам BigQuery может не перенести данные за прошлый день в основной набор таблиц или сделать это в совершенно произвольное время, а не утром. Рассчитывать на то, что в 6:00 по Москве данные будут в основном наборе нельзя.

Как правило, мы строим отчеты, обращаясь к основному набору таблиц, но если BigQuery не перенес их в основной набор, то вчерашнего (а бывает и позавчерашнего дня) в ваших отчетах попросту не будет и объяснять это заказчику, как правило, бывает очень и очень проблематично.

Следующий фокус позволяет легко решить эту проблему. Нужно просто включить стриминг, если он еще не включен, и брать данные сразу из обоих наборов таблиц, что-то примерно так:

WITH my_data AS (
    -- Основной набор
    SELECT * 
    FROM `myproject.analytics_0000000.events_*`
    WHERE _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
    UNION ALL
    -- Стриминг
    SELECT * 
    FROM `myproject.analytics_0000000.events_intraday_*`
    WHERE _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE    
) 
SELECT ... FROM my_data

Легко можно заметить, что мы объединяем два запроса по UNION ALL, вытаскивая в каждой лишь нужные таблицы по дням.

Этот нехитрый приём позволяет показывать в отчетах хоть сегодняшний день, но нужно помнить, что Data Studio по умолчанию кэширует данные, полученные запросом, поэтому время от времени при просмотре сегодняшнего дня их надо обновлять кнопкой в GDS.

Передача и расчет параметров в запросах BigQuery

Суть проблемы в следующем: когда мы пишем запросы для отчетов, мы, понятное дело, отлаживаем их в консоли BigQuery и только потом, когда запрос будет отлажен, переносим его в источник данных Data Studio. Проблема в том, что в консоли BigQuery не поддерживаются параметры запроса, например, те же даты из календарика Data Studio, то есть параметры @DS_START_DATE и @DS_END_DATE. Или любые другие параметры, которые вы создали в отчете. Вот и приходится их в консоли заменять на реальные значения, например, ‘20211028’. И хорошо, если они используются только в одном месте, а не «размазаны» по всему SQL запросу в 500 строк.

Есть переменные в SQL, возразит мне внимательный читатель, и будет на 100% прав! Конечно же, очень удобно записать в самом начале SQL скрипта значения параметров в переменные и далее их уже использовать. Плюс всевозможные расчеты, типа «начало анализа ретроспективы при расчете модели атрибуции начинается за девяносто дней до начальной даты».

НО! Есть очень большое «но»! Когда Google Data Studio выполняет запрос в BigQuery, она «оборачивает» его еще одним SELECT, поэтому в этом запросе не могут использоваться переменные! Получается, что в Data Studio есть параметры запроса, но нет переменных, а в консоли BigQuery есть переменные, но нет параметров. Спасибо вам, добрые люди из корпорации зла!

Тем не менее проблема решается очень простым трюком – достаточно просто добавить любые переменные или значения, рассчитанные или взятые из параметров, в каждый ряд исходных данных как дополнительные поля. Это очень легко сделать кросс-объединением в самом начале запроса, чтобы потом, в Data Studio долго не искать, куда поставить значения параметров.

Примерно вот так:

WITH my_data AS (
    SELECT * FROM (
        -- Основной набор
        SELECT * 
        FROM `myproject.analytics_0000000.events_*`
        UNION ALL
        -- Стриминг
        SELECT * 
        FROM `myproject.analytics_0000000.events_intraday_*`
    ) CROSS JOIN (
        -- Посчитаем нужные значения для дальнейших расчетов
        SELECT 
            PARSE_DATE('%Y%m%d', @DS_START_DATE) AS date_start,
            PARSE_DATE('%Y%m%d', @DS_END_DATE) AS date_end,
            DATE_SUB(PARSE_DATE('%Y%m%d', @DS_START_DATE), INTERVAL 90 DAY) AS date_retro_start,
    )
) 
SELECT ... FROM my_data

Внимание! Этот код не совсем оптимальный с точки зрения запрашиваемого объема начальных данных! На больших объемах настоятельно рекомендуется в запросе основного набора таблиц добавить условие WHERE _TABLE_SUFFIX BETWEEN …, иначе вы будете «лопатить» все таблицы в наборе, и счета в конце месяца вас могут не обрадовать!  

Использование любой JavaScript функции в SQL BigQuery, например, URLDecode

Суть проблемы в следующем: несмотря на обилие функций в диалекте BigQuery Standard SQL, всё равно, некоторых функций не хватает. Например, при расшифровке UTM-меток на русском языке не хватает функции URLDecode, которая переведет строку «%D0%BF%D1%80%D0%B8%D0%B2%D0%B5%D1%82» в «привет».

Лёгкое нагугливание дает массу готовых примеров, как это реализовать. Но особый интерес вызывает так называемые JavaScript UDFs, которые позволяют использовать любой JavaScript код в операторах SQL. А вот это уже очень интересно!

Конечно же, готовый пример по ссылке, указанной выше, мы использовать не можем по причине, описанной выше – Data Studio оборачивает ваш запрос своим SELECT, поэтому никаких временных функций мы в запросе использовать не можем. Но кто запрещает нам использовать постоянно объявленные функции в своем датасете? Никто.

Давайте это сделаем!

  1. Создадим в консоли BigQuery новый датасет с именем, например, fn. Ну, или можно использовать любой существующий.
Новый датасет в Google BigQuery Console
  1. Добавим в него функцию URLDecode следующим кодом:
CREATE FUNCTION `myproject.fn.URL_DECODE`(enc STRING)
RETURNS STRING
LANGUAGE js AS """
  try { 
    return decodeURI(enc);;
  } catch (e) { return null }
  return null;
""";
  1. Проверим её работу:
SELECT fn.URL_DECODE('%D0%BF%D1%80%D0%B8%D0%B2%D0%B5%D1%82')

Видно, что всё работает замечательно. Точно также можно создать любую функцию на языке JavaScript и использовать ее в SQL, а это открывает такие горизонты что аж дух захватывает.

Надеюсь, эти мелкие колхозные хитрости помогут вам в решении задач так, как помогают нам.

Метки

Похожие записи

Добавить комментарий