Мир аналитика данных

Отвал клиентов

У меня тут на днях попросили сделать статистику по отвалу клиентов по месяцам. Причем в строках должны быть месяцы первой покупки тарифа. Допустим, купили 766 компаний первый тариф в январе 2022 года. А через месяц из них 149 отвалились, значит 149 стоит в строке “2022-01” и в колонке “отвал через 1 месяц”.
Клиент сначала покупает тариф, потом может его менять. Тариф действует определенный период времени. Есть его первая дата покупки. Есть дата, до которой он действует. Если срок истек, значит клиент считается не актуальным, выбывшим. И вот какой алгоритм я придумала.
Тут видно лучше https://telegra.ph/Statistika-po-klientam-01-27

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

По результатам опроса у нас из 243 проголосовавших 143 человека используют в работе Excel и 100 человек – не используют. В любом случае он нужен, как правило, тем, кто будет устраиваться аналитиком и нужен тем, кто работает, но уже в качестве прокачки скиллов. Короче он нужен всегда! 😜😂
Поэтому иногда буду показывать какие-то эксельные хитрости. 👌

Иногда может потребоваться список всех файлов с какого-то вашего диска. Как их выгрузить в Excel? Подруга подсказала способ. Сейчас кратко его вам опишу. На картинке для удобства все видно.
Вбиваем в ячейку путь откуда смотрим файлы: C:\Пример\*.*
Потом идем в Формулы-> Диспетчер Имен (Ctrl+F3). Жмем Создать. Задаем имя. Я так и назвала “Файл”.
В Диапазоне прописываем =ФАЙЛЫ(Лист1!$A$1) - ссылка на ячейку, где путь прописан. Сохраняем, закрываем. В столбце в Excel пишем 1, 2 и т.д. Можно зажать ctrl и протянуть 1, автоматически получим столбец от 1 до сколько у вас примерно файлов.
Продолжение ->

Как ваш первый рабочий день? Я в запаре. Все отделы заказывают отчеты. Поэтому сегодня в блоге тема легкая. Мозг бережем. 😜

Когда я только создавала канал, я зарегистрировалась на https://tgstat.ru – это каталог Telegram – каналов и чатов и вот какую открытку я от них получила по итогу года. Вас уже так много! ❤️ Мне очень радостно, что мой труд идет кому-то на пользу в работе или учебе, а кого-то подпитывает вдохновеньем. 🎈
Давайте проведем небольшой опрос, чтобы лучше Вас узнать.

С наступающим! А кого-то уже с наступившим!🎄

Друзья, хочу пожелать, чтобы каждый в Новом Году получил заветный оффер, нашел направление, которое принесет радость, деньги и конечно удовлетворение!
Исполняйте мечты, будьте здоровы, полны энергии и верьте в свои силы! 🌟
А теперь моя маленькая история. Когда я получала отказы, то в какой-то момент, я решила не отсылать больше свое резюме, руки опускались, так как пробиться в IT очень трудно. Но я отчетливо помню, как в какой-то момент я задумалась, а какую работу я бы хотела получить, чтоб прям по сердцу. Все равно ведь есть та вакансия, которую мне предложит вселенная, которая будет именно моей. Я без уныния, с каким-то теплым чувством в душе мысленно представила условия вакансии, и чтобы я там делала, и не поверите, спустя небольшое количество времени именно такой оффер я и получила. Фантастика!
Продолжение -»

🍀 Как мне помогла теория вероятности.
Прислали мне тут вот такую задачку – отобрать всех клиентов, у которых нет заявок на определенный доп.функционал или тех, у кого заявку одобрили, но клиент пока отключил у себя эту опцию. И еще у них среднемесячная выручка с начала 2022 года должна быть больше 800 тыс. руб.
Сначала я нашла тех, у кого больше 800 тыс.руб. в мес. - df_more_800

