The discussion revolves around measuring active users in a Discourse community. sianwhite initiated the conversation by asking how to measure active/super active users, and JagWaugh suggested using the Data Explorer plugin for more granular data analysis. HAWK provided SQL queries to identify top posters and likers for a given monthly period.
robmc recommended exploring tools like @DiscourseMetrics.com and @Bas for community analytics, and also shared their own experience of tracking user engagement using Excel. Bas agreed that most analysis tools undervalue the role of readers and suggested ways to guide Google Analytics to measure reader engagement.
Moin clarified that the Dashboard > User Visits metric is based on logged-in users, not unique IPs. Isambard requested additional user metrics, such as topics created and likes given/received, to be included in the admin user list. Mike_Taku inquired about tools to track community engagement beyond posting activity, especially for measuring active readers. Finally, copymonopoly asked about creating badges for top posters and likers.
Overall, the discussion highlights the importance of measuring user engagement and provides various approaches to achieve this, including using plugins, SQL queries, and external tools. simon wrote the queries as mentioned by HAWK.
How does everyone measure active/super active users? ie people who have logged in or commented in the past month, for example.
The dashboard tells me the number of visits and comments etc, but I don’t know how to chunk this down easily into actual users. We’ve got 1400 members on our community, but I’d like to know how many of those are engaging with it month on month.
Thanks,
Sian
The data explorer plugin will let you look at the data in a more granular fashion than the basic information which admin>dashboard shows.
See, for example, Active users in the last 30 days
Oo sounds interesting, how do I go about setting that up? I’m not a developer so have no idea where to begin.
See “Installation” at the bottom of: Discourse Data Explorer
These queries will help:
Returns the top 50 posters for a given monthly period. Results are ordered by post_count. It accepts a ‘months_ago’ parameter, defaults to 1 to give results for the most recently completed calendar month.
-- [params]
-- int :months_ago = 1
WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
),
user_posts_in_period AS (
SELECT
p.user_id
FROM posts p
INNER JOIN query_period qp
ON p.created_at >= qp.period_start
AND p.created_at <= qp.period_end
WHERE p.user_id > 0
)
SELECT
up.user_id,
count(1) as post_count
FROM user_posts_in_period up
GROUP BY up.user_id
ORDER BY post_count DESC
LIMIT 50
Returns the top 50 likers for a given monthly period. Results are ordered by like_count. It accepts a ‘months_ago’ parameter, defaults to 1 to give results for the most recently completed calendar month.
-- [params]
-- int :months_ago = 1
WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)
SELECT
ua.user_id,
count(1) AS like_count
FROM user_actions ua
INNER JOIN query_period qp
ON ua.created_at >= qp.period_start
AND ua.created_at <= qp.period_end
WHERE ua.action_type = 1
GROUP BY ua.user_id
ORDER BY like_count DESC
LIMIT 50you could also get in touch with @DiscourseMetrics.com and @Bas (about Community Analytics)
their tools can give you loads of extra stats so you don’t have to do the work … I’m looking at such tools at the moment myself
Up till now I’ve been doing this just in excel - you can export your user data and run a few queries to monitor this. I gave a value to each post read, to posts (replies) and to new threads, then categorised them accordingly
So far it shows 20% are ‘readers’, 25% are ‘participants’ and 15% are ‘creators’
I’ve found that most analysis tools are under-valuing the role of readers because of a focus just on the activity of posting. There is a big difference between inactive members and active readers!
Good luck!
I think you are correct in saying this.
There are ways (e.g. separating logged in/out users, measuring read time, collating paged threads etc.) to guide Google Analytics to do quite a bit of this for you; but it takes some proper GA-fu to accomplish.
Great queries Sarah! Thanks for share.
I updated the query list, if you have more to add, please just send PR 
Thanks again !
I can’t take credit – @simon wrote them.
how to get the daily counts based on unique IPs ?
is the Dashboard> User Visits based on IPs, (including logged in users and vistors ?)
thanks…
I don’t think so. It is about logged in users.
You can also have a look at What is "user visits" on the admin dashboard?
I think this is similar to what you are looking for. Since you want all users and not only anonymous, you would have to remove the condition that the user is anonymous.
In the userlist, we have these stats:
Username, Last Emailed, Seen, Topics Viewed, Posts Read, Read Time, Created
Would it be possible to add:
Number of topics created, number of posts created, number of likes given, number of likes received.
These are useful to measure the most active contributors.
Those are all included in Discourse Meta
Thanks. It seems it is there for the user directory, but not in the admin user list at
https://meta.discourse.org/admin/users/list/active
I wonder if the same fields could be shown in admin, or if the user directory (when disabled in settings) can still be viewed by admins so these additional details are visible.
Hi there, just saw this thread, if you are here can you please tell me what tools can help track community engagement beyond just posting activity, especially for measuring active readers?
Thank You,
Mike Taku.
Can I create a badge for these posters and likers?