clickstream-data-platform/docs/labs/optional/revenue-over-time.md

Почему вчерашняя выручка меняется сегодня

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

Факультатив доступен после ступени 1. Понадобятся Airflow и DBeaver с подключением student по инструкции. Пользователь student может читать данные стенда и записывать ваши наблюдения в базу default. Вы будете наблюдать один день оформления заказов, продвигая мир по одному модельному дню. Проверьте, можно ли при загрузке выручки каждый раз считать только новый день. Новые данные останутся в стенде.

Подготовка

Поставьте world_live на паузу. Дождитесь завершения начатых прогонов генерации и обработки, затем запустите etl_pipeline и дождитесь успеха. Так исходное наблюдение будет снято после обновления витрин.

Узнайте последний день событий и дату последнего полученного слепка:

SELECT
    (SELECT max(event_date) FROM dds.event_v) AS event_day,
    (SELECT max(snapshot_date) FROM ods.order_v) AS snapshot_day;

День event_day обозначим D. Наблюдайте именно его до конца работы. Дата слепка, по которому сделано очередное наблюдение, будет S. Это модельные даты магазина. Календарная дата запуска Airflow день D не задает.

Слепок содержит состояние заказов на конец модельных суток и доставляется при следующем запуске генератора. Поэтому покупки последнего завершившегося дня уже видны в событиях, а их заказов в хранилище еще нет. Проверьте это для D в dds.order_v и dm.purchase_vs_orders_v.

Таблицы для истории заказов

Представление dds.order_v показывает текущее состояние заказов, а сырые слепки хранятся ограниченное время. Сохраните историю в двух своих таблицах: потом вы сможете сравнивать наблюдения обычными SQL-запросами в том же DBeaver.

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

CREATE TABLE default.lab_order_history
(
    order_date Date,
    snapshot_date Date,
    order_id String,
    status String,
    items_total Decimal(18, 2)
)
ENGINE = MergeTree
ORDER BY (order_date, snapshot_date, order_id);

CREATE TABLE default.lab_revenue_history
(
    order_date Date,
    snapshot_date Date,
    revenue Decimal(18, 2),
    awaiting_order_ids Array(String)
)
ENGINE = MergeTree
ORDER BY (order_date, snapshot_date);

Первое наблюдение

Напишите два INSERT INTO ... SELECT с явным списком колонок назначения:

Куда записать	Что взять из стенда
default.lab_order_history	По строке на заказ дня D из dds.order_v: order_date, order_id, status, items_total; добавьте дату наблюдения S
default.lab_revenue_history	Одну строку с D, S, суммой revenue за report_date = D из dm.revenue_daily_v и массивом номеров awaiting_order за order_day = D из dm.purchase_vs_orders_v

Во втором запросе сумму и массив получите отдельными агрегатами, чтобы заказы не умножили строки выручки при соединении. Для массива пригодится groupArray(order_id). Дату S берите из max(snapshot_date) в ods.order_v.

Сначала выполните части SELECT отдельно и проверьте результаты, затем сохраните их. До первого слепка заказов дня D еще нет: в таблицу заказов строки не добавятся, но в таблице итогов должна появиться строка с нулевой выручкой. Она зафиксирует, что наблюдение сделано.

Каждую пару D и S сохраняйте один раз: повторный INSERT добавит копии строк. После записи проверьте, что на эту пару приходится одна строка итогов, а число заказов и число разных order_id совпадают с dds.order_v за D.

Выручка этой витрины — стоимость позиций оплаченных заказов без скидки и доставки. Для каждого сохраненного наблюдения посчитайте число заказов по статусам и сумму items_total только у paid. Эта сумма должна сходиться с сохраненной выручкой, сложенной по всем категориям.

Наблюдения за следующие дни

Для каждого следующего наблюдения:

1. Запустите world_next_day с days = 1 и дождитесь успеха.
2. Запустите etl_pipeline и дождитесь успеха: world_next_day сам витрины не обновляет.
3. Повторите два запроса сохранения с новой датой S и проверьте записанные строки.

Во всех запросах остается прежний день D. За один запуск добавляйте один день, чтобы сохранить промежуточные состояния. Пакетный world_next_day отправляет события без пауз; ждать 24 минуты живого дня здесь не нужно.

Продолжайте до слепка D + 7 включительно. Заказы дня D могут меняться семь дней, от D до D + 6. Этот период называется окном изменяемости. Последний шаг нужен для сравнения состояния в конце окна и после него. От исходного наблюдения потребуется восемь однодневных запусков.

Изменения заказов и выручки

Для сравнения прочитайте default.lab_order_history дважды: в одном подзапросе отберите прежнюю S, в другом — новую; день D остается общим. Соедините состояния по order_id, сохранив и заказы без пары. Итоги для этих же D и S возьмите из default.lab_revenue_history. Найдите:

• заказы, отсутствовавшие в первом слепке D, но появившиеся в следующем;
• переходы из created в paid;
• отмены заказов, для которых раньше был виден статус paid.

Первый слепок, полученный на следующий день, — обычная доставка заказов. Опоздавшими здесь считаются те, которых не было уже в этом слепке. Покажите на нескольких номерах, как они исчезают из awaiting_order.

Выберите два соседних наблюдения, между которыми хотя бы один заказ перешел из paid в cancelled. Разложите изменение выручки по заказам. Составьте таблицу: номер заказа, прежний и новый статусы, вклад в выручку до и после изменения. Вклад определите по правилу расчета витрины, указанному выше; учтите и заказы, которых еще не было в первом наблюдении. Сумма изменений вкладов должна совпасть с изменением revenue до копейки.

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

Требования к истории заказов

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

Теперь возьмите отмененный заказ, для которого ни в одном вашем наблюдении нет paid. Проверьте утверждение «этот заказ никогда не был оплачен»: что известно о его состоянии между двумя слепками? Достаточно ли сохраненных наблюдений для такого вывода?

Сформулируйте требования к данным для двух задач: восстановить состояние витрины на момент прошлого расчета и узнать, был ли заказ когда-либо оплачен. Какие сведения для каждой задачи должен передавать источник и сохранять хранилище? Проверьте, каких нужных сведений нет в ваших таблицах наблюдений.

Правило пересчета витрины

Сравните сохраненные состояния при S = D + 6 и S = D + 7: состав заказов, их статусы, суммы и выручку. Какой слепок последним мог изменить заказы дня D и каким прогоном он доставлен? Укажите наблюдение, после которого отчет можно считать окончательным. Было бы достаточно совпадения двух соседних сумм, если бы окно еще не закончилось?

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

После собственных выводов сверьтесь с описанием слепка и его доставки и границ истории отмен.

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

• сохранена последовательность наблюдений одного дня D с датами слепков S;
• на опоздавших заказах и изменениях статусов показаны ошибки загрузки только новых дней или только новых заказов;
• изменение выручки между наблюдениями сходится с изменением вкладов заказов;
• для одного отмененного заказа найдены два наблюдения: с оплатой и с отменой;
• сформулированы требования к истории расчетов и истории оплаты;
• правило пересчета и момент закрытия дня обоснованы наблюдениями и правилом доставки слепков.

После работы оставьте world_live на паузе либо верните автоматическое продвижение, если пользовались им до наблюдения. Таблицы истории останутся для повторной проверки выводов. Когда они больше не нужны, удалите только свои default.lab_order_history и default.lab_revenue_history.