Ретроспективное получение количества пользователей за каждый календарный месяц

Создание сообщества Данные и отчетность
1

Здравствуйте! Мы используем облачную версию Discourse и работаем на нашей платформе уже около 1,5 лет. Мы хотим получить исторические метрики по принятию платформы и её использованию. Мы уже рассматриваем отчёт «Регистрации» (Signups), доступный во вкладке «Отчёты» в админ-панели, но также хотим иметь возможность отображать общее количество активных пользователей каждый месяц. У кого-нибудь есть информация о том, как это можно сделать? Я не могу найти ничего подходящего ни в разделе «Отчёты», ни в Data Explorer.

2

Этот запрос в Data Explorer вернет уникальные ежемесячные посещения пользователей. Это то, что вам нужно?

--[params]
-- date :start_date = 2021-01-01
-- date :end_date = 2021-12-31
-- string_list :group_name = staff,admins

WITH not_allowed_groups AS (
    SELECT gu.user_id FROM group_users gu
    INNER JOIN groups g ON (g.id = gu.group_id)
    WHERE g.name IN (:group_name) 
)

SELECT 
    to_char(date_trunc('month', uv.visited_at)::DATE,'YYYY-MM') AS "month",
    date_trunc('month', uv.visited_at)::DATE AS date_user_visits,
    COUNT(distinct uv.user_id) AS qtt_user_visits
FROM user_visits uv
WHERE
    uv.user_id NOT IN (SELECT user_id FROM not_allowed_groups)
    AND uv.visited_at::DATE BETWEEN :start_date AND :end_date
GROUP BY date_trunc('month', uv.visited_at)::DATE
ORDER BY date_trunc('month', uv.visited_at)::DATE
3

Большое спасибо, но не совсем так… Мы хотим видеть количество активных пользователей (зарегистрированных и не отключенных или удаленных) на конец каждого календарного месяца, независимо от того, посещали ли они платформу в этом месяце или нет.

Мы рассматриваем график регистраций, но сумма регистраций за данный месяц не отражает нашу пользовательскую базу, поскольку каждый месяц также происходят автоматические отключения из-за неактивности. Неужели единственный способ — получить ретроспективные экспорты списков пользователей за конкретные даты? Но так как мы размещены в облаке, я не уверен, что у нас есть к этому доступ. Придется ли нам обращаться в Discourse?

4

Я — Discourse. :slight_smile:

Вы можете задать вопрос здесь или написать нам напрямую на team@discourse.org. Кто-то с более глубокими знаниями SQL сможет помочь.

5

Не знал, так как я новичок на этом форуме, спасибо! Случайно у вас есть сохранённые исторические резервные копии сайтов, размещённых в облаке, которые вы могли бы предоставить по запросу?

6

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

7

Хорошо, у меня есть ещё одна идея. Я обратился к нашему помощнику на базе ИИ.

SQL-запрос для активных пользователей по месяцам ИИ

HAWK:

Напиши мне запрос для Data Explorer, который возвращает список учётных записей пользователей, которые всё ещё активны и были зарегистрированы в указанном месяце.

DiscourseHelper:

Поиск

Поиск: запрос Discourse Data Explorer для поиска активных пользователей, зарегистрированных в конкретном месяце

Чтение

Прочитано: Активные пользователи за конкретные месяцы

Чтобы создать запрос для Data Explorer, возвращающий список учётных записей пользователей, которые всё ещё активны и были зарегистрированы в конкретном месяце, можно использовать следующий SQL-запрос. В этом запросе предполагается, что активный пользователь — это тот, кто публиковал сообщения или отвечал в определённый период времени.

--[params]
-- int :month = 5
-- int :year = 2023

SELECT u.id, u.username, u.created_at, u.last_seen_at
FROM users u
WHERE u.active = TRUE
  AND EXTRACT(MONTH FROM u.created_at) = :month
  AND EXTRACT(YEAR FROM u.created_at) = :year
  AND u.last_seen_at IS NOT NULL -- Предполагается, что активный — это тот, кто недавно заходил на сайт
