Знакомство интернет-маркетолога с Google BigQuery

BigQuery - один из продуктов платформы Google Cloud

Основные компоненты Google Cloud Platform:

• Вычислительные ресурсы - Compute Engine, Kubernetes Engine, App Engine;
• Хранение данных - Google Cloud Storage, Cloud SQL, Cloud Bigtable, Firestore;
• Аналитика и Big Data - BigQuery, Dataflow, Dataproc;
• Искусственный интеллект и машинное обучение - Vertex AI, AutoML, Natural Language API, Vision API;
• Сети и безопасность - Virtual Private Cloud, Cloud Load Balancing, Cloud IAM;
• Инструменты разработки - Cloud SDK, Cloud Code, Cloud Build.

Нельзя не учитывать роль технологического стека в разработке окончательного решения, поскольку от его выбора зависит:

• производительность и масштабируемость приложения;
• скорость и стоимость разработки;
• безопасность и удобство поддержки;
• возможности интеграции и будущее развитие продукта.

Именно поэтому многие предприятия выбирают сервисы и службы от одной компании, поскольку все они друг с другом интегрированы. Например, если вы работаете в России, где множество продуктов компании Google запрещены и заблокированы, разумным решением будет использование стека технологий от компании Яндекс (тот же ClickHouse - Yandex Cloud - Яндекс Метрика - Яндекс Тег Менеджер - Яндекс Директ - Yandex DataLens и др.), или аналоги от менее известных производителей.

В BigQuery встроен BigQuery ML - это набор функций машинного обучения. Он позволяет вам создавать и использовать модели машинного обучения на ваших данных BigQuery, используя только язык SQL.
BigQuery ML предлагает широкий спектр моделей машинного обучения, включая:

• Классификация - предсказывает категориальную переменную, например, "класс" или "прогноз";
• Регрессия - предсказывает числовую переменную, например, "стоимость" или "продажи";
• Прогнозирование временных рядов - предсказывает будущие значения переменной на основе ее исторических значений;
• Обнаружение аномалий - определяет данные, которые отклоняются от ожидаемого поведения;
• Факторный анализ - выделяет из данных набор факторов, которые объясняют взаимосвязь между переменными.

Подробнее о BigQuery ML читайте в официальной документации Google.

Таким образом, BigQuery ML предоставляет аналитикам данных мощные инструменты для интеграции машинного обучения в их рабочие процессы, делая анализ данных более эффективным и доступным.

BigQuery для интернет-маркетолога (веб-аналитика)

Если разработчик и аналитик данных рассматривают BigQuery как часть ВСЕЙ инфраструктуры компании, и их работа связана со ВСЕЙ информацией организации, то интернет-маркетолог (веб-аналитик) работает, преимущественно, только со статистикой, полученной с помощью рекламных и аналитических инструментов. И вот здесь мы возвращаемся к Google Analytics 4.

Одна из парадигм, которая изначально была заложена компанией Google в Google Analytics 4 – это возможность работать с «сырыми», необработанными данными. И чтобы упростить процесс обмена информации между двумя продуктами Google, в GA4 добавили новую интеграцию – связь с Google BigQuery.

Google BigQuery

Примечание: В Universal Analytics (GA3) прямой интеграции между сервисами не было. Она была доступна только в Google Analytics 360 (платной версии Google Analytics).

Теперь мы можем включить экспорт данных в BigQuery и начать использовать необработанные данные о событиях, собранных на сайте и ​​в мобильном приложении без какой-либо выборки и ограничений.

Что такое необработанные, «сырые» данные?

Когда вы работаете с отчетами Google Analytics 4, вы видите табличные данные в окончательном виде. Это может быть сумма, расчет среднего значения, процент от итогового значения и т.д. Например, вот так выглядит стандартный отчет Привлечение трафика (Traffic acquisition) за определенный диапазон дат:

Отчет "Привлечение трафика"

