clickstream-data-platform/docs/labs/1-5-utm-effectiveness.md

Задание 5: показатели рекламы, dm.utm_effectiveness

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

Подготовка

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

Покупки по рекламным каналам

Аналитик сравнивает рекламные каналы по дням: трафик, добавления в корзину, покупки и их суммы из событий сайта.

События сайта показывают действия посетителя и рекламные метки его переходов. Заказы магазина служат источником выручки. Но в заказах нет UTM-меток, поэтому в этой витрине вы возьмете суммы из событий покупки. Их передает браузер посетителя; дальше они называются клиентскими суммами. Приставка declared_ в имени показателя напоминает об этом источнике.

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

Читайте события из dds.event_v, без других витрин DM. В результате нужна одна строка на день и сочетание трех меток: utm_source, utm_medium, utm_campaign. События одного дня с одинаковыми метками попадают в одну группу.

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

• основные таблицы dm.utm_effectiveness_rep и dm.utm_effectiveness_dist;
• промежуточные таблицы dm.utm_effectiveness_stage_rep и dm.utm_effectiveness_stage_dist;
• представление для чтения dm.utm_effectiveness_v.

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

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

Колонка	Тип	Значение
report_date	Date	День события
utm_source	LowCardinality(String)	Источник кампании
utm_medium	LowCardinality(String)	Канал кампании
utm_campaign	String	Имя кампании
visitors	UInt64	Число разных client_id
pageviews	UInt64	Число просмотров страниц
add_to_carts	UInt64	Число добавлений в корзину
purchases	UInt64	Число покупок
declared_revenue	Decimal(18, 2)	Сумма покупок, заявленная клиентом
_load_id	String	Идентификатор запуска, собравшего день
_load_ts	DateTime64(3, 'UTC')	Время сборки

Для declared_revenue сложите purchase_revenue[1] событий purchase. В параллельных массивах purchase* хранятся один номер заказа и одна сумма на событие. В сверке purchase_vs_orders используют тот же индекс.

Тип purchase_revenue — Array(Decimal(18, 2)): исходный Float64 преобразован в ODS. Сложение Decimal точное; CAST до или после sum не меняет результат и не нужен. Тип итога — Decimal(18, 2).

Для utm_campaign выбран тип String: имен кампаний со временем становится много, и затраты на словарь LowCardinality перестают окупаться.

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

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

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

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

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

Рекламные метки событий

Группируйте события по их собственным меткам. Модели «первое касание» и «последнее касание» здесь не рассчитываются.

В dds.session_v записаны UTM первого события визита. Распределение по каналам может отличаться от этой витрины, и это не означает ошибку в расчете. Укажите в отчете, что распределяете по рекламным каналам отдельные события. Такое распределение называют атрибуцией.

События без рекламных меток

Сохраните события без меток: прямой трафик обычно крупнейший канал, и без него доля рекламы будет завышена. Если отфильтровать такие строки, дневные итоги перестанут сходиться с источником — а по этому совпадению вы проверяете сборку. Разница составит вклад исключенных событий.

Оставьте пустые метки пустыми строками: новое обозначение прямого трафика потребовало бы объяснения.

Расхождение с dm.revenue_daily_v

Сравните за один день sum(declared_revenue) своей витрины и sum(revenue) из dm.revenue_daily_v. Суммы не сходятся; проверять их равенство не нужно.

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

• событие покупки остается после отмены заказа, а revenue_daily учитывает только оплаченные заказы;
• событие покупки может потеряться при доставке, хотя заказ поступил, или поступить дважды;
• клиентская сумма может отличаться от серверной суммы позиций: генератор намеренно создает это расхождение у одного-двух заказов из ста.

Запишите свой вывод в двух-трех фразах. Число расхождений по заказам и их причины можно посмотреть в dm.purchase_vs_orders_v, где выделены классы расхождений.

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

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

Дополнительно сверьте дневные итоги:

• суммы pageviews, add_to_carts и purchases равны числу событий соответствующих типов в dds.event_v;
• сумма declared_revenue равна сумме purchase_revenue[1] по всем событиям purchase того же дня.

Сумма visitors по строкам повторно учитывает одного посетителя, приходящего по разным меткам. Для числа посетителей дня она не подходит.

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

• у вас есть пять объектов витрины; промежуточная таблица объявлена через AS и использует ключ шардирования основной;
• строки без UTM-меток сохранены, а declared_revenue хранится в Decimal(18, 2);
• вы записали, почему сумма отличается от dm.revenue_daily_v;
• проверка utm_effectiveness в homework_check прошла успешно;
• витрина обновляется при запуске dm_transform.