Dashboard Report - Consolidated Pageviews with Browser Detection

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Consolidated Pageviews with Browser Detection.

This Data Explorer report provides a view of daily site pageviews with browser detection over a specified date range. The pageviews are categorized into four types:

  • Logged In Browser
  • Anonymous Browser
  • Known Crawler
  • Other pageviews
-- [params]
-- date :start_date
-- date :end_date

SELECT
  ar.date::date AS "date",
  SUM(CASE WHEN ar.req_type = 15 THEN count ELSE 0 END) AS "Logged In Browser",
  SUM(CASE WHEN ar.req_type = 13 THEN count ELSE 0 END) AS "Anonymous Browser",
  SUM(CASE WHEN ar.req_type = 6 THEN count ELSE 0 END) AS "Known Crawler",
  SUM(
    CASE WHEN ar.req_type = 8 THEN count
        WHEN ar.req_type = 7 THEN count
        WHEN ar.req_type = 13 THEN -count
        WHEN ar.req_type = 15 THEN -count
        ELSE 0
    END
  ) AS "Other pageviews"
FROM
  application_requests ar
WHERE
  ar.date::date >= :start_date
  AND ar.date <= :end_date
GROUP BY
  ar.date
ORDER BY
  ar.date ASC

SQL Query Explanation

  • Parameters:
    • :start_date - The start date for the report.
    • :end_date - The end date for the report.
    • Both date parameters accept the date format of YYYY-MM-DD .
  • SELECT Clause:
    • ar.date::date AS "date": Converts the date field to a date type and labels it as “date”.
    • SUM(CASE WHEN ar.req_type = 15 THEN count ELSE 0 END) AS "Logged In Browser": Sums up the counts where req_type is 15, labeling the result as “Logged In Browser”.
    • SUM(CASE WHEN ar.req_type = 13 THEN count ELSE 0 END) AS "Anonymous Browser": Sums up the counts where req_type is 13, labeling the result as “Anonymous Browser”.
    • SUM(CASE WHEN ar.req_type = 6 THEN count ELSE 0 END) AS "Known Crawler": Sums up the counts where req_type is 6, labeling the result as “Known Crawler”.
    • SUM(CASE WHEN ar.req_type = 8 THEN count WHEN ar.req_type = 7 THEN count WHEN ar.req_type = 13 THEN -count WHEN ar.req_type = 15 THEN -count ELSE 0 END) AS "Other pageviews": Sums up the counts for other types of requests, adjusting the counts for req_type 13 and 15 by subtracting them, and labels the result as “Other pageviews”.
  • FROM Clause:
    • FROM application_requests ar: Specifies the application_requests table as the source of the data, aliasing it as ar.
  • WHERE Clause:
    • ar.date::date >= :start_date AND ar.date <= :end_date: Filters the records to include only those where the date falls within the specified :start_date and :end_date range.
  • GROUP BY Clause:
    • GROUP BY ar.date: Groups the results by the date field to aggregate the counts for each day.
  • ORDER BY Clause:
    • ORDER BY ar.date ASC: Orders the results in ascending order by the date field.

Results

The results of this query will provide a daily breakdown of pageviews categorized by the type of browser or crawler. Each row in the result set will contain:

  • date: The specific date.
  • Logged In Browser: The total count of pageviews from logged-in users.
  • Anonymous Browser: The total count of pageviews from anonymous users.
  • Known Crawler: The total count of pageviews from known crawlers.
  • Other pageviews: The total count of other types of pageviews.

Example Results

date Logged In Browser Anonymous Browser Known Crawler Other pageviews
2024-05-02 1238 1085 2727 3983
2024-05-03 1039 1068 5718 3958
2024-05-04 572 628 3847 3761
2024-05-05 447 551 2093 3773
2 Likes
2

Strange. I get zero for both browsers. Only bots are counted.

3

Does the dashboard report of ‘Consolidated Pageviews with Browser Detection’ also only show bots?

4

You mean that experimental one? It works and shows everything nicely.

5

This report was added to Discourse on April 25th, 2024, and will not show results for Logged In Browser or Anonymous Browser views prior to this date.

If you’re missing results for Logged In Browser or Anonymous Browser views after April 25th, 2024, you will need to update your Discourse instance to the latest version.

6

I wondered why SQL snippet didn’t show views by users, only bots. And then I tried june. Plus yes, my instance gets upgrade like three times a week :stuck_out_tongue_winking_eye:

(Damn these virtual keyboards… too easy touch wrong places :smirk: :man_facepalming:)

But I rebuilded without data expolerer and right after that with it, and numbers came.

So, this will stay as another case from twilight zone, I reckon. Can somekind caching issue be possible with SQL :flushed: