• Головна
  • /
  • Блог
  • /
  • Повний посібник з SQL для Google Analytics 4 у BigQuery: Від основ до просунутої маркетингової аналітики (частина 1)
кавер статті 33

Повний посібник з SQL для Google Analytics 4 у BigQuery: Від основ до просунутої маркетингової аналітики (частина 1)

photo

Макс Гапчук

Вебаналітик & Маркетолог
Автор

чт, 18 грудня 2025 р.

Google BigQuery

Екосистема цифрової аналітики стрімко змінюється, і разом із нею змінюється й сам підхід до роботи з даними. Якщо раніше можна було «жити» всередині інтерфейсу Google Analytics, то сьогодні — цього вже недостатньо.

Сирі дані, які GA4 передає у BigQuery, дають повний контроль над обчисленнями. Але щоб витягти з них користь, потрібна одна ключова навичка — SQL.

Цей посібник — детальне й практичне введення в SQL на прикладі сирих даних GA4. Ми будемо рухатися крок за кроком, з поясненнями, реальними кейсами та прикладами запитів.

Частина 1: Архітектура Експорту GA4 та фундаментальні оператори

Частина 2: Розрахунок кількості Користувачів та Сесій на даних експорту GA4 до BigQuery

Частина 3: Побудова Звіту "Сторінки та Екрани" (Pages and Screens)

Висновки

Частина 1: Архітектура Експорту GA4 та фундаментальні оператори

Перш ніж переходити до аналізу поведінки користувачів, необхідно зрозуміти, як GA4 зберігає дані всередині BigQuery. І якщо цього не розуміти, запити будуть або дорогими, або неправильними.

Дані експорту зберігаються не у вигляді агрегованих звітів, як в інтерфейсі, а у форматі сирих подієвих записів, тобто кожен рядок у таблиці BigQuery — це окрема подія: перегляд сторінки, клік, початок сесії, взаємодія з контентом, транзакція та інші івенти, які надсилає ваш сайт або застосунок.

Саме тому перед тим, як писати будь-які SQL-запити, важливо розуміти:

  • яку структуру мають таблиці експорту GA4;
  • які поля в них доступні;
  • які з цих полів є вкладеними структурами;
  • як об’єднувати щоденні таблиці в один запит і багато іншого, про що ми поговоримо далі.

Структура даних у BigQuery: з чого складається таблиця

Після налаштування експорту GA4 у BigQuery у вас з’являється датасет, у якому для кожного дня створюється окрема таблиця формату:

events_YYYYMMDD

Щоб зрозуміти, як звертатись до цієї таблиці на SQL, важливо знати загальну ієрархію BigQuery: Project > Dataset > Таблиці

33.1 data hierarchy in bigquery

У випадку GA4 датасет завжди має назву analytics_{ga4_property_id}, а таблиці - events_YYYYMMDD. Тобто кожна таблиця — це події за один день.

33.2 Events in table

Коли ви відкриваєте будь-яку з таких таблиць у BigQuery, у правій частині інтерфейсу відображається її SCHEMA — повний перелік доступних полів. На перший погляд може здатися, що це звичайна таблиця, але структура значно складніша: в деяких випадках, як наприклад, з event_params ви бачите додаткову вкладеність.

33.3 Schema

Як видно зі схеми, таблиця містить велику кількість різноманітної інформації. Ось лише приклад даних по одній події:

33.4 Event parameters in a table

Серед полів, з якими ми працюватимемо найчастіше:

  • event_date — дата події по часовому поясу GA4;
  • event_timestamp — точний таймстемп (час) події в UNIX форматі;
  • event_name — назва події;
  • event_params — масив параметрів, у якому можуть міститися URL сторінки, utm-мітки, session_id, screen_name та інші значення;
  • user_pseudo_id — унікальний ідентифікатор користувача;
  • traffic_source — джерело, канал та кампанія трафіку з якого користувач вперше відвідав сайт;
  • device, geo— набори вкладених полів із даними про пристрій та гео.

Офіційна документація Google має повний опис схеми експорту GA4, включно з типами даних та їх вкладеністю. Рекомендую звертатися до неї щоразу, коли ви працюєте з новими параметрами або шукаєте специфічні значення.

Тепер, коли ви трохи зрозуміли схему даних - давайте напишемо перші запити.

Фундаментальні Оператори SQL

1.1 Оператор SELECT: як вибирати лише потрібні дані

Оператор SELECT - це фундаментальна частина будь-якого SQL-запиту. Його завдання — визначити, яку саме інформацію потрібно повернути з таблиці. Повний синтаксис може бути складним, але базово виглядає так:

sql
SELECT  перелік полів таблиці, які хочете вивести
FROM таблиця

Про оператор FROM ми говоримо далі.

Вибір усіх полів таблиці

У найпростішому випадку оператор SELECT використовується разом зі спеціальним символом *, який означає «усі поля таблиці»:

sql
SELECT * 
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
33.5 Run SQL request

Такий запит повертає повну структуру таблиці — всі поля, (включно з вкладеними масивами та RECORD-структурами, про які ми поговоримо далі).

