Как рассчитать DAU и MAU на основе ежедневных кликов с помощью SQL

Вопрос или проблема

WITH RECURSIVE t(date) AS (
    SELECT 
        CURRENT_DATE as date
        ,c1.product AS cmd 
        ,COUNT(DISTINCT(c1.user)) as mau
    FROM clicks c1
    WHERE (c1.date BETWEEN CURRENT_DATE - interval '30 day' AND CURRENT_DATE) 
    GROUP BY 1, 2
UNION ALL
        SELECT 
        (t.date - interval '1 day')::DATE date,
        ,c2.product 
        ,COUNT(DISTINCT(c2.user)) as mau
    FROM t, clicks c2
    WHERE (c2.date BETWEEN t.date - interval '30 day' AND t.date) 
    GROUP BY 1, 2)
SELECT *
FROM t;

Ответ или решение

Для расчета DAU (ежедневные активные пользователи), MAU (ежемесячные активные пользователи) и SF (степень вовлеченности) на основе данных о кликах, представленных в таблице clicks, необходимо сформировать SQL-запросы, которые позволяют агрегировать данные по пользователям и времени.

DAU (Ежедневные активные пользователи)

DAU рассчитывается как количество уникальных пользователей, которые взаимодействовали с продуктом хотя бы один раз в день. В SQL это можно сделать с использованием функции COUNT(DISTINCT ...).

Вот как можно рассчитать DAU для каждого продукта за каждый день:

SELECT
    date,
    product,
    COUNT(DISTINCT user) AS DAU
FROM
    clicks
GROUP BY
    date, product
ORDER BY
    date, product;

MAU (Ежемесячные активные пользователи)

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

Запрос для расчета MAU

WITH UserActivity AS (
    SELECT
        date,
        product,
        user
    FROM
        clicks
    WHERE
        date >= CURRENT_DATE - INTERVAL '30 days'
),
UserCounts AS (
    SELECT
        a.date,
        a.product,
        COUNT(DISTINCT a.user) AS MAU
    FROM
        UserActivity a
    JOIN UserActivity b ON a.product = b.product AND a.user = b.user
    WHERE
        b.date BETWEEN a.date - INTERVAL '30 days' AND a.date
    GROUP BY
        a.date, a.product
)
SELECT
    date,
    product,
    MAU
FROM
    UserCounts
ORDER BY
    date, product;

SF (Степень вовлеченности)

После того как DAU и MAU рассчитаны, можно вычислить SF как отношение DAU к MAU, умноженное на 100%.

Запрос для расчета SF

WITH DAU AS (
    SELECT
        date,
        product,
        COUNT(DISTINCT user) AS DAU
    FROM
        clicks
    GROUP BY
        date, product
),
MAU AS (
    SELECT
        a.date,
        a.product,
        COUNT(DISTINCT a.user) AS MAU
    FROM
        clicks a
    WHERE
        a.date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY
        a.date, a.product
)
SELECT
    d.date,
    d.product,
    d.DAU,
    m.MAU,
    ROUND((d.DAU::FLOAT / NULLIF(m.MAU, 0)) * 100, 2) AS SF
FROM
    DAU d
JOIN
    MAU m ON d.date = m.date AND d.product = m.product
ORDER BY
    d.date, d.product;

Объяснение

1. DAU: Мы агрегируем данные, используя COUNT(DISTINCT user), чтобы получить количество уникальных пользователей для каждой даты и продукта.

2. MAU: Мы создаем временную таблицу UserActivity, которая включает только пользователей, активных за последние 30 дней, а затем связываем её сама с собой для подсчета уникальных пользователей.

3. SF: Мы объединяем результаты DAU и MAU. Применяем ROUND для округления результата SF до двух знаков после запятой. Используем NULLIF для предотвращения деления на ноль.

Таким образом, вы получите полные данные о DAU, MAU и SF для каждого продукта и дня, которые можно использовать для анализа вовлеченности пользователей и планирования улучшений в продукте. Надеюсь, это поможет вам эффективно работать с данными о кликах и анализировать активность пользователей.