This is an SQL version of the Dashboard Report for Consolidated Pageviews. This SQL report provides a daily count of pageviews from logged in users, anonymous users, and crawlers on a Discourse site. -- [params] -- date :start_date -- date :end_date SELECT ar.date::date AS "date", SUM(ar.count) AS "Total Views", SUM(CASE WHEN ar.req_type=6 THEN ar.count ELSE 0 END) AS "Crawler Views", SUM(CASE WHEN ar.req_type=7 THEN ar.count ELSE 0 END) AS "Logged in User Views", SUM(CASE WHEN ar.req_type=8 THEN ar.count ELSE 0 END) AS "Anonymous User Views" FROM application_requests ar WHERE req_type IN (6,7,8) AND ar.date::date BETWEEN :start_date AND :end_date GROUP BY date ORDER BY date SQL Query Explanation The query operates on the application_requests table, which logs various types of requests made to the forum. It focuses on three specific types of requests identified by req_type values: 6: Crawler Views 7: Logged in User Views 8: Anonymous User Views Here’s a step-by-step explanation of what the query does: Parameters: The query accepts two parameters, :start_date and :end_date, allowing the user to specify the date range for the report. Both date parameters accept the date format of YYYY-MM-DD . Selection: The query selects the date of the request (ar.date) and casts it to a date type. It also calculates the total views and the breakdown of views by type (crawler, logged-in user, and anonymous user) for each date within the specified range. Summation: It sums up the count column for each type of view. The CASE statements are used to conditionally include counts in the sum based on the req_type. Filtering: The WHERE clause filters the records to include only those with req_type values of 6, 7, or 8 and with dates falling between the specified :start_date and :end_date. Grouping: The query groups the results by date, ensuring that the counts for each type of view are aggregated per day. Ordering: The results are ordered by date to provide a chronological view of the traffic. Example Results date Total Views Crawler Views Logged in User Views Anonymous User Views 2023-11-18 5608 3052 565 1928 2023-11-19 9207 6564 470 2273 2023-11-20 6271 2631 1016 2924

@SaraDev when looking at our admin dashboard, it’s showing req_type = 13 (page_view_anon_browser) and 15 (page_view_logged_in_browser). Is this a bug? Is there any explanation of the sub request types? For example, I don’t see how any combination of the logged in types get to the 705 number. [image] [The image shows a table listing various counts across different request types with an ID, date, request type, and count for each record, highlighted on two specific entries. (Captioned by AI)]

[image] tyler.lamparter: Is there any explanation of the sub request types The req_type field in the application_requests corresponds to the follow types of application requests in Discourse: http_total http_2xx http_background http_3xx http_4xx http_5xx page_view_crawler page_view_logged_in page_view_anon page_view_logged_in_mobile page_view_anon_mobile api user_api page_view_anon_browser page_view_anon_browser_mobile page_view_logged_in_browser page_view_logged_in_browser_mobile You can also view this information directly in the Data Explorer when looking at the Database Tables and Schema. [image] tyler.lamparter: Is this a bug? In September 2024, we improved the way in which pageviews are tracked and counted. Here’s an explanation of what changed. The metrics you are seeing on your dashboard are likely using the new “Site Traffic” method for tracking pageviews, and you can find the corresponding SQL for this method here: Dashboard Report - Site Traffic .

Thanks Sara, the query in the Dashboard Report - Site Traffic topic answers my question around using 13 and 15 and also why 13 + 14 != 8.

Rapport du tableau de bord - Pages vues consolidées

Communauté Données et rapports

tyler.lamparter (Tyler Lamparter) Mars 24, 2025, 11:37 4

Merci Sara, la requête dans le sujet Dashboard Report - Site Traffic répond à ma question concernant l’utilisation des 13 et 15 et aussi pourquoi 13 + 14 != 8.

Sujet		Réponses	Vues
Dashboard Report - Consolidated Pageviews with Browser Detection Data & reporting sql-query , dashboard-reports , dashboard-sql	5	470	Juin 21, 2024
Dashboard Report - Web Crawler Pageviews Data & reporting sql-query , dashboard-reports , dashboard-sql	0	399	Février 8, 2024
Dashboard Report - Site Traffic Data & reporting sql-query , dashboard-reports , dashboard-sql	0	266	Mars 24, 2025
Dashboard Report - Anonymous Data & reporting sql-query , dashboard-reports , dashboard-sql	9	661	Décembre 21, 2023
Dashboard Report - Logged In Data & reporting sql-query , dashboard-reports , dashboard-sql	0	421	Janvier 10, 2024

Rapport du tableau de bord - Pages vues consolidées

Sujets connexes