Головна
/
Блог
/
Повний посібник з SQL для Google Analytics 4 у BigQuery: побудова звіту Traffic Acquisition (частина 2)

Повний посібник з SQL для Google Analytics 4 у BigQuery: побудова звіту Traffic Acquisition (частина 2)

Макс Гапчук

чт, 23 квітня 2026 р.

У першій частині посібника ми заклали технічну основу роботи з “сирими” даними GA4 у BigQuery: розібрали структуру експорту, навчилися працювати з вкладеними параметрами через скалярні підзапити та зібрали базовий звіт Pages and Screens (Сторінки й екрани). Фактично — навчилися коректно діставати дані з сирого експорту.

Але наша мета — перейти від простого отримання даних до створення складної бізнес-логіки. Тому сьогодні ми розберемося, як у BigQuery реалізована атрибуція трафіку та чому джерела існують на різних рівнях даних (user, session, event). Саме на цих моментах найчастіше виникають помилки, коли запит виглядає коректним, але результати вводять в оману. Після цього ми перейдемо до практики і крок за кроком побудуємо повноцінний звіт Traffic Acquisition (Залучення трафіку) у BigQuery.

Розділ 1. Оператори для трансформації та підготовки сирих даних до аналізу

1.1. PARSE_DATE: перетворюємо текст на дату
1.2. COALESCE: як працювати з NULL і не ламати розрахунки
- Приклад 1. Захист від NULL у числових обчисленнях
- Приклад 2. Робота з текстовими полями
1.3. Оператор CASE WHEN

Розділ 2. Атрибуція та Джерела трафіку

2.1. Що таке атрибуція і чому вона важлива?
2.2. Три рівні даних про трафік
- Як логіка рівнів GA4 переноситься у BigQuery
- Поля джерел трафіку в BigQuery: traffic_source, session_traffic_source_last_click, collected_traffic_source

Розділ 3. Побудова звіту Traffic Acquisition на session_traffic_source_last_click

3.1 Структура звіту Traffic Acquisition (Залучення трафіку)

Висновки

Погляд у майбутнє (Pipe-синтаксис)

Розділ 1: Оператори для трансформації та підготовки сирих даних до аналізу

Перш ніж будувати складні звіти, нам потрібно знати набір інструментів для «чистки» та трансформації даних. Базові оператори та функції ми вже розібрали у першій частині посібника, і сьогодні ми розширимо цей арсенал.

1.1 PARSE_DATE: перетворюємо текст на дату

Поле event_date в експорті GA4 у BigQuery зберігається у вигляді рядка (STRING) формату YYYYMMDD, наприклад, 20240118. Попри те, що таке значення виглядає як дата, для SQL це звичайний текст. Через це з ним неможливо виконувати типові часові операції — наприклад, обчислювати різницю між датами, групувати дані за тижнями або місяцями, чи використовувати інші стандартні функції роботи з датами.

Оператор PARSE_DATE дозволяє явно повідомити SQL, у якому форматі записана дата, та перетворити текстове значення на повноцінний тип DATE. Після цього значення стає придатним для будь-яких часових обчислень.

Базовий приклад перетворення event_date:

sql

SELECT
  event_date,
  PARSE_DATE('%Y%m%d', event_date) AS event_date_parsed
FROM
  `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`;

У цьому прикладі:

%Y%m%d описує формат рядка дати (перші чотири цифри відповідають за рік, наступні дві за місяць, і ще дві за день. Все без розділювачів),
PARSE_DATE перетворює текстове значення на тип DATE,
нове поле event_date_parsed вже можна використовувати у функціях на кшталт DATE_DIFF, DATE_TRUNC або EXTRACT.

І давайте розберемо практичний приклад використання оператора PARSE_DATE для групування за тижнями:

sql

SELECT
  DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), WEEK) AS week_start,
  COUNT(*) AS events_count
FROM
  `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_202401*`
GROUP BY
  1
ORDER BY
  1;

У результаті запиту ми отримуємо кількість подій, що відбулися протягом кожного календарного тижня у межах обраного датасету та заданого періоду даних. Для цього всі дати приводяться до початку тижня за допомогою DATE_TRUNC, після чого події агрегуються через COUNT(*).

Зверніть увагу. При використанні в функції DATE_TRUNC значення WEEK ви отримаєте тижні, які починаються з неділі. Якщо потрібно повертати більш класичний для бізнесу варіант з понеділка - використовуйте ISOWEEK.

Ще один частий приклад використання функції PARSE_DATE - при роботі з _TABLE_SUFFIX для більш зручної, очевидної і правильної фільтрації даних за певний період. Наприклад, в запиті нижче ми повертаємо дані з 15 по 28 січня 2024 року.

1.2 COALESCE: як працювати з NULL і не ламати розрахунки

У сирих даних GA4 NULL — це не виняток, а норма. Він може з’являтись тому що подія не завжди має певні параметри, наприклад, такі як value, transaction_id тощо. Тобто поле існує в схемі, але фактично не заповнене для конкретної події.

Проблема в тому, що:

NULL може ламати арифметичні обчислення;
агрегації можуть повертати неочікувані результати;
дані стають важчими для інтерпретації у звітах.

Саме тому нам потрібен COALESCE.

