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
