The discussion revolves around automatically emailing admin report CSVs on a recurring cadence. paigen11 initially sought a pre-built solution to automate emailing reports, such as weekly or monthly CSV reports of Trending Topics, but ended up building a Python script to achieve this.
sam suggested using Discourse Automation, which paigen11 found helpful. However, paigen11 had difficulty finding the query for the “Trending Search Terms” report and eventually figured it out.
paigen11 then asked if it’s possible to run the query in Data Explorer and attach the results as a CSV to the automated email. sam replied that this feature is not currently available but suggested posting a feature request, which paigen11 did.
A side discussion between Thas and sam occurred, where Thas asked about the “PM” abbreviation in sam’s screenshot, which Moin clarified as “Personal Message.” Thas then asked if the automated email could be sent to their Outlook instead, to which sam replied that it should work as long as the email address is the same as their Discourse account.
Hello,
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 very much,
Paige
Unless you want to write a plugin, that’s what to do.
Discourse automation has this option you may find handy, can you try it out and let us know what you think?
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.
I figured out the query to run in Data Explorer, it is:
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
from search_logs
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?
Not at the moment but it is a very good feature request, and should not be too hard to change
Can you post a dedicated topic for this ?
Happy to. New feature request here - thanks very much!
What does PM stand for?
PM is user for personal message
The second line also says “to your messages”
Yes but it hasn’t come to my messages therefore, I thought I may be missing something here.
Could I get this sent to my outlook instead?
As long as your outlook uses the same email address as your Discourse account, it should land there.