COALESCE — це SQL-функція, яка повертає перше не-NULL значення зі списку аргументів. Простою мовою: якщо значення відсутнє (NULL) — підстав інше, яке ми вкажемо. Синтаксис виглядає так:

sql

COALESCE(value_1, value_2, value_3, ...)

BigQuery перевіряє аргументи зліва направо і повертає перший, який не дорівнює NULL.

Давайте розберемо приклади використання:

Приклад 1. Захист від NULL у числових обчисленнях

Щоб спростити пояснення розберемо не на даних GA4, а на спрощеному прикладі. Уявімо таблицю, де три записи мають значення, а один запис — NULL:

event_value
100
50
NULL
25

Якщо просто написати:

sql

SELECT SUM(event_value) AS revenue

Результат буде 175. BigQuery просто ігнорує NULL.

А тепер явно скажемо, що NULL = 0:

sql

SELECT SUM(COALESCE(event_value, 0)) AS revenue

Результат також буде 175.

І може здатись що різниці немає. Але це не так. Давайте тепер розберемо приклад з тими ж даними і функцією AVG.

Зверніть увагу, NULL і 0 — це різні аналітичні стани:

NULL означає, що даних не було взагалі (немає жодного релевантного рядка).
0 означає, що події були, але значення дорівнює нулю або не передано.

Для того щоб однозначно керувати процесом, а не надіятись на “SQL магію” - не забувайте про використання COALESCE.

Приклад 2. Робота з текстовими полями

COALESCE корисний не лише для чисел. Наприклад, якщо джерело трафіку не визначене, то ми можемо змінювати NULL на більш зрозуміле (not set):

sql

SELECT
  COALESCE(traffic_source.source, '(not set)') AS source,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
GROUP BY source
ORDER BY users DESC;

У результаті:

замість NULL у звіті буде зрозуміле значення;
таблиці та дашборди стають читабельнішими.

Про ще один приклад використання COALESCE ми поговоримо ще в пункті Метрика Engaged Sessions (Сеанси із взаємодією).

1.3 Оператор CASE WHEN

У BigQuery оператор CASE — це інструмент, який повертає різні значення залежно від виконання заданих умов. У спрощеному вигляді логіка CASE виглядає так:

«Перевір, чи виконується ця умова. Якщо так — поверни задане значення. Якщо ні — перевір наступну умову…»

Завдяки цьому CASE дозволяє:

створювати кастомні категорії;
групувати контент;
формувати сегменти користувачів;
обчислювати аналітичні ознаки без зміни сирих даних і без додаткових таблиць.

Фактично, CASE — це спосіб додати аналітичний сенс до подієвих даних прямо в SELECT.

Загальна структура виглядає так:

sql

CASE
  WHEN умова1 THEN результат1
  WHEN умова2 THEN результат2
  WHEN умова3 THEN результат3
  ELSE результат_за_замовчуванням
END

Важливо:

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

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

Зверніть увагу на другу частину, яка стосується використання CASE WHEN. Що стосується першої частини з session_traffic_source_last_click.cross_channel_campaign значеннями - про них ми поговоримо трохи далі.

sql

WITH session_data AS (
  SELECT
    user_pseudo_id,
    -- Витягуємо ID сесії з параметрів (як ми робили в попередніх частинах)
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
   
    -- Звертаємось до правильних полів для сесійного Traffic Acquisition
    session_traffic_source_last_click.cross_channel_campaign.source AS source,
    session_traffic_source_last_click.cross_channel_campaign.medium AS medium
  FROM `project-2fdab5d4-7e4c-43c5-ab8.analytics_000001.events_20240118`
  WHERE event_name = 'session_start'
)

SELECT
  CASE
    -- 1. Шукаємо прямі заходи (Direct)
    WHEN source = '(direct)' OR source IS NULL THEN 'Direct'
   
    -- 2. Визначаємо платну рекламу (PPC)
    WHEN medium IN ('cpc', 'cpm', 'cpv', 'cpa') THEN 'Paid Search/Ads'
   
    -- 3. Групуємо органіку з соцмереж (використовуємо регулярні вирази)
    WHEN REGEXP_CONTAINS(source, r'^(facebook|instagram|linkedin|t\.me)$') OR medium = 'social' THEN 'Organic Social'
   
    -- 4. Органічний пошук
    WHEN medium = 'organic' THEN 'Organic Search'
   
    -- 5. Все інше, що не підпало під умови вище
    ELSE 'Unassigned'
  END AS channel_grouping,
 
  -- Рахуємо унікальні сесії, об'єднуючи Client ID та Session ID
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-', ga_session_id)) AS total_sessions
FROM session_data
GROUP BY channel_grouping
ORDER BY total_sessions DESC;

Що тут відбувається:

CASE створює нове поле channel_grouping;
кожен WHEN — це окрема логічна умова;
END завершує логіку і повертає значення;
COUNT(DISTINCT ...) рахує сесії у кожній групі;
GROUP BY агрегує дані для кожної групи.

Звісно, в реальності зазвичай виділяють більше груп каналів, вище лише приклад роботи функції, а не повний варіант.

Розділ 2: Атрибуція та Джерела Трафіку

