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 ofYYYY-MM-DD
.
- The query accepts two parameters,
- Selection and Filtering:
- The query selects records from the
application_requests
table wherereq_type
is6
. Thereq_type
column categorizes the type of request, and a value of6
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.
- The query selects records from the
- 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, wherear.count
represents the number of crawler requests. This sum is labeled as"Crawler Views"
in the output.
- The query groups the results by the date of the request (
- 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 |
… | … |