Назва статті вийшла довгою, і на перший погляд занадто специфічною. Насправді, це приклад того, як будь-який розрахунок тривалості можна вивести в Power BI у зручному для читання вигляді. Наприклад:
- Час перегляду відео на сайті
- Час до конверсії
- Час між отриманням ліда і закриттям угоди тощо
Усе, що має відношення до тривалості, закриває цей приклад. А сам він доволі популярний: час сесії, важлива метрика для розуміння утримання відвідувачів та релевантності контенту.
Якщо вас цікавить розрахунок часу сесії — продовжуйте читати.
Якщо ж ви хочете вивести свій власний показник тривалості в Power BI у форматі HH:MM:SS, вам краще одразу переміститись сюди.
Так-от, час сесії можна рахувати двома шляхами:
- Як різницю між
event_timestampпершої і останньої події за сеанс. Своєрідна класика від Universal Analytics. Однак, в GA4 є певне вдосконалення – під час закриття сторінки теж відправляється івент, тому час на останній сторінці теж зараховується, на відміну від Universal. Проте, такий метод враховує і час на неактивній вкладці, що може бути небажаним в певних випадках. - На основі суми значень параметра
engagement_time_msec. За довідкою цей параметр рахує час на активній сторінці і передається як різниця мікросекунд між попереднім івентом і новим. Тут довідка і далі розберемо трохи детальніше.
У цій статті я розглядаю другий підхід, а загальний план на сьогодні такий:
Огляд даних
У мене є проста таблиця з даними про сесії з GA4.
user_pseudo_id– значення cookie, яку призначає девайсу Google Analyticssession_id– поєднанняuser_pseudo_idта параметруga_session_idevent_date– дата сесіїcountry та device– відповідно країна та категорія девайсу користувачаsession_duration_sec– про неї пояснення одразу під скріном
Остання колонка – це якраз сума значень параметру engagement_time_msec, що, в GA4, відповідає за час у мілісекундах, проведений на активній вкладці від попереднього івента.
Тобто, якщо людина відкрила сайт о 9:00:00 (відправився перший івент page_view) та доскролила до кінця сторінки о 9:00:10, то о 9:00:10 відправиться івент scroll зі значенням engagement_time_msec = 10000 мілісекунд, або 10 секунд, бо саме стільки пройшло від івенту page_view до scroll. Вважаємо, що всі ці 10 секунд вкладка зі сторінкою була активна.
Оскільки це значення передається в мілісекундах, а нам потрібні секунди, в BigQuery я перевела його простим діленням на 1000 та привела результат до цілого числа:
CAST(
SUM(
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')
) / 1000
AS INT64) AS session_duration_secДодаємо нову колонку в Power Query
Завантажуємо дані в Power BI та йдемо в Power Query:
Тут додаємо нову кастомну колонку:
Даємо їй назву (напр., session_duration) та прописуємо формулу:
#duration(0,0,0,[session_duration_sec])
Ця функція переводить передані в неї значення в дробове число, яке відповідає долі періоду від доби.
Опис вище прямо від Боженьки, згодна. Давайте ще раз.
Синтаксис у функції наступний:
#duration(дні,години,хвилини,секунди)В неї ви передаєте назву колонки, в якій у вас записаний час. У мене він в секундах, тому перші три аргументи у мене нулі, а в останньому – назва колонки з тривалістю в секундах.
Якщо у ваших даних складові тривалості, наприклад, години, хвилини та секунди, знаходяться в різних колонках, то ви задаєте їх всі у відповідних аргументах функції. Вона в свою чергу переводить тривалість у дробове число за принципом розділення кількості, наприклад, секунд на 86400 (кількість секунд в одній добі). Якщо у вас тільки години, то кількість годин ділиться на 24 і тощо. Таким чином одна година стане в Power BI числом 0.04166666667 (3600 секунд / 86400 або 1 година / 24), а один день і одна година (25 годин / 24) - 1.04166666667.
Ну і, звісно, лінк на довідку по ній. Сподіваюсь, стало зрозуміліше, але якщо досі є питання, задавайте їх в коментарях нижче.
Після додавання нової колонки залишилось тільки назначити їй тип Duration (Тривалість) та натиснути Close and Apply, щоб завантажити зміни в PBI.
В Power BI ця колонка має такий вигляд:
Розраховуємо середню тривалість сесії
Ця міра буде складатися з чотирьох змінних, і я буду її писати поступово, щоб детальніше донести, що саме в ній відбувається. До того ж раджу взяти за звичку перевіряти себе на кожному степі написання мір в PBI, якщо її у вас поки немає. Зі змінними це робити набагато простіше.
- Рахуємо середнє значення по колонці
session_duration, яку ми створили в Power Query, та зберігаємо значення в змінну_avg
AVG Session Duration =
VAR _avg = AVERAGE(sessions[session_duration])
RETURN _avgВ розбивці по сесіях ми, звісно, побачимо ті ж цифри, що і в оригінальній таблиці.
Але тепер це також працює і в розбивці по параметрах. Наприклад, по девайсах маємо дані по тривалості на їхньому рівні.
2. Форматуємо значення у зручний для читання вигляд
Все, що стосується форматів, – робота для функції FORMAT. Ось вичерпна довідка по ній. Згідно довідки, для годин треба задати H або HH, в залежності чи хочете ви бачити 0 перед годинами з 0 до 9. Для хвилин використовується N або NN за тією ж логікою. Для секунд – S або SS.
Відповідно доповнюємо нашу міру новою змінною _h та виводимо її значення.
AVG Session Duration =
VAR _avg = AVERAGE(sessions[session_duration])
VAR _h = FORMAT(_avg, "HH:NN:SS")
RETURN _h
Виглядає як те, що нам треба. Але тільки на перший погляд. Ось тут ви б мали зрозуміти, для чого я виводжу міру в розбивці по сесіях, які в звіті використовувати ми не будемо.
Уважні, напевно, помітили, що на попередньому схожому скріні є 2 сесії, тривалість яких більша за 1, тобто більша за добу. Але на другому скріні найдовша сесія у нас 23:13:56. Зафільтрую ті 2 і виведу вам для порівняння 3 міри – зі значенням змінної _avg, окремо _h і правильну, до якої ми йдемо.
Як бачите, наша змінна _h повертає тільки час, який залишився від кількості діб, але ігнорує дні. Сподіваюсь, зараз ви відчули, як легко пропустити щось дуже важливе в процесі розрахунку. За моїм суб’єктивним відчуттям, десь 60-70% часу роботи з даними, точно не менше половини, йде і МАЄ ЙТИ на перевірки і дебаг.
Самовпевненість – лютий ворог аналітика. Вона веде до помилок, які потім ведуть до неправильних даних і неправильних рішень на їхній основі. Тому подружіться з параноєю і постійно перевіряйте те, що ви робите. Не спішіть пояснювати собі те, що ви бачите. Раджу відноситись до всього з певним рівнем недовіри і критично мислити завжди.
І ще одна ремарка: звісно, я б могла дати фінальну формулу і сказати: “ось, юзайте”, і ви б скопіпастили її собі, не розуміючи що в ній взагалі відбувається. Але не для того я пишу свої мануали, не для того…
3. Додаємо дні
Йдемо далі. Фактично змінна _h виводить те, що ми просимо – години, хвилини та секунди. Ми не просимо дні. А треба б. Оскільки дні – це ціла частина дробу, ми можемо забрати її простою функцією INT, яка і поверне ціле число. Додамо нову змінну _days перед _h. Там вона логічно вписується.
AVG Session Duration =
VAR _avg = AVERAGE(sessions[session_duration])
VAR _days = INT(_avg)
VAR _h = FORMAT(_avg, "HH:NN:SS")
RETURN _days
Ця змінна віддає нам дні. Нам залишилось скомбінувати дні та час, при цьому дні виводити ми будемо тільки тоді, якщо дні є, тобто ціле число більше нуля.
AVG Session Duration =
VAR _avg = AVERAGE(sessions[session_duration])
VAR _days = INT(_avg)
VAR _h = FORMAT(_avg, "HH:NN:SS")
RETURN
IF(
_days > 0,
_days & "d " & _h,
_h
)
На рівні звіту і для сесій, можливо, немає сенсу завжди виводити години, якщо ви – не Netflix, тому можна додати ще одну умову – _avg > 0.04155 – вона буде відображати години, якщо вони є (середнє число має бути більшим за 0.04155, це на пару секунд менше години), а в решті випадків – хвилини : секунди зі змінної _m.
Тут є ще нюанс. Функція FORMAT завжди повертає значення, навіть пусті, у форматі тексту. Тому якщо просто повернути _m, ми можемо побачити пусті рядки в звіті – там, де є значення з довідника, але не було сесій. Щоб такого не було, додаємо перевірку – виводити _m тільки, якщо якась тривалість була (значення _avg не пусте). Для цього скористаємось функцією SWITCH, яка за замовчуванням повертає BLANK, що нам і треба, бо PBI такі рядки ховає в віжуалі.
AVG Session Duration =
VAR _avg = AVERAGE(sessions[session_duration])
VAR _days = INT(_avg)
VAR _h = FORMAT(_avg, "HH:NN:SS")
VAR _m = FORMAT(_avg, "NN:SS")
RETURN
SWITCH(
TRUE(),
_days > 0, _days & "d " & _h,
_avg > 0.04155, _h,
NOT ISBLANK(_avg), _m
)Фінальний вигляд
Тепер можна сміливо використовувати нашу фінальну міру в звіті, наприклад, з кількістю сесій та розбивкою по країнах.
Або в тому числі і по девайсах.
Замість висновку
… і для тих, хто продовжує читати 🙂
Не знаю, чи ви подумали про цей, інший, спосіб під час читання статті, але якщо ні, я вам про нього розповім.
Насправді ж поділити на 86400 ми можемо і без Power Query. А ще на етапі обробки даних в BigQuery, дописавши “ / 60 / 60 / 24”.
CAST(
SUM(
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')
) / 1000
AS INT64) / 60 / 60 / 24 as session_duration_bqТип даних цієї колонки можна так і залишити – “дробове число”, не змінюючи на “тривалість”, і просто використати її в фінальній мірі – результат буде однаковим.
Цей спосіб:
а) швидший,
б) не навантажує звіт додатковою колонкою з високою гранулярністю.
О, я зараз чую ваше – “а чого ти, Наташа, не розказала нам про нього одразу ж?!!”
Все просто: я хотіла, щоб ви знали обидва методи. І змогли досягти своєї цілі, не маючи доступу до правок сирих даних та/або якщо години / хвилини / секунди у вас знаходяться в різних колонках.
Загалом, думаю, чим більше способів вирішення задач у вашому “активному лексиконі”, тим оптимізованіше та витонченіше ви працюєте.
Сподіваюсь, тепер ви на мене не дуже злі, але якщо що – можна “висказатися” в коментарях. Для чого ж ще ця можливість тут придумана ? 🙂
Завантаження коментарів…