2.1 Що таке атрибуція і чому вона важлива?

Атрибуція та джерела трафіку — одна з найскладніших тем у вебаналітиці. Один і той самий користувач може прийти на сайт із реклами, повернутися з органіки, потім зайти напряму (Direct) і підписатись на розсилку, а конверсію зробити вже після переходу з email-розсилки. Те, якому джерелу “зарахується” сесія або конверсія, залежить від моделі атрибуції та правил пріоритизації джерел. Відповідно, саме атрибуція визначає, які канали й кампанії виглядають “ефективними” у звітах, а які — ні.

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

яку модель атрибуції ми відтворюємо (бо звіти можуть базуватися на різних підходах);
яке саме значення в експорті GA4 до BigQuery за який рівень даних відповідає.

Почнемо ми з другого.

2.2 Три рівні даних про трафік

Одна з ключових причин плутанини під час роботи з даними GA4 у BigQuery — це ігнорування того, що джерела трафіку існують на різних рівнях (scopes). Як і в інтерфейсі GA4, тут немає єдиного “правильного” поля source / medium — натомість є кілька рівнів даних, кожен із яких відповідає на своє аналітичне питання.

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

У контексті аналізу трафіку в даних експорту GA4 до BigQuery варто чітко розрізняти три рівні (вони схожі до тих, що є в інтерфейсі):

рівень користувача (User),
рівень сесії (Session),
рівень події (Event).

Як логіка рівнів GA4 переноситься у BigQuery

Як ми вже знаємо, експорт GA4 у BigQuery має подієву структуру: один рядок таблиці відповідає одній події (event). Проте це не означає, що всі дані в цьому рядку мають однаковий аналітичний сенс. Навпаки — всередині кожного “івентного” рядка зберігається інформація, яка логічно належить до різних рівнів сутностей: користувача, сесії та події.

User-level (Рівень користувача) — ви аналізуєте користувача як окрему сутність. Базовою одиницею такого аналізу є user_pseudo_id, який ідентифікує користувача протягом усього часу його взаємодії з сайтом або додатком. З точки зору джерела трафіку тут ми говоримо про джерело, яке вперше залучило користувача на наш сайт.
Session-level (Рівень сеансу) — ви аналізуєте окремі візити користувача. Один користувач може мати багато сесій і ви вже знаєте, що кожна сесія визначається комбінацією user_pseudo_id та ga_session_id. Коли ми говоримо про джерела на цьому рівні зазвичай мається на увазі джерело трафіку з якого почалась сесія.
Event-level (Рівень події) — ви працюєте з конкретними діями користувача в межах сесії. Тут кожен рядок таблиці є окремою подією з власною назвою (event_name) та часом виконання (event_timestamp), а додатковий контекст події передається через параметри. З точки зору джерел трафіку це “сирий рівень”. В нас є інформація про кожну подію і ми можемо робити з нею все що завгодно, іншими словами, саме на даних з цього рівня ми можемо будувати власні моделі атрибуції.

Таким чином, хоча на перший погляд експорт в BigQuery має івентну логіку, аналітик завжди працює не з “подієвими рядками”, а з ієрархією сутностей: користувач → сесія → подія. Рівень, на якому ви агрегуєте дані, безпосередньо визначає сенс метрик і коректність висновків. А неправильне розуміння рівня, з яким ви маєте працювати/працюєте - приводить до неправильних розрахунків і хибних висновків.

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

Поля джерел трафіку в BigQuery: traffic_source, session_traffic_source_last_click, collected_traffic_source

Рівень (Scope)	Поле в інтерфейсі BigQuery	Поле в інтерфейсі GA4	Опис та логіка полей	Коли використовувати
User (Користувач)	traffic_source Наприклад, traffic_source.source, traffic_source.medium, traffic_source.name	First User Наприклад, First user source (Джерело першого залучення користувача), First user medium (Канал першого залучення користувача), First user source / medium (Джерело або канал першого залучення користувача), First user campaign (Кампанія, пов’язана з першим залученням користувача).	Це джерело, яке привело користувача на сайт уперше. Воно записується один раз і не змінюється протягом усього життя cookie.	Використовується для аналізу залучення нових користувачів. Звіт в інтерфейсі User Acquisition (Залучення користувачів).
Session (Сеанс)	Session_traffic_source_last_click.cross_channel_campaign Наприклад, session_traffic_source_last_click.source, session_traffic_source_last_click.medium, session_traffic_source_last_click.campaign, session_traffic_source_last_click.cross_channel_campaign	Session Наприклад, Session source / medium (Джерело / канал сеансу), Session medium (Канал сеансу), Session source (Джерело сеансу), Session source platform (Платформа джерела сеансу), Session campaign (Кампанія сеансу).	Атрибутоване джерело сесії, визначене за логікою Last Non-Direct Click (Останній непрямий клік). Last Non-Direct Click значить, що останній Direct заміниться на останнє відоме не Direct джерело. Тобто, якщо користувач прийшов з Organic, а потім повернувся Direct — сесія буде зарахована як Organic.	Використовується для аналізу трафіку. Звіт в інтерфейсі Traffic Acquisition (Залучення трафіку).
Event (Подія)	collected_traffic_source Наприклад, collected_traffic_source.source, collected_traffic_source.medium, collected_traffic_source.campaign, collected_traffic_source.source_platform	Наприклад, Source (Джерело), Medium (Канал), Source / medium (Джерело / канал), Campaign (Кампанія), Source platform (Вихідна платформа), Google Ads campaign (Кампанія Google Ads).	Це “сирі” дані про джерело конкретної події. Ці дані не проходять через логіку якої б то не було атрибуції і можуть змінюватися навіть у межах однієї сесії.	Використовується для аналізу всіх точок дотику та побудови кастомних моделей атрибуції. В інтерфейсі дані з цього рівня використовуються для побудови звітів з блоку Advertising (Реклама).

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