А вот потом в мозгу у меня все смешалось – что с чем соединить? 😜 И тут я составила табличку вероятностного пространства всех возможных вариантов, чтобы наглядно понять, как вытащить данные.
Получилась вот такая таблица:
| Есть заявка| Одобрена | Клиент включил опцию у себя |
| Есть заявка| Одобрена | Клиент НЕ включил опцию у себя |
| Есть заявка| НЕ одобрена| - |
| НЕТ заявки | - | - |

Сначала ищу клиентов, кто вообще подал заявку.
query = f"""
select account_id
from sales.request
where account_id in {tuple(df_more_800['account_id'])}
"""
df_request = pd.read_sql(query, connection)

Потом я нашла аккаунты тех клиентов, кто не включил опцию:
query = f"""
select account_id
from sales.request
where account_id in {tuple(df_more_800['account_id'])}
and status = 1 -- одобренность заявки
and active = 0 -- клиент отключил у себя
"""
df_off = pd.read_sql(query, connection)
Исключим с помощью isin те школы, кот подавали заявку, чтобы оставить тех, кто НЕ ПОДАВАЛ ЗАЯВКУ . Вот этот знак (~) оставляет в df_more_800 все, кроме тех аккаунтов, что указаны в df_request

df_wo_application = df_more_800[~df_more_800['account_id'].isin(tuple(df_request['account_id']))]

А теперь объединим с помощью concat тех, кто не подавал заявку и тех, кто выключил у себя эту опцию. Это и будет финалочка! 👌
df_final = pd.concat([df_wo_application, df_off], ignore_index = True)

✅ Сегодня вышел бесплатный курс по SQL у Карпова! Это офигеть событие в условиях, что качественное обучение, как правило платно. Еще и скачивать ничего не нужно.
Чему можно научиться:
- Составлять простые и продвинутые SQL-запросы к базам данных.
- Выгружать данные, анализировать их и визуализировать результаты.
- Проверять гипотезы и принимать решения на основе данных. (вот это очень хочу, практики в таком очень мало)
А на десерт будет раздел с продуктовыми задачами.

Там 60+ SQL-ЗАДАЧ! https://karpov.courses/simulator-sql

🔥 Сводная таблица pivot_table().
Очень часто в работе используется эта функция из Pandas библиотеки. Она преобразовывает DataFrame в сводную таблицу. Хоть таблица и называется сводная, но она не сводит все ваши проблемы на “нет”. А вот задачи преобразования табличных данных решает на “ура”.

Разберемся как она работает на примере. Есть таблица с визитами на страницы. В ней есть дата захода на конкретную страницу, номер юзера, который зашел, сайт самой страницы и ее название.
| created_date | user | page | title
| 2022 – 11 – 01 | 3548329123|https://… | Как добавить счётчик Яндекс.Метрики на страницу
| 2022 – 11 – 02 | 3596921140|https://… | Новый редактор автовебинаров

💡 Задача – подсчитать кол-во заходов и кол-во уникальных юзеров (какое кол-во человек заходили на данную страницу)

df = df.pivot_table(
index = ['title','page'], # в строках будет сайт страницы и его название
columns = 'created_date' , # в колонках - дата захода (если дата не важна, то можно не прописывать)
values = 'user', # подсчитываем по юзерам
aggfunc=('count','nunique')) # как именно подсчитываем, тут считаем кол-во и уникальность.

В качестве aggfunc можно использовать в других задачах 'sum' если нужно подсчитать сумму, допустим выручки по клиентам.
В результате получим таблицу c такой структурой:
| title | page | count | nunique
| Как добавить ... |https://… | 22 | 17
| Новый редактор автовебинаров|https://… | 10 | 10

Учебный курс «Анализ данных»
Если вам не хватает к резюме каких-то свежих "корочек", то вот, что нашлось на любимом Степике:

Регистрируйтесь на курс до 1 декабря 2022 года и получите удостоверение о повышении квалификации государственного образца от Тюменского Государственного Университета!
https://stepik.org/course/126333/syllabus
Им нужно прислать скан диплома о первом высшем или о среднем профессиональном образовании.
Ну и курс пройти конечно. Пишут в отзывах, что базовый, легкий. По сути ознакомительный.

