Data analysis - DA SQL

Таблицы фактов без фактов Если вспомнить классические схемы БД, такие как звезда или снежинка, то мы обнаружим, что центральная таблица - таблица фактов - обычно содержит ключи и числовые метрики, которые отображают факт произошедшего события (например, сколько товаров было продано). Но встречаются ситуации, когда у события нет численных метрик. Например, когда нужно отразить, посетил ли человек мероприятие или нет; была ли активна подписка или нет; было ли отправлено письмо и т.п. 🟠Для этих случаев используются таблицы фактов без фактов (Factless Fact Table). И выглядят они следующим образом. 1️⃣Если нам нужно зафиксировать сам факт наступления события (например, отправку письма), то используется такая структура: CREATE TABLE fact_email_sends ( date_id INT NOT NULL, user_id INT NOT NULL, campaign_id INT NOT NULL, channel_id INT NOT NULL) Таблица содержит информацию о том, когда и кому было отправлено письмо, а также частью какой рекламной кампании это письмо является и в каком виде оно было отправлено. Важно понимать, что все поля в этой таблице - это внешние ключи, которые ведут к другим таблицам-измерениям. Никаких других метрик (особенно числовых) тут нет. 2️⃣Есть и другой тип таблиц фактов без фактов - это таблицы покрытия и состояний. Они предназначены для того, чтобы оценивать статус объекта на какой-то момент времени (в отличие от фиксации события как в прошлом примере). Имеют такую структуру: CREATE TABLE subscription ( date_id INT NOT NULL, user_id INT NOT NULL, sub_id INT NOT NULL, device_id INT NOT NULL) Таблица показывает, какая подписка с какого устройства была активна у пользователя на момент времени. Точно так же, как и в предыдущем случае, таблица содержит только ключи. 3️⃣Выделяют еще таблицы отрицательных фактов. Они наоборот показывают те события, которые НЕ произошли. И обычно это не отдельная таблица, а результат джоинов многих других factless таблиц, который может как и вычисляться по мере необходимости, так и храниться в виде витрин. Например, если цель - отразить тех, кто НЕ пришел на мероприятие, то для этого берется таблица регистраций (кто записался), к которой присоединяется таблица событий (кто пришел). И из получившегося результата выбираются id со значением NULL.

Как искать расхождения? Болезненная и довольная частая тема. Допустим, мы написали какой-то SQL-запрос, выгрузили по нему данные из БД и решили проверить, правильно ли всё сделано. Но внезапно выяснилось, что контрольные суммы по всей выгрузке и по изначальной таблице различаются. 🟠Значит, где-то есть ошибки, и надо их искать. Но как? ▶️Самый очевидный вариант - проверить дубликаты при джоинах. Делается это достаточно просто и возможно даже быстро: SELECT id, COUNT(*) AS cnt FROM table GROUP BY id HAVING COUNT(*) > 1 ▶️Может еще быть такое, что дубликатов при джоинах не возникло, зато какие-то id не включились в выгрузку. Или наоборот включились лишние. В этом случае могут помочь подзапросы: SELECT * FROM table WHERE id NOT IN (SELECT id FROM query_result) ▶️Возможно, что с id вообще всё в порядке, но проблема не уходит. Тогда как вариант можно предложить группировать по какому-то признаку. Например, если выгрузка представляет собой отчет за месяц в подробной детализации, то можно сгруппировать данные по дням. И каждый день сравнивать с тем же агрегированным днем в таблице. Если какие-то дни не совпадают, то уже распутывать и смотреть более детально, что же там не совпадает. ▶️Если в WHERE используется много условий, то можно проверять каждое условие по очереди, накопительно. Вполне возможно, что на каком-то этапе фильтрация работает неправильно. Например, при WHERE col = cond_1 контрольные суммы совпадают, а при WHERE col = cond_1 AND cond_2 - уже нет. ▶️Еще нельзя исключать тот факт, что банально сама исходная таблица / витрина могла обновиться. Хоть это может казаться маловероятным, но проверить стоит - просто через время заново пересчитать контрольные суммы.