Користувач уперше заходить на сайт з органічного пошуку Google (google / organic) і, наприклад, підписується на розсилку.
Через кілька днів він переходить на сайт з email-розсилки (esputnik / email)
Пізніше відкриває сайт напряму, ввівши URL у браузері (direct / (none)), і саме під час цього візиту здійснює покупку

У цьому сценарії залежно від рівня даних, який ми використовуємо в BigQuery інформація буде наступна:

traffic_source (User-level)
Для цього користувача джерелом трафіку завжди буде google / organic, оскільки саме з цього каналу він потрапив на сайт уперше. Навіть покупка, здійснена під час direct-візиту, на user-level буде асоційована з google / organic. Саме тому це поле підходить для аналізу залучення нових користувачів, але не для оцінки ефективності кампаній.
session_traffic_source_last_click (Session-level)
Сесія, у межах якої відбулася покупка, буде атрибутована до esputnik / email (це був останній non-direct канал). Це поле відтворює логіку стандартного звіту Traffic Acquisition (Залучення трафіку) у GA4 і підходить для аналізу ефективності трафіку та конверсій на рівні сесій.

Важливий нюанс. Всередині session_traffic_source_last_click ви побачите багато різної інформації. Найближче до класичного інтерфейсного значення джерел на рівні сесії буде зберігатись в cross_channel_campaign.

collected_traffic_source (Event-level)
На рівні подій же вас чекатимуть сирі дані. Самі по собі, без додаткової обробки вони не дуже корисні. Але саме на основі цих даних можна побудувати власну логіку атрибуції. Наприклад, використовуючи Linear non-direct attribution (лінійна атрибуція без урахування direct) ми можемо 50% цінності покупки призначити google / organic, а інші 50% esputnik / email.

Детальніше про структуру цих та інших полів у BigQuery можна почитати в офіційній довідці.

Розділ 3: Побудова звіту Traffic Acquisition на session_traffic_source_last_click

Перш ніж ми перейдемо до практики, важливо зауважити: ці дані не є ідентичними на 100% тим, що ви бачите в інтерфейсі GA4. Наша команда провела дослідження на 15 проєктах і виявила, що дані збігаються менше ніж у половині випадків. Зазвичай у BigQuery фіксується менше трафіку google / cpc, ніж в інтерфейсі. Докладніше про це дослідження ви можете прочитати у статті «Порівняння джерел трафіку між GA4 та session_traffic_source_last_click в BigQuery».

Незважаючи на ці розбіжності, а особливо враховуючи, що вони були помічені не на всіх проєктах, використання полів session_traffic_source_last_click — це найкращий старт для знайомства з атрибуцією в BigQuery. Особливо, якщо врахувати, що офіційно Google позиціонує їх як аналог даним зі звіту Traffic Acquisition (Залучення трафіку), тобто саме те, що нам потрібно.

3.1 Структура звіту Traffic Acquisition (Залучення трафіку)

Ось ми й підбираємося до найцікавішого й найпрактичнішого. Але, щоб почати писати SQL-запит, вам потрібно дуже добре розуміти, що саме команда Google виводить у цьому звіті.

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

ключова сутність — сесія;
джерело трафіку визначається для сесії;
всі метрики агрегуються навколо сесій.

Параметри:

Session primary channel group - основна група каналів сесії, що показує основний спосіб, як користувачі приходять на сайт або додаток.
Session default channel group - стандартна група каналів сесії, яка визначає основні канали, через які користувачі приходять на сайт (наприклад, органічний пошук, пряма взаємодія, реферали тощо).
Session source / medium - комбінація джерела і каналу трафіку, який привів користувачів на сайт або в додаток.
Session medium - канал трафіку (наприклад, органічний пошук, платний пошук).
Session source - джерело трафіку (наприклад, google, facebook).
Session source platform - платформа джерела трафіку, через яку користувачі прийшли на сайт або в додаток.
Session campaign - кампанія джерела трафіку, через яку користувачі прийшли на сайт або в додаток.

39.12 GA4 Traffic Acquisition Parameters

Метрики:

Sessions (Сеанси) - кількість сесій, які почалися з певного джерела, каналу або кампанії.
Engaged sessions (Сеанси із взаємодією) - кількість сесій, в яких користувачі активно взаємодіяли з контентом.
Engagement rate (Частка взаємодій) - відсоток залучених сесій від загальної кількості сесій.
Average engagement time per session (Середній час взаємодії за сеанс) - середній час взаємодії на сесію.
Events per session (Кількість подій на сеанс) - кількість подій на сесію.
Event count (Кількість подій) - загальна кількість подій.
Key events (Основні події) - ключові події, що відбулися на сайті.
Session key event rate (Частка сеансів із ключовими подіями) - відсоток сесій, у яких відбулися ключові події, від загальної кількості сесій.
Total revenue (Загальний дохід) - загальний дохід, отриманий від користувачів, які прийшли через певне джерело, канал або кампанію.

