仪表盘报告 - 匿名

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

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

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

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.

I can’t think of a reason not to include them.

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?

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:

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.

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