При запуске некой акции необходимо информировать клиента по различным каналам коммуникации (email/push/sms и тд) о старте акции и что нужно сделать, чтобы получить бонус. Например, с 1 июня запускается акция, тогда клиенту будет направлен email со следующим текстом:
“C 1 по 30 июня совершите покупку от 1000 рублей у Партнера X и получите 15% от суммы покупки бонусами”
Каждая коммуникация имеет свой идентификатор. Под идентификатором коммуникации понимается некий уникальный номер, характеризующий время отправки коммуникации, канал и по какой акции совершена коммуникация.
В хранилище данных есть таблица с историей коммуникаций со следующей структурой, таблица с покупками у партнера и таблица с информацией о клиенте:
Таблица на клиента
| Ow_id | уникальный идентификатор клиента |
|---|---|
| Birth_dt | Дата рождения |
| Email_flg | Бинарный флаг. Наличие email-а |
| Mobile_app_flg | Бинарный флаг. Наличие мобильного приложения |
Пример
| Ow_id | Birth_dt | Email_flg | Mobile_app_flg |
|---|---|---|---|
| 123 | 17.06.2019 | 0 | 1 |
| 123 | 25.06.2019 | 1 | 1 |
| 142 | 16.06.2019 | 1 | 1 |
| 123 | 17.06.2019 | 0 | 1 |
| 123 | 17.06.2019 | 0 | 0 |
| 142 | 29.06.2009 | 0 | 0 |
Таблица коммуникаций
| Ow_id | уникальный идентификатор клиента |
|---|---|
| sent_date | дата коммуникации |
| channel_nm | канал коммуникации |
| Partner_id | Уникальный идентификатор партнера |
| Sent | Бинарный флаг. Коммуникация отправлена |
| Delivered | Бинарный флаг. Коммуникация доставлено |
| Opened | Бинарный флаг. Коммуникация открыта |
| Cliked | Бинарный флаг. Переход по ссылке |
| CELL_PK | Уникальный идентификатор коммуникации. По такому идентификатору можно вытащить всех клиентов, которым была отправлена выбранная коммуникация в определенное время. |
Пример
| Ow_id | sent_date | channel_nm | Partner_id | Sent | Delivered | Opened | Cliked | CELL_PK |
|---|---|---|---|---|---|---|---|---|
| 123 | 18.06.2019 | 5555 | 1 | 0 | 0 | 0 | 1788 | |
| 123 | 20.06.2019 | viber | 1111 | 1 | 1 | 1 | 0 | 1720 |
| 142 | 18.06.2019 | 5555 | 1 | 1 | 1 | 1 | 1788 |
Таблица покупок у партнеров
| Ow_id | уникальный идентификатор клиента |
|---|---|
| Partner_id | Уникальный идентификатор партнера |
| POS | Оборот по карте |
| CASH_DT | Дата покупки |
Пример
| Ow_id | Partner_id | POS | CASH_DT |
|---|---|---|---|
| 123 | 5555 | 1001 | 17.06.2019 |
| 123 | 1111 | 200 | 25.06.2019 |
| 142 | 1111 | 500 | 16.06.2019 |
| 123 | 5555 | 800 | 17.06.2019 |
| 123 | 1111 | 600 | 17.06.2019 |
| 142 | 5555 | 700 | 29.06.2009 |
Составить выборку для отправки email-а по партнеру 1111 клиентам, которые не получали коммуникации по данному партнеру за прошлую неделю ни по одному из каналов. Так же необходимо разделить клиентов на три сегмента, исходя из их трат у этого партнера за последние 30 дней:
1. Клиент не совершал покупки у партнера.
2. Клиент совершил покупки на сумму до 1000 р(включительно).
3. Клиент совершил покупки на сумму более 1000 р.
В результате необходимо сформировать таблицу следующего вида:
| Клиент | Сегмент |
|---|
Необходимо найти всех клиентов, чей торговый оборот в текущем месяце увеличился более, чем на 10%, по сравнению с предыдущим месяцем, и вывести коммуникации, которые они получали в течение прошлых 30-ти дней.
Необходимо помесячно:
• Количество отправленных коммуникаций, количество доставленных коммуникаций, количество открытых коммуникаций, количество коммуникаций с переходами по ссылке
• Среднее количество отправленных коммуникаций на клиента, среднее количество партнеров, по которым клиент получил коммуникации, среднее количество каналов, по которым клиент получил коммуникации
Необходимо посчитать показатели выше 1-3 запросами. В результате выполнения запросов должна сформироваться таблица следующего вида (все метрики из обоих пунктов задачи должны быть собраны в одной таблице):
| Месяц | Кол-во отправленных коммуникаций | Кол-во доставленных коммуникаций | …… | среднее кол-во каналов, по которым клиент получил комм-ии |
|---|
В период 3.12-5.12 пользователям приложения рассылались пуш-уведомления с предложением посетить раздел приложения «Партнёры» и ознакомиться с партнёрскими скидками. Для сегмента была выделена контрольная группа.
Воронка получилась следующая:
| Пилотная группа | Контрольная группа | |||
|---|---|---|---|---|
| Всего отправлено (для контрольной группы - объем ЦА) | 139 545 | 14 976 | ||
| Всего доставлено | 69 337 | |||
| Зашли в мобильный банк 3.12-5.12 | 25 483 | 18,3% | 2 606 | 17,4% |
| Зашли в партнерский раздел 3.12-5.12 | 5 780 | 22,7% | 567 | 21,8% |
Необходимо: • Проанализировать воронку рассылки и сделать выводы: можно ли считать рассылку успешной? Что можно изменить в процессе рассылки в следующий раз, чтобы увеличить ее эффективность?