На практиці, звісно, такий запит не має сенсу, окрім як для пояснення основ, оскільки у вкладці Preview BigQuery можна побачити аналогічний вигляд.

33.6 Preview table

У реальних задачах краще явно вказувати, які саме поля потрібні. Тому далі ми переходимо до більш точного вибору полів.

Вибір конкретних полів

Щоб отримати тільки необхідні стовпчики, ви просто перелічуєте їх назви у SELECT: наприклад, ось таким запитом, я можу отримати тільки назву події та дату, коли вона була виконана:

sql
SELECT
  event_name,
  event_date
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
33.7 SELECT

Виконавши цей запит, або просто глянувши на скрін вище, у вас могло б з'явитися наступне запитання:

Чому в результаті є багато однакових рядків?

Відповідь проста: тому що SELECT повертає усі значення з вибраних стовпців у тому вигляді, в якому вони є в таблиці. Якщо в таблиці є 300 подій які відбулись в один і той же день - SELECT поверне 300 рядків.

Якщо потрібно отримати лише унікальні значення, слід додатково використовувати DISTINCT.

Оператор DISTINCT

DISTINCT дозволяє отримати унікальні значення поля або унікальні комбінації значень кількох полів. Давайте спробуємо отримати всі можливі комбінації параметрів джерела/каналу трафіку, з якого користувач вперше відвідав наш сайт:

sql
SELECT DISTINCT
  traffic_source.source,
  traffic_source.medium
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
33.8 SELECT DISTINCT

Це дозволяє побачити:

  • які саме джерела присутні в таблиці;
  • які комбінації source + medium зустрічалися.

Важливо: DISTINCT застосовується до всього списку полів після SELECT.

Чому в запиті назви полів інколи пишуться через крапку?

У таблицях GA4 частина даних зберігається не окремими стовпчиками, а у вигляді вкладених структур, які містять інші поля всередині. BigQuery дозволяє звертатися до внутрішніх значень через крапку. Таким чином:

  • traffic_source — це одне поле таблиці (стовпчик), всередині якого є інші параметри;
  • traffic_source.source — це вже конкретне вкладене значення всередині цього поля.
  • traffic_source.medium — так само, звернення до іншого внутрішнього значення.

Тобто крапкова нотація — просто спосіб “дійти” до потрібного параметра всередині складної структури.

33.9 RECORD

1.2 Оператор FROM

Оператор FROM визначає, звідки саме BigQuery має взяти дані для запиту. У контексті експорту GA4 він працює максимально прямо: ми вказуємо конкретну таблицю або таблиці, з яких потрібно отримати дані. У BigQuery, як вже розбиралось вище, структура завжди однакова:

Project (Проєкт) -> Dataset (Датасет) -> Table (Таблиця)

Саме тому у FROM ми завжди бачимо шлях типу:

sql
project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118

Це означає:

  • project-2fdab5d4-7e4c-43c5-ab8 — проєкт;
  • analytics_000001 — датасет;
  • events_20240118 — таблиця з подіями за 18 січня 2024 року.

Оскільки BigQuery — це платний сервіс із безкоштовними лімітами, перш ніж ми продовжимо розбирати основні SQL-оператори, важливо зрозуміти ціноутворення.

Вартість SQL запитів в BigQuery

Кожного разу, коли ви пишете SQL-запит, система рахує, скільки байтів інформації буде оброблено для його виконання. Саме за ці байти ви і платите. Тобто BigQuery працює за моделлю «оплата за використання».

33.10 Requests cost

При цьому, сервіс щомісяця надає базовий безкоштовний обсяг, якого зазвичай достатньо для малого бізнесу. Зокрема:

  • 1 TiB обробки даних для SQL-запитів;
  • 10 ГіБ зберігання даних у таблицях.

Оскільки ці квоти оновлюються щомісяця, для невеликих проєктів вони зазвичай повністю закривають потреби в аналізі даних. Для великих компаній витрати на BigQuery, хоча й можуть складати декілька сотень, а інколи й тисяч доларів, завжди залишаються прогнозованими, адже оплата нараховується лише за той обсяг даних, який був реально опрацьований або збережений. Офіційна інформація про вартість доступна в документації Google Cloud.

1.3 Оператор WHERE: Фільтрація та Економія Ресурсів

Оператор WHERE використовується для того, щоб відібрати з таблиці лише ті рядки, які відповідають певній умові. Найпростіше уявити його як аналог фільтра в Excel або Google Sheets: ви вказуєте поле та умову — і у відповідь отримуєте тільки потрібні рядки.

Це ключовий інструмент у роботі з BigQuery, адже таблиці GA4 містять тисячі й мільйони подій, і без фільтрації аналіз практично неможливий. Давайте розберемо деякі приклади використання цього оператора.

Порівняння значень: >, <, !=

Найпростіший запит з фільтрацією виглядає так:

sql
SELECT
  event_name,
  traffic_source.source
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
WHERE traffic_source.source = 'google';

І в результаті отримаємо таблицю лише з тими подіями, де джерело трафіку (з якого користувач вперше відвідав сайт) дорівнює "google".

33.11 WHERE

