Как мы анализировали ретеншн автостраховки в Катаре

Привет! Я Исламбек Темирбек, Data Analyst в QIC digital hub.

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

О том, как мы его проводили и визуализировали, я расскажу в этом материале.

Как мы анализировали ретеншн автостраховки в Катаре

Покупка и продления страховых полисов совершаются из таких источников, как:

– Агенты

– Онлайн

– Оффлайн

Это можно увидеть по следующим двум графикам покупок и продления полисов за 2023 год. Покупка новых полисов чаще всего совершалась онлайн и через агентов (52% и 36%), а продление происходило через онлайн и оффлайн каналы (66% и 29% соответственно). У нас есть такие данные с 2017 года по 2023 год с тремя основными источниками продаж, поэтому необходимо сделать детальный ретеншн-анализ , чтобы понять, как продлеваются страховки на авто от года к году из разных источников.

Retention rate – ключевая метрика, обозначающая привязанность пользователей к продукту. По закону Парето, 20% постоянных покупателей приносят 80% выручки. Для нашей компании, как и для многих других, привлечение новых пользователей часто обходится дороже, чем удержание старых.

Существуют 2 основных метода для расчета ретеншн:

N-Day Retention: оценивает, сколько пользователей остаются активными через N дней после первого взаимодействия с приложением. Например, если мы анализируем 7-Day Retention, то смотрим, сколько пользователей продолжают использовать приложение на седьмой день после установки.
Rolling Retention: измеряет процент пользователей, которые остаются активными на определенном временном интервале после первого входа в приложение. Например, если мы рассматриваем 7-Day Rolling Retention, то оцениваем, сколько пользователей остаются активными в любые семь дней после первого входа, а не только в течение первых семи дней.

* Более подробно о методах расчета ретеншн можно прочитать на Go practice.

В нашем случае мы будем использовать N-Day Retention в когортах, используя диаграмму Sankey для визуализации нескольких источников покупок и продления полисов по годам.

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

С помощью диаграммы Sankey мы иллюстрировали поток ресурсов, отражая как их источники, так и куда они уходят. Мы решили применить диаграмму Sankey в нашей работе из-за её способности поддерживать несколько уровней просмотра. Это позволило нам эффективно представлять общую картину данных и анализировать детали. Если интересно узнать больше о возможностях диаграммы, рекомендую изучить статью от Allison Stafford, менеджера по аналитике в ThriftBooks.

В примере нашего графика можно заметить, как полисы, приобретенные в разные годы, продлеваются в последующие годы через разные источники покупки. По графику видно, что всего 26% новых полисов, проданных агентами, стабильно продлеваются, в то время как при продаже онлайн и в оффлайн-точках — более 40%.

А теперь я остановлюсь подробнее на том, как мы писали SQL запросы и обрабатывали данные для получения такой визуализации.

Вот так выглядит исходная таблица, которая нам нужна:

Давайте разберемся подробнее.

Первоначальная таблица `motor_policy` содержит информацию о полисах: дате покупки (`date`), идентификаторе полиса (`policy_id`), типе транзакции (`transaction_type`), названии продукта (`product_name`) и источнике бизнеса (`business_source`). В ней есть информация о всех транзакциях с полисами, включая как новые покупки, так и продления.

Однако для анализа когортного поведения нам необходимо преобразовать данные таким образом, чтобы для каждого полиса были представлены его первоначальная покупка и все последующие продления, если они есть. Таким образом, мы строим новую таблицу `cohort_data`.

В таблице `cohort_data` каждая строка представляет собой конкретный полис в определенном году (`date_year`). Столбец `future_year` указывает на год следующего обновления или продления полиса; если он пустой, то полис не продлился. Столбец `period` отображает разницу в годах между первоначальной покупкой полиса и его обновлением, что помогает нам понять, на каком этапе находится полис (например, "year 1" означает первое обновление после покупки). Таким образом, для каждой строки в `cohort_data`, мы можем видеть историю покупок и продлений конкретного полиса.

Новая таблица `cohort_data` помогает нам агрегировать данные о полисах и их обновлениях для дальнейшего анализа когортного поведения. Для формирования данной таблицы, нужен следующий запрос:

, cohort_data as (SELECT date_trunc(curr_data.date, year) as date_year , curr_data.policy_id as policy_id , curr_data.transaction_type as transaction_type , curr_data.product_name , curr_data.business_source , date_trunc(future_data.date, year) as future_year , 'year '|| DATE_DIFF(future_data.date, curr_data.date, year) as period , future_data.transaction_type as future_transaction_type , future_data.product_name as future_product_name , future_data.business_source as future_business_source FROM db.datamart as curr_data left join motor_policy as future_data on curr_data.policy_id = future_data.policy_id and DATE_DIFF(future_data.date, curr_data.date, year) = 1 order by 1 ) select * from cohort_data

В запросе используется концепция LEFT JOIN одной и той же таблицы с ключами policy_id и DATE_DIFF (future_data.date, curr_data.date, year) = 1.

Это означает, что полисы этого года соединяются с продлениями следующего.

Далее мы применяем GROUP BY GROUPING SETS, что позволяет группировать данные по разным комбинациям столбцов (полезно для агрегирования данных по различным уровням детализации).