Далі ми покроково відтворимо кожну колонку цього звіту та розберемо їх нюанси, а в кінці об’єднаємо все у фінальний SQL-запит.

3.1.1 Параметр звіту: Session source / medium

Найбільш часто використовуваний параметр в звіті Traffic Acquisition (Залучення трафіку) це Session source / medium (Джерело / канал сеансу), тому розпочнемо наш звіт саме з нього. Для цього використаємо session_traffic_source_last_click.cross_channel_campaign. Це частина об’єкта session_traffic_source_last_click, яка містить атрибутоване джерело сесії.

sql

SELECT
  CONCAT(
    session_traffic_source_last_click.cross_channel_campaign.source,
    ' / ',
    session_traffic_source_last_click.cross_channel_campaign.medium
  ) AS session_source_medium
FROM `project_id.analytics_xxxxx.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20251101' AND '20251130'
GROUP BY 1;

39.14 Session source medium parameter SQL query

3.1.2 Метрика Sessions (Сеанси)

Її ми вже визначили вище. Нагадую, що в BigQuery ми визначаємо сесію як унікальну комбінацію user_pseudo_id та ga_session_id:

sql

COUNT(
  DISTINCT CONCAT(
    user_pseudo_id,
    '-',
      (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'ga_session_id')
  )
) AS sessions

3.1.3 Метрика Engaged Sessions (Сеанси із взаємодією)

Engaged Sessions (Сеанси із взаємодією) — тут вже стає цікавіше. За офіційною логікою GA4 сесія вважається “із взаємодією”, якщо виконується хоча б одна з умов:

сесія тривала понад 10 секунд, або
містила key event (конверсію), або
мала 2 або більше переглядів сторінок/екранів.

При цьому в самих налаштуваннях GA4 кількість секунд для умови “сесія тривала понад 10 секунд” можна змінити.

Але не хвилюйтеся, вам не доведеться враховувати всі ці умови в SQL запиті самостійно. В експорті GA4 є спеціальний параметр session_engaged, який позначає сесії, що відповідають цим критеріям.

Параметр session_engaged може мати такі значення:

"1" — сесія відповідає правилам engaged session;
"0" або NULL — сесія НЕ відповідає правилам engaged session.

Давайте розберемо більш практично:

Не забувайте, що параметр session_engaged так як і ga_session_id — зберігаються у вкладеному масиві event_params. Щоб BigQuery їх побачив, спочатку потрібно розпакувати ці параметри використовуючи оператор UNNEST, що ми вже детально розібрали в попередній частині посібника.

Якщо не вдаватись в деталі і нюанси, то запит для отримання сесій із взаємодією буде виглядати якось так: ми просто рахуємо унікальні real_session_id де значення session_engaged дорівнює 1. І хоча запит нижче повертає певний результат - в більшості випадків він буде неправильним.

sql

SELECT
  COUNT(
    DISTINCT IF(
      (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'session_engaged') = '1',
      CONCAT(
        user_pseudo_id,
        '-',
        (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'ga_session_id')
      ),
      NULL
    )
  ) AS engaged_sessions
FROM `project_id.analytics_xxxxx.events_*`

Причина помилки в тому, що в даних експорту GA4 є нюанси. Один з яких в тому, що цілі числові значення можуть записатись як в event_params.value.string_value так і в event_params.value.int_value.

Тому правильний запит має враховувати цю особливість. Для порівняння нижче можна побачити результати “першого” рішення і правильного:

Як видно на скріні вище, різниця в цьому конкретному випадку склала 1 − (3633 / 4372) ≈ 17%, що є досить суттєвим.

sql

SELECT
  -- Варіант 1: "Наївний" підхід (перевіряє тільки string_value)
  -- Може пропустити сесії, де значення записано як число
  COUNT(
    DISTINCT IF(
      (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'session_engaged') = '1', 
      CONCAT(
        user_pseudo_id, 
        '-', 
        (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'ga_session_id')
      ), 
      NULL
    )
  ) AS engaged_sessions_strict,

  -- Варіант 2: Безпечний підхід з COALESCE
  -- Перевіряє і string_value, і int_value, зводячи їх до спільного типу (STRING)
  COUNT(
    DISTINCT IF(
      (SELECT COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) FROM UNNEST(event_params) AS ep WHERE ep.key = 'session_engaged') = '1', 
      CONCAT(
        user_pseudo_id, 
        '-', 
        (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'ga_session_id')
      ), 
      NULL
    )
  ) AS engaged_sessions_safe
FROM `project_id.analytics_xxxxx.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20251101' AND '20251130'

Пояснення нюансів запиту:

