One of my favorite new discourse 1.9 features is the “time spent (time spent recently)” stat on user cards. This is super helpful for understanding whether the person I am looking at is a truly active, participating member of the community who is curious and engaged.
I’d be interested in getting this information about discourse groups. Specifically, how much time have members of a particular discourse group spent collectively reading discussions? It will help to monitor the health of different parts of the community, and also to generate some competitive energy between groups to get them reading.
As far as I know this data does not exist anywhere, though it likely can be arrived at by downloading user lists into excel or via the query explorer. Before I devote any time to it, please let me know if it does exist somewhere or if someone has already or would be willing to write a data explorer query. It would simply spit out a list of groups along with two numbers: collective time spent reading overall and time spent reading in the last 60 days. Maybe a column with the number of members in the group would also be interesting to look at in connection with the time spent numbers.
One possible use case: we’ve been talking about ways to encourage logging in and reading discussions by staff in my organization. We’re over 100 people spread around the globe, and the level of participation is mixed. Some log in every day and participate regularly by posting and liking and replying, some log in and read as lurkers without posting and liking much, and many (vast majority really) rely on email notifications and reply by email or don’t reply at all. All of them are in various discourse group by staff and by team. So would be neat to let them know once in a while how much time they are spending as a team on discourse as compared to other teams.
(On the path towards “full citizenship” for groups, it would be amazing to eventually display group stats including collective read time on group pages. Group “user card” may be overkill but potentially also a nice thing for some communities.)
Has anyone got or willing to create a query for calculating the amount of time spent reading by discourse groups?
Because groups have a different number of members, it would be valuable to be able to show both the cumulative time spent as well as the average time spent. E.g. to add up total number of hours spent by everyone on the team divided by the number of members.
Down the road I’d love to see user cards for groups and stats like read time displayed on the group page.
I’d be grateful for some help with creating this query - it’s not my forte and I’m not too proud to admit it.
I used a spreadsheet yesterday to manually calculate the average amount of time spent reading by staff groups in my community, and it’s really quite interesting to understand how our community is using (or not using) discourse, and to nudge people to encourage their colleagues to log in and read more.
Last month I announced a “friendly competition” amongst staff groups to see which group can log the most time reading discussions. Here’s a post I just sent out announcing the winners, with some info redacted. People love it, I see lots of people logged in right now that I don’t usually see, and I want to do this every month from now on.
The results are in! As of 8 February, the team that spends the most time reading forum discussions is… drum roll … @group1. Congratulations! Your competitive streak and dedication is paying off.
Namati team
Time spent reading since joining (average)
Time spent recently (average)
@group1
113:06
6:46
@group2
45:00
2:20
@group3
15:26
0:59
I wonder which team will win next month? The time spent reading number above is an average, so if your team is near the bottom this month it is likely because some colleagues are not logging in. To win, urge your colleagues to log into https://community.namati.org as frequently as they can to read discussions. There are some fabulous topics to look at - just go to TOP, choose a topic that looks interesting, and when you get to the bottom look at the next suggested topics, choose one to read, repeat. To paraphrase Kid President, “It’s like shampoo, but with your community!”
I took a look at this for @tobiaseigen and came up with the following:
SELECT group_users.group_id, ROUND((cast(sum(user_visits.time_read) as decimal)/60), 2) FROM user_visits
INNER JOIN group_users ON group_users.user_id = user_visits.user_id
WHERE user_visits.visited_at >= current_date - interval '60' day
GROUP BY group_users.group_id
We want each group compared against their time_read sum
We need to join group_users to user_visits to make the comparison
We want to restrict the records to the last 60 days (for the recent time read)
We want to group the results by group_id
If you want to get the results in hh:mm:ss format, just add interval '1' minute * before ROUND.
drum roll 
