This is an SQL version of the Dashboard Report for Anonymous. This report shows the number of pageviews a site has received each day from anonymous users (users who are not logged into an account) over a specified date range. --[params] -- date :start_date = 2023-12-01 -- date :end_date = 2024-01-01 SELECT date, SUM(count) AS pageviews FROM application_requests WHERE req_type = 8 AND date BETWEEN :start_date AND :end_date GROUP BY date ORDER BY date SQL Query Explanation Parameter Definition: The query starts by defining two parameters, :start_date and :end_date, which are used to filter the data to the desired time frame. Both date parameters accept the date format of YYYY-MM-DD . Data Selection: It selects two columns, date and the sum of count, which is aliased as pageviews. The count represents the number of pageviews for each record. Data Source: The data is sourced from the application_requests table, which logs various types of requests made to the application. Filtering: The WHERE clause filters records to include only those that are of type page_view_anon (req_type = 8) and fall within the specified date range. Aggregation: The GROUP BY clause groups the results by the date column, which allows the SUM function to calculate the total pageviews for each date. Sorting: Finally, the results are ordered by the date in ascending order, providing a chronological view of the anonymous pageviews. Example Results date pageviews 2023-12-01 12345 2023-12-02 11346 2023-12-03 18344 2023-12-04 15344 2023-12-05 12890 … … Notes on req_type The req_type column in the application_requests table categorizes the type of request. In this query, we are interested in req_type = 8 , which corresponds to anonymous pageviews. Other req_type values represent different kinds of requests, such as crawler page views, logged in page views, and various HTTP response statuses. All req_type Values: 0. http_total 1. http_2xx 2. http_background 3. http_3xx 4. http_4xx 5. http_5xx 6. page_view_crawler 7. page_view_logged_in 8. page_view_anon 9. page_view_logged_in_mobile 10. page_view_anon_mobile 11. api 12. user_api

I was originally considering that as well, but the admin dashboard report only includes page_view_anon, and not page_view_anon_mobile. With the way this query is now, it is a true mirror of the dashboard report, and you can verify this by running the query and comparing against the dashboard report results. Which does bring up a different, but related question - should the dashboard report include anonymous mobile pageviews? Intuitively, I would think yes, but it would be interesting to hear other thoughts about this.

[תמונה] Sara Devlaeminck: should the dashboard report include anonymous mobile pageviews? I can’t think of a reason not to include them.

[תמונה] Sara Devlaeminck: With the way this query is now, it is a true mirror of the dashboard report, and you can verify this by running the query and comparing against the dashboard report results. Ah, sorry. I should’ve been more specific in my comment. I did clock that this one was an exact replica and I meant that the dashboard report itself should include it. :pray:

Sorry, it’s morning here and I need more coffee, so I hit against language wall hard. Are you thinking now should tot include mobiles too in the meaning mobiles are excluded now? Or should it show two metrics, actual total views AND mobile views?

At the moment, the dashboard report currently only seems to include the one req_type (8) which means it’s only counting desktop anon views. We’re wondering why it’s not counting the mobile data as well (either just as a total, or as two numbers so you can see it in more detail).

It can’t be true or it counts bots too. By that I have around 1000 a day and that sounds right. But I know I have very small amount desktops. And situation where I would have 1000 deskstops and 9000 mobiles (yes, the ratio is that) it can’t be true.

Hmm. I’m not sure. I think the code is here: lib/middleware/request_tracker.rb main # Middleware::RequestTracker.register_ip_skipper do |ip| # ip == "1.2.3.4" # end # ``` def self.register_ip_skipper(&blk) raise "IP skipper is already registered!" if @@ip_skipper @@ip_skipper = blk end def self.ip_skipper @@ip_skipper end def self.reset_rate_limiters_stack @@stack = begin # Update the documentation for the `add_request_rate_limiter` plugin API if this list changes. default_rate_limiters = [ There’s a possibility that page_view_anon_mobile is a subset of page_view_anon. Hopefully someone who knows how to read this can chip in and advise. :slight_smile: :crossed_fingers:

[תמונה] JammyDodger: elsif !SiteSetting.login_required ApplicationRequest.increment!(:page_view_anon) ApplicationRequest.increment!(:page_view_anon_mobile) if data[:is_mobile] end Huh. Yeah that logic would increment page_view_anon whether or not the page view is classified as from a mobile device. I think I need to update some queries . . .

דוח דאשבורד - אנונימי

קהילה נתונים ודיווח

JammyDodger 18 בדצמבר,‏ 2023,‏ 11:29pm 2

Should this one be req_type IN (8,10) to include the mobile numbers too?

נושא		תגובות	צפיות
Dashboard Report - Consolidated Pageviews Data & reporting sql-query , dashboard-reports , dashboard-sql	3	772	24 במרץ,‏ 2025
Dashboard Report - Site Traffic Data & reporting sql-query , dashboard-reports , dashboard-sql	0	276	24 במרץ,‏ 2025
Dashboard Report - Consolidated Pageviews with Browser Detection Data & reporting sql-query , dashboard-reports , dashboard-sql	5	478	21 ביוני,‏ 2024
Dashboard Report - Logged In Data & reporting sql-query , dashboard-reports , dashboard-sql	0	423	10 בינואר,‏ 2024
Dashboard Report - Web Crawler Pageviews Data & reporting sql-query , dashboard-reports , dashboard-sql	0	404	8 בפברואר,‏ 2024

דוח דאשבורד - אנונימי

נושאים קשורים