Вверху таблицы представлены агрегированные (объединенные) значения по активным пользователям, сеансам, доле взаимодействий, количеству событий и т.д. Часть показателей рассчитана на основе других показателей. Например, Доля взаимод., Среднее время взаимодействия, Количество событий на сеанс и другие. То есть над ними были совершены преобразования и произведены математические операции - сложение, деление, умножение, вычитание. А поскольку над ними проводились различные манипуляции, значит они подверглись обработке. Агрегированные данные удобны тем, что вы имеете быстрый доступ к информации на уровне событий и пользователей. Google Analytics 4 уже за вас произвел определенные действия и предоставил готовый отчет в виде таблиц, графиков, диаграмм.

Другие тип отчетов в GA4 - Исследования. Исследования (Explorations) - это инструмент работы с данными в Google Analytics 4 (вынесен в отдельную категорию), который позволяет значительно глубже, детальнее и более гибко анализировать полученную информацию о ваших пользователях по сравнению со стандартными отчетами.

Пример Исследования в свободной форме

В Исследованиях у вас есть возможность создавать свои собственные отчеты, используя шаблоны или свободную форму, менять тип визуализации анализируемых данных, добавлять к ним необходимые параметры, показатели, которые доступны в вашем ресурсе, фильтры, сегменты (сравнивать их между собой), а также выбирать методику анализа простым перетаскиванием. Благодаря Исследованиям, вы можете выйти за рамки стандартных отчетов, обзорных отчетов, подробных и сводок GA4.

Другие материалы в моем блоге:

• Начало работы с Исследованиями (Свободная форма)
• Когортное исследование
• Исследование пути
• Исследование воронки
• Наложение сегментов
• Статистика пользователей
• Фильтр по Client ID в Исследовании Google Analytics 4
• Общая ценность пользователя

Но есть и другая проблема - выборка данных (data sampling). Выборка - это анализ некоторого подмножества данных из общего множества с целью выделения неких свойств исходного множества. Иными словами, Google берет некоторую часть данных, например, 10%, умножает ее на 10 и говорит нам, что так вели бы себя все 100%. Это позволяет быстрее извлекать данные и почти не влияет на их качество.

В официальной справке Google приведен другой пример для лучшего понимания выборки данных: если вы хотите оценить количество деревьев на площади в 100 гектаров с более или менее равномерным распределением деревьев, можно подсчитать количество деревьев на одном гектаре и умножить на 100. Вы также можете подсчитать число деревьев на половине гектара земли и умножить его на 200. Это позволит определить количество деревьев на всей площади в 100 гектаров.

В Google Analytics 4 выборка данных может применяться, если количество событий, используемых в отчетах, Исследованиях или запросе, превосходит ограничение для ресурса. В этом случае GA4 использует часть данных и масштабирует расчеты, чтобы вы получили репрезентативные результаты.

Если результаты создаются на основе выборки, на значке Качество данных показывается, какой процент данных был использован.

Исследование с очень малой выборкой

При работе с выборочными данными соотношение между общим объемом данных и размером выборки может влиять на точность результатов запроса. Обычно чем больше размер выборки (в процентах от общего объема данных), тем точнее результаты. Если размер выборки для Исследования слишком мал, попробуйте сократить диапазон дат, чтобы уменьшить объем данных, к которым применяется выборка. Это позволит получить более точные результаты.

Примечание: для запросов на уровне событий применяются ограничения в 10 млн событий для Google Analytics 4 и до 1 млрд – для ресурсов Google Analytics 360.

Таким образом, с помощью неагрегированных (сырых) данных вы можете:

• работать со статистикой без выборки;
• обойти интерфейсные ограничения Google Analytics 4;
• отслеживать путь каждого пользователя по вашему сайту или мобильному приложению;
• строить собственные модели атрибуции, чтобы оценивать вклад каждого рекламного канала;
• находить различные зависимости в поведении определенных групп пользователей, сегментировать их и прицельно «таргетироваться»;
• объединять данные Google Analytics 4 со сторонними API и другими сервисами (например, CRM);
• корректировать уже собранную статистику (исправлять ошибки после сбора);
• визуализировать данные в таких инструментах, как Looker Studio, Tableau, Microsoft Power BI и др.;
• использовать накопленную статистику в качестве входных значений для прогнозирования будущих показателей (Machine Learning).