*Подробнее о том, что такое SQL GROUPING SETS Clause и как его использовать, можно прочитать в статье Andrew Bone.

, pre_final_data as ( SELECT date_year , transaction_type , business_source , future_year , future_transaction_type , future_business_source , count(distinct policy_id) as policy_count FROM cohort_data where date_year <= "2023-01-01" GROUP BY GROUPING SETS((date_year, transaction_type, business_source), (date_year, transaction_type, business_source, future_year, future_transaction_type, future_business_source)) order by 1,2,3,4,5,6,7 desc ) select * from pre_final_data

Мы используем GROUPING SETS для группировки данных по различным комбинациям столбцов date_year, transaction_type, business_source, а также их комбинации с future_year, future_transaction_type, future_business_source. GROUPING SETS группирует данные по двум группам с префиксом future и без.

Проанализировав данные в таблице, можно заметить, что строки без future имеют значения policy_count = 13150 и 19003. Значение policy_count = 19003 представляет все новые полисы, проданные агентами в 2022 году, а policy_count = 13150 - это отток, то есть полисы, которые не были продлены в 2023 году. Путем вычислений можно определить отток: 19003 - (17+1130 + 1650 + 2500) = 13150 отток.

Не смотря на то, что таблицу можно использовать для анализа в ее текущем виде, мы можем улучшить ее внешний вид и исключить из нее отток. Например, можно исключить полисы с policy_count = 12047 (не продленные в 2022 году) путем LEFT JOIN с предыдущей таблицей, но только с данными, содержащими префикс future.

, curr_data as ( SELECT date_year , transaction_type , business_source , MAX(policy_count) as curr_policy_count FROM pre_final_data WHERE future_year is null GROUP BY 1,2,3 ORDER BY 1,2,3 ) SELECT * , round(policy_count / curr_policy_count*100) as retention FROM curr_data LEFT JOIN pre_final_data USING(date_year, transaction_type, business_source) WHERE pre_final_data.future_year is not null and future_business_source is not null ORDER BY 1,2,3,5,6,7

Если объединить все предыдущие этапы, полный запрос для получения финальной таблицы выглядит так:

with motor_policy as ( SELECT date , policy_id , transaction_type , product_name , business_source FROM `db.datamart` where date >= "2017-01-01" and lob_code = "01" and transaction_type <> "Endorsement" ) , cohort_data as (SELECT date_trunc(curr_data.date, year) as date_year , curr_data.policy_id as policy_id , curr_data.transaction_type as transaction_type , curr_data.product_name , curr_data.business_source , date_trunc(future_data.date, year) as future_year , 'year '|| DATE_DIFF(future_data.date, curr_data.date, year) as period , future_data.transaction_type as future_transaction_type , future_data.product_name as future_product_name , future_data.business_source as future_business_source FROM db.datamart_policy as curr_data left join motor_policy as future_data on curr_data.policy_id = future_data.policy_id and DATE_DIFF(future_data.date, curr_data.date, year) = 1 order by 1 ) , pre_final_data as ( SELECT date_year , transaction_type , business_source , future_year , future_transaction_type , future_business_source , count(distinct policy_id) as policy_count FROM cohort_data where date_year <= "2023-01-01" GROUP BY GROUPING SETS((date_year, transaction_type, business_source), (date_year, transaction_type, business_source, future_year, future_transaction_type, future_business_source)) order by 1,2,3,4,5,6 ) , curr_data as ( SELECT date_year , transaction_type , business_source , MAX(policy_count) as curr_policy_count FROM pre_final_data WHERE future_year is null GROUP BY 1,2,3 ORDER BY 1,2,3 ) SELECT * , round(policy_count / curr_policy_count*100) as retention FROM curr_data LEFT JOIN pre_final_data USING(date_year, transaction_type, business_source) WHERE pre_final_data.future_year is not null and future_business_source is not null ORDER BY 1,2,3,5,6,7

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

Вуаля, наша финальная таблица готова:

Например, только 14% полисов, приобретенных в 2022 году через агентов, продлевались в онлайне в следующем году, в сравнении с 65% полисов приобретенных онлайн. Чтобы заметить такую детализацию нужно визуализировать правильно.

Теперь важная часть – получить интересующую визуализацию, и для этого я использовал Sankeymatic. Здесь никакой магии, я вручную внес все полученные цифры. Возможно, вы здесь предложите что-то лучше как можно было бы автоматизировать, буду рад вашим комментариям.

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

Плюсы данного ретеншн-анализа:

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

Минусы:

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

Выводы:

Важно понимать, как продлеваются полисы или как пользователи возвращаются в продукт.
Если у вас несколько источников совершения покупок, то очень удобно использовать диаграмму Sankey с разными когортами.
Написания запроса для ретеншн анализа использует join для объединения полисов по ID и по первой дате покупки с последующими продлениями. Дополнительно, запрос имеет функцию grouping sets для группировки данных по различным комбинациям столбцов.
Для визуализации используется самый простой онлайн инструмент – Sankeymatic, где требуется ручная работа.

Как мы анализировали ретеншн автостраховки в Катаре

Как происходит покупка и продление страховых полисов

Выбор метода ретеншн-анализа

Готовим данные для ретеншн-анализа

Плюсы, минусы, итоги