Statistics per group and per trust level?


(Jono Bacon) #1

Hi Everyone,

I am finding with more and more rollouts of Discourse with clients that I am wanting to track the following:

  • I would like to see the existing graphs but for specific groups (the trust level groups included). I can do this for the read graphs, but not topic and post graphs.
  • I would also like to be able to see a list of which people move between trust levels on a set cadence (e.g. every month).

Am I missing something, or are these statistics not currently available?


(Mittineague) #2

I’m not clear as to exactly what you’re looking for. Maybe something like this using the Data Explorer plugin?


WITH trust_level_one AS ( SELECT to_char(user_badges.granted_at, 'YYYY-MM') AS tl1ym
  , COUNT(user_badges.user_id) as tl1uc 
  FROM user_badges
  WHERE user_badges.badge_id IN(
    SELECT id 
    FROM badges 
    WHERE badges.name LIKE 'Basic User')
    GROUP BY to_char(user_badges.granted_at, 'YYYY-MM')
    )
, trust_level_two AS ( SELECT to_char(user_badges.granted_at, 'YYYY-MM') AS tl2ym
  , COUNT(user_badges.user_id) as tl2uc 
  FROM user_badges
  WHERE user_badges.badge_id IN(
    SELECT id 
    FROM badges 
    WHERE badges.name LIKE 'Member')
    GROUP BY to_char(user_badges.granted_at, 'YYYY-MM')
    )
SELECT trust_level_one.tl1ym
 , trust_level_one.tl1uc
 , trust_level_two.tl2uc
FROM trust_level_one
FULL JOIN trust_level_two 
ON trust_level_two.tl2ym = trust_level_one.tl1ym
GROUP BY trust_level_one.tl1ym, trust_level_one.tl1uc, trust_level_two.tl2uc
ORDER BY trust_level_one.tl1ym

example results

tl1ym tl1uc tl2uc
2016-02 11 6
2016-03 19 4
2016-04 12 7
2016-05 5 13
2016-06 1 1
2016-12 1 NULL
2017-01 1 1
2017-11 1 1
2018-01 2 1

(Jono Bacon) #3

In a nutshell, there are two primary things I want to know:

  • Firstly, I would like to know which users are transitioning between trust levels each month (e.g. “Foo, Bar, and Baz went from Trust Level 2 to 3 this month”). This way I can connect those people to my incentive programs.

  • Secondly, I want to see the current graphs in the dashboard applied to specific groups. I can do this for user visits and new users, but I can’t see this for topics/posts and other graphs.

I just wanted to ask if there is a way to do this inside the current admin interface, or do I need a different plugin or query for this?


(Tobias Eigen) #4

Interesting - I’ve been wanting better stats about groups as well, but different ones. Personally I’ve given up on trying to use trust levels for incentivization and have hidden them from view.

If you look in the new groups management pages you can look in the log to see who has joined groups and when. At the moment this doesn’t appear to be available for automatic groups. (cc @tgxworld)

Here’s what I’d love to see:


(Jono Bacon) #5

Interesting. How have trust levels not worked for you for incentivization and how did you use them? If there is another topic with this, happy to read it.


(Tobias Eigen) #6

There are a few topics about this from a few years ago. The main thing to know about trust levels is that they are mostly about privileges and powers, and admins can put a halt to automatic promotion by setting trust levels. There is also not enough transparency to the members about how they work and when people are promoted and for what reason. In my community this caused some grief among moderators who got competitive and were frustrated that they were not being promoted. Pointless, really, because they were already moderators.

So… we just focus on badges, and metrics like time spent reading!