Admin dashboard report reference guide

[Yotam_Hagay]

@JammyDodger

Could you run that on your site and see if it marries up?

Thanks Jammy, it does add up to the numbers in the stock report :]

[JammyDodger]

Brilliant

Inspired by your questions, Sara has done a burst of mapping the SQL out for the other dashboard reports, including one for Time to First Response with the hours as the decimal to match the stock report more closely - Dashboard Report - Time to First Response

You can also find the others using the dashboard-sql tag (with more en route )

[SaraDev (Sara Devlaeminck)]

I wanted to provide an update here, and let anyone watching this topic know that the guide has now been updated to link to all corresponding SQL topics for each report.

If you have any questions or comments about the individual SQL queries for these reports, please share your thoughts on those topics.

[Arkshine]

You did a fantastic job.

It’s a great source of inspiration to understand how the queries are made, and your thorough explanations made it easy!

[CaptainJ]

Can anyone explain why “Consolidated pageviews” says “58” for “Logged in users” for today so far, and yet according to /admin/users/list/active, only one user has visited the site in the past 24 hours (“Seen 39m”) and I know who it is and they’ve just had 1 page open on a browser in that time (not even reloaded/scrolled).

And then yesterday apparently there were 539 pageviews from logged in users with at most 3 users visiting according to /admin/users/list/active, and I independently know the activity of 2 of them to be very low and the third has a total Read Time of 5 minutes.

[gassim (Al-Gassim Sharaf Addin)]

Hi
Thanks for the new Topic View Status report. It’s very helpful (we can filter by logged in/anonymous, date, and category!)

It doesn’t look like it has been added here. I want to know how far back in time I can go in the date range to get the stats?

Thanks!

[d4v1d]

hi @SaraDev ,

Hope you and the team are having a great time at the offline meetup. Could you please find some time to add the SQL query for the Topic View Status report? I managed to do it myself, but the data are not the same.

SELECT
    t.id AS topic_id,
    t.views,
    t.reply_count,
    t.like_count,
    SUM(CASE WHEN tv.user_id IS NULL THEN 1 ELSE 0 END) AS anonymous_views,
    SUM(CASE WHEN tv.user_id IS NOT NULL THEN 1 ELSE 0 END) AS logged_in_views
FROM 
    topics t
LEFT JOIN 
    topic_views tv ON t.id = tv.topic_id
WHERE 
    t.user_id = 3049483
    AND t.category_id = 25
    AND t.deleted_at IS NULL
GROUP BY
    t.id, t.views, t.reply_count, t.like_count
ORDER BY 
    t.views DESC

Thanks,

[Moin]

I think the query for the topic view stats is something like

-- [params]
-- date              :start_date = 24 aug 2024
-- date              :end_date = 25 sep 2024
-- null category_id  :category_id

SELECT
  tvs.topic_id,
  t.title AS topic_title,
  SUM(tvs.anonymous_views) AS total_anonymous_views,
  SUM(tvs.logged_in_views) AS total_logged_in_views,
  SUM(tvs.anonymous_views + tvs.logged_in_views) AS total_views
FROM topic_view_stats tvs
INNER JOIN topics t ON t.id = tvs.topic_id
WHERE tvs.viewed_at BETWEEN :start_date AND :end_date
  AND (:category_id IS NULL OR t.category_id = :category_id)
GROUP BY tvs.topic_id, t.title
ORDER BY total_views DESC
LIMIT 100

Does that help you?

[d4v1d]

the query works perfectly! thanks Moin!

[EricGT]

Can the report Consolidated API Requests be added here.

Trying to understand the difference between API and User API and if it is related to the list of API keys

---

Also related for those interested in this are the site settings for

API and User API

https://<site URL>/admin/site_settings/category/api

https://<site URL>/admin/site_settings/category/user_api

and API keys list

https://<site URL>/admin/api/keys

[SaraDev (Sara Devlaeminck)]

Hi @EricGT,

The Consolidated API Requests report has been added to this topic, and information about the SQL for this report can be found here: Dashboard Report - Consolidated API Requests

To answer your question:

@EricGT

Trying to understand the difference between API and User API and if it is related to the list of API keys

Regular API requests (req_type = 11): These are API calls made to the system that don’t require user authentication. They include public API endpoints, system integrations, or third party services accessing public data.

Some examples of Regular API requests would include:

• Public topic listing - Retrieving a list of public topics using /latest.json or /categories.json
• Reading public posts - Getting content from public posts with /t/{topic_id}.json
• Search queries on public content - /search.json?q={search_term}
• RSS feeds - Accessing public RSS feeds from categories or topics
• Site information - Getting basic site statistics via /site.json
• Public user profiles - Viewing public user information with /users/{username}.json
• Tag listings - Retrieving public tag information with /tags.json

User API requests (req_type = 12): These are API calls made with user authentication, typically representing actions performed on behalf of specific users and require the user of an API key.

Some examples of User API requests would include:

• Creating content - Posting new topics or replies using POST to /posts.json
• User management - Adding, updating, or suspending users with /admin/users/
• Private messaging - Accessing or sending private messages
• User-specific actions - Liking posts, following users, or bookmarking content
• Administrative functions - Changing site settings, managing categories
• Group management - Adding or removing users from groups
• User preferences - Updating a user’s preferences or profile information
• Badge assignments - Granting badges to users