Пример таблицы с сырыми данными по событиям Google Analytics 4 в BigQuery

Не стоит забывать, что срок хранения данных о пользователях и событиях в Google Analytics 4 по умолчанию составляет 2 месяца. Вы можете увеличить его до 14 месяцев, выставив в настройках ресурса в разделе Сбор и редактирование данных – Хранение данных.

Хранение данных

Расхождение данных между общим количеством событий в Google Analytics 4 и BigQuery допустимо, но оно не должно превышать 2-5%.

Однако этого может быть недостаточно, если вы хотите проанализировать события пользователей, которые последний раз заходили более 14 месяцев назад. И здесь вам тоже может помочь Google BigQuery. Настроив экспорт данных один раз, данные в BigQuery будут поступать ежедневно/потоково и храниться там без ограничений по времени.

Ограничения интерфейса Google Analytics 4

Ни в стандартных отчетах, ни в Исследованиях Google Analytics 4 вы не сможете проанализировать данные в разрезе конкретных пользователей, опираясь на их идентификатор (он же Client ID, в BigQuery - user_pseudo_id). Исключение составляет только Исследование Статистика пользователей. Там эта информация представлена, но сам отчет сильно ограничен по своему функционалу.

А в BigQuery такую статистику можно получить с помощью простого SQL-запроса:

Таблица с данными о продажах по пользователям

И затем, при желании, объединить эти данные с другими источниками (например, продажами из CRM или рекламной статистикой).

А если вы желаете связать поисковые запросы Search Console с покупками Google Analytics 4?

Поисковые запросы Search Console и продажи из Google Analytics 4

Сделать это через связь Search Console с GA4 не получится, нужна более глубокая аналитика.

А построить сводную таблицу со всеми событиями улучшенной статистики с группировкой по пользователям? В BigQuery без каких-либо проблем, а в интерфейсе Google Analytics 4 это сделать невозможно.

Общая таблица в BigQuery по всем событиям улучшенной статистики для каждого пользователя

Еще в Google Analytics 4 нельзя передавать персональные данные пользователя - его имя, фамилию, контактные данные, IP-адрес, почтовый индекс, адрес и другие сведения, позволяющие в явном виде идентифицировать пользователя. Но эту же информацию можно загружать в хранилище Google BigQuery из CRM-системы, а затем посредством SQL связывать аналитические данные и данные о клиентах.

И таких ограничений и запретов в GA4 много. Слышали ли вы когда-нибудь о HyperLogLog++?

Значение точности для нескольких показателей Google Analytics 4

• Сеансы (Sessions) - precision 12
• Активные пользователи (Active Users) - precision 14
• Всего пользователей (Total Users) - precision 14

В HLL+ есть еще один параметр - sparse precision. В BigQuery его значение не определяется пользователем и фиксируется на уровне precision + 5.

Построив график по табличным данным, можно наблюдать такую картину:

График зависимости точности от размера (quantable.com)

Здесь мы видим, что с увеличением настройки точности количество ошибок уменьшается, а объем используемой памяти увеличивается. Интересно отметить, что, хотя по умолчанию в BigQuery установлена ​​точность (precision) 15, настройки в GA4 ниже - 12 для сеансов и 14 для пользователей. Это имеет смысл с точки зрения Google, поскольку они решили, что дополнительная точность в 0,5% не стоит удвоения размера хранилища с 16 КБ до 32 КБ… Оптимизация затрат, вычислительных ресурсов и производительности со стороны Google. Ничего личного, это просто бизнес (с).

