I would like to reach out to the different groups of people who participated in different categories and subcategories. Is there some way to easily generate the list of people who contributed to a category or subcategory?
Not by us moderators afaik.
@matthias helped me with this once…
We have now the Data Explorer plugin that allows us to run queries that would return what you are looking for (needs being a Discourse admin user, so e-mail addresses etc. are still protected this way).
This needs creating the queries once, which I can do (given a bit of time). When would you need this?
When is a reasonable time frame? Nice to have soon, but not as cost of stress.
Let’s give it a week. It’s not that much work, so I can probably fit it in earlier.
Ok, here’s your data explorer query to see users and their post counts per top-level category. Just click the link and then Run to see the results.
Any post directly in the top-level category or in any of its sub-categories is counted as a contribution for the top-level category this query. By default, users who contributed 5 posts or less are excluded, but you can easily adapt this in the
HAVING clause near the bottom. You can also restrict the output to one top-level category only as per the instructions. This is useful in case there are more than 1000 results, in which case not all of them would be shown.
Backup of that query, also in case anyone else from the Interwebs finds it useful:
/* Sum up codes in sub-level and top-level categories */ SELECT category_id, SUM(contributions) AS posts_count, username FROM ( /* Users with topics in top-level categories. */ SELECT topics.category_id, COUNT(*) AS contributions, users.username FROM users INNER JOIN posts ON users.id = posts.user_id INNER JOIN topics ON posts.topic_id = topics.id INNER JOIN categories ON topics.category_id = categories.id WHERE categories.parent_category_id IS NULL GROUP BY category_id, username UNION /* Users with topics in sub-level categories, by top-level category. */ SELECT categories.parent_category_id AS category_id, COUNT(*) AS contributions, users.username FROM users INNER JOIN posts ON users.id = posts.user_id INNER JOIN topics ON posts.topic_id = topics.id INNER JOIN categories ON topics.category_id = categories.id WHERE categories.parent_category_id IS NOT NULL GROUP BY categories.parent_category_id, username ) AS users_by_category INNER JOIN categories ON category_id = categories.id /* To see results for one category only, enable exactly one of the lines below by removing the comment markers at the beginning and end. You can then use "HAVING SUM(contributions) > 1" below as including rarely contributing users will then not exceed the max. results. */ */ /* WHERE categories.name = 'Campfire' */ /* WHERE categories.name = 'Wellbeing in Europe' */ /* WHERE categories.name = 'Internet of Humans' */ /* WHERE categories.name = 'Culture Squad' */ /* WHERE categories.name = 'Research Network' */ /* WHERE categories.name = 'EarthOS' */ /* WHERE categories.name = 'Participio' */ /* WHERE categories.name = 'OCI Lab' */ /* WHERE categories.name = 'Documentation & Support' */ /* WHERE categories.name = 'Knowledge Collection' */ /* WHERE categories.name = 'Workspaces' */ /* WHERE categories.name = 'Uncategorized' */ GROUP BY category_id, username HAVING SUM(contributions) > 5 ORDER BY category_id, posts_count DESC
Ohh thank you!!
Hi @matthias, thanks for this! I was wondering if you have a github repository of this code? I work with some people on a software for mailinglist analysis called BigBang and we are doing very similar things. Would be cool to link up!
Hmm no, it’s not on Github. If I’d post it there, it would go into this list.
This is code that’s only useful within the Discourse forum software that we use, relying on a plugin for it called Data Explorer. It’s not immediately useful to your mailing list analysis software.
If you want the code, take it. I hereby make it public domain (CC0).
Hallo Matthias, does this include all the users who have contributed in the subcategories to a category also?
Yes, contributions to a sub-level category are counted towards the containing top-level category. I updated the description above and the one in Data Explorer accordingly.
Hi @matthias, is it possible to get the list with ONLY contributors to a subcategory?
Thinking about Wellbeing in Europe, where subcommunities are very strong and looking at the big list we cannot identify who;s in the PL or CZ language group…
Just checking again if this is possible @matthias?
AFAIK It is possible, you just use the same script but pointing to a subcategory.
This new query can list users and post counts for any category, whether top-level or sub-level. This means, adapting the original query “Posts: by top-level category and user” to limit it to one top-level category is no longer needed, so I removed that part from the original query (cc @nadia, who will have to use the new query for these purposes now).
Thanks Matt, it works!!