Задание #1
Дашборд продаж с возвратами
У команды продукта новая квартальная задача: работа с возвратами. Чтобы вы могли вывести аналитику возвратов в дашборды, команда IT выкатила в релизе новое поле в таблице с заказами (инвойсами) -- тип заказа (type).
Теперь там есть не только приход (income), но и возвраты (refund). При этом возвратные инвойсы – это не статус старого заказа, а новые заказы, которые дважды в неделю добавляет вручную отдел продаж и теперь в дашбордах если просто выводить сумму по выручке без фильтра по типу заказа, то каждый заказ, по которому был проведен возврат, дублируется возвратным инвойсом и у каждого инвойса свой invoice_id.
Ваша задача – собрать дашборд, который будет показывать продактам динамику и регулярную ситуацию по возвратам в контексте общей ситуации по продуктам.
Это продуктовый дашборд, который должен помогать продуктовой команде отслеживать ситуацию по возвратам и указывать на то, в каких срезах проблемы и куда стоит направить внимание, чтобы сократить возвраты.
Сумма возврата не всегда равна сумме платежа, так как в первичных оплатах мы удерживаем комиссию платежных систем, а в продлениях чаще всего возвращаем полную сумму.
Пришлите в результате проработки темы:
1/ Запрос или запросы, которые позволяют предобработать данные для вашей задачи с помощью SQL 2/ Информативный дашборд. Используйте доступные данные и любую систему визуализации. Мы используем Looker. 3/ Логику вашего дашборда (можно в текстовом формате, можно в формате скринкаста):
как продакт должен смотреть на данные
на какие вопросы отвечают таблицы и графики
какие ограничения данных стоит учитывать продактам, анализируя данные дашборда.
4/ Если текущих данных недостаточно, чтобы ответить на какие-то ваши вопросы, напишите запрос в IT на доработку полей в таблице заказов.
Материалы для выполнения задания: 1/ Ссылка на датасет 2/ Документация к БД
* Дашборд делайте на выгрузке, не нужно подключаться к тестовому BQ из BI-системы
поля таблицыописание поля--определение продуктов/курсов
schoolшкола productпродукт внутри школы course_idID внутри курса course_titleназвание курса--срезы дат
paid_dateдата оплаты invoice_created_dateдата создания платежки, при инициации клиентом может создаваться на один заказ несколько раз в зависимости от платежной системы, также продления подписки идут под одним заказом. Возвратные инвойсы создаются вручную специалистами отдела продаж order_created_dateдата регистрации заказа subscription_created_dateдата старта подписки--срезы валюты/страны
currencyвалюта оплаты payment_systemплатежная система countryстрана, из которой проведена оплата--срезы покупок
paid_count1 - по ивойсу проведен один платеж любого типа paid_amountсумма платежа в евро first_paid_count1 - по инвойсу проведен один первичный платеж в подписке или платеж по единоразовым покупкам first_paid_amountсумма первичного платежа в евро manual_created_count1 - заказ создан вручную отделом продаж manual_created_amountсумма по ручному заказу в евро extend_subscription_count1 - по инвойсу проведено одно продление extend_subscription_amountсумма продления в евро--доп параметры
subscription_periodколичество месяцев в тарифе rankочередность оплаты в подписке0 - единоразовая покупка без подписки1 - первичный платеж2 и т.д. - очередное продление parent_invoice_idID исходного платежа, который присваивается платежам типа refund для прямой связи прихода и возврата typeтип инвойса