Мне даже интересно с какой целью Тюменский Университет такую акцию запустил? Может хочет потом базу потенциальных клиентов на уже платный большой курс собрать. А может что-то еще..

🔥Дополнение к hh.ru

Смотрите какой сайт с вакансиями есть! Даже и не знала про него раньше. Тут подборки по странам, по позициям. Вот, что для младших аналитиков есть:

Младший аналитик / Отисифарм
https://careerspace.app/job/52601

Стажер бизнес-аналитик / ЭКОПСИ
https://careerspace.app/job/52641

Младший аналитик / ivi.ru
https://careerspace.app/job/52460

P.S. Не бойтесь отказов. Это нормально. Не упускайте возможность. Вдруг это ваше 🍀

Задания по Excel.

В каждом втором тестовом на аналитика есть задания по Excel. Так что тренируемся на чем можем. 💪
Сперва необходимо было построить сводную таблицу и создать условное форматирование. Если таблицу сделать не сложно, то вот с условным форматированием, как правило, все мало работают.
Я вот только на этой работе стала им пользоваться по просьбе отдела продаж для определенных отчетов.

Подскажу как я сделала форматирование тут. Сначала построим саму таблицу.

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

На картинке видно, что в строки мы поставили Статьи расходов, а в столбцы – Регионы. В значения перетащила суммы. Тут все просто.

Задание по условному форматированию.
Продолжение тут ->

Yandex DataLens 💡

Вот какой дашборд получилось создать в DataLens. Переходите по ссылке и потыкайте в фильтры. Красотища ведь!

Оказывается Yandex создал свой BI инструмент для визуализации данных. И все - бесплатно. Можно подключиться к базам или просто залить csv файл. На том бесплатном курсе (выше пост) была дана инструкция как сделать такую красоту.

Мне понравилось! Если кто не успел записаться тогда, то вот тут инструкция как создать подключение к базе и как сконструировать дашборд.

Наткнулась на бесплатный курс по визуализации данных в Нетологии. 🌟
Начался ВЧЕРА! С 7 ноября — 24 ноября. А почему бы и нет, что называется! 👍
https://netology.ru/programs/analyst-bifree?utm_source=ba_sa_analytics&utm_medium=influence&utm_campaign=bds_bifree_bou_tg_ba_sa_analytics&utm_content=2112022#/main

Стек: DataLens - без понятия что это, Excel, Power BI и Tableau.

Дата сайентисты vs аналитики 😜
Работаю я аналитиком данных, а училась на data scientist. Почему же я не пошла сразу в дата сайнтисты? Ответ прост – туда очень сложно пробиться и нужно много знать, а еще иметь уже готовый опыт какого-нибудь проекта для компании. А где его взять? У меня были учебные проекты и они не плохи, но для оффера – “хотелось бы хоть один реальный проект”, как сказала мне одна эйчар. Есть варианты стажировок, но я не могу позволить себе терять в з/пл, да и берут туда в основном выпускников вузов. Кстати, если вы один из них – стажировки – это вообще бомба для вас и реальный шанс.

Так вот, для себя я выбрала путь – через аналитику. Сейчас я практикую Питон, SQL, возможно позже придумаю какой-нибудь DS проект для компании, и тогда буду дальше пробиваться на позиции data scientist. Есть еще вариант, что мне захочется углубиться в анализ (есть разные направления) и я отложу науку о данных. Все возможно.
Кстати, если вам нужна информация, что там твориться у дата сайентистов, то вот вам блог моего одногруппника. Блог про DS, там много нужного и полезного, ссылки, книги, кладезь всего! 🌟 https://t.me/ds1pro

Долгожданная пятница! 🌟
Как не поговорить в пятницу о..понедельниках 😂

Дело в том, что в одном отчете я вытаскиваю данные за каждую неделю и проставлю там даты понедельников. Надоело это делать вручную и вот какое решение я нашла.
Воспользуемся функцией python strftime() и функцией pandas.date_range() для создания диапазона дат в pandas.

