GA4SQL - ваш помощник при построении SQL-запросов Google Analytics 4 в BigQuery

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

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

GA4SQL сформирует вам запрос, и вы легко сможете применить его в редакторе запросов самого BigQuery, получив после его выполнения заветный результат:

Чтобы использовать GA4SQL, сначала вам необходимо настроить связь Google Analytics 4 с Google BigQuery. После этого вы можете приступить к созданию первого запроса.

Итак, в поле Table Name вам нужно добавить название таблицы. Взять его вы можете в своем проекте BigQuery в Google Cloud, выбрав любую таблицу events_. На вкладке DETAILS в строке Table ID будет отображаться полный путь к вашей таблице:

Скопируйте часть, начинающуюся с analytics_ и до events_, и вставьте ее в первое поле инструмента:

В Date Range выберете нужный диапазон дат. Доступны следующие варианты:

• Yesterday - за вчера;
• Last Week - за прошлую неделю;
• Last month - за прошлый месяц;
• Month to yesterday - за месяц до вчерашнего дня;
• Last 7 days - за последние 7 дней;
• Last 14 days - за последние 14 дней;
• Last 30 days - за последние 30 дней;
• Year to yesterday - за год до вчерашнего дня;
• Last year - за прошлый год;

Если вам необходимо задать свой собственный диапазон дат, тогда используйте Custom date range с датой начала и датой конца:

В поле Metrics выберете те показатели, по которым хотите получить данные. Из выпадающего списка доступны следующие варианты:

1. Sessions (Сеансы)
2. The unique count of session id + pseudo user id
3. Engaged Sessions (Сеансы с взаимодействием)
4. These sessions are the ones that have triggered the engaged_session event.
5. Engagement Rate (Доля взаимод.)
6. Sessions Per User (Сеансов с взаимодействием на пользователя)
7. Average Session Duration
8. Average Engagement Time Per Session (Среднее время взаимодействия на сеанс)
9. Bounce Rate
10. Total Users (Всего пользователей)
11. Active Users (Активные пользователи)
12. New Users (Новые пользователи)
13. Returning Users (Вернувшиеся пользователи)
14. First Time Purchasers
15. Total Purchasers
16. Average Engagement Time Per User
17. Screen Page Views
18. Screen Page Views Per Session
19. Screen Page Views Per User
20. Event Count (Количество событий)
21. Event Value
22. Events Per User
23. Events Per Session
24. Purchases
25. Purchase Revenue
26. Purchase Revenue USD
27. Add To Carts
28. View Cart
29. Add To Wishlist
30. The number of add to wishlist events. For example, 19
31. Checkouts
32. Add Shipping Info
33. The number of add shipping info events. For example, 19
34. Add Payment Info
35. Item View Events
36. The number of view item events. For example, 19
37. Item List View Events
38. The number of view item list events. For example, 19
39. Promotion Views
40. Item Quantity
41. Item Revenue
42. Item Revenue USD
43. Item Refund
44. Item Refund USD

А в поле Dimensions укажите параметры для вашего запроса:

1. Session Source Medium (Источник / канал сеанса)
2. Session Source (Источник сеанса)
3. Session Medium (Канал сеанса)
4. Session Campaign Name
5. First User Source
6. First User Medium
7. First User Source Medium
8. First User Campaign Name
9. User Pseudo ID (Уникальный идентификатор пользователя, Client ID)
10. User ID (Идентификатор пользователя, определенный программно на вашем сайте или в мобильном приложении)
11. Landing Page
12. Landing Page Title
13. Page Location
14. Page Title
15. Host Name
16. Platform
17. Stream ID
18. Device Category
19. Mobile Device Branding
20. Mobile Device Model
21. Mobile Device Marketing Name
22. Operating System
23. Operating System Version
24. Browser (Браузер)
25. Browser Version
26. Language
27. Advertising ID
28. Vendor ID
29. App Version
30. App ID
31. App Install Store
32. Firebase App ID
33. Continent
34. Sub Continent
35. Country (Страна)
36. Region (Регион)
37. City (Город)
38. Metro
39. Date (Дата)
40. Week (Неделя)
41. Month (Месяц)
42. Year (Год)
43. Event Name (Название события)
44. Transaction ID (Идентификатор транзакции)
45. Item ID
46. Item Name
47. Item Brand
48. Item Variant
49. Item Category
50. Item Category2
51. Item Category3
52. Item Category4
53. Item Category5
54. Item List Name
55. Item List ID
56. Item List Position
57. Item Promotion Name
58. Item Promotion ID
59. Item Coupon
60. Item Promotion Creative Name
61. Item Promotion Creative Slot
62. Item Price

Если вы не знаете, что из себя представляет тот или иной параметр или показатель GA4, вы можете просто навести указатель мыши на значок справки рядом с каждым полем и просмотреть его определение. Это может помочь вам лучше понять, какие данные извлекаются и как они связаны с вашим анализом.

Сразу стоит отметить, что в некоторых случаях определенные параметры и показатели в Google Analytics 4 могут быть несовместимы друг с другом в рамках одного запроса, поэтому они будут недоступны для выбора или просто отключены. Если вы столкнулись с этой проблемой, попробуйте выбрать другие комбинации параметров и показателей, которые совместимы друг с другом.

