Measuring reading engagement with the Discourse Data Explorer plugin


(Sarah Hawk) #15

That would be useful but it might be hard to deliver via the data explorer interface.


(David McClure) #16

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

(Sarah Hawk) #17

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.


(Alessio Fattorini) #18

Yeah, how can I pay you back?

Definitely, you deserve it!

I was sure that it was possible, it’s matter of SQL query!


(David McClure) #19

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 :wink:


(Sarah Hawk) #20

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.


(Alessio Fattorini) #21

I’m doing the same :slight_smile:


(Jørgen) #22

Hi, Jørgen from DiscourseMetrics here :slight_smile:

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.


(Bas van Leeuwen) #23

I really like that one; mind if I steal it for Community Analytics? :slight_smile:

(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)


(Jørgen) #24

Hi :slight_smile: Sure, just send me a PM :slight_smile:


(Stephen Gates) #25

I’ve just started using data explorer this week and started copying some queries from this topic on meta.discourse.org.

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

From @techAPJ on meta.discourse.org.


(Sarah Hawk) #26

Brilliant. That’s potentially useful – thanks for sharing.

I’m really interested in this as well. We’ve discussed it previously and I’ve hacked together a messy workaround, but I’d love to find an automated way of doing it.


(Sarah Hawk) #27

Ping @mcwumbly
David, I’d love a query that tells me who has read a specific topic. Is that possible?


(Sarah Hawk) #28

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

(Bas van Leeuwen) #29

Why do you exclude closed and archived topics in this? Is the assumption that those are spam?


(Sarah Hawk) #30

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.


(Anton) #31

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 :slight_smile:


(Nick Lawson) #32

Hi @DiscourseMetrics did you do this test? Thinking of doing something similar and would be keen hear your results/learnings?


(Sarah Hawk) #33

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.


(Nick Lawson) #34

Will do thanks