Dashboard Report - Web Crawler Pageviews

[SaraDev (Sara Devlaeminck)]

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
…	…