Is there a way to get a complete list of searches for a given date range, instead of just the top 100?
I think this may be a candidate for a data explorer query. I’d have to have a little rummage around to build the exact query, but I think the data is in the
Let me know if you need any extra help.
I don’t know SQL, but here is what ChatGPT told me when I asked how to query search logs for the past 3 months. Would this work (I don’t have admin access to try it out, but I would like to have a solid query to pass along to my admin).
To get all search log data for the past 3 months using Discourse Data Explorer, you can create a new query with the following code:
-- [params] -- date :datetime SELECT action_logs.id AS action_log_id, action_logs.created_at AS action_log_date, action_logs.action AS action_type, users.id AS user_id, users.username AS user_username, users.email AS user_email, user_actions.details AS search_query FROM action_logs JOIN user_actions ON user_actions.id = action_logs.action_id JOIN users ON user_actions.user_id = users.id WHERE action_logs.action = 'searched' AND action_logs.created_at >= :date - INTERVAL '3 months' ORDER BY action_logs.created_at DESC;
In this query, we use the
-- [params] syntax to define a parameter named
date of type
datetime. This parameter allows you to specify the date for which you want to retrieve the search log data. The query then uses this parameter to filter the search logs for the past 3 months from the specified date.
To use this query in Discourse Data Explorer, follow these steps:
- Go to your Discourse site’s admin dashboard.
- Click on “Data Explorer” in the left-hand menu.
- Click on “New Query” to create a new query.
- Give your query a name and description if desired.
- Copy and paste the above SQL code into the “Query” text area.
- Click on the “Save” button to save the query.
Once saved, you can then execute the query by clicking on its name in Data Explorer. A popup will appear asking you to enter the parameter value for
date. Provide a date in the format
YYYY-MM-DD (e.g., 2023-07-28) and click “Run Query.”
The query will retrieve all search log data for the past 3 months from the specified date, and the results will be displayed in a table format.
I don’t think that’s right. I don’t think we have an
Let me have a look and see what I can whip together.
I think something like this should be more like it:
-- [params] -- date :start_date -- date :end_date WITH results AS ( SELECT LOWER(term) AS term, COUNT(*) AS searches, SUM(CASE WHEN search_result_id IS NOT NULL THEN 1 ELSE 0 END) AS click_through FROM search_logs WHERE created_at BETWEEN :start_date AND :end_date GROUP BY term ) SELECT term AS "Search Term", searches AS "Searches", click_through AS "Clicks-Through", CEIL ((click_through/searches::float) * 100) || '%' AS "CTR" FROM results ORDER BY searches DESC
Hopefully that helps, but let me know if you want any tweaks.
This is awesome, thank you! I’ll pass along to my admin
We ran this query and it worked perfectly, thanks again!