Как спрогнозировать сезонные данные на Python Представим, что у нас есть временной ряд (например, продажи). И нужно понять, какая часть колебаний - это регулярный сезонный паттерн, а какая - тренд и "чистое" изменение показателя без сезонного шума. ❗️Так как тема достаточно объемная, код будет написан не весь, а только самые важные моменты. Считаю важным рассказать именно про логику и последовательность. 🟠Итак, для этих целей есть 2 вида моделей: ▶️Аддитивная модель используется тогда, когда амплитуда сезонных колебаний постоянна и не зависит от уровня ряда (например, каждый декабрь +500 продаж сверх тренда, всегда +500, вне зависимости от того, растёт бизнес или нет). ▶️Мультипликативная модель используется тогда, когда амплитуда колебаний растёт вместе с уровнем ряда (например, декабрь даёт +20% к тренду, и если тренд вырос, абсолютная прибавка тоже растёт). 🟠В реальных бизнес-данных мультипликативная модель встречается чаще, потому что рост бизнеса обычно масштабирует и сезонные всплески. Её мы и обсудим. 1️⃣Первым делом импортируем библиотеки и подготавливаем данные: from statsmodels.tsa.seasonal import seasonal_decompose from sklearn.linear_model import LinearRegression import numpy as np import pandas as pd df = df.set_index('date').asfreq('MS') Пропущенные месяцы нужно заполнить. Это критично, потому что без этого модель может сломаться. 2️⃣Затем подготовленные данные прогоним через встроенную функцию seasonal_decompose. В качестве аргументов выбирается имеющийся временной ряд, тип модели 👆и разбивка на 12 периодов (по месяцам): result = seasonal_decompose(y, model='multiplicative', period=12) seasonal = result.seasonal deseasonalized = y / seasonal Это нужно для того, чтобы убрать из данных сезонность. Для этого ряд делится на полученный из seasonal_decompose сезонный коэффициент. В результате получатся "чистые" данные, которые уже можно использовать для построения прогноза. 3️⃣Потом строится линейная регрессия на основе полученных ранее десезонализированных данных: model = LinearRegression() model.fit(X, Y) trend_forecast = model.predict(X_future) По факту это будет уже готовый прогноз на основе нашего ряда, только без фактора сезонности. 4️⃣Для того, чтобы прогноз получился наиболее полным, логичный шаг - вернуть в прогноз временно убранную сезонность: seasonal_by_month = seasonal.groupby(seasonal.index.month).mean() seasonal_forecast = future_dates.month.map(seasonal_by_month).values forecast = trend_forecast * seasonal_forecast Для этого нужно просто спрогнозированный тренд умножить на сезонный коэффициент. В итоге получим готовый прогноз, но без проверки этого прогноза на точность. Но это уже отдельная история.

Проблемы в оптимизации SQL-запросов Иногда приходится сталкиваться с запросами, где должно быть большое количество условий. Например, много условий в WHERE, много полей в GROUP BY, много JOIN и т.п. И такие запросы будут выполняться очень медленно и даже не факт, что выполнятся вообще. 🟠Но почему такое происходит? ▶️WHERE сам по себе работает как фильтр данных. Если вспомнить порядок выполнения SQL-запроса, то WHERE выполняется одним из первых. И по идее, если добавлять в WHERE по 5-10 условий через AND, то лишних данных может отсеяться очень много, что в итоге сделает запрос легче и быстрее. И в целом, это так и работает, и запрос реально может стать быстрее или хотя бы не измениться. Но в случае, если у нас прописаны индексы на 1-2 самых часто используемых столбцах, то добавление дополнительных неиндексированных столбцов может значительно замедлить работу. Происходит это из-за того, что оптимизатор перестает эффективно использовать индексы, и СУБД вынуждена тратить дополнительные ресурсы на проверку каждого условия для каждой строки. ▶️В случае с GROUP BY заложена другая логика. И тут замешана банальная математика. Чем больше столбцов в условие GROUP BY мы добавляем, тем больше разных вариантов система вынуждена отработать. И количество вариантов растет мультипликативно. Например, если группировать по полю "day", то мы будем иметь 365 разных комбинаций. Если добавить поле "country", то добавится условно еще 50 вариантов, итого будет 365*50 = 18 250. И каждое такое крупное поле в разы увеличивает нагрузку и, как следствие, время выполнения. ▶️Большое кол-во JOIN тоже имеет чисто математическую причину замедления. Например, мы присоединяем к таблице с клиентами таблицу с их счетами. Очевидно, что если у каждого клиента в среднем по 3 счета, то кол-во строк увеличится в 3 раза. Если еще к каждому счету присоединить транзакции, которые с него совершались, то кол-во строк увеличится еще в 50 раз. И так будет продолжаться и далее. Ну и также не стоит забывать, что чем больше таблиц, тем сложнее СУБД просчитать идеальный порядок их объединения (количество вариантов растет экспоненциально). ▶️Много COUNT(DISTINCT) тоже желательно не использовать в одном запросе. Это очень "дорогостоящая" операция сама по себе, потому что заставляет базу держать в памяти полный список всех уникальных значений, чтобы отсечь дубликаты. И если это делать по 3-4 колонкам одновременно, то СУБД будет строить несколько гигантских структур данных в памяти. И это будет медленно.

Маркетинговые метрики Шпаргалка по наиболее популярным маркетинговым метрикам, может быть очень полезна при собеседованиях в профильные компании: 🟠Финансовые метрики и окупаемость. Показывают, насколько маркетинг выгоден для бизнеса. ▶️ROAS (Return on Ad Spend) - окупаемость затрат на рекламу. Показывает, сколько рублей дохода приносит каждый рубль, вложенный в рекламу. Доход от рекламы / Затраты на рекламу ▶️LTV (Lifetime Value) - пожизненная ценность клиента. Общая сумма денег, которую клиент приносит компании за все время сотрудничества. Средний чек * Частота покупок * Время жизни клиента ▶️CAC (Customer Acquisition Cost) - стоимость привлечения одного платящего клиента. Затраты на рекламу / Кол-во новых клиентов ▶️LTV:CAC - соотношение ценности клиента к стоимости его привлечения. Самой оптимальной считается пропорция 3:1 и выше. 🟠Метрики привлечения. Показывают, сколько стоит привести нового клиента или потенциального покупателя. ▶️CPA (Cost Per Action) - стоимость одного целевого действия (покупка, регистрация, установка приложения). Затраты на рекламу / Кол-во целевых действий ▶️CPL (Cost Per Lead) - стоимость одного лида (контакта потенциального клиента, оставившего заявку). Затраты на рекламу / Кол-во лидов ▶️CPC (Cost Per Click) - стоимость одного клика по рекламному объявлению. Затраты на рекламу / Кол-во кликов ▶️CPM (Cost Per Mille) – стоимость 1000 показов рекламного объявления. (Затраты на рекламу / Кол-во показов) * 1000 🟠Метрики удержания и лояльности. Критически важны для долгосрочных / подписочных проектов. ▶️Retention Rate - процент клиентов, которые продолжают пользоваться продуктом через определенный период времени. (Клиенты на конец периода - Новые клиенты за период) / Клиенты на начало периода ▶️Churn Rate - процент клиентов, которые перестали пользоваться продуктом или отменили подписку за определенный период. Клиенты, ушедшие за период / Клиенты на начало периода ▶️CSI (Customer Satisfaction Index) - индекс удовлетворенности клиентов качеством продукта или сервиса. Может исчисляться как и средней оценкой, так и процентом довольных потребителей Сумма всех оценок / Кол-во опрошенных ИЛИ Кол-во выс. оценок / Кол-во опрошенных 🟠Метрики конверсии и трафика. Оценивают, насколько эффективно трафик превращается в целевые действия. ▶️CR (Conversion Rate) - коэффициент конверсии. Это процент пользователей, которые совершили целевое действие Кол-во целевых действий / Кол-во посетителей ▶️CTR (Click-Through Rate) - кликабельность объявления или ссылки. Это отношение числа кликов на баннер к числу показов Кол-во кликов / Кол-во показов ▶️Bounce Rate - процент посетителей, которые покинули сайт после просмотра только одной страницы (или провели на нем меньше определенного времени, например, 15 секунд).

Как на SQL найти постоянных клиентов, которые уже ушли Предположим, есть таблица с заказами, в которой нас интересуют лишь поля: ▶️client_id - id клиента ▶️order_date - дата заказа И нужно найти тех клиентов, которые не совершали покупок более 90 дней, НО которые при этом раньше покупали регулярно (например, минимум 1 раз каждые 7 дней, суммарно не менее 5 покупок). С маркетинговой точки зрения такая выборка покажет, какие из наиболее лояльных и постоянных клиентов вдруг потеряли полностью интерес к нашей продукции 🟠Сделать такую выборку можно на SQL, используя СТЕ и оконные функции. 1️⃣Начать запрос можно с того, чтобы вывести с помощью оконных функций самую последнюю order_date и предыдущую от текущей order_date для каждого клиента, а также посчитать общее кол-во покупок у каждого клиента: WITH client_orders AS ( SELECT client_id, order_date, MAX(order_date) OVER (PARTITION BY client_id) AS last_order_date, LAG(order_date) OVER (PARTITION BY client_id ORDER BY order_date) AS prev_order_date, COUNT(*) OVER (PARTITION BY client_id) AS total_orders FROM orders ), 2️⃣Затем можно сразу отдельно исключить тех клиентов, у которых есть хотя бы 1 перерыв между двумя заказами более 7 дней. Для этого используем разницу между текущей датой заказа и предыдущей, полученной с помощью LAG в шаге 1️⃣: lag_orders as ( SELECT DISTINCT client_id FROM client_orders WHERE DATEDIFF (order_date, prev_order_date) > 7 ) 3️⃣И в конце выводим тех клиентов, у которых не менее 5 покупок и которые не совершали заказов более 90 дней, и при этом всём которые не пересекаются с client_id клиентов, выведенных в шаге 2️⃣: SELECT DISTINCT client_id FROM client_orders WHERE total_orders >= 5 AND DATEDIFF(CURDATE(), last_order_date) > 90 AND client_id NOT IN (SELECT * FROM lag_orders) Результат получится в виде уникальных client_id. Это как раз и будут искомые клиенты.

