clickstream-data-platform/docs/labs/1-3-session-overview.md

Задание 3: визиты по устройствам, dm.session_overview

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

Подготовка

• «Хранилище и его слои»;
• «Генерация и обработка данных в Airflow».

Показатели визитов

Витрина нужна, чтобы по дням сравнивать визиты с телефона и компьютера: их число, длительность, просмотры страниц и частоту покупок.

Сессии уже рассчитаны в DDS. Из них вы соберете дневные показатели по устройствам. Почему такие итоги хранят в отдельной таблице, объясняет справочник DM.

Создание витрины

Читайте готовые сессии из dds.session_v. В результате нужна одна строка на категорию устройства за день: например, итоги всех визитов с телефонов за один день.

Создайте пять объектов по общим правилам ступени 1:

• основные таблицы dm.session_overview_rep и dm.session_overview_dist;
• промежуточные таблицы dm.session_overview_stage_rep и dm.session_overview_stage_dist;
• представление для чтения dm.session_overview_v.

Разбейте данные на партиции по дням.

Состав колонок

Колонка	Тип	Значение
report_date	Date	День сессии
device_category	LowCardinality(String)	Категория устройства
sessions	UInt64	Число визитов
visitors	UInt64	Число разных client_id
bounce_sessions	UInt64	Визиты ровно из одного события
purchase_sessions	UInt64	Визиты хотя бы с одной покупкой
duration_seconds_total	UInt64	Суммарная длительность визитов
events_total	UInt64	Число событий во всех визитах
pageviews	UInt64	Число просмотров страниц
cart_adds	UInt64	Число добавлений в корзину
purchases	UInt64	Число покупок
_load_id	String	Идентификатор запуска, собравшего день
_load_ts	DateTime64(3, 'UTC')	Время сборки

Возьмите device_category из сессии: DDS уже заменил код словом. Из четырех категорий встречаются desktop, mobile и tablet. Строку для tv создавайте только при появлении таких визитов.

Для расчета средней длительности храните общую длительность и число сессий. Готовую среднюю avg_duration_seconds сохранять не нужно. Число сессий в категориях различается, поэтому среднее из трех средних даст неверный ответ за день. Чтобы получить его правильно, разделите сумму duration_seconds_total на сумму sessions. Для доли отказов разделите сумму bounce_sessions на сумму sessions. Усреднение готовых долей тоже даст неверный результат.

Дни для пересчета

Сессии завершившегося модельного дня больше не меняются. Как в задании 2, найдите первый завершившийся день, которого еще нет в витрине. Рассчитайте все дни от него до последнего завершившегося, запишите их в промежуточную таблицу и замените дневные партиции основной. Добавьте задачу в dm_transform.

Ключ шардирования

Выберите ключ самостоятельно. Визитов с планшета мало, с телефонов и компьютеров — значительно больше. Если хотите взять хеш только от device_category, сначала посчитайте строки на каждом шарде. Затем объясните, почему такое распределение вас устраивает.

У основной и промежуточной таблиц должен быть одинаковый ключ шардирования: см. общие правила ступени 1.

Число посетителей за день

Сравните сумму visitors по устройствам за день с dm.daily_traffic_v. На стенде значения совпадут: одному client_id всегда соответствует одно устройство, поэтому за день он учитывается только в одной категории. Это свойство мира стенда; см. раздел «Паспорт куки» в словаре понятий.

Если появятся визиты одного client_id с двух устройств, сумма начнет завышать аудиторию. Проверки счетчиков по категориям этого не обнаружат. Число посетителей дня берите из dm.daily_traffic_v, где оно рассчитано для дня целиком.

Типичная ошибка

Повторное выделение визитов из событий может дать другие границы сессий, чем в DDS. Используйте готовые сессии из dds.session_v. Если общее число событий совпало с источником, а число сессий завышено, вероятная причина — сборка витрины из событий вместо сессий.

Проверка результата

Рассчитайте те же показатели по dds.session_v за выбранный день. Сопоставьте результат со строками dm.session_overview_v за этот день через FULL OUTER JOIN по device_category. Найдите категории, которые есть только с одной стороны, и строки, где различается хотя бы один счетчик. Расхождений должно быть ноль.

Еще две проверки дневных итогов:

• сумма sessions равна числу строк dds.session_v за день — без потерь и повторного учета визитов;
• сумма events_total равна сумме events_total по сессиям того же дня.

Результат работы

• у вас есть пять объектов витрины; промежуточная таблица объявлена через AS и использует ключ шардирования основной;
• вы явно перечислили колонки в dm.session_overview_v и собрали показатели из готовых сессий dds.session_v;
• в таблице хранятся суммы и счетчики для расчета средних и долей;
• проверка session_overview в homework_check прошла успешно;
• витрина обновляется при запуске dm_transform.