Dashboard Report - Consolidated Pageviews

Data & reporting
, ,
1

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
5 Likes
2

@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
image440Ă—192 14.7 KB

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)
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)2136Ă—1190 159 KB

1 Like
3

The req_type field in the application_requests corresponds to the follow types of application requests in Discourse:

  1. http_total
  2. http_2xx
  3. http_background
  4. http_3xx
  5. http_4xx
  6. http_5xx
  7. page_view_crawler
  8. page_view_logged_in
  9. page_view_anon
  10. page_view_logged_in_mobile
  11. page_view_anon_mobile
  12. api
  13. user_api
  14. page_view_anon_browser
  15. page_view_anon_browser_mobile
  16. page_view_logged_in_browser
  17. 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.

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 .

4

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.

1 Like