UNNEST(event_params) використовується для доступу до параметра session_engaged, оскільки він зберігається у вкладеному масиві.
COALESCE + CAST — це наш захист від "брудних" даних. Ми перевіряємо текстове поле string_value. Якщо воно порожнє, ми беремо числове int_value, безпечно перетворюємо його на текст за допомогою CAST і віддаємо далі. Це гарантує, що ми знайдемо нашу одиничку, як би GA4 її не записав.
IF формує умовне значення real_session_id:
- Якщо після всіх перевірок session_engaged дорівнює '1' (тобто сесія відповідає критеріям «залученої»), ми повертаємо унікальний ідентифікатор сесії.
- Якщо сесія не залучена, вираз повертає NULL.
CONCAT формує цей самий унікальний ідентифікатор. Він об’єднує user_pseudo_id та ga_session_id. Завдяки механізму неявного приведення типів (implicit coercion), BigQuery сам розуміє, що числове значення ID сесії потрібно перетворити на текст для склейки.
COUNT(DISTINCT…) рахує лише унікальні ідентифікатори залучених сесій. Це критично важливо, адже одна сесія містить багато подій (page_view, scroll, user_engagement), і якби ми не використали DISTINCT, ми б просто порахували кількість подій замість кількості сесій. А значення NULL (незалучені сесії) функція COUNT просто ігнорує.

Хух.. Це було одне з найскладніших на сьогодні. Далі буде легше.

3.1.4 Метрика Engagement rate (Частка взаємодій)

Engagement rate (Частка взаємодій) — це частка залучених сесій від загальної кількості сесій. Для її обчислення нам треба поділити engaged_sessions на sessions.

Обидва ці значення в нас уже є, тому теоретично ми могли б просто використати символ “/”, але ми вчимось писати одразу правильний і безпечний код, тому зробимо це за допомогою SAFE_DIVIDE. Ця функція виконує ділення двох значень і запобігає помилці ділення на нуль. Обчислення буде виглядати так:

sql

SAFE_DIVIDE(engaged_sessions, sessions) * 100 AS engagement_rate

Додатково множимо на 100, щоб отримувати значення одразу у відсотках.

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

Час взаємодії зберігається всередині event_params в параметрі engagement_time_msec. Як працювати з цим вкладенням ми розбирали вище. А нюанси підрахунку цього показника я вже розбирав в попередній статті в блоці Метрика Average engagement time per user (Середній час взаємодії на користувача). Єдина різниця в тому, що там ми ділили на кількість користувачів, а тут буде кількість сесій.

sql

ROUND(
  SAFE_DIVIDE(
    SUM(
      (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'engagement_time_msec' ) 
    ) / 1000,
    sessions
  ), 
  2
) AS avg_engagement_time_per_session

У запиті ми переводимо мілісекунди в секунди та ділимо на кількість сесій. Якщо хочете одразу в хвилинах, треба поділити ще на 60.

ROUND() - можна додавати за бажанням, щоб одразу округлити значення до звичної подачі відсотків з 2 знаками після коми.

3.1.6 Метрика Events per session (Кількість подій на сеанс)

Events per session (Кількість подій на сеанс) показує середню кількість подій, які відбуваються в одній сесії. Оскільки в таблиці експорту кожен окремий рядок це окрема подія - підрахунок кількості подій одна з найлегших операцій COUNT(*). Фінальна формула тоді така:

sql

SAFE_DIVIDE(
  COUNT(*),
  sessions
) AS events_per_session

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

За замовчуванням в інтерфейсі ця колонка - це загальна кількість усіх подій, що дорівнює простому COUNT(*). Але якщо ви хочете відтворити логіку вибору конкретної події з випадаючого списку (наприклад, тільки purchase), ми використовуємо функцію COUNTIF, яка рахує лише ті рядки, де виконується умова:

sql

COUNTIF(event_name = 'purchase') AS event_count

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

Хоча в інтерфейсі, щоб подія рахувалася як ключова, вам потрібно позначити її відповідним чином, у BigQuery таких умовностей немає. Якщо потрібно, будь-яка подія може миттєво стати ключовою — достатньо просто задати їй відповідну назву ))

sql

COUNTIF(event_name = 'purchase') AS key_events

3.1.9 Метрика Session key event rate (Частка сеансів із ключовими подіями)

Ця метрика відповідає на питання: у якій частці сесій відбулась хоча б одна ключова подія? Давайте розберемо на прикладі події purchase (покупки).

Формула буде наступна:

Session key event rate (Частка сеансів із ключовими подіями) = кількість сесій, у яких була подія purchase / загальну кількість сесій.

Звертаю увагу на часту помилку: в чисельнику ми використовуємо не кількість покупок, а кількість сесій, у яких була подія purchase. Якщо в одній сесії з якихось причин сталося 2 purchase — це все одно одна сесія для цього підрахунку.

Логіка SQL запиту наступна:

визначаємо унікальну сесію,
відбираємо тільки ті сесії, в яких є event_name = 'purchase',
ділимо кількість таких сесій на загальну кількість сесій.

Отримуємо такий запит:

sql

ROUND(
  SAFE_DIVIDE(
    -- Чисельник: кількість унікальних сесій, у яких була подія purchase
    COUNT(
      DISTINCT IF(
        event_name = 'purchase',
        CONCAT(user_pseudo_id, '-', (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'ga_session_id')),
        NULL 
      )
    ),
    -- Знаменник: загальна кількість унікальних сесій
    sessions
  ) * 100, 
  2
) AS session_key_event_rate

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

