How to get the number of mentions groups have had over the past year, and number of members

data & reporting
,
1

Hi, I’m a moderator on the Hopscotch forum, and I was wondering if we could ask for help with these Data Explorer queries:

  • How many @mentions that groups have had in the past 365 days
    • sorted from lowest to highest,
    • and only if the group has existed for more than 365 days (i.e. the group creation date is more than 365 days ago)
  • How many @mentions that groups have had in the past 365 days
    • only if the group has less than 5 mentions in the past 365 days
    • sorted from lowest to highest,
    • and only if the group has existed for more than 365 days (i.e. the group creation date is more than 365 days ago)
  • How many members that groups have,
    • only if the group has less than 10 members
    • sorted from lowest to highest

The aim is for us to work through removing inactive groups. (I would pass queries to an admin to create)

Thank you in advance,
Tracey

(I would add the tag data-explorer but it seems like I can’t do that at the moment)

1 Like
2

Hello,

Just to clarify, you need three queries?

  1. For all groups that are older than 365 days, list how many mentions they have had, in ascending order
  2. For all groups that are older than 365 days, list how many mentions they have had, in ascending order, only if they have had less than five mentions
  3. For all groups with less than 10 members, list how many members they have

If so, I can do this for you - all the information you need would be stored in groups, group_users and group_mentions.

3

As far as I can tell, Discourse doesn’t explicitly keep track of mentions, but it does keep track of links that are added to topics. A group mention in a post will be recorded as the value of the url in the topic_links table with the following form: "/groups/<group_name>". The following query should give you close to what you are looking for. You’ll need to supply values for the start_date and end_date parameters. Those dates should be in the form yyyy-mm-dd. For example, to get data for the past year, enter 2020-01-01 as the start_date and 2020-12-31 as the end_date:

--[params]
-- date :start_date
-- date :end_date

WITH group_mentions AS (
SELECT
split_part(url, '/', 3) AS group_name
FROM topic_links tl
JOIN topics t ON t.id = tl.topic_id
WHERE internal = true
AND url LIKE '/groups/%'
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND tl.created_at::date BETWEEN :start_date AND :end_date
)

SELECT
gm.group_name,
COUNT(gm.group_name) AS mention_count
FROM group_mentions gm
JOIN groups g ON g.name = gm.group_name
WHERE g.created_at::date <= :start_date
GROUP BY gm.group_name
ORDER BY mention_count DESC

The query is only returning mentions that have been added to regular posts (it’s including mentions that have been added to whisper posts though.) If you would like mentions that have been added to personal messages to be included in the results, remove the AND t.archetype = 'regular' line from the first query.

Let me know if this isn’t returning the kind of data you are looking for. I’ll try to get to your other questions tomorrow if no one answers them before then.

3 Likes