Примечание: в запросе можно задавать показатели без параметров и параметры без показателей.

В качестве примера я буду использовать простой запрос по подсчету количества активных пользователей (Active Users) по своему проекту за три недели с 1 по 21 апреля 2023 года. В GA4SQL конфигурация запроса будет выглядеть вот так:

После нажатия на кнопку Generate Query в правой части страницы отобразится запрос на основе заданных настроек:

Сам код запроса:

Его можно скопировать с помощью кнопки Copy и вставить в редактор запросов BigQuery. Размер запроса получился 77,25 MB, а результат - 13119 активных пользователей:

Если не использовать сервис, а писать запрос самостоятельно, то его конструкция может выглядеть так:

Несмотря на это результат получился тем же - размер запроса 77,25 MB и 13119 активных пользователей:

GA4SQL при создании запросов использует подзапросы (subquerys), а не CTE (Common Table Expressions) с синтаксисом WITH table AS (...), поскольку разработка универсального шаблона запроса для всех параметров и показателей GA4 с использованием CTE является более сложной задачей и может привести к более длинным запросам с несколькими объединениями (multiple joins). Хотя подзапросы могут быть менее удобочитаемыми, разработчики сервиса решили отдать предпочтение функциональности, а не удобочитаемости. Разницы в производительности между CTE и подзапросами нет.

Полученное значение в BigQuery вы можете сравнить с данными из интерфейса Google Analytics 4. Например, через Исследование:

Как видите, результат в BigQuery отличается от интерфейсных значений (14346 vs 13119). Все дело в том, что данные в пользовательском интерфейсе GA4 проходят несколько этапов обработки. Вы, как владелец счетчика, можете отправлять данные в Google Analytics 4 с помощью одного из методов сбора - Google Tag, Google Tag Manager, Measurement Protocol , SDK и импорт данных. В зависимости от настроек вашего ресурса Google Analytics значительно увеличивает ценность собранных данных, прежде чем они попадут в итоговые отчеты GA4, включая стандартные отчеты, Исследования и Data API. Эти дополнительные преимущества могут включать в себя включение сигналов Google, моделирование, атрибуцию трафика, прогнозирование и т.д. Однако данные, экспортируемые в BigQuery, обычно представляют собой собранные данные самим счетчиком Google Analytics 4, без вышеупомянутых преимуществ и дополнительных сведений, которые вы передаете. Поэтому не удивляйтесь текущим расхождениям статистики в двух продуктах Google.

Примечание: не так давно в официальной документации Google вышла статья на тему различий статистики между пользовательским интерфейсом Google Analytics 4 и экспортом BigQuery. Обязательно прочтите ее, чтобы иметь детальное представление о расхождении данных в Google Analytics 4 и Google BigQuery.

Еще в GA4SQL есть возможность задания фильтра. Для этого используются поля Filters. В качестве фильтра можно выбирать как параметры, так и показатели Google Analytics 4. Доступны такие операторы:

• Equals (равно)
• Not equals (не равно)
• Contains (содержит)
• Not contains (не содержит)
• Greater than (больше, чем)
• Less than (меньше, чем)

Например, если вы желаете включить в запрос только статистику по источнику трафика google / organic, то можете добавить такой фильтр - Session Source Medium Equals google / organic:

В запросе можно использовать несколько условий фильтраций (добавляются с помощью +), объединяя их в логические ИЛИ (OR), когда может быть выполнен любой из добавленных критериев или И (AND), когда должно быть выполнено оба условия фильтра. Например, запрос, содержащий два источника трафика (google / organic ИЛИ yandex / organic), будет выглядеть так:

ga4sql.com - отличный инструмент (и пока бесплатный!) по созданию и проверке SQL-запросов для данных Google Analytics 4 в BigQuery. Если вы новичок в этой теме, никогда не работали с облачным хранилищем данных и только начали осваивать возможности Google Analytics 4, то без каких-либо сомнений GA4SQL может служить для вас хорошей отправной точкой при изучении SQL-команд и построении различных запросов к данных GA4 в BigQuery. Еще есть классный портал www.ga4bigquery.com, на котором вы так же можете найти много интересного и полезного для работы с Google Analytics 4 и Google BigQuery, включая примеры SQL-запросов.

Однако, не забывайте, что Google BigQuery - платный инструмент, и его ценообразование состоит из двух компонентов: хранения и анализа. То есть при каждом выполнении запроса вы расходуете свои ресурсы, а при достижении определенных ежемесячных лимитов начнете платить деньги. Плата за использование по требованию зависит от количества байтов, обработанных каждым запросом, и составляет 5 долларов за 1ТБ. Первый ТБ в месяц пользователям предоставляется бесплатно. Чем сложнее запрос (выбрали много различных параметров/показателей) и больше диапазон дат, тем его размер будет выше. Поэтому перед запуском запроса всегда проверяйте свои лимиты и его размер, который отображается в правом верхнем углу окна:

Дополнительная литература:

• Бесплатное электронное руководство по Google Analytics 4
• Книга "Learning Google BigQuery" (E. Brown, H. Thirukkumaran)