Розрахунок Total revenue (Загальний дохід) досить простий, оскільки це значення зберігається в окремій колонці ecommerce.purchase_revenue. Щоб уникнути порожніх значень NULL у звіті для тих джерел, які не принесли доходу, ми використаємо функцію IFNULL і замінимо порожнечу на 0:

sql

IFNULL(SUM(ecommerce.purchase_revenue), 0) AS total_revenue

3.1.11 Фінальний запит: збираємо звіт Traffic Acquisition (Залучення трафіку) у BigQuery

Ось ми і дісталися найцікавішого. Ми розібрали кожну цеглину, кожну метрику та параметр. Тепер настав час об'єднати їх у єдиний SQL-запит, який відтворить звіт Traffic Acquisition (Залучення трафіку) з інтерфейсу GA4, використовуючи параметр Session source / medium (Джерело / канал сеансу).

Щоб запит працював коректно, ми не можемо просто звалити всі формули в один блок SELECT. Чому? Тому що такі метрики як Engagement rate (Частка взаємодій) або Events per session (Кількість подій за сеанс) вимагають ділення однієї метрики на іншу (наприклад, engaged_sessions / sessions). Двигун BigQuery не дозволяє використовувати щойно створені назви стовпців для математики в тому ж самому блоці (як мінімум в звичайному режимі).

Саме тому ми використаємо конструкцію WITH (Common Table Expression, або CTE).

Магія оператора WITH

Оператор WITH дозволяє створити віртуальну тимчасову таблицю (ми назвемо її base_metrics) прямо під час виконання запиту. Як це виглядає в коді (Трирівнева архітектура) :

Крок 1 (prep_events): Підготовка. Дістаємо всі вкладені параметри (session_id, session_engaged, engagement_time) і робимо з них зручні колонки.
Крок 2 (base_metrics): Агрегація. Рахуємо наші COUNT та SUM, використовуючи вже готові чисті колонки.
Крок 3 (Фінальний SELECT): Математика. Рахуємо рейти, відсотки та округлюємо.

Завдяки цьому код стає модульним, читабельним і максимально безпечним.

Ось наш фінальний запит:

sql

WITH prep_events AS (
  -- РІВЕНЬ 1: Підготовка даних
  SELECT
    -- Наш основний параметр (Dimension)
CONCAT(session_traffic_source_last_click.cross_channel_campaign.source, ' / ', session_traffic_source_last_click.cross_channel_campaign.medium) AS session_source_medium,
    -- Базові поля події
    event_name,
    ecommerce.purchase_revenue,
    -- Витягуємо параметри з масивів в окремі змінні
    CONCAT(user_pseudo_id, '-', (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'ga_session_id')) AS real_session_id,
    (SELECT COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) FROM UNNEST(event_params) AS ep WHERE ep.key = 'session_engaged') AS session_engaged,
    (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'engagement_time_msec') AS engagement_time_msec
  FROM `project_id.analytics_xxxxx.events_*` -- вкажіть тут свою таблицю
  WHERE _TABLE_SUFFIX BETWEEN '20251101' AND '20251130' -- вкажіть тут потрібний період
),
base_metrics AS (
  -- РІВЕНЬ 2: Агрегація
  SELECT
    session_source_medium,
    COUNT(DISTINCT real_session_id) AS sessions,
  COUNT(DISTINCT IF(session_engaged = '1', real_session_id, NULL)) AS engaged_sessions,
    SUM(engagement_time_msec) AS total_engagement_time_msec,
    COUNTIF(event_name = 'add_to_cart') AS event_count,
    COUNTIF(event_name = 'purchase') AS key_events,
  COUNT(DISTINCT IF(event_name = 'purchase', real_session_id, NULL)) AS sessions_with_purchase,
    IFNULL(SUM(purchase_revenue), 0) AS total_revenue
  FROM prep_events
  GROUP BY 1
)
-- РІВЕНЬ 3: Фінальні розрахунки відсотків та часток
SELECT
  session_source_medium,
  sessions,
  engaged_sessions,
  ROUND(SAFE_DIVIDE(engaged_sessions, sessions) * 100, 2) AS engagement_rate,
 ROUND(SAFE_DIVIDE(total_engagement_time_msec / 1000, sessions), 2) AS avg_engagement_time_per_session,
  ROUND(SAFE_DIVIDE(event_count, sessions), 2) AS events_per_session,
  event_count,
  key_events,
 ROUND(SAFE_DIVIDE(sessions_with_purchase, sessions) * 100, 2) AS session_key_event_rate,
  total_revenue
FROM base_metrics
ORDER BY sessions DESC;

39.16 final query for Traffic Acquisition report in BigQuery

Параметризація: як зробити звіт універсальним

В інтерфейсі GA4 ви можете змінити основний параметр звіту одним кліком у випадаючому списку (наприклад, перемкнути з Source / Medium (Джерело / канал) на Campaign (Кампанія)). Наш SQL-запит теж так вміє!

