Попри те, що протягом 2023 та 2024 років я проходив кілька курсів з SQL та SQL для GA4 BigQuery, в кінці цього року мені довелося зробити перші кроки в зборі даних з GA через BigQuery. Тому я вирішив опублікувати цю статтю з першими запитами, які я написав для збору кількох метрик: Users, Active Users і Sessions.
Це прості запити, і мета цієї статті — не лише поділитися SQL-запитом для кожного з випадків, але й надати деякі коментарі щодо них та результатів їх виконання.
Якщо у вас є відповіді на коментарі, не вагайтеся надіслати мені повідомлення.
Загальна кількість користувачів
Підрахунок "Загальних користувачів"
SELECT
COUNT(DISTINCT user_pseudo_id) as Total_Users
FROM
`your_ga_table_scema.events_*`
WHERE
stream_id in (‘123456789’, ‘987654321’) AND
_table_suffix between ‘20240801’ and ‘20240810’
Декілька визначень важливих вимірів, використаних у цьому запиті
- userpseudoid
userpseudoid — це унікальний ідентифікатор, який Google використовує для ідентифікації кожного користувача. Тобто, насправді це унікальний ідентифікатор, який Google використовує для ідентифікації веб-браузера, через який користувач заходить на сайт чи в додаток.
Інакше кажучи, якщо користувач відвідає вебсайт через десктоп, потім через ноутбук, а згодом через смартфон, кількість "Загальних користувачів", яку обчислить Google (Analytics), буде 3.
- user_id
userid, навпаки, є ідентифікатором, який використовується Google з надією ідентифікувати "користувачів". Я кажу "з надією", тому що userid працює лише у випадку, якщо користувач авторизувався на вебсайті, тобто сайт має функціонал реєстрації/входу. І по-друге, для того щоб user_id був зібраний Google, він має бути реалізований.
У наведеному вище SQL-запиті ми можемо прибрати умову WHERE, оскільки її значення в цьому контексті полягає в тому, щоб фільтрувати певний потік даних, що присутній у GA, і з іншого боку, отримувати результати тільки для перших 10 днів серпня 2024 року.
Активні користувачі
Перед тим, як пояснити виміри, давайте розглянемо концепцію Active User, згідно з визначенням самого Google:
Кількість "Унікальних активних користувачів", які взаємодіяли з вебсайтом чи додатком у певний період часу.
Активним користувачем є той, хто здійснив "engaged_session" або коли GA збирає:
- Подія "firstvisit" або параметр "engagementtime_msec" для вебсайту
- Подія "firstopen" або параметр "engagementtime_msec" для Android додатку
- Подія "firstopen" або "userengagement" для iOS додатку
Користувач вважається активним, як тільки подія user_engagement фіксується навіть на секунду.
- isactiveuser
З липня 2023 року Google створив нову колонку в схемі GA під назвою "isactiveuser", що має тип boolean (true або false). Це зробило підрахунок "Активних користувачів" значно простішим.
До цієї новини, необхідно було використовувати інший запит, який застосовував вимір "engagementtimemsecs", що є параметром події "pageview" або "screenview", якщо ми аналізуємо дані додатків.
Досі не можу зрозуміти, чому результат "Активних користувачів", отриманий за новою формулою з використанням колонки "isactiveuser", відрізняється від результату, отриманого за допомогою старого запиту.
Гадаю, що це пов'язано з вимірами “engagementtimemsecs”
Якщо хтось може мені пояснити, надішліть повідомлення, будь ласка.
Сесії
Підрахунок сесій
SELECT
count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = ‘ga_session_id’))) as Total_Sessions
FROM
`your_ga_table_scema.events_*`
WHERE
stream_id in (‘123456789’, ‘987654321’) AND
_table_suffix between ‘20240801’ and ‘20240810’
Сесії
- userpseudoid
“userpseudoid” ми вже розглядали вище, коли аналізували запит для підрахунку “Загальних користувачів”, і зрозуміли, що він означає.
- gasessionid
gasessionid або session_id — це, ймовірно, вимір, який “унікально” ідентифікує сесію. Але насправді це не зовсім так, тому що:
1 — “gasessionid” — це вимір, який є типом “string”, тобто насправді це відмітка часу, що вказує, коли сесія була розпочата.
2 — Якщо “gasessionid” — це відмітка часу, що вказує (до секунди), коли сесія була розпочата, тоді ми можемо мати різних користувачів (“userpseudoid”), які одночасно (в той самий час, хвилину і секунду) активують подію “sessionstart”. Тому потрібно робити конкатенацію “userpseudoid” + “gasession_id”, щоб гарантовано була одна “унікальна сесія”.
Але можемо поставити запитання: А чому не використовувати подію session_start, наприклад, через наступний SQL-запит:
Добре, є безліч випадкових причин, чому подія sessionstart може не спрацьовувати, коли користувач відвідує вебсайт. Одна з них, наприклад, це тимчасова (чи постійна) помилка GA, через те, що подія “pageview” не була надіслана...
Наприкінці, я не знаю, чи є цей SQL-запит найпоширенішим для підрахунку сесій в GA, але він здається найбільш типовим і у випадку, коли він давав результати, дуже схожі на ті, що представлені в інтерфейсі GA.
Стаття спочатку була опублікована на: 3 Queries SQL Básicas para consultar o Schema do GA em BigQuery | João Carlos Matos
Перекладено з: 3 Queries SQL Básicas para consultar o Schema do GA em BigQuery