Stats reports on a per-category basis?
The discussion revolves around generating reports for pageviews, topics, and posts on a per-category basis in Discourse. shooj inquires about the possibility of customizing the stock Discourse reports UI or using analytics tools to achieve this.
Grayden_Shand recommends the Data Explorer plugin as a solution, providing a sample SQL query that captures topics, posts, likes, and reads for a specified date range. The reports can be shared with groups via the Data Explorer UI.
cogdog finds the solution helpful and extends the query by adding a count of replies in a topic using sum(t.reply_count) replies.
Hi folks, wasn’t able to find this discussed previously.
Is there a way to generate reports (pageviews, topics, posts) on a per-category basis?
We created a new category that’s generating interest from our community, and we’d like to see its growth curve over time as we make people aware it’s there.
Would that be possible? Could that be done by 1) customizing the stock Discourse reports UI, or 2) by using analytics tools on my end to look at the Discourse database through the API?
Check out the Data Explorer plugin.
You can use this to generate reports from SQL queries, here’s a query that will capture some of what you want to track.
-- [params]
-- date :start_date = 2020-04-01
-- date :end_date = 2020-04-29
SELECT c.id category_id, COUNT(DISTINCT(t.id)) topics, COUNT(p.id) posts, sum(p.like_count) likes, sum(p.reads) reads
FROM categories c
INNER JOIN topics t ON (t.category_id = c.id)
INNER JOIN posts p ON (p.topic_id = t.id AND p.post_type = 1)
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
You can share these reports with groups via the Data Explorer UI:
And it will appear in the group page like this:
Thanks for this, it’s exactly what I needed (and also has me digging more into what I can query). I added to the SELECT statement to get the count of replies in a topic
sum(t.reply_count) replies,