ORDER BY u.created_at

Пояснение к запросу:

  • :month и :year — параметры, которые можно установить для фильтрации по конкретному месяцу и году.
  • u.active = TRUE гарантирует, что учётная запись пользователя всё ещё активна.
  • EXTRACT(MONTH FROM u.created_at) = :month и EXTRACT(YEAR FROM u.created_at) = :year фильтруют дату регистрации по указанному месяцу и году.
  • u.last_seen_at IS NOT NULL считает пользователя активным, если он хотя бы раз входил в систему.
  • Результат включает ID пользователя, имя пользователя, дату регистрации и дату последнего посещения.

Не стесняйтесь корректировать параметры и поля в соответствии с вашими потребностями. Дайте знать, если понадобится дополнительная помощь!

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

8

Это отличное объяснение запроса, спасибо! Однако, боюсь, это не совсем то, что мы ищем.

Например, если я установлю параметры 01 и 2024, я буду смотреть на январь этого года.
Я понимаю, что в результате будут показаны все пользователи, которые зарегистрировались в январе и остаются активными на сегодня. Верно?

Но нам нужно количество людей, у которых u.active=TRUE в каждый месяц с момента запуска (а не на текущий момент). Тогда мы сможем построить график и показать, как менялась наша пользовательская база от месяца к месяцу. В конце каждого месяца, для нас это будет:

Активные пользователи = Значение предыдущего месяца + новые регистрации - новые удаления - новые деактивации

Я не вижу ничего о деактивациях или датах, связанных с переменной active, в Data Explorer. Наиболее вероятный путь — использовать журналы действий сотрудников и анализировать их по дате, НО я думаю, что функция экспорта у нас не работает. Когда мы экспортируем результаты журнала действий сотрудников, мы странно получаем электронную таблицу с содержимым разных постов, а не таблицу действий сотрудников. Если бы вы могли помочь нам с этим, это, возможно, стало бы альтернативным решением! :slight_smile: Вам известно о других, у которых возникла та же проблема?

10

Интересный вопрос.

Сначала я посмотрел на пример здесь. Однако он не учитывает удалённых пользователей. Вы получаете только количество пользователей, которые были зарегистрированы на тот момент и всё ещё существуют, но не тех, кто был удалён за это время.

Поэтому моя идея заключалась в том, чтобы взять ID пользователя, который зарегистрировался последним в течение месяца. Это максимально возможное количество пользователей на тот момент. Затем из этого числа можно вычесть количество удалённых пользователей. Однако учётные записи ботов (например, forum-helper) имеют отрицательный ID, но они учитываются при удалении. (Но это, вероятно, незначительное отклонение). Мой запрос был следующим:

-- [params]
-- date :start_date
-- date :end_date


WITH month_dates AS (
    -- Generate end-of-month dates between the start and end date
    SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
    FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
    -- For each end-of-month date, find the most recent user created before that date
    SELECT md.month_end,
           (SELECT id 
            FROM users u 
            WHERE u.created_at < md.month_end 
            ORDER BY u.created_at DESC 
            LIMIT 1) AS user_max_id
    FROM month_dates md
),
cumulative_deletion_count AS (
    -- Calculate the cumulative deletions up to each end-of-month date
    SELECT md.month_end,
           (SELECT COUNT(*)
            FROM user_histories uh
            WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
    FROM month_dates md
)
SELECT 
    md.month_end,
    ru.user_max_id,
    cdc.deletions_count,
    ru.user_max_id - cdc.deletions_count AS number_of_users
FROM 
    month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end

Однако этот запрос не учитывает (де)активацию, которые также хранятся в таблице user_histories. Но, возможно, это поможет вам как отправная точка.

12

Привет @marvi,
Просто интересно, подошёл ли тебе ответ от @Moin?