Search Logs - How to get a complete list for a given date range?

data & reporting
1

Is there a way to get a complete list of searches for a given date range, instead of just the top 100?

2

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 search_logs table.

Let me know if you need any extra help. :slight_smile:

3 Likes
5

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:

  1. Go to your Discourse site’s admin dashboard.
  2. Click on “Data Explorer” in the left-hand menu.
  3. Click on “New Query” to create a new query.
  4. Give your query a name and description if desired.
  5. Copy and paste the above SQL code into the “Query” text area.
  6. 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.

6

I don’t think that’s right. I don’t think we have an action_logs table. :thinking:

Let me have a look and see what I can whip together. :+1: :slight_smile:

1 Like
7

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. :slight_smile:

5 Likes
8

This is awesome, thank you! I’ll pass along to my admin :grinning:

1 Like
9

We ran this query and it worked perfectly, thanks again!

2 Likes