Нечёткий поиск и дедупликация записей на Python В DWH бывают ситуации, когда разные записи означают одно и то же. Например, "ООО Ромашка", "ООО Ромашкa" (с лат. 'a') и "Ромашка ООО" могут быть 3 разными клиентами в витрине, но 1 реальным бизнесом. Таких расхождений может быть много. Вручную перебирать это долго и неэффективно, т.к. есть риск что-то пропустить. На помощь приходит кластеризация дубликатов при помощи косинусного сходства. Суть метода в следующем: 1️⃣Данные нужно подготовить, т.е. привести к одному формату: убрать лишние пробелы и переносы, сокращения, привести текст к нижнему регистру. Нужно это для облегчения работы алгоритма и для снижения риска лишних ошибок. Делается это просто и быстро: import pandas as pd import numpy as np from sklearn.feature_extraction.text import TfidfVectorizer from sklearn.metrics.pairwise import cosine_similarity #ДАННЫЕ + DataFrame df['name1'] = df['name'].str.lower().str.strip().str.replace(r'\s+', ' ', regex=True) 2️⃣Затем делается блокировка - т.е. выбирается сильный атрибут (ИНН, ОГРН, номер телефона) и вокруг него строится кластеризация, где ищутся названия с опечатками. Но стоит сделать оговорку. Во-первых, если нужно сделать JOIN, то можно сделать это по ИНН или другому сильному атрибуту, а не по битым названиям. Во-вторых, если такой JOIN невозможен или не нужен, то можно в каком-нибудь полу-автоматическом режиме заменить названия, фильтруя их по логике SELECT name FROM table WHERE inn = XXX Представить на практике ситуации, где невозможно ни то, ни другое, довольно сложно. Поэтому логичнее усложнить задачу и представить, что твердого атрибута нет. Например, такое возможно в клиентских таблицах, где клиенты - физ лица. В таких таблицах часто единственный идентификатор помимо id - это ФИО человека, и всяких ИНН там может и не быть. 🟠Но если в таблице много строк, то блокировка все равно нужна, т.к. она делит расчет на блоки. Если же в лоб сравнивать каждую запись между собой, то скорее всего такая операция никогда не будет завершена. Если нет ни одного сильного атрибута, то универсальный способ - это блокировать по первым буквам ФИО. Если есть хоть какая-то другая информация, то можно использовать её: год рождения, последние цифры номера телефона... И еще в случае с ФИО важно отсортировать слова по алфавиту: df['name_sorted'] = df['name1'].apply(lambda x: ' '.join(sorted(x.split()))) df['block_key'] = df['name_sorted'].str[0] print(df[['name', 'name_sorted', 'block_key']].to_string(index=False)) 3️⃣И, наконец, построение самой кластеризации: all_pairs = [] threshold = 0.9 for block_key, group in df.groupby('block_key'): if len(group) < 2: continue vectorizer = TfidfVectorizer(analyzer='char_wb', ngram_range=(2, 4)) tfidf_matrix = vectorizer.fit_transform(group['name_sorted']) cosine_sim = cosine_similarity(tfidf_matrix) rows, cols = np.where(cosine_sim > threshold) pairs = [ (r, c, round(cosine_sim[r, c], 3)) for r, c in zip(rows, cols) if r < c ] for r, c, score in pairs: all_pairs.append({ 'id_1': group.iloc[r]['client_id'], 'id_2': group.iloc[c]['client_id'], 'score': score }) df_pairs = pd.DataFrame(all_pairs) df_pairs['name_1'] = df_pairs['id_1'].map(df.set_index('client_id')['name']) df_pairs['name_2'] = df_pairs['id_2'].map(df.set_index('client_id')['name']) print(df_pairs[['name_1', 'name_2', 'score']].to_string(index=False)) Задается порог косинусного сходства (threshold) от 0 до 1. 1 - выводятся идентичные данные. 0 - выводятся все данные. Потом данные группируются по признаку блокировки. Затем для блоков текст режется на n-граммы (кусочки по 2-4 буквы). Это нужно для того, чтобы находить опечатки. Т.к. там, где есть опечатки, только 1-2 n-грамма будут различаться, что в итоге даст высокий threshold и позволит обнаружить дубликаты. Ну а дальше текст превращается в вектора, между ними ищется косинусное сходство и выводится в виде матрицы. И фильтруются те пары, которые проходят заданный нами порог threshold.

Данные с запозданием в БД Предположим, в базе данных случаются ситуации: операция была произведена 30-ого числа, а в DWH эта запись отразилась только 5-ого. В случае, если по таким данным строятся отчеты, то такие отчеты будут неполными, т.к. в них не будут входить подобные операции (если берем отчеты за месяц). Исправлять же уже готовые отчеты задним числом тоже не совсем правильно и допустимо. И для того, чтобы избегать таких ситуаций и иметь корректные отчеты, было придумано несколько способов: ▶️Отчеты можно делать в виде Insert-only витрин. Это значит, что все факты добавляются новыми строками, а старые не перезаписываются. Отчёт считается и обновляется автоматически и является динамическим, при этом он защищен от удалений и включает все когда-либо пришедшие данные. Но такой способ подойдет далеко не всем и не всегда. ▶️Также в DWH можно ввести понятие "закрытый период", который учитывает этот самый пятидневный лаг. И сделать так, что отчет доступен для изменений, условно, до 5-ого числа, а после этого он фиксируется окончательно. В случаях же, если данные из отчетного периода приходят после 5-ого числа, то они считаются "корректировками". Корректировки, в свою очередь, могут идти как в отдельную таблицу, так и в следующий отчетный период с пометкой "корректировка". В будущем они могут быть использованы и для пересчета старых отчетов, и для создания новых версий сданных отчетов, и для пересчета новых отчетов и т.д. Но, очевидно, такой способ подходит далеко не везде и не всегда. ▶️Еще один выход - это хранить в таблицах 2 даты: дату совершения операции (event_date) и дату загрузки в DWH (load_date). Для формирования классического отчёта на определённую дату идет агрегация по event_date, всё как и обычно. Но в случае, когда позже нужно будет найти и объяснить расхождения, потребуется уже load_date. Благодаря такой воспроизводимости будет видно, какие именно операции не были учтены в отчете по причине поздней загрузки. Довольно универсальный способ, хоть и потребует небольшую адаптацию архитектуры БД.