Для чего я привел эти таблицы с эскизами и значения precision для некоторых показателей GA4? Чтобы продемонстрировать вам практически расхождения в интерфейсных данных и данных, экспортируемых в BigQuery, а также несколько запросов, с точным подсчетом и приблизительным результатом.

Итак, давайте построим отчет за 6 месяцев и подсчитаем уникальное количество пользователей (Total Users) с 3 января по 3 июля 2023 года. В стандартном отчете по событиям этот показатель равен 101 625:

Всего пользователей за выбранный диапазон дат - 101 625

В Исследованиях за аналогичный период всего пользователей точно такое же число - 101 625:

Всего пользователей за выбранный диапазон дат - 101 625

В проекте настроена связь Google Analytics 4 с Google BigQuery. И при выполнении SQL-запроса подсчета количества пользователей за выбранный диапазон дат я могу пойти разными путями:

1. использовать функцию COUNT(DISTINCT) для точного измерения числа пользователей. Этот подход требует больше памяти и требует больше времени для выполнения, особенно для больших наборов данных;
2. использовать функцию APPROX_COUNT_DISTINCT, которая аппроксимирует результаты с помощью HLL++. Она не позволяет вам настраивать точность приближения;
3. использовать функцию HLL_COUNT.INIT и значение точности precision в HLL++ из таблицы, которую я приложил выше.

Давайте рассмотрим каждый запрос в BigQuery и результат его выполнения.

Точный подсчет с помощью функции COUNT(DISTINCT) будет выглядеть так:

Для моего проекта в BigQuery SQL-запрос за выбранный диапазон дат (с 3 января по 3 июля 2023 года) даст следующий результат (размер - 33,36 МБ и количество пользователей 101 451):

Точный подсчет пользователей в BigQuery с помощью COUNT(DISTINCT)

Как видите, количество пользователей в интерфейсе GA4 и BigQuery при точном подсчете отличается. Но процент расхождения допустимый - всего 0,17%. В моем счетчике в эти даты были активированы сигналы Google. А как вы уже знаете, GA4 не экспортирует в BigQuery данные, связанные с сигналами Google, поэтому количество событий в Google Analytics и в BigQuery может быть разными. Это связано с тем, что сигналы Google дедуплицируют полученные данные о событиях.

Другой способ - это использовать функцию APPROX_COUNT_DISTINCT для приблизительного подсчета:

Для моего проекта в BigQuery SQL-запрос за выбранный диапазон дат даст следующий результат (размер запроса не изменится - 33,36 МБ, а количество пользователей составит 101 597):

Подсчет пользователей в BigQuery с помощью APPROX_COUNT_DISTINCT

Процент расхождения при приблизительном подсчете еще меньше - всего 0,03%. В моем счетчике аналитики не так много данных, поэтому цифры при точном подсчете и приблизительном не сильно отличаются. Однако если в вашем проекте много статистики и миллионы пользователей и событий, то % расхождения будет больше, но не должен выходить за доверительный интервал, представленный в таблице выше.

Еще вы можете выполнить репликацию APPROX_COUNT_DISTINCT с помощью функций BigQuery HLL++. Это вернет идентичные или очень похожие результаты, как APPROX_COUNT_DISTINCT:

Результат выполнения SQL-команды в BigQuery для моего проекта (размер запроса и количество пользователей не изменилось - 33,36 МБ и 101 597):

Подсчет пользователей в BigQuery с помощью функции HLL++

И, наконец, чтобы реплицировать данные в пользовательском интерфейсе Google Analytics, то есть сделать очень похожими данные в BigQuery с интерфейсом GA4, используйте функцию HLL++ и значение точности precision = 14:

Результат выполнения SQL-команды в BigQuery для моего проекта с помощью функции HLL++ и задания точности 14 (размер запроса - 33,36 МБ):

Подсчет пользователей в BigQuery с помощью функции HLL++ и указании точности (precision = 14)

