The discussion is about understanding pageviews in Discourse. ganncamp noticed a discrepancy between their direct database query results and the admin homepage reporting. They found that their query results for request types 6, 7, 8, and 10 summed up to 57.0k, while the dashboard reported 55.5k. michebs helped explain the difference, stating that the dashboard’s “Consolidated Pageviews” chart includes logged in users, anonymous users, and crawlers (req_type 6, 7, and 8). They also provided the SQL query used for the Consolidated Pageviews Report. ganncamp asked why the Consolidated Pageviews chart does not include mobile views.
This thread tells me which table to look in for pageview data & how to understand it (what each request_type means).
I used that to write some year-over-year reports. I was looking at them today and started wondering about the numbers versus what’s reported on the admin homepage.
So I started digging in a little. Here’s what a direct DB query tells me about yesterday:
I don’t know how to map the last two counts from my query, but even leaving them aside (which I’m happy to do since they’re tiny) I don’t know how to map what my query finds to what the dashboard reporting says.
Request types 7, 8, 9, and 10 sum to 43.8k. Add in crawlers (type 6) and I’m at 57.0k.
Is the dashboard supposed to reflect types 6-10 & the difference between 55.5k and 57.0k should be chalked up to timezone differences?
Types of requests code 11 are those made by API, and requests code 12 are made by the user API.
Activity Metrics consolidates Logged in Users (req_type: 7), Anonymous users (req_type: 8 ), and Crawlers (req_type: 6). They are the same considered in the first chart of the Admin Panel “Consolidated Pageviews”.
Below is an example of the query for the Consolidated Pageviews Report
SQL Consolidated Pageviews
-- [params]
-- date :start_date
-- date :end_date
SELECT
ar.date::date AS "date",
CASE
WHEN ar.req_type=6 THEN 'Crawlers'
WHEN ar.req_type=7 THEN 'Logged in users'
WHEN ar.req_type=8 THEN 'Anonymous users'
END user_type,
SUM(ar.count) AS 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", user_type
ORDER BY "date"
SUM Consolidated Pageviews
-- [params]
-- date :start_date
-- date :end_date
SELECT
ar.date::date AS "date",
SUM(ar.count) AS pageviews
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" DESC