Так само можна виключити певні значення за допомогою умови “не дорівнює” (!=):

sql
SELECT
  event_name,
  traffic_source.source
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
WHERE traffic_source.source != 'google';

При цьому WHERE може працювати не тільки з текстом, а й з числами або датами, завдяки операторам >, <, >=, <= .

Фільтрація за кількома умовами

Умови можна поєднувати через AND (виконуються всі) або OR (виконується хоча б одна):

sql
SELECT
  event_name,
  traffic_source.source,
traffic_source.medium
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
WHERE traffic_source.source = 'google'
  AND traffic_source.medium = 'organic'
33.12 WHERE AND

Фільтрація за частиною слова: оператор LIKE

Іноді потрібно знайти всі рядки, де поле містить певний фрагмент тексту. Для цього використовується оператор LIKE:

sql
SELECT
  event_name,
  traffic_source.source
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
WHERE traffic_source.source LIKE '%facebook%'

Символи % означають “будь-яку кількість будь-яких символів”. Тобто в результаті ми повернемо всі варіації джерела facebook: m.facebook, lm.facebook і т.д.:

33.13 LIKE

ФІльтрація за регулярними виразами (REGEX)

Ну і якщо простих умов недостатньо - завжди можна підключити регулярні вирази, використовуючи REGEXP_CONTAINS. Наприклад, в цьому випадку ми повернемо всі події де джерело трафіку відносилось до google чи facebook:

sql
SELECT
  event_name,
  traffic_source.source
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
WHERE REGEXP_CONTAINS(traffic_source.source, 'google|facebook')

Тобто LIKE підходить для простих текстових шаблонів (“містить”, “починається з”, “закінчується на”), а REGEXP_CONTAINS підтримує повноцінні регулярні вирази для складних умов пошуку: варіантів, патернів, довжини, класів символів та пошуку по словах.

33.14 REGEXP_CONTAINS

Фільтрація за датою (_TABLE_SUFFIX)

Ну і найцікавіше: у BigQuery дані GA4 зберігаються у вигляді щоденних таблиць, наприклад: events_20240101, events_20240102, events_20240103. Частина після підкреслення (20240101) — це суфікс таблиці.

Щоб звернутися одразу до кількох таблиць усередині одного датасету, чиї назви починаються однаково, у FROM використовують шаблон: events_*, наприклад:

sql
SELECT *
FROM `project.dataset.events_*`

Якщо потрібно вибрати дані лише за певний період, то ми ставимо зірочку після частини дати:

sql
SELECT *
FROM `project.dataset.events_202401*`
33.15 Data for exact period

А іноді потрібно взяти дані лише за певний діапазон дат. І ось тут дуже важливе: я часто зустрічаю, як ті, хто починають свій шлях в BigQuery пишуть щось типу:

sql
SELECT
  event_name,
  traffic_source.source
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_*`
WHERE event_date BETWEEN '20240101' AND '20240131';

Але хоч з точки зору синтаксису SQL це і правильно, з точки зору виставлення рахунків за BigQuery це одна з найчастіших помилок. У випадку попереднього запиту ви оплатите всі дані GA4, які у вас є в датасеті, навіть за ті дати, які вам не потрібні.

Щоб уникнути зайвих платежів, використовуйте спеціальну псевдоколонку _TABLE_SUFFIX. Виглядати запит буде так:

sql
SELECT *
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

Обидва попередні запити повертають дані лише за 1–31 січня 2024 року. Різниця тільки в тому, що в першому випадку ви оплатите всі дані, які є в датасеті. А в другому, тільки за 1–31 січня 2024 року.

33.16 BETWEEN dates

Зверніть увагу, оператор BETWEEN включає в діапазон обидва кінцеві значення, тобто в нашому випадку і 1 і 31 січня.

Чому через _TABLE_SUFFIX, а не event_date?

Фільтрація через _TABLE_SUFFIX дозволяє BigQuery пропускати таблиці, які не потрапляють у діапазон, ще до їхнього сканування. Це робить запит значно швидшим і дешевшим порівняно з фільтрацією за полем event_date.

Тепер ви можете фільтрувати дані та обирати тільки потрібні колонки з таблиці, але це лише початок: найцікавіше починається з розрахунків основних показників. Саме це ми і розберемо далі.

Частина 2: Розрахунок кількості Користувачів та Сесій на даних експорту GA4 до BigQuery

Робота з даними GA4 у BigQuery починається з розуміння того, як правильно рахувати користувачів. Тому почнемо ми з них, а потім розберемо нюанси підрахунку інших показників.

Ну і щоб ми не просто розбирали якісь розрахунки в вакуумі в кінці всьго процесу, ми застосуємо отримані знання для побудови звіту Pages and Screens (Сторінки та екрани).

2.1 Функція COUNT у поєднанні з DISTINCT: Підрахунок Користувачів

У моделі даних GA4 базовим технічним ідентифікатором є user_pseudo_id — автоматично згенерований GA4 анонімний клієнтський ID, який прив’язує всі події до конкретного браузера або пристрою. Він присутній у кожному event-рядку, тому саме через нього здійснюється підрахунок унікальних користувачів.

33.17 user_pseudo_id

Для підрахунку чогось в SQL використовується функція COUNT, яка підраховує кількість записів у таблиці. Наприклад, ось так я можу отримати кількість подій в таблиці:

sql
SELECT COUNT(event_name) 
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`

