Dashboard Report - Site Traffic

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Site Traffic.

This report provides an overview of site traffic metrics grouped by date. It breaks down page views into several categories, including logged-in browser views, anonymous browser views, crawler views, and other calculated traffic types.

See: Understanding pageview tracking on hosted plans, for additional details about this report.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-02-01

WITH first_browser_pageview_date AS (
  SELECT 
    MIN(date) AS first_date
  FROM 
    application_requests
  WHERE 
    req_type IN (13, 15) -- 13: page_view_anon_browser, 15: page_view_logged_in_browser
)

SELECT 
  date,
  SUM(CASE WHEN req_type = 15 THEN count ELSE 0 END) AS page_view_logged_in_browser, -- 15: page_view_logged_in_browser
  SUM(CASE WHEN req_type = 13 THEN count ELSE 0 END) AS page_view_anon_browser, -- 13: page_view_anon_browser
  SUM(CASE WHEN req_type = 6 THEN count ELSE 0 END) AS page_view_crawler, -- 6: page_view_crawler
  SUM(
    CASE 
      WHEN req_type = 8 THEN count -- 8: page_view_anon
      WHEN req_type = 7 THEN count -- 7: page_view_logged_in
      WHEN req_type = 13 THEN -count -- 13: page_view_anon_browser
      WHEN req_type = 15 THEN -count -- 15: page_view_logged_in_browser
      ELSE 0
    END
  ) AS page_view_other
FROM 
  application_requests
WHERE 
  date >= :start_date 
  AND date <= :end_date
  AND date >= (SELECT first_date FROM first_browser_pageview_date)
GROUP BY 
  date
ORDER BY 
  date ASC

SQL Query Explanation

  • Parameters
    • :start_date: The start date for the report.
    • :end_date: The end date for the report.
  • CTE: first_browser_pageview_date
    • This Common Table Expression (CTE) identifies the earliest date where a browser page view (either anonymous or logged-in) was recorded.
    • Filters req_type for values 13 (anonymous browser views) and 15 (logged-in browser views).
  • Main Query
    • Aggregates data from the application_requests table by date.
    • Filters rows to include only those within the specified date range (:start_date to :end_date) and after the first browser page view date (first_browser_pageview_date).
    • Calculates the following metrics:
      • page_view_logged_in_browser: Total count of logged-in browser views (req_type = 15).
      • page_view_anon_browser: Total count of anonymous browser views (req_type = 13).
      • page_view_crawler: Total count of crawler views (req_type = 6).
      • page_view_other: Total count of other page views, calculated as:
        • Adding counts for req_type = 8 (anonymous views) and req_type = 7 (logged-in views).
        • Subtracting counts for req_type = 13 (anonymous browser views) and req_type = 15 (logged-in browser views).
  • Results
    • Logged-In Browser Views: Total page views from logged-in users using a browser.
    • Anonymous Browser Views: Total page views from anonymous users using a browser.
    • Crawler Views: Total page views generated by crawlers (e.g., search engine bots).
    • Other Page Views: Includes other types of page views not categorized as browser or crawler views.

Example Results

Date Logged-In Browser Views Anonymous Browser Views Crawler Views Other Page Views
2025-03-20 150 300 50 20
2025-03-21 200 400 60 30
2025-03-22 180 350 55 25