I was hoping there might be a way to automate emailing the downloadable reports available on the Discourse admin panel to a defined email list on a recurring basis (like weekly or monthly CSV reports of Trending Topics, for instance).
In the meantime, I built my own Python script to get the data from the Discourse API and create a CSV, but if there’s already a pre-built solution I could use instead, I’d prefer it.
Thanks for pointing this out, Sam - I had no idea it existed even after the amount of time I spent perusing various Discourse threads in my search for it.
Follow up question though: after looking through the existing SQL Data Explorer queries in the GitHub repo and making a few attempts at writing my own, is there somewhere where I can pull the query that generates the “Trending Search Terms” report available in the Discourse admin dashboard for the past month (term, search count, CTR)?
I’ve used the admin/reports/trending_search.json API to get the info manually, but I’d like to use a Discord cron job here if possible.
SELECT term, count(*) searches,
sum(case when search_result_id is not null then 1 else 0 end) clicks,
round(sum(case when search_result_id is not null then 1 else 0 end) * 100.0 / count(*), 1) as ctr
where created_at > current_timestamp - interval '30' day
group by term
order by count(*) desc
So my final, final question is: is there a way in the Automation, to have this query run and turn the results into a CSV attached to the email for the recipients instead of the results posted in the body of the email?