Dashboard Report - Web Crawler Pageviews

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Web Crawler Pageviews.

This dashboard report shows the total pageviews a site receives from web crawlers per day, over a specific date range. Understanding web crawler behavior can help site admins understand how frequently search engine bots are visiting and indexing their site.

-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08

SELECT
    ar.date::date AS "date",
    SUM(CASE WHEN ar.req_type=6 THEN ar.count ELSE 0 END) AS "Crawler Views"
FROM application_requests ar
WHERE req_type IN (6)
    AND ar.date::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date

SQL Query Explanation

The query operates on the application_requests table, which logs various types of requests made to the Discourse application, including those made by web crawlers. Here’s a step-by-step breakdown of how the query works:

  • Parameters:
    • The query accepts two parameters, :start_date and :end_date, which define the time range for the report. Both date parameters accept the format of YYYY-MM-DD .
  • Selection and Filtering:
    • The query selects records from the application_requests table where req_type is 6. The req_type column categorizes the type of request, and a value of 6 specifically identifies requests made by web crawlers.
    • It further filters these records to include only those that fall within the specified date range, using the :start_date and :end_date parameters.
  • Aggregation:
    • The query groups the results by the date of the request (ar.date::date). This grouping allows for the aggregation of crawler views per day.
    • It then calculates the sum of ar.count for each group, where ar.count represents the number of crawler requests. This sum is labeled as "Crawler Views" in the output.
  • Ordering: Finally, the results are ordered by the date in ascending order. This ordering facilitates easy visualization of crawler activity trends over the specified period.

Example Results

date Crawler Views
2024-01-07 3217
2024-01-08 2342
2024-01-09 3995
2024-01-10 3667
2024-01-11 3095
… …
1 Like