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,