Бинго! С помощью функции HLL++ и указании точности 14 мы добились 100% совпадения интерфейсных данных GA4 и табличных в BigQuery. Но опять же, здесь есть свои нюансы и об этом прописано в официальной документации Google. Для подсчета пользователей Google Analytics использует sparse precision значение 25. Поскольку sparse precision значение BigQuery всегда равно precision + 5, по умолчанию будет установлено значение 19 (14+5). Таким образом, этот параметр не будет соответствовать пользовательскому интерфейсу Google Analytics при подсчете общего количества пользователей. Будет небольшая разница. У меня данные сошлись, потому что их не так много. Но у вас ситуация может быть другой.

Это все? Разумеется, нет! Когда дело доходит до подсчета различных значений, то в Google Analytics 4 немаловажным фактором играет такое понятие, как кардинальность (cardinality). В контексте баз данных кардинальность относится к уникальности значений данных, содержащихся в столбце. Высокая мощность означает, что столбец содержит большой процент полностью уникальных значений. Низкая мощность означает, что столбец содержит много повторов в своем диапазоне данных. Если проще, высокая кардинальность - уникальные данные, низкая кардинальность - повторяющиеся данные.

Например:

• Пол - повторяющиеся значения, низкая кардинальность, поскольку значений пола всего несколько и они фиксированы - male, female и unknown;

Пол - высокоповторяющиеся данные, низкая кардинальность

• Браузер - данные повторяются, но уже не так часто - средняя кардинальность;

Браузер - среднеповторяющиеся данные, нормальная кардинальность

• Город - данные уникальны, высокая кардинальность, так как география пользователей очень обширна.

Город - много уникальных значений, высокая кардинальность

• Путь к странице - уникальных значений еще больше (на сайтах электронной торговли их может быть десятки и сотни тысяч), высокая кардинальность.

Путь к странице - еще больше уникальных значений, высокая кардинальность

Предположим, вы просматриваете стандартный отчет или выгружаете данные с помощью Data API. Он содержит большой объем данных и имеет параметры с высокой кардинальностью. Под высокой кардинальностью в Google Analytics 4 подразумеваются параметры, количество уникальных значений которых превышает 500. При наличии таких параметров может быть достигнуто максимальное количество строк в отчете, и тогда GA4 сгруппирует менее часто встречающиеся значения и отобразит их в строке (other).

Строка (other) в стандартных отчетах

Это еще одна проблема, поскольку она не позволяет точно проанализировать весь набор данных в GA4. Какие значения находятся в строке (другое)? Узнать невозможно. Когда Google использует агрегированные данные, в отчете или Исследовании может образоваться больше строк, чем допустимо. Тогда создается строка (other).

В отчете с дополнительными параметрами, фильтрами или сравнениями это появление строки (other) более вероятно, так как для таких отчетов требуются таблицы базы данных с большим количеством параметров. В ресурсах, где много данных, легко может быть превышено ограничение на количество уникальных значений, и тогда данные будут сгруппированы и отображены в одной строке.

Примечание: именно поэтому Google не рекомендует передавать в специальных определениях данные, которые имеют множество уникальных значений - идентификаторы транзакций, Client ID, User ID, IP-адрес, пользовательское время и т.д. и т.п.

Стоит пару слов сказать о моделировании. По умолчанию в стандартных отчетах и Исследованиях моделирование ключевых событий и моделирование поведения включено, в том числе в реальном времени.

Данные BigQuery содержат сведения о проверках связи без использования файлов cookie, которые собирает GA4, если включен режим согласия и каждый сеанс использует новое значение user_pseudo_id. Поэтому моделирование может привести к различиям между стандартными отчетами и детализированными данными в BigQuery, например меньшему числу активных пользователей в отчетах по сравнению с показателями из BigQuery, поскольку при моделировании прогнозируется наличие нескольких сеансов от одного пользователя, который отклонил файлы cookie. Подробнее про моделирование читайте в этом материале.

