Admin dashboard report reference guide

Documentation Site Management
, , ,
27

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

28

Brilliant :slight_smile:

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 :steam_locomotive: :slight_smile:)

7 Likes
29

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

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

5 Likes
30

You did a fantastic job. :+1:

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

2 Likes
32

3 posts were split to a new topic: Top X posters in a given timeframe

33

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.

35

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!

36

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.

The image displays two screenshots of a web browser with a search result page. The screenshots are side by side, showing different parts of the page. (Captioned by AI)
The image displays two screenshots of a web browser with a search result page. The screenshots are side by side, showing different parts of the page. (Captioned by AI)1751×278 35.8 KB

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,

37

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?

4 Likes
38

the query works perfectly! thanks Moin!

1 Like