Але щоб підрахувати кількість користувачів, нам потрібно перерахувати тільки унікальні user_pseudo_id, тому в додаток до COUNT використаємо DISTINCT, або іншими словами метод COUNT(DISTINCT...). Виглядати запит буде ось так:

sql
SELECT COUNT(DISTINCT user_pseudo_id) 
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`

Як бачимо в обраний період 58 унікальних користувачів відвідало наш сайт.

33.18 COUNT DISTINCT

Зверніть увагу, функція COUNT не рахує значення, які дорівнюють NULL. Це означає, що підраховуються лише ті рядки, де поле, яке ви вказуєте, містить реальне значення.

Якби ми не використали DISTINCT, тоді нам би повернулося число 1971:

33.19 failed user id

Тобто ми б отримали лише загальну кількість рядків (переданих подій), які містять user_pseudo_id.

33.20 all rows result

На основі цих підрахунків можна будувати й інші метрики. Наприклад, якщо ви хочете зрозуміти, скільки подій у середньому виконує один користувач, можна одразу обчислити все в одному запиті:

sql
SELECT
  COUNT(user_pseudo_id),
  COUNT(DISTINCT user_pseudo_id),
  COUNT(user_pseudo_id) / COUNT(DISTINCT user_pseudo_id)
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`

Зверніть увагу на важливий момент: так, ми можемо робити розрахунки прямо в SELECT — наприклад, поділити кількість подій на кількість унікальних користувачів. І саме це робить SQL таким потужним.

От тільки подивіться на результат, так, наші показники розрахувались, але назви колонок…

33.21 column name AS

AS для найменування колонок

Щоб колонки результатів було зручно читати, ви можете задати назву для них. Для цього використовується оператор AS. Давайте вкажемо назви для наших колонок і подивимось на результат повторно:

sql
SELECT
  COUNT(user_pseudo_id) AS events,
  COUNT(DISTINCT user_pseudo_id) AS unique_users,
  COUNT(user_pseudo_id) / COUNT(DISTINCT user_pseudo_id) AS avg_events_per_user
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
33.22 AS operator

Стало набагато краще.

Функції для математичних розрахунків

Окрім підрахунку користувачів і подій, у SQL можна виконувати інші математичні обчислення. Це дозволяє створювати додаткові метрики прямо в запитах і аналізувати поведінку користувачів у різних розрізах. Ми вже розглянули COUNT, шо рахує кількість подій у таблиці, але існують й інші:

  • SUM — повертає суму всіх числових елементів у колонці. Корисно, наприклад, для підрахунку доходу за період.
sql
SELECT
  SUM(ecommerce.purchase_revenue) AS purchase_revenue
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
WHERE event_name = "purchase"
  • AVG — обчислює середнє значення числової колонки. Функція працює лише з числовими типами даних. Це може бути корисно наприклад при розрахунку середнього чеку.
sql
SELECT AVG(ecommerce.purchase_revenue) AS avg_check
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
WHERE event_name = "purchase"
  • MIN/MAX — повертає найменше/найбільше значення у вибраному полі. Наприклад, ми можемо визначити першу та останню подію за день:
sql
SELECT 
  MIN(event_timestamp) AS first_event_received,
  MAX(event_timestamp) AS last_event_received
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
33.23 MIN MAX

Щоб перетворити значення у більш читабельний формат datetime, можна використати TIMESTAMP_MICROS():

sql
SELECT
  TIMESTAMP_MICROS(MIN(event_timestamp)) AS first_event_received,
  TIMESTAMP_MICROS(MAX(event_timestamp)) AS last_event_received
FROM  `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
33.24 TIMESTAMP_MICROS

Звісно, існує набагато більше таких функцій, але ідею, думаю, ви зрозуміли. Детальніше тут.

2.2 Оператор CONCAT: як отримати ID Сесії та порахувати кількість сесій на даних експорту GA4 до BigQuery

Ще одна часта помилка, яку я зустрічаю в тих, хто тільки починає свій шлях у BigQuery - неправильний підрахунок кількості сесій. В даних експорту є значення ga_session_id і новачки часто сприймають його за унікальний id сесії. Але це помилка. ga_session_id — це timestamp, тобто час початку сесії, округлений до секунди.

Через це можливий наступний сценарій:

  • два різні користувачі можуть почати сесію в одну і ту саму секунду,
  • у результаті обидва отримають однаковий ga_session_id,
  • тому умовний COUNT(DISTINCT ga_session_id) поверне занижену кількість сесій, оскільки сесії різних відвідувачів з однаковим timestamp початку сесії будуть пораховані, як одна сесія.

Щоб отримати коректну кількість сесій, потрібно сформувати унікальний ключ, який поєднує і ідентифікатор користувача, і час початку сесії. Але перед тим як ми це зробимо, нам потрібно розібрати ще один важливий момент.

2.3 Вкладені поля (Nested Fields) та роль UNNEST у роботі з даними GA4

Однією з головних відмінностей експорту GA4 у BigQuery від багатьох інших табличних даних є те, що дані події не зберігаються у вигляді плоскої таблиці з десятками окремих колонок. Натомість GA4 використовує вкладену структуру даних.

Найпоказовішим прикладом є поле event_params. На перший погляд це одна колонка, але всередині вона містить масив об’єктів типу RECORD, де кожен елемент — це пара:

  • key — назва параметра події (наприклад, page_location, page_title, ga_session_id)
  • value — об’єкт з кількома полями: string_value, int_value, float_value, double_value.

Таким чином, замість десятків окремих колонок GA4 «пакує» параметри у вкладену структуру, що робить таблицю з однієї сторони гнучкою, а з іншої - складнішою для читання.

33.25 RECORD event_params

Щоб працювати з цими даними, ми не можемо просто написати SELECT event_params.page_location. Тому що такої колонки не існує: значення параметра знаходиться всередині масиву, і до нього потрібно звертатися за ключем. Нам потрібно "розпакувати" цей масив. Для цього використовується функція UNNEST:

sql
SELECT
  event_date,
  event_name,
  user_pseudo_id,
  param.key,
  param.value.string_value,
  param.value.int_value,
  param.value.float_value
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`,
  UNNEST(event_params) AS param;

У результаті кожен елемент масиву event_params стає окремим рядком для кожної з подій.

33.26 UNNEST event_params

Отже, UNNEST використовується для розгортання (розпакування) масивів та вкладених структур у рядках таблиці в BigQuery.

Скалярні Підзапити — практичний спосіб працювати з параметрами GA4

Тепер коли ми зрозуміли що робить функція UNNEST, спробуємо отримати значення конкретного параметра для кожної події. Щоб отримувати значення параметра без дублювання рядків, у BigQuery використовують так звані скалярні підзапити (scalar subqueries). Це маленький запит всередині SELECT, який:

  1. виконує UNNEST тільки для одного конкретного рядка;
  2. знаходить потрібний параметр за ключем;
  3. повертає лише одне значення;
  4. додає його як нову колонку.

Наприклад, давайте витягнемо значення параметра ga_session_id у масиві event_params і повернемо його як звичайну колонку.

sql
SELECT
  event_date,
  event_name,
  (SELECT value.int_value
   FROM UNNEST(event_params)
   WHERE key = 'ga_session_id') AS ga_session_id
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`;

У цьому запиті може виникнути питання, що значить value.int_value. У масиві event_params кожен параметр має структуру key -> value, де value — це поле типу RECORD з кількома можливими варіантами значень:

  • string_value — якщо параметр є текстовим;
  • int_value — якщо значення ціле число;
  • double_value або float_value — якщо це число з крапкою.

Тому value.int_value означає: «візьми цілочислове значення параметра».

33.27 value parameter structure

У результаті отримуємо додаткову колонку зі значенням ga_session_id:

33.28 get ga_session_id

Така техніка є стандартною у роботі з даними GA4 в BigQuery у випадках, коли необхідно діставати вкладені значення, перелік яких доволі великий, зокрема, але не виключно: page_location, page_title, ga_session_id, всі кастомні параметри подій.

Ну що ж, тепер коли ми можемо отримати значення ga_session_id давайте отримаємо реальний ID сесії та підрахуємо їх кількість.

Для цього використаємо новий оператор CONCAT, який об’єднує кілька значень у рядок. Давайте сформуємо цей унікальний real_session_id:

sql
SELECT
  user_pseudo_id,
  (SELECT value.int_value
   FROM UNNEST(event_params)
   WHERE key = 'ga_session_id') AS session_start_time,
  CONCAT(
    user_pseudo_id,
    '-',
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'ga_session_id')
  ) AS real_session_id
FROM
  `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`;

У такому форматі кожна сесія кожного користувача отримує власний, унікальний ідентифікатор, що дозволяє точно будувати метрику sessions у BigQuery.

Важливо: якщо хоча б один аргумент функції CONCAT NULL, то й результат також буде NULL. В нашому випадку це ролі не грає. Але загалом знати це важливо, інакше в деяких складніших розрахунках ви можете отримати не те, що очікували.

33.29 CONCAT

Для підрахунку кількості сесій можна скористатись таким SQL запитом:

sql
SELECT
  COUNT(DISTINCT
    CONCAT(
    user_pseudo_id,
    '-',
    (SELECT value.int_value 
     FROM UNNEST(event_params) 
     WHERE key = 'ga_session_id')
    )
  ) AS sessions
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`;
33.30 Count sessions number

Альтернатива CONCAT: оператор ||

У BigQuery також можна робити конкатенацію записом:

sql
user_pseudo_id || '-' || ga_session_id

2.4 GROUP BY - виводимо розрахунки в розрізі потрібних параметрів

Після того як ми навчилися обчислювати кількість подій, кількість унікальних користувачів та кількість сесій, виникає потреба подивитися ці значення у певному розрізі. Для цього використовують оператор GROUP BY.