Предвзятость отбора в аналитике Предвзятость отбора (selection bias) - это систематическая ошибка, которая возникает в случаях, когда выборка не является репрезентативной для генеральной совокупности. И из-за того, что аналитика изначально уже строится на таких искаженных данных, получаются неверные выводы. И хоть это кажется интуитивно понятным, что всего-то и нужно, что брать объективные данные, которые отображают как "плюс", так и "минус", на деле это бывает далеко не всегда очевидно. Существуют множество видов так называемых bias, но самые основные из них это: ▶️Sampling bias – наиболее очевидный вариант: способ сбора данных искажает распределение. Возникает на этапе формирования выборки. Например, если строится модель кредитоспособности клиентов, которая должна уметь предсказывать дефолты, то нужно брать не только тех, кто получил кредит, а еще и тех, кому было отказано. Потому что у отказников могли быть принципиально иные характеристики, и без их учёта аналитика будет строиться только на хороших клиентах. И в результате модель не сможет предсказывать дефолты среди потенциальных новых заёмщиков, потому что просто не знает, как выглядят рискованные профили. ▶️Если говорить о Survivorship bias (смещение выжившего), то, в отличие от предыдущего искажения, которое возникает на этапе формирования выборки, это искажение возникает в результате "естественного отбора" во времени. Анализируются только те, кто просуществовал до момента наблюдения, а выбывшие игнорируются. Например, при расчете доходности кредитного портфеля, сформированного из кредитов пятилетней давности, берутся только активные кредиты, которые существуют до сих пор. При этом игнорируются досрочно погашенные кредиты и кредиты, закрытые из-за дефолта. Из-за этого итоговая доходность может сильно исказиться. ▶️Self-selection bias возникает в основном в социальных исследованиях, когда участники сами могут решать, участвовать или нет. Потому что обычно решение участвовать или нет - не случайно, а связано с характеристиками участников, которые могут влиять на изучаемый показатель. Вспоминая пример из Sampling bias с кредитной моделью, обсуждалось только 2 категории клиентов: те, кому одобрили кредит и те, кому отказали. Но в случае с Self-selection bias появляется уже третья категория - те, кто в принципе не обращался за кредитом по той или иной причине. И если банк, например, считает "вероятность взять кредит" без учета третьей категории, то он игнорирует конверсию самого кредитного предложения, из-за чего результаты будут сильно завышены. ▶️Collider bias - одно из самых неочевидных искажений, потому что связано с двумя и более факторами, которые влияют на само событие. Например, возвращаясь обратно к кредитам, представим, что есть 2 фактора, которые влияют на одобрение: это финансовая дисциплина (А) и уровень дохода (Б). И достаточно выполнения только лишь одного из этих условий, чтобы кредит одобрили. При этом также допустим, что в популяции А и Б никак между собой не связаны. Теперь представим, что есть выборка только среди тех, кому кредит одобрили. И на этой выборке исследуются факторы А и Б. Такая выборка уже изначально может содержать только лишь данные, в которых: А = 1 Б = 1 ----------- А = 0 Б = 1 ----------- А = 1 Б = 0 Из-за этого между А и Б возникает отрицательная корреляция, потому что если у клиента А = 0, то у него якобы обязательно должен быть Б = 1, и наоборот. То есть эти 2 фактора становятся связанными между собой, хотя, как было сказано ранее, в популяции А и Б между собой вообще никак не связаны.