Задаю начальную и конечную дату периода.
start_date = '2022-01-03'
end_date = datetime.datetime.now() - это текущий день

Потом циклом с частотой в 7 дней (7D) проходимся по всем датам в промежутке и собираем понедельники.
week_lst = [d.strftime('%Y-%m-%d') for d in pd.date_range(start_date,end_date, freq = '7D')]

Тип week_lst будет представлять собой лист.
А потом из этого листа делаем dataframe (табличку):
df_weeks = pd.DataFrame({'Пн':week_lst})
Ну и посмотрим, что получилось:
df_weeks.tail() - показать последние строки сформированной таблицы.

На прикрепленной картинке видно как срабатывает запрос - видны последние пять понедельников.

Смотрите, что я нашла!
В воскресенье будет стрим с лайвкодингом! Школу эту не знаю, это не реклама, если что, но у них будет в качестве учителя аналитик Глеб Михайлов. Мне о нем один дата сайентист рассказывал, что у него классный курс по SQL на Юдеми есть. Курс был платный, но не дорого, около 500р. Очень хвалили его курс, качественный типа. Я хотела купить, но не успела тогда - как раз карты наши не стали работать на иностранных сайтах.
📌 Так вот ЗАВТРА в 19:00 будет стрим и Симулятор «Аналитик данных с Глебом Михайловым с лайвкодингом!
Глеб будет делать распаковку симулятора и решать задачи на SQL.
Обещают каждому участнику доступ к главе и к задачам, чтобы вместе с ним кодить 🤟🏻
Всегда ввязывайтесь во все движухи, хакатоны и такие вот бесплатные штуки!

Найти беглеца! 😂

Формирую я себе спокойненько топ 150 клиентов по выручке в разрезе месяцев (df). У меня есть номер клиента, месяц выручки, еще несколько колонок с разной инфой, ну и сумма естественно.
Мне нужно перевернуть таблицу, чтобы месяцы превратились в столбцы, и на каждого клиента была бы одна строчка. В таком случае всегда используется pivot_table.
В индексы загоняю номер школы (account_id), первую дату оплаты (first_payed_product_at), а в колонки месяцы (columns=['month']). В значения (values) - чем будет заполнена таблица - суммы выручки 'all_sum'.

df2 = pd.DataFrame(df)
df_pivot1 = df2.pivot_table(index = ['account_id','first_payed_product_at'], columns=['month'], values = 'all_sum',aggfunc = 'sum',margins=True).reset_index().rename_axis(None, axis=1)

Смотрю я что получилось и вижу, что вместо 150 клиентов, у меня – 149! Куда делся один не понятно. Оказалось, что когда выгрузилась первоначальная таблица df, то в столбце first_payed_product_at по одному клиенту было пусто (данных не было), и pivot забил на такого клиента и не перевернул по нему данные, полностью его проигнорировав.

Выход из этого – проставить нули везде, где нет данных, тогда никто не будет потерян - fillna(0)

Условное форматирование в Google таблицах. ✨

Я обычно не люблю раскрашивать Excel таблички в цвета "вырви-глаз". Максимум, я красила светло-зеленым заголовки когда работала еще фин.контролером, и на этом все.

А вот теперь наш отдел продаж просил применить разноцветное условное форматирование к их данным. Я им формировала рейтинг менеджеров.

Так неудобно делать правило для каждой колонки отдельно, но я не нашла другого способа. 🙈

Итак, выделяем колонку. Формат -> Условное Форматирование. Выбираем “Градиент”. Щелкаем по нему и выбираем “От зеленого к красному через желтый” (нижний ряд, в середине). Можно и другие цвета выбрать, конечно. Настраиваем цвет для мин.значения, макс.значения и для точки середины. Любуемся своим творением и повторяем те же действия для другой колонки, судорожно вспоминая оттенок цвета выбранного для первой колонки 😂
Продолжение ->

