Retrospectively pulling number of users each calendar month

Data & reporting
1

Hello! We are using the cloud-hosted version of Discourse and have been running our platform for about 1.5 years. We would like to pull some historical metrics on platform adoption and use. We are already looking at the ‘Signups’ report available in the Admin Reports tab, but we also want to be able to show the total number of active users each month. Does anyone have insight on how I could do this? I can’t seem to find anything appropriate in Reports or Data Explorer.

2

This data explorer query will return unique monthly user visits. Is that what you want?

--[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