GROUP BY об’єднує рядки таблиці за значеннями певних полів і дозволяє застосовувати агрегатні функції (COUNT, SUM, AVG тощо) для кожної групи окремо.

Наприклад, давайте порахуємо:

  • скільки подій виконали користувачі на різних типах пристроїв,
  • і скільки унікальних користувачів було на кожному з них.
sql
SELECT
  device.category,
  COUNT(user_pseudo_id) AS events,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
GROUP BY device.category;

І ми побачимо таблицю, де кожен рядок — це окрема категорія пристрою (desktop/mobile), а поруч — кількість подій та користувачів.

33.31 GROUP BY device.category

Ми також можемо групувати за кількома параметрами. Наприклад, подивімося метрики в розрізі пристрою та джерел трафіку (з яких користувач вперше відвідав сайт):

sql
SELECT
  device.category,
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
GROUP BY
  device.category,
  traffic_source.source,
  traffic_source.medium;

І отримаємо таку таблицю:

33.32 Group by several parameters

2.5 ORDER BY - як відсортувати результати

Після того як метрики розраховані, результати зазвичай потрібно впорядкувати. Ми можемо це зробити за допомогою оператора ORDER BY, який дозволяє сортувати значення в таблиці. Наприклад, впорядкувати за спаданням/зростанням (функції DESC/ASC). Давайте впорядкуємо наш попередній запит за спаданням по кількості користувачів:

sql
SELECT
  device.category,
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
GROUP BY
  device.category,
  traffic_source.source,
  traffic_source.medium
ORDER BY users DESC;
33.33 ORDER BY

Текстові значення будуть відсортовані по алфавіту.

Частина 3: Побудова Звіту "Сторінки та Екрани" (Pages and Screens)

Тепер давайте обєднаємо всі отриманні знання і побудуємо ваш перший звіт з інтерфейсу GA4 на сирих даних. Я обрав один з найпростіших для розуміння звітів Pages and Screens (Сторінки й екрани). Саме він показує, який контент отримує найбільше уваги, як користувачі взаємодіють зі сторінками та скільки подій там відбувається.

У цій частині статті ми відтворимо цей звіт повністю у SQL, крок за кроком — так, щоб він працював без семплінгу, без обмежень інтерфейсу та з повним контролем над логікою обчислень.

3.0 Структура звіту Pages and Screens: які параметри та метрики ми маємо повторити

Перш ніж переходити до SQL, треба чітко розуміти, які саме дані присутні у звіті GA4. Google офіційно описує структуру звіту у своїй довідці.

Параметри у звіті Pages and Screens (Сторінки й екрани):

  • Content group (Група контенту) — група контенту.
  • Hostname — домен.
  • Page path and screen class (Шлях до сторінки й клас екрана) — шлях сторінки (разом із query string) або клас екрана.
  • Page title and screen class (Назва сторінки й клас екрана) — заголовок сторінки чи клас екрана.
  • Page title and screen name (Назва сторінки й екрана) — заголовок сторінки чи ім’я екрана.

Для статті ми сфокусуємося на найпопулярнішому параметрі — Page Path (Шлях до сторінки), бо саме з нього зазвичай починають аналіз і саме він є основою звіту в інтерфейсі.

33.34 Pages and Screens report

Метрики у звіті:

  • Views (Перегляди) — перегляди сторінки.
  • Active users (Активні користувачі) — це користувачі, які по мірках команди GA4 були активні в період аналізу. Я вважаю, що активність на кожному проекті має рахуватись по своєму, тому ми будемо рахувати Total Users, а не Active Users, але все по порядку.
  • Views per active user (Кількість переглядів на активного користувача) — середня кількість переглядів на активного користувача, в нашому випадку просто на користувача.
  • Average engagement time per active user (Середній час взаємодії на активного користувача) — середній час взаємодії зі сторінкою на одного користувача.
  • Event count (Кількість подій) — кількість подій.
  • Key Events (Основні події) — кількість ключових подій (конверсій).
  • Total revenue (Загальний дохід) — дохід, сформований на сторінці.
33.35 Pages and Screens report metrics

Усі ці метрики ми відтворимо в BigQuery.

3.1 Виводимо параметр page_location

У звіті Pages and Screens (Сторінки й екрани) перший стовпчик — це параметр Page path and screen class (Шлях до сторінки й клас екрана). Саме він задає розріз, у якому показуються всі інші метрики (Views, Users, Event count тощо).

У сирих даних GA4 в BigQuery шлях до сторінки зберігається в параметрі page_location всередині масиву event_params. Це поле містить повний URL сторінки (разом із доменом та параметрами запиту). На першому кроці нас влаштовує саме page_location — ми просто навчимося його діставати.

Давайте отримаємо page_location за допомогою UNNEST. Починаємо з простого запиту, який:

  • бере таблицю подій GA4
  • розпаковує масив event_params функцією UNNEST,
  • витягує з нього тільки значення з ключем page_location,
  • додає його в окрему колонку page_location.
sql
SELECT
  -- Витягуємо URL сторінки з event_params
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_location,
  -- Виводимо всі інші стандартні колонки події
  *