SQL + Pandas = ❤️

Вот такой запрос мне недавно прислали.
Отдел продаж просил сформировать группу (почты клиентов), которые отвечают нескольким условиям:

1) Основная платежная система (ПС) – Клаудпеймент.
2) % комиссии выше 3.5%
3) Оборот за август больше 1 млн
4) Минус тех, у кого есть заявка на определенную платежную систему

Сначала я выгрузила SQL запросом:
- оборот за август. Получила таблицу df_aug с номером аккаунта клиента 'account_id', типом ПС - 'type', суммой – 'amount' и комиссией – ' сommission'.
- оборот с августа до текущей даты - df_from_aug, чтобы найти основную платежную систему – то есть ту, через которую прошли максимальные платежи c августа.
df_big = df_from_aug.groupby(by='account_id').last().reset_index()

Потом с помощью пандаса делаем:
Отсортируем базу по тем, у кого основной ПС - cloud_payments.
df_cloud= df_big[df_big['type'] == 'cloud_payments']
Найдем аккаунты, у кот выручка за август больше 1 млн. Берем наш df_aug и суммируем все суммы по каждому аккаунту
df_grouped = df_aug.groupby(['account_id']).agg({'amount':'sum'}).reset_index()
df_more_1mln = df_grouped[df_grouped['amount'] > 1000000]

Создаем кортеж из аккаунтов, которые удовлетворяют условию больше ляма:
account_lst_more_1mln = tuple(df_more_1mln['account_id'])
Теперь из аккаунтов, у которых основная ПС=cloud_payments отбираем те, у которых выручка за август была БОЛЬШЕ 1 млн. Воспользуемся isin и нашим кортежем (можно и списком, но кортеж вроде быстрей срабатывает)

df_cloud_more_1mln = df_cloud[df_cloud['account_id'].isin(account_lst_more_1mln)]

Добавим расчет rate %.
df_cloud_more_1mln['rate'] = df_cloud_more_1mln['сommission']/df_cloud_more_1mln['amount']

Отфильтруем те, у кот rate > 3.5%
df_cloud_more_1mln_rate = df_cloud_more_1mln[df_cloud_more_1mln['rate'] > 0.035]

Потом SQL запросом вытаскиваю аккаунты подключённые к внутренней системе нашей компании
query = """
select DISTINCT ACCOUNT_ID
from sales.checkout_request
"""
params = {}
gp_requests = mql.read_query( connection_gp, query, params )

Вот тут опять загоняем аккаунты в кортеж:
lst_gp_requests = tuple(gp_requests['ACCOUNT_ID'])
Минусуем тех, кто содержится в этом кортеже с помощью isin и значка ~ (кроме)
df_cloud_more_1mln = df_cloud_more_1mln_rate[~df_cloud_more_1mln_rate['account_id'].isin(lst_gp_requests)]

Создаем кортеж из оставшихся аккаунтов клиентов:
accounts_group = tuple((df_cloud_more_1mln['account_id'].drop_duplicates()))

Ну и в финале выгружаем SQL почты владельцев эти аккаунтов в csv формате.
Как-то так..

HAVING Это Present Continuous в английском, но речь тут не об этом. 😜
Это команда в SQL, которая фильтрует результат группировки. Разберем на рабочем примере.

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

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

select p.ACCOUNT_ID,
sum(case when p.currency in ('RUB','RUR') then p.amount else (p.amount * cr.rate) end)/(MONTH(CURRENT_DATE) - 1) as avg_amount,
from sales.payment p -- вытаскиваем из базы платежей
left join sales.currency c - - присоединяем базу с валютами
on p.currency = c.code - - по совпадающему ключу
left join sales.currency_rate cr - - присоединяем базу с курсами валют
on c.id = cr.currency_id - - по совпадающему ключу
where p.created_at <= CURRENT_DATE group by 1 - - группировка по номеру клиента
having avg_amount > 1000000

Тут, кстати, видно как работает оператор case 👌 когда нужно посчитать выручку в рублях