Статистика по клиентам*

У меня тут на днях попросили сделать статистику по отвалу клиентов по месяцам. Причем в строках должны быть месяцы первой покупки тарифа. Допустим, купили 766 компаний первый тариф в январе 2020 года. А через месяц из них 149 отвалились, значит 149 стоит в строке “2022-01” и в колонке “отвал через 1 месяц”.

Клиент сначала покупает тариф, потом может его менять. Тариф действует определенный период времени. Есть его первая дата покупки. Есть дата, до которой он действует. Если срок истек, значит клиент считается не актуальным, выбывшим. И вот какой алгоритм я придумала.

Я ищу sql запросом (датафрейм df_finish), все оплаты первых тарифов с января 2022 года. Получаю таблицу. Столбцы:

account_id 

month  - substring(дата,1,7) as month – получаем дату в таком виде 2020-01

кол-во дней с даты окончания действия тарифа до первой даты оплаты тарифа

DATEDIFF(sa.paid_till, DATE(sa.first_payed_product_at)) as days_to_paid_till

кол-во дней с даты окончания действия тарифа до сегодня (если >0 то активные еще клиенты)

DATEDIFF(sa.paid_till, CURDATE()) as days_from_today_to_finish

И потом группирую по месяцам, чтобы получить первый столбец с кол-вом покупок тарифов. Это будет первый столбец на картинке.

df_part1 = df_finish.groupby(['month'],as_index = False).agg({"account_id":"count"})

df_part1=df_part1.rename(columns = {'account_id':'кол-во аккаунтов, купивших первый платный тариф'})

Теперь через loc прописываю условия и присваиваю новой колонке 'result' значения ‘в течении 1 мес', 'через 1 мес' и т.п. Очень удобно без lambda и apply. Синтаксис простой, теперь этим способом пользуюсь.

df_finish.loc[(df_finish['days_to_paid_till'] >= 0) & (df_finish['days_to_paid_till'] < 30) ,'result']='в течении 1 мес'

df_finish.loc[(df_finish['days_to_paid_till'] >= 30) & (df_finish['days_to_paid_till'] < 60) ,'result']='через 1 мес'

df_finish.loc[(df_finish['days_to_paid_till'] >= 60) & (df_finish['days_to_paid_till'] < 90) ,'result']='через 2 мес'

и т.п И в самом конце даем клиентам срок в 7 дней, чтобы купили тариф, считаем их пока активными.

df_finish.loc[df_finish['days_from_today_to_finish'] >-7,'result']='active'

Теперь переворачиваем табличку, колонками будут значения из 'result' и подсчитываем кол-во клиентов в разрезе строк index = ['month']

df_part2 = df_finish.pivot_table(index = ['month'], columns=['result'], values = 'account_id',aggfunc = 'count').fillna(0).reset_index()

Вуаля!

*цифры изменены