That would be useful but it might be hard to deliver via the data explorer interface.
Here you go:
-- [params] -- integer :user_min = 1 -- integer :user_max = 5 -- integer :since_days_ago = 7 with topic_timing as ( select user_id, topic_id, sum(msecs) / 1000 as seconds from post_timings where user_id >= :user_min and user_id <= :user_max group by user_id, topic_id ) SELECT tv.topic_id, tv.user_id, tv.viewed_at, tt.seconds from topic_views tv left join topic_timing tt on tv.topic_id = tt.topic_id and tv.user_id = tt.user_id where tv.user_id >= :user_min and tv.user_id <= :user_max and viewed_at + :since_days_ago > CURRENT_TIMESTAMP order by user_id asc, seconds desc
Brilliant. This stuff is gold. I suddenly have a new level of visibility and it’s empowering.
I’ve just awarded you a bespoke badge.
Yeah, how can I pay you back?
Definitely, you deserve it!
I was sure that it was possible, it’s matter of SQL query!
Just keep an eye out for good stuff from your own experience or other’s and please share!
I’d especially like to hear more stories of how data is actually being used to drive or validate decisions
At the moment I’m using it to try and pinpoint the pain points of new users that sign up and read but don’t engage. If I can see that someone joined and read 5 or 6 posts about boosting engagement (for instance), I can personally contact them asking them if they need support with an engagement issue and offer appropriate resources. I’ll have to come back to you with evidence of efficacy once I’ve been doing it for a while. In short, being able to really specifically personalise the contact seems like a powerful tool.
I’m doing the same
Hi, Jørgen from DiscourseMetrics here
One idea I’m planning to test out for my own forum is sending a welcome email campaign to all new users to encourage engagement, with useful links to popular posts, asking them questions etc. These would be spaced every few days for a few weeks.
I’m also looking into adding metrics to measure engagement by new users:
- Time from signup to first like or post (the faster the better)
- Number of posts in first week and 30 days (the higher the better)
Tracking these over time could indicate how well you are engaging new users.
I really like @HAWK’s outreach idea, it would be interesting to see if this could be automated somehow.
I really like that one; mind if I steal it for Community Analytics?
(on a related note, I think we’re roughly in the same business, you up for a chat? I always like to share/bounce ideas)
Hi Sure, just send me a PM
I am not a community manager but was thinking about queries like:
- new members that have posted but without a like
- unanswered topics
- helping members find their “birds of a feather” group and ensuring they earn a badge to make them feel part of a team
I’d like to understand what other queries can make people feel welcome and encourage contributions.
EDIT: Reading elsewhere about “Lurkers”, reminded me I found a query that I parameterised to identify people that have read a lot but not posted.
--[params] -- int :num_of_posts = 100 WITH posts_by_user AS ( SELECT COUNT(*) AS posts, user_id FROM posts GROUP BY user_id ), posts_read_by_user AS ( SELECT SUM(posts_read) AS posts_read, user_id FROM user_visits GROUP BY user_id ) SELECT u.id, u.username_lower AS "username", u.created_at, u.last_seen_at, COALESCE(pbu.posts, 0) AS "posts_created", COALESCE(prbu.posts_read, 0) AS "posts_read" FROM users u LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id WHERE u.active = true AND posts IS NULL AND posts_read > :num_of_posts ORDER BY posts_read DESC
Brilliant. That’s potentially useful – thanks for sharing.
David, I’d love a query that tells me who has read a specific topic. Is that possible?
Updating this topic with monthly active users query
-- [params] -- date :date_from -- date :date_to -- int :min_posts = 1 WITH user_activity AS ( SELECT p.user_id, count (p.id) as posts_count FROM posts p LEFT JOIN topics t ON t.id = p.topic_id WHERE p.created_at::date BETWEEN :date_from::date AND :date_to::date AND t.deleted_at IS NULL AND t.visible = TRUE AND t.closed = FALSE AND t.archived = FALSE AND t.archetype = 'regular' AND p.deleted_at IS NULL GROUP BY p.user_id ) SELECT COUNT(user_id) FROM user_activity WHERE posts_count >= :min_posts
Why do you exclude closed and archived topics in this? Is the assumption that those are spam?
Good question. @meglio wrote the query and I included it here in its entirety. He may be able to answer on this.
I don’t actually include closed or archived when I use it.
Looks like there isn’t any specific reason for that.
To be fair, I don’t remember if I did so intentionally. It looked logical to me at the time of query writing - maybe because in our community we used to never close a topic unless it was useless or spammy. But it might not be the case in your community.
P.S. @HAWK I wanna mine as well if that is possible
Hi @DiscourseMetrics did you do this test? Thinking of doing something similar and would be keen hear your results/learnings?
I do this in a couple of communities @nick_lawson (as do a few others here I think).
If you want to start a new topic we can dig into it a bit deeper.
Will do thanks