Таким образом, после ознакомления с различными ограничениями в сборе, обработке и представлении данных в стандартных отчетах и Исследованиях, мы пришли к вполне очевидному и закономерному выводу - Google хочет «подсадить» как можно большее количество проектов и ресурсов GA4 на работу с Google Cloud и облачным хранилищем BigQuery. Только в нем вы сможете работать с данными без какой-либо выборки, пороговых значений и иметь возможность максимально гибко создавать нужные срезы, сводные таблицы и визуализации для своих итоговых дашбордов.

Несомненно, Google Analytics 4 стал новым стандартом в области веб-аналитики, предложив нам в 2020 году мощные инструменты для сбора и анализа данных о пользователях веб-сайтов и мобильных приложений. Но несмотря на все многообразие и гибкость, максимальную пользу от работы мы получаем только тогда, когда выходим за пределы его интерфейса. И это касается не только GA4, но и других сервисов Google.

Интеграция GA4 с BigQuery открывает новые возможности для анализа. Мы можем обрабатывать большие объемы данных без выборки, отслеживать путь каждого пользователя в отдельности или создавать пользовательские группы, сегментируя трафик нужным образом, строить собственные модели атрибуции, чтобы оценивать вклад каждого рекламного канала, объединять данные Google Analytics 4 со сторонними сервисами (например, CRM или рекламными инструментами), использовать накопленную статистику в качестве входных значений для прогнозирования будущих показателей (машинное обучение), а также визуализировать полученные данные в Looker Studio и других сервисах.

Умение писать SQL-запросы - один из ключевых навыков при работе с базами данных. Он высоко ценится работодателями при трудоустройстве на позицию аналитика данных. Это те навыки и умения, которые пригодятся вам уже в ближайшем будущем и настоящем, если вы желаете шагать в ногу со временем. В конце концов, если бы это не было так нужно и важно, то и Google не добавлял бы в Google Analytics 4 интеграцию с BigQuery. Ведь, если звезды зажигают — значит — это кому-нибудь нужно? (с)

Gemini в BigQuery

ChatGPT, Gemini, YandexGPT, Grok, Copilot, DeepSeek, чат-боты, нейросети, искусственный интеллект, машинное обучение... Для нас (интернет-маркетологов/веб-аналитиков) все это и вовсе не пустой звук, поскольку мы все время находимся в поиске инструментов, упрощающих выполнение задач и повышающих нашу эффективность.

Кто бы мог представить, что искусственный интеллект так быстро проникнет в BigQuery и откроет перед нами удивительные возможности работы с SQL даже для тех, кто раньше не имел ни малейшего представления о сырых данных и облачном хранилище Google, не знал с чего начать и как подступиться к его изучению? Не имея опыта и соответствующих навыков, интернет-маркетологу было страшно даже открывать Google Cloud, не говоря уже о написании собственного SQL-запроса в BigQuery. Все это казалось фантастическим еще пару лет назад и для меня. А теперь - реальность!

Gemini (чат-бот с искусственным интеллектом от Google) стал доступен в BigQuery в конце 2023 года. Для специалистов по работе с данными, включая инженеров данных, аналитиков данных и администраторов баз данных, он предлагает широкий спектр функций:

• исследование и анализ данных с помощью Data insights (помогают выявлять закономерности, оценивать качество данных и выполнять статистический анализ);
• поиск данных, преобразование, создание SQL и визуализация данных с помощью Data canvas;
• помощь в SQL и Python (помогает с написанием кода, исправлением ошибок, расшифровкой запросов и их описанием);
• подготовка данных к анализу (дает контекстно-зависимые рекомендации по преобразованию и улучшению данных таблиц, включая такие операции, как: трансформация, фильтрация, добавление/удаление столбцов и многое другое);
• оптимизация инфраструктуры (предлагает рекомендации по секционированию, кластеризации и материализованному представлению с целью повышения производительности и сокращения расходов);
• устранение неполадок для бессерверных конвейеров Spark.