Is there a way to create a report on how many users posted new queries each month?

Community Building Data & reporting

AI-generated summary

The discussion centers on generating a monthly report to track how many users post new questions (topics) on the site. Initially, jericson misunderstood the request and provided a SQL query for search queries via the Data Explorer plugin, assuming the user meant site searches. After clarification from jleblanc that the goal was to count users posting new topics (not search queries), jericson corrected the approach. He suggested using the admin reports at /admin/reports/topics for a visual chart of monthly topics, or alternatively, running a SQL query via Data Explorer to count distinct users and topics per month:

select to_char(created_at, 'YYYY-MM') as month,
       count(distinct user_id) users,
       count(distinct id) topics
from topics
group by to_char(created_at, 'YYYY-MM')
order by to_char(created_at, 'YYYY-MM')

This requires the Data Explorer plugin, which may need sysadmin installation. jleblanc thanked the community and planned to share the solution with their team. The conversation highlights the importance of clarifying whether “queries” refers to search logs or topic posts, and confirms that built-in admin reports can partially fulfill the need without SQL.

1

I am trying to figure out how many users created a new query each month. Any help appreciated

3

Assuming you mean search queries and not SQL queries in the Data Explorer plugin, you’ll need to make sure log search queries is enabled. (It’s the default, so it should be.) You can see recent queries in /admin/logs/search_logs, but that doesn’t answer your question, I think.

You can use the aforementioned Data Explorer plugin to get a number of searching users and searches per month:

select to_char(created_at, 'YYYY-MM') as month,
       count(user_id) users,
       count(term) searches
from search_logs
group by to_char(created_at, 'YYYY-MM')
order by to_char(created_at, 'YYYY-MM')

That might do the trick depending on your definition of “new query”.

4

Thank You will ask someone to try this appreciate your help

5

Just to clarify, did you mean Search or were you referring to posting a question on the site?

6

I am new to the software, I was asked to figure out how many users queries are posted each month. I am a moderator. Is there any way to determine this from the admin account management screen? Appreciate all the help.

8

Correct trying to identify how many users post a new question each month.

10

Oh. Yeah. That’s not what my (SQL) query does. Guess I misunderstood. You can see how many topics are posted each month by going to /admin/reports/topics and selecting the chart mode. You can also see engaged users on /admin/reports/daily_engaged_users, but that counts replies and likes. If I understand correctly, you are looking for something like this:

select to_char(created_at, 'YYYY-MM') as month,
       count(distinct user_id) users,
       count(distinct id) topics
from topics
group by to_char(created_at, 'YYYY-MM')
order by to_char(created_at, 'YYYY-MM')

That does require the Data Explorer, which might require a sysadmin installing it.

11

Thank you I will share with the team