FROM `project_name.dataset_name.events_YYYYMMDD`;

Оскільки це скалярний підзапит у SELECT, ми отримуємо одне значення page_location на кожен рядок events-таблиці. Далі * додає всі інші стандартні колонки (event_name, event_timestamp, user_pseudo_id тощо), щоб було зручно звірятися з даними.

Як результат — у нас з’являється перший стовпчик майбутнього звіту Pages and Screens (Сторінки й екрани): URL сторінки, за яким ми згодом будемо групувати всі метрики.

3.2 Розрахунок Метрик

Маючи базовий параметр page_location, ми можемо переходити до побудови метрик зі стандартного звіту Pages and Screens (Сторінки й екрани).

3.2.1 Метрика Views (Перегляди)

Перегляд — це подія page_view. Щоб порахувати кількість переглядів, найзручніше використати функцію COUNTIF, яка рахує тільки ті рядки, що відповідають певній умові.

sql
COUNTIF(event_name = 'page_view') AS views

Врахуйте, що у вебі це подія page_view, але якщо вам потрібно для мобільних додатків використовуйте — screen_view.

3.2.2 Метрика Total Users (Усього користувачів)

Тут ви вже знаєте, ми використовуємо COUNT(DISTINCT...) по колонці user_pseudo_id — унікальному ідентифікатору браузера/пристрою в GA4. Оскільки ми групуємо по сторінці, це покаже кількість унікальних користувачів, які відвідали конкретну сторінку.

sql
COUNT(DISTINCT user_pseudo_id) AS users

Це кількість всіх користувачів, які взаємодіяли зі сторінкою.

У стандартних звітах GA4 показник “Users” означає Active users (Активні користувачі), але в BigQuery ми отримуємо Total users (Усього користувачів), тобто всіх користувачів без фільтра активності. Це нормальна й очікувана відмінність.

3.2.3 Метрика Views per user (середня кількість переглядів на користувача)

Після того як ми отримали базові метрики — кількість переглядів (views) та кількість користувачів (users), — можна переходити до розрахунку похідних показників. Одним з ключових є Views per user (середня кількість переглядів на користувача), тобто середня кількість переглядів, яку здійснив один користувач на конкретній сторінці.

У GA4 цей показник відображає, наскільки “повертаються” користувачі на сторінку та як часто вони з нею взаємодіють. У BigQuery його легко відтворити у вигляді простого ділення однієї метрики на іншу.

Формула складається з двох частин, які ми вже розрахували:

  • чисельник: кількість подій page_view
  • знаменник: кількість унікальних користувачів user_pseudo_id

Підсумкова формула виглядає так:

sql
COUNTIF(event_name = "page_view") / COUNT(DISTINCT user_pseudo_id) AS views_per_user

Тобто ми просто ділимо кількість переглядів на кількість людей, які здійснили ці перегляди.

3.2.4 Метрика Average engagement time per user (Середній час взаємодії на користувача)

Середній час взаємодії — один із ключових поведінкових показників GA4. Він відображає не просто час перебування на сторінці, а період, коли користувач реально взаємодіє зі сторінкою або бачить її у фокусі браузера.

У BigQuery ця метрика не існує як готове поле, її потрібно відтворити вручну, використовуючи параметр engagement_time_msec. Це значення передається у кожному івенті, який відправляється у період активної взаємодії користувача з сайтом.

Як GA4 формує engagement_time_msec

У GA4 взаємодія — це неперервний процес. Поки сторінка активна, браузер регулярно відправляє події (scroll, user_engagement тощо) з накопиченим часом.

Наприклад, це може бути такий хід подій:

Подіяengagement_time_msecЩо означає

scroll

8781

Користувач прокрутив сторінку ~8.7 сек

user_engagement

11856

Користувач перейшов на наступну сторінку. Система передала ще 11.8 сек взаємодії

Ці значення — частини одного й того самого накопичувального часу. Щоб його отримати, треба додавати всі ці шматочки.

Отже, давайте збирати Average engagement time per user (Середній час взаємодії на користувача) у SQL:

  1. Витягуємо значення engagement_time_msec із event_params

Це окремий параметр у масиві event_params. Для кожного рядка таблиці виконується скалярний підзапит:

sql
(SELECT value.int_value
 FROM UNNEST(event_params)
 WHERE key = "engagement_time_msec")

Цей підзапит повертає:

  • або число (мс),
  • або NULL (якщо подія не має інформації про час взаємодії).

2. Сумуємо всі значення для конкретної сторінки

Кожен рядок містить частину взаємодії, тому всі значення потрібно додати:

sql
SUM(
  (SELECT value.int_value
   FROM UNNEST(event_params)
   WHERE key = "engagement_time_msec")
)

Це дає загальний engagement time для всіх подій на сторінці.

3. Ділимо на кількість користувачів.

sql
SUM(...) / COUNT(DISTINCT user_pseudo_id)

4. Переводимо мілісекунди у хвилини

  • 1000 — мілісекунди в секунду
  • 60 — секунди в хвилину
sql
/ 1000 / 60

5. З’єднуємо все в фінальну формулу метрики:

sql
SUM(
  (SELECT value.int_value
   FROM UNNEST(event_params)
   WHERE key="engagement_time_msec")
) / COUNT(DISTINCT user_pseudo_id) / 1000 / 60 AS avg_engagement_time

3.2.5 Метрика Event count (Кількість подій)

Ця метрика показує, скільки подій відбулося на сторінці. Наприклад, у моїх тестових даних є подія з назвою click_button яка позначає клік на кнопку.

sql
COUNTIF(event_name = "click_button") AS event_count

Важливо зберігати саме таке написання події, як у даних GA4.

Таким же способом можна додавати у звіт інші івенти — наприклад: scroll, file_download, sign_up тощо.

3.2.6 Метрика Key Events (Основні події)

У GA4 ключові події — це ті, що позначені як Key events (Основні події). У SQL ми можемо рахувати будь-які події. Наприклад, давайте використаємо подію purchase (покупка).

sql
COUNTIF(event_name = "purchase") AS conversions

3.2.7 Метрика Total revenue (Загальний дохід)

У BigQuery дохід від покупки доступний через поле ecommerce.purchase_revenue.

sql
SUM(ecommerce.purchase_revenue) AS total_revenue

Зверніть увагу:

  • якщо сайт не використовує eCommerce, значення буде 0;
  • якщо на сторінці немає транзакцій, значення буде 0.

3.3 Фінальний Зведений Запит

На цьому кроці ми вже маємо все необхідне:

  • параметр, за яким будуємо розріз — page_location;
  • набір метрик: views, users, views_per_user, avg_engagement_time, event_count, conversions, total_revenue.

Залишається об’єднати їх в один запит, згрупувати за сторінкою і відсортувати.

sql
SELECT
  -- Параметр: шлях сторінки (Page path)
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = "page_location") AS page_location,
  -- Метрика: перегляди
  COUNTIF(event_name = "page_view") AS views,
  -- Метрика: користувачі
  COUNT(DISTINCT user_pseudo_id) AS users,
  -- Метрика: перегляди на користувача
  COUNTIF(event_name = "page_view") / COUNT(DISTINCT user_pseudo_id) AS views_per_user,
  -- Метрика: середній час взаємодії (хвилини)
  SUM(
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = "engagement_time_msec")
  ) / COUNT(DISTINCT user_pseudo_id) / 1000 / 60 AS avg_engagement_time,
  -- Метрика: кількість подій
  COUNTIF(event_name = "click_button") AS event_count,
  -- Метрика: конверсії (key events)
  COUNTIF(event_name = "purchase") AS conversions,
  -- Метрика: дохід
  SUM(ecommerce.purchase_revenue) AS total_revenue
FROM `project_name.dataset_name.events_YYYYMMDD`
GROUP BY
  page_location
ORDER BY
  views DESC;
33.36 Pages and Screens in BigQuery

Вітаю, тільки що ви побудували свій перший звіт на сирих даних GA4.

Висновки

Робота з експортом GA4 у BigQuery відкриває перед аналітиками зовсім інший рівень можливостей. Те, що в інтерфейсі подається у вигляді готових метрик і агрегованих звітів, у BigQuery перетворюється на сирі дані — і саме SQL стає інструментом, який дозволяє зібрати ці події у змістову аналітичну картину.

Основні моменти, на яких будується робота з даними GA4 у BigQuery, можна сформулювати так:

  1. Структура таблиць визначає логіку аналізу.
    GA4 експортує події у вигляді вкладених структур та масивів. Без розуміння моделі даних складно коректно витягнути навіть базовий параметр. Вміння працювати з event_params через UNNEST та скалярні підзапити — це фундамент.
  2. Ідентифікація користувачів і сесій — це не “очевидні” операції.
    user_pseudo_id дозволяє швидко рахувати унікальних користувачів по логіці device-based, а ga_session_id потребує додаткової обробки, щоб уникнути повторів між різними користувачами.
  3. SQL дозволяє відтворювати звіти GA4 без обмежень.
    Ми побудували всі ключові метрики Pages and Screens (Сторінки й екрани) вручну — від Views (Перегляди) і Total users (Усього користувачів) до Average engagement time per user (Середній час взаємодії на користувача) та Total revenue (Загальний дохід). Кожна формула прозора: аналітик бачить, з яких подій і параметрів вона складається, і може адаптувати логіку під конкретні бізнес-завдання.
  4. BigQuery — це не лише “повторення” GA4, а можливість піти далі.
    SQL немає семплінгу, немає прихованих обчислень, немає обмежень інтерфейсу. Ви контролюєте кожну частину метрики та можете будувати аналітику точно так, як цього потребує ваш бізнес.
  5. BigQuery - це набагато більше ніж просто робота з сирими даними.

Так, про цей останній пункт в статті не було сказано багато, тому рекомендую вам прочитати на цю тему іншу статтю - Все, що треба знати про BigQuery: що це, навіщо та які переваги для маркетингу.

  • #Intermediate
  • #Google BigQuery
  • #SQL

Завантаження коментарів…