Can I generate a report of users who have NOT posted in a given topic?

Support
1

Subject says it all…

Can I see a list of users who have not posted in a particular category? I’m trying to find users who aren’t actively posting so I can try to interact with them more…

3

I think you can do it with Data Explorer Plugin. :+1:

2 Likes
4

With data explorer, something like this should do the trick, for a given topic:

-- [params]
-- int :topic_id = 1

SELECT id, username
FROM users u
WHERE u.id NOT IN 
(SELECT user_id FROM posts WHERE topic_id=:topic_id)

And for users who have not posted in a given category, something like:

-- [params]
-- int :cat_id = 1

SELECT id, username
FROM users u
WHERE u.id NOT IN 
(SELECT user_id FROM posts WHERE topic_id IN
    (SELECT id from topics where category_id=:cat_id)
)

(You can find category ids by running select id, name from categories).

Hmm actually that second query doesn’t seem to work for me for all categories though, not sure why, maybe someone else can test / tweak if needed!

1 Like
5

What about something like this? :thinking:

-- [params]
-- int :cat_id = 4
-- int :limit = 10

SELECT id, username
FROM users u
WHERE u.id NOT IN 
(SELECT user_id FROM categories WHERE id=:cat_id)
LIMIT :limit
6

For sites that have the Data Explorer plugin installed, the easiest approach would be to write a Data Explorer query to get the results. On a busy forum, you might need to refine your criteria to avoid returning too many users.

For sites that don’t have the Data Explorer plugin - especially for sites that don’t have many users - you can find users who aren’t actively posting by going to the site’s users page. On that page, you can select a time period from the time filter that’s at the top-left of the page. You can then find users who aren’t posting by clicking on either the ‘Topics’ or the ‘Replies’ table headers. When the caret icon next to a table header is pointing up, the table will display results in ascending order. Here’s an example from meta: https://meta.discourse.org/u?asc=true&order=post_count.

While the approach won’t tell you which categories users are posting in, it will give information about who is active on the site.

4 Likes
7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.