Постановка задачи
В конкурентной телекоммуникационной сфере восприятие компании в целом формируется критической оценкой ее услуг клиентами. Сбои в работе чреваты ростом обеспокоенности, поэтому так важен анализ оттока.
Уровнем оттока характеризуется потеря клиентов, а это сказывается на доходах.
На выводы анализа опираются при разработке стратегий, сегментном таргетировании, совершенствовании обслуживания для роста доверия и удовлетворенности клиентов.
Что такое «отток клиентов»?
Отток, или оборот клиентов, называемый также оттоком покупателей, — это явление, при котором клиенты или подписчики прекращают сотрудничать с компанией или пользоваться ее продуктами/услугами. Оно случается, когда привлеченные ее деятельностью или предложениями клиенты решают отказаться от дальнейших отношений или покупок.
Возможные причины: предложения конкурентов, цена, неудовлетворенность продуктом или услугой, недостаточная их ценность в глазах покупателя, личные обстоятельства.
Очистка данных
Определение общего количества клиентов
select distinct count(customer_id) as TotalCustomers from churndata;
То есть выбираем из churndata отдельный счетчик count(customer_id) по общему числу клиентов TotalCustomers:
В наборе данных найдено 7032 клиента.
Проверка дублей
select customer_id , count(customer_id) as no_of_times from churndata group by customer_id having count(customer_id)>1;
То есть выбираем из churndata customer_id, считаем count(customer_id) в no_of_times, группируем по customer_id со счетчиком count(customer_id)>1:
Запросом в таблице данные не возвращаются, то есть дублей и повторных записей в наборе данных нет — все значения уникальные.
Проверка наличия значений «null»
Поскольку нельзя определить, в каких столбцах — строкового типа данных или целочисленного — содержатся значения «null», выполним полную проверку:
select ‘SeniorCitizen’ as ColumnName, count(*) as nullcount from churndata where ‘SeniorCitizen’ is null UNION
select ‘tenure’ as ColumnName, count(*) as nullcount from churndata where ‘tenure’ is null UNION
select ‘MonthlyCharges’ as ColumnName, count(*) as nullcount from churndata where ‘MonthlyCharges’ is null UNION
select ‘TotalCharges’ as ColumnName, count(*) as nullcount from churndata where ‘TotalCharges’ is null UNION
select ‘numAdminTickets’ as ColumnName, count(*) as nullcount from churndata where ‘numAdminTickets’ is null UNION
select ‘churn’ as ColumnName, count(*) as nullcount from churndata where ‘churn’ is null UNION
select ‘numTechTickets’ as ColumnName, count(*) as nullcount from churndata where ‘numTechTickets’ is null;
То есть из churndata выбираем SeniorCitizen, tenure, MonthlyCharges, TotalCharges, numAdminTickets, numTechTickets в ColumnName, считаем count(*) в nullcount, где SeniorCitizen, tenure, MonthlyCharges, TotalCharges, numAdminTickets, numTechTickets — это все null, и объединяем каждый раз с помощью UNION:
В таблице данных нет, поэтому о null беспокоиться не нужно. Данные очищены и теперь готовы к извлечению из них выводов.
Изучение данных
Вопросы на основе данных
- Каково распределение оттока клиентов по полу?
Select gender, count(*) as Totalcustomer, sum(churn) as Customers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by gender order by churnrate desc;
То есть из churndata выбираем пол gender, считаем count(*) в Totalcustomer, суммируем отток sum(churn) по клиентам Customers, приводим сумму по оттоку, деленную на посчитанное count(*), умножаем на 100 cast(sum(churn) * 1.0 / count(*) *100 и получаем уровень оттока churnrate в виде десятичного числа (10,2), то есть с максимальной суммарной точностью 10 цифр, две из которых после десятичной точки и восемь до. Группируем по полу, упорядочиваем по убыванию:
У женщин уровень оттока выше, но разница относительно невелика. Поэтому спрогнозировать отток клиентов исключительно по полу нельзя.
2. Рассчитаем общие затраты ушедших клиентов в зависимости от пола.
select gender, count(*) as totalcustomer, round(sum(TotalCharges),2) as totalcharges from churndata group by gender order by totalcharges desc;
То есть из churndata выбираем пол gender, считаем count(*) в totalcustomer, округляем сумму общих затрат round(sum(TotalCharges),2) в totalcharges. Группируем по полу, упорядочиваем общие затраты по убыванию:
Разница незначительна, общие уплаченные суммы тоже близки. Поэтому уплаченная сумма или затраты — это не решающий фактор для оттока.
3. Каков уровень оттока среди старшего поколения?
select case when SeniorCitizen=1 then ‘Senior’ else ‘non-senior’ end as SeniorCitizen, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, round(sum(TotalCharges),2) as totalcharges, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by SeniorCitizen order by churnrate desc;
То есть из churndata, в случае когда SeniorCitizen=1, выбираем Senior, иначе non-senior, выполняем end в SeniorCitizen, считаем count(*) в Totalcustomer, суммируем отток sum(churn) по ушедшим клиентам ChurnedCustomers, округляем сумму общих затрат round(sum(TotalCharges),2) в totalcharges, приводим сумму по оттоку, деленную на посчитанное count(*), умножаем на 100 cast(sum(churn) * 1.0 / count(*) *100 и получаем уровень оттока churnrate в виде десятичного числа (10,2), то есть с максимальной суммарной точностью 10 цифр, две из которых после десятичной точки и восемь до. Группируем по старшему поколению, упорядочиваем по убыванию:
Среди общего числа клиентов большее число ушедших — это молодежь non-senior, а вот уровень оттока выше среди старшего поколения.
То есть на уровне оттока клиентов сказывается принадлежность к старшему поколению: по сравнению с молодежью здесь этот уровень выше, а затраты ниже; молодежь предпочитает эти услуги больше, нежели старшее поколение.
4. Каков общий уровень оттока клиентов, а также семейных и одиноких?
Select Partner, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by Partner order by churnrate desc;
То есть из churndata выбираем семейный Partner, далее — аналогично, например, приведенному в пункте 1. Группируем по семейным, упорядочиваем по убыванию:
Одинокие клиенты уходят чаще семейных, и уровень оттока у них заметно выше. То есть семейность связана с меньшей вероятностью оттока.
5. Каков уровень оттока клиентов в зависимости от продолжительности пользования услугами?
sSELECT CASE
WHEN tenure <=12 THEN ‘0–12’
WHEN tenure <=24 THEN ‘12–24’
WHEN tenure <=36 THEN ‘24–36’
WHEN tenure <=48 THEN ‘36–48’
WHEN tenure <=60 THEN ‘48–60’
ELSE ‘60+’
END AS tenurerange,
ROUND((SUM(CASE WHEN churn =’1′ THEN 1 ELSE 0 END )/ COUNT(*) *100), 2) AS churnrate,
COUNT(*) AS Totalcustomer,
SUM(churn) AS ChurnedCustomers
FROM churndata
GROUP BY tenurerange
ORDER BY churnrate DESC;
Самый высокий отток наблюдается среди клиентов с продолжительностью пользования до года, они менее заинтересованы становиться долгосрочными подписчиками.
Возможное решение здесь — стратегия привлечения и удержания клиентов с помощью специальных предложений и вознаграждений.
6. Каков уровень оттока клиентов, которые пользуются услугами телефонной связи?
Select PhoneService, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by PhoneService order by churnrate desc;
То есть из churndata выбираем телефонную связь PhoneService, далее — аналогично, например, приведенному в пункте 1. Группируем по телефонной связи PhoneService, упорядочиваем по убыванию:
Большинство клиентов выбрали услуги телефонной связи, уровень оттока у них немного отличается по сравнению с теми, кто выбрал также резервное копирование онлайн.
В случае с PhoneService удержано больше клиентов, даже не имевших телефонной связи, по сравнению с числом клиентов, которые прекратили пользоваться этой услугой.
7. Каков отток пользователей услуг многоканальной связи, онлайн-безопасности, резервного копирования онлайн, защиты устройств, техподдержки? Какую услугу выбирают чаще и при этом у нее самый высокий отток?
Select MultipleLines, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by MultipleLines order by churnrate desc;
То есть из churndata выбираем многоканальную связь MultipleLines, далее — аналогично, например, приведенному в пункте 1. Группируем по многоканальной связи MultipleLines, упорядочиваем по убыванию:
В случае с многоканальной связью ее наличие или отсутствие не сказывается на оттоке: он отличается незначительно, поэтому перейдем сразу к следующему аспекту.
Select OnlineSecurity, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by OnlineSecurity order by churnrate desc;
То есть из churndata выбираем онлайн-безопасность OnlineSecurity, далее — аналогично, например, приведенному в пункте 1. Группируем по онлайн-безопасности, упорядочиваем по убыванию:
Это главная тема, ее и обсудим. Здесь разница в оттоке больше: у клиентов с онлайн-безопасностью отток ниже, чем у клиентов без нее, еще ниже он у клиентов без интернет-услуг. То есть уходить предпочитают клиенты без онлайн-безопасности.
Select OnlineBackup, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by OnlineBackup order by churnrate desc;
То есть из churndata выбираем резервное копирование онлайн OnlineBackup, далее — аналогично, например, приведенному в пункте 1. Группируем по резервному копированию онлайн, упорядочиваем по убыванию:
Другой фактор — немалое влияние на отток услуг резервного копирования онлайн. Теперь данные чище и очевидно, что отток выше среди клиентов, которые не выбрали эти услуги.
То есть клиенты выбирают резервное копирование онлайн в целом неохотно, но даже среди тех, кто выбрал его, отток заметен.
select DeviceProtection, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by DeviceProtection order by churnrate desc;
То есть из churndata выбираем защиту устройств DeviceProtection, далее — аналогично, например, приведенному в пункте 1. Группируем по защите устройств, упорядочиваем по убыванию:
Учитывая выводы выше и текущий анализ, становится очевидным, что на оттоке сильно сказываются все услуги, связанные с безопасностью. Это наблюдение согласуется с обсужденным ранее контекстом, где процент клиентов, которые не выбрали эти услуги, был бо́льшим, и в итоге ушло более трети.
Select TechSupport, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by TechSupport order by churnrate desc;
То есть из churndata выбираем техподдержку TechSupport, далее — аналогично, например, приведенному в пункте 1. Группируем по техподдержке, упорядочиваем по убыванию:
Когда главное — удовлетворенность клиентов, важнейшим фактором становятся услуги, связанные с онлайн-безопасностью, резервным копированием онлайн, защитой устройств, техподдержкой.
Очевидно, значительное число клиентов предпочли сменить поставщика услуг из-за неудовлетворительного качества обслуживания, связанного с этими конкретными аспектами.
8. Сказываются ли на оттоке клиентов подписки на потоковое тв и фильмы?
Select StreamingTV, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by StreamingTV order by churnrate desc;
То есть из churndata выбираем потоковое ТВ StreamingTV, далее — аналогично, например, приведенному в пункте 1. Группируем по потоковому ТВ, упорядочиваем по убыванию:
Select StreamingMovies, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by StreamingMovies order by churnrate desc;
То есть из churndata выбираем потоковые фильмы StreamingMovies, далее — аналогично, например, приведенному в пункте 1. Группируем по потоковым фильмам, упорядочиваем по убыванию:
Значения подписок на потоковое ТВ и фильмы очень похожи. Из тех клиентов, что подписались на потоковое ТВ и фильмы, ушло значительное количество. И проблема, похоже, не связана исключительно с потоковым контентом.
9. Зависит ли отток от типа контракта и как?
Select Contract, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by Contract order by churnrate desc;
То есть из churndata выбираем контракт Contract, далее — аналогично, например, приведенному в пункте 1. Группируем по контракту, упорядочиваем по убыванию:
Отток клиентов особенно велик для одномесячных контрактов. Клиенты тестируют различные сервисы и, чтобы сэкономить, выбирают услугу на месяц.
Сказываются и возможные проблемы с бесперебойностью интернета, потоковых сервисов, телефонной связи. У каждого клиента свои приоритеты, и неудовлетворительная работа любого из этих компонентов чревата отказом от использования всей услуги.
10. Сказываются ли контракты на потоковых сервисах?
SELECT
Contract,
SUM(CASE WHEN StreamingTV IN (‘yes’) THEN 1 ELSE 0 END) AS TotalCustomers,
SUM(Churn) AS ChurnedCustomers,
CAST(SUM(Churn) * 100.0 / SUM(CASE WHEN StreamingTV IN (‘yes’) THEN 1 ELSE 0 END) AS DECIMAL(10, 2)) AS ChurnRate
FROM
churndata
WHERE
StreamingTV IN (‘yes’)
GROUP BY
Contract
ORDER BY
ChurnRate DESC;
То есть из churndata выбираем контракт Contract, далее — аналогично, например, приведенному в пункте 1. Группируем по контракту, упорядочиваем по убыванию:
Select Contract, SUM(CASE WHEN StreamingMoviesIN (‘yes’) THEN 1 ELSE 0 END) AS TotalCustomers, SUM(Churn) AS ChurnedCustomers,
CAST(SUM(Churn) * 100.0 / SUM(CASE WHEN StreamingMovies IN (‘yes’) THEN 1 ELSE 0 END) AS DECIMAL(10, 2)) AS ChurnRate FROM churndata WHERE StreamingMovies IN (‘yes’) GROUP BY Contract ORDER BY ChurnRate DESC;
Отток клиентов из помесячных потоковых сервисов намного выше. Клиенты обычно подписываются на эти услуги только на срок одномесячной бесплатной пробной версии, поэтому отток выше. Вдолгую же отток снижается.
11. Привлекательна ли для клиентов система безбумажного выставления счетов?
Select PaperlessBilling, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by PaperlessBilling order by churnrate desc;
То есть из churndata выбираем безбумажное выставление счетов PaperlessBilling, далее — аналогично, например, приведенному в пункте 1. Группируем по безбумажному выставлению счетов, упорядочиваем по убыванию:
Результатами использования системы безбумажного выставления счетов обнаруживается негативная тенденция, и компании необходимо обратить внимание на этот аспект. Безбумажное выставление счетов чревато значительным оттоком клиентов.
Возможно, это обусловлено проблемами с оплатой или чеками. Похоже, это нововведение клиентам не по нраву.
12. Каковы самые популярные способы оплаты?
Select PaymentMethod, count(*) as Totalcustomer, sum(churn) as ChurnedCustomers, cast(sum(churn) * 1.0 / count(*) *100 as decimal (10,2)) as churnrate from churndata group by PaymentMethod order by churnrate desc;
То есть из churndata выбираем способ оплаты PaymentMethod, далее — аналогично, например, приведенному в пункте 1. Группируем по способу оплаты, упорядочиваем по убыванию:
Что касается способов оплаты, у электронных платежей отток высокий, у автоматических — банковским переводом и кредитной картой — он ниже: новые клиенты не спешат их выбирать.
13. Каковы общие затраты по каждому способу оплаты?
SELECT
PaymentMethod,
COUNT(*) AS TotalCustomers,
ROUND(SUM(TotalCharges), 2) AS TotalCharge
FROM
churndata
GROUP BY
PaymentMethod
ORDER BY
TotalCharge DESC;
У способа оплаты с электронным чеком больше затрат и клиентов, высок и отток.
Выводы и рекомендации
- Чтобы создать надежную клиентскую базу, компании Telco следует предложить простой и экономичный доступ к своим услугам. В первые полгода важно сосредоточить усилия на совершенствовании онлайн-безопасности, резервного копирования онлайн, защиты устройств и техподдержки. Этот период очень неопределенный и важный для клиентов.
- Компании следует адаптировать свои стратегии к трем категориям клиентов: старшему поколению, семейным и одиноким.
- Число клиентов старшего поколения ограничено, но минимальный порог их ежемесячных затрат выше, чем у других групп клиентов. То есть клиенты старшего поколения готовы серьезно вкладываться, ожидая взамен первоклассных услуг, адаптированных под их предпочтения и потребности.
Для семейных и одиноких:
- Чтобы сгладить отток, рекомендуется отказаться от электронных чеков в пользу автоматических платежей: банковских переводов и кредитных карт. Однако, чтобы решить для этих двух способов оплаты проблему сокращения средней продолжительности пользования услугами из-за оттока клиентов — сейчас это свыше 20 месяцев, что вдвое больше, чем для электронного чека, — потребуются дополнительные усилия.
- Когда ежемесячные затраты на одну услугу становятся больше $70, чувствительность клиентов к своим расходам значительно увеличивается. Поэтому компании Telco крайне важно выделяться исключительным качеством обслуживания. Акцент на качестве должен стать ее уникальной торговым предложением. Ожидается, что за счет реализации этих мер не только увеличатся доходы, но и усовершенствуется имеющийся процесс создания стоимости.
Дашборд оттока клиентов
Читайте также:
- Что такое большие данные: комплексный обзор
- Отчего «паникует» даже камнеукладчик: инцидент с удалением строк
- Как создать первый проект по инженерии данных: инкрементный подход. Часть 2
Читайте нас в Telegram, VK и Дзен
Перевод статьи Sateesh Godewar: CUSTOMER CHURN ANALYSIS: UNVEILING INSIGHTS USING SQL
