Как считать пенетрацию пользователей в продукте на SQL? 🎮 В сервисе у нас есть чарт, характеризующий количество пользователей в сервисе (MAU / DAU / WAU), мы смотрим за определенный промежуток времени количество пользователей. Этот график интуитивно понятен, есть практически во всех продуктах и является одной из тех метрик, которую отслеживают.

Тут достаточно понятно, берем группировку по дням / неделям / месяцам, считаем уникальных пользователей в приложении и готово! `WITH user_activity AS ( SELECT user_id, event_date, DATE_TRUNC('week', event_date) AS week_start, DATE_TRUNC('month', event_date) AS month_start FROM user_events WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31' ) SELECT event_date, COUNT(DISTINCT user_id) AS DAU, COUNT(DISTINCT CASE WHEN event_date = week_start THEN user_id END) AS WAU, COUNT(DISTINCT CASE WHEN event_date = month_start THEN user_id END) AS MAU FROM user_activity GROUP BY event_date ORDER BY event_date;

❓ Пенетрация позволяет ответить на вопрос: "Сколько всего пользователей пользуются продуктом в динамике?". В сервисе есть старички, которые регулярно продукт используют и за время мы их учитываем несколько раз (по дням). Мы можем взять весь год и посмотреть сколько всего пользователей использовали фичу X и посчитать статично, найти долю и все. Но хочется понимать как инициативы влияют на абсолютные значения / доли относительно всех пользователей продукта до момента T.

`WITH daily_users AS ( SELECT event_date, user_id FROM user_events WHERE event_date BETWEEN '2024-01-01' AND '2024-01-30' ), date_series AS ( SELECT DISTINCT event_date FROM daily_users ), cumulative_users AS ( SELECT d.event_date, COUNT(DISTINCT u.user_id) AS cumulative_unique_users FROM date_series d LEFT JOIN daily_users u ON u.event_date <= d.event_date GROUP BY d.event_date ORDER BY d.event_date ) SELECT * FROM cumulative_users;

⬆️ Выше представлен скрипт, который считает накопительно пользователей по дням, теперь мы можем это применить для ответа на вопрос: "Какой процент пользователей когда-либо использовал продукт на момент времени T?". Это нам может быть нужно для отслеживания доли использования от всей аудитории накопительно. Мы можем более явно отслеживать как наша база (в тотале) реагирует по дням, когда мы используем какие-то механики, например, или запускаем новые фичи

`WITH daily_feature_users AS ( SELECT event_date, user_id FROM user_events WHERE event_name = 'feature_x' AND event_date BETWEEN '2024-01-01' AND '2024-01-30' ), daily_total_users AS ( SELECT event_date, user_id FROM user_events WHERE event_date BETWEEN '2024-01-01' AND '2024-01-30' ), date_series AS ( SELECT DISTINCT event_date FROM daily_total_users ), cumulative_feature_users AS ( SELECT d.event_date, COUNT(DISTINCT u.user_id) AS cumulative_feature_users FROM date_series d LEFT JOIN daily_feature_users u ON u.event_date <= d.event_date GROUP BY d.event_date ORDER BY d.event_date ), cumulative_total_users AS ( SELECT d.event_date, COUNT(DISTINCT u.user_id) AS cumulative_total_users FROM date_series d LEFT JOIN daily_total_users u ON u.event_date <= d.event_date GROUP BY d.event_date ORDER BY d.event_date ) SELECT cfu.event_date, cfu.cumulative_feature_users, ctu.cumulative_total_users, ROUND(100.0 * cfu.cumulative_feature_users / (ctu.cumulative_total_users, 0), 2) AS penetration_rate FROM cumulative_feature_users cfu JOIN cumulative_total_users ctu ON cfu.event_date = ctu.event_date ORDER BY cfu.event_date;

⬆️ Выше представлен код, как мы считае долю тех, кто использовал фичу относительно всех пользователей до момента T. 🐖 Используете ли вы пенетрацию для отслеживания доли относительно всех пользователей? Был ли этот пост полезен? Ставьте 100 🐳 и я выложу еще что-нибудь по этой тематике)```