Как считать пенетрацию пользователей в продукте на SQL?

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

Тут достаточно понятно, берем группировку по дням / неделям / месяцам, считаем уникальных пользователей в приложении и готово!

❓ Пенетрация позволяет ответить на вопрос: "Сколько всего пользователей пользуются продуктом в динамике?". В сервисе есть старички, которые регулярно продукт используют и за время мы их учитываем несколько раз (по дням). Мы можем взять весь год и посмотреть сколько всего пользователей использовали фичу 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 🐳 и я выложу еще что-нибудь по этой тематике)