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.
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.
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.
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?
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.
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 . . .