Вам не потрібно переписувати всю логіку підрахунку метрик. Достатньо змінити лише один перший рядок у блоці WITH. Наприклад, якщо ви хочете подивитися цей самий звіт у розрізі рекламних кампаній, просто замініть рядок визначення session_source_medium на:

sql

session_traffic_source_last_click.cross_channel_campaign.campaign AS session_campaign,

Усе інше спрацює автоматично, оскільки всі наші метрики прив'язані до агрегації (оператор GROUP BY 1 згрупує дані за цим новим першим стовпцем).

Висновки

У другій частині мого посібника ми зробили ще один великий крок до розуміння і відтворення логіки GA4 у BigQuery.

У цій статті ми розібрали:

Підготовку сирих даних GA4 до аналізу в BigQuery — навіщо потрібні PARSE_DATE, CAST, COALESCE та CASE WHEN, і як саме ці оператори впливають на коректність метрик, агрегацій і подальших висновків.
Логіку атрибуції в таблицях експорту GA4 до BigQuery та чому для аналізу залучення трафіку варто використовувати об’єкт session_traffic_source_last_click.
Як працюють різні рівні даних (user / session / event) і чому в GA4 не існує одного універсального source / medium. Надіюсь, тепер ви чітко розмежовуєте traffic_source, collected_traffic_source та session_traffic_source_last_click і розумієте, для яких аналітичних задач кожне з них підходить.
І Крок за кроком відтворили звіт Traffic Acquisition (Залучення трафіку) у BigQuery

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

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

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

Ну і наостанок — для тих, хто любить зазирнути трохи далі.

Погляд у майбутнє (Pipe-синтаксис)

Ми щойно побудували повноцінний, "дорослий" аналітичний звіт. Він враховує особливості типів даних (через COALESCE), страхує від ділення на нуль (через SAFE_DIVIDE) і працює максимально прозоро.

Для тих, хто хоче писати ще більш елегантний код, Google нещодавно додав у BigQuery підтримку Pipe-синтаксису (|>). Цей підхід дозволяє читати запит не зсередини назовні, а строго згори донизу — як конвеєр на заводі.

Наша архітектура з трьох кроків (Підготовка ➝ Агрегація ➝ Розрахунок метрик) лягає на цей синтаксис просто ідеально. Замість блоків WITH ми використовуємо оператор EXTEND, щоб "на льоту" створювати змінні.

Ось як стильно та сучасно виглядатиме наш фінальний звіт:

sql

FROM `project_id.analytics_xxxxx.events_*` -- вкажіть тут свою таблицю
|> WHERE _TABLE_SUFFIX BETWEEN '20251101' AND '20251130' -- вкажіть тут потрібний період
-- Крок 1: Підготовка
-- Створюємо зручні змінні з вкладених параметрів
|> EXTEND 
    CONCAT(session_traffic_source_last_click.cross_channel_campaign.source, ' / ', session_traffic_source_last_click.cross_channel_campaign.medium) AS session_source_medium,
 CONCAT(user_pseudo_id, '-', (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'ga_session_id')) AS real_session_id,
    (SELECT COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) FROM UNNEST(event_params) AS ep WHERE ep.key = 'session_engaged') AS session_engaged,
    (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'engagement_time_msec') AS engagement_time_msec

-- Крок 2: Агрегація
-- Рахуємо базові метрики, використовуючи вже готові чисті змінні
|> AGGREGATE
    COUNT(DISTINCT real_session_id) AS sessions,
  COUNT(DISTINCT IF(session_engaged = '1', real_session_id, NULL)) AS engaged_sessions,
    SUM(engagement_time_msec) AS total_engagement_time_msec,
    COUNT(*) AS event_count,
    COUNTIF(event_name = 'purchase') AS key_events,
  COUNT(DISTINCT IF(event_name = 'purchase', real_session_id, NULL)) AS sessions_with_purchase,
    IFNULL(SUM(ecommerce.purchase_revenue), 0) AS total_revenue
   GROUP BY session_source_medium

-- Крок 3: Фінальні розрахунки (Розширення метрик)
-- Виконуємо ділення та округлення на основі агрегованих даних
|> EXTEND
    ROUND(SAFE_DIVIDE(engaged_sessions, sessions) * 100, 2) AS engagement_rate,
    ROUND(SAFE_DIVIDE(total_engagement_time_msec / 1000, sessions), 2) AS avg_engagement_time_per_session,
    ROUND(SAFE_DIVIDE(event_count, sessions), 2) AS events_per_session,
    ROUND(SAFE_DIVIDE(sessions_with_purchase, sessions) * 100, 2) AS session_key_event_rate

-- Крок 4: Фінальне сортування та вибір колонок для виводу
|> SELECT 
    session_source_medium, 
sessions, engaged_sessions, 
engagement_rate, 
    avg_engagement_time_per_session, 
events_per_session, 
event_count, 
    key_events, 
session_key_event_rate, 
total_revenue
|> ORDER BY sessions DESC;

Переваги такого підходу: Ви бачите весь рух даних як на долоні. Кожен |> — це наступний етап обробки. Під капотом обидва варіанти (і класичний WITH, і Pipe) працюють однаково і дають той самий результат, тому фінальний вибір залишається лише за вами.

#Intermediate
#Google BigQuery
#SQL

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