Googlebot Crawl Report in BigQuery SQL

You have raw log lines in BigQuery — loaded as described in loading nginx logs into BigQuery — and you want a repeatable Googlebot crawl report: status mix, top crawled sections, and a daily trend, all cheap to run. This page is the exact SQL, plus the one habit that keeps it inexpensive: always filter on the partition column so a query scans days, not years.

Diagnosis: A Query That Scans Too Much

The signature of an expensive report is a dry run that reports the whole table. Check before you run:

bq query --use_legacy_sql=false --dry_run \
  'SELECT COUNT(*) FROM seo_logs.crawl WHERE user_agent LIKE "%Googlebot%"'

Expected Output (the symptom): will process 812 GB of data — because there is no partition filter, BigQuery reads every day ever loaded. Add AND DATE(request_time) = '2026-06-19' and the same dry run drops to a few hundred megabytes.

Concept: Filter to Verified Traffic, Then Slice

A user-agent LIKE '%Googlebot%' is a first-pass filter that also catches spoofed bots. It is fine for aggregate reporting, but before acting on the numbers, confirm the IPs are really Google's with the forward-confirmed reverse DNS method in verifying Googlebot with reverse DNS. In BigQuery you can encode that verification as a join against a table of known crawler ranges, but the user-agent filter is the pragmatic default for a trend report.

Step-by-Step: Build the Report

Step 1: Status distribution for a date range. A window function attaches each status its share in one pass.

SELECT status, COUNT(*) AS hits,
       ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM seo_logs.crawl
WHERE DATE(request_time) BETWEEN '2026-06-01' AND '2026-06-30'
  AND user_agent LIKE '%Googlebot%'
GROUP BY status ORDER BY hits DESC;

Expected Output: status rows with percentages; a non-200 share above ~10–15% flags budget lost to redirects and errors.

Step 2: Crawl by section for one day. Extract the first path segment and rank.

SELECT REGEXP_EXTRACT(path, r'^/([^/?]+)') AS section, COUNT(*) AS hits
FROM seo_logs.crawl
WHERE DATE(request_time) = '2026-06-19' AND user_agent LIKE '%Googlebot%'
GROUP BY section ORDER BY hits DESC LIMIT 10;

Expected Output: the day's most-crawled sections; a faceted or search path near the top is wasted budget.

Step 3: Daily crawl trend. Group by the partition date to watch volume over time.

SELECT DATE(request_time) AS day,
       COUNTIF(status = 200)      AS ok,
       COUNTIF(status BETWEEN 300 AND 399) AS redirects,
       COUNTIF(status >= 400)     AS errors
FROM seo_logs.crawl
WHERE DATE(request_time) BETWEEN '2026-06-01' AND '2026-06-30'
  AND user_agent LIKE '%Googlebot%'
GROUP BY day ORDER BY day;

Expected Output: one row per day with productive vs wasted hits side by side — a falling redirect/error count after a fix is the confirmation you want.

Edge-Case Handling

  • Null statuses from malformed lines. SAFE_CAST during parsing leaves bad rows NULL; COUNTIF(status = 200) simply ignores them, so the report stays correct. Track the null rate separately as a data-quality signal.
  • Multiple Googlebot variants. Smartphone and image crawlers share the Googlebot token; if you need to split them, match the fuller user-agent string (Googlebot-Image, Googlebot/2.1 (... Mobile ...)).

Verification

Confirm the section totals reconcile with the day's Googlebot total, so no rows are silently lost to a null path:

SELECT
  (SELECT COUNT(*) FROM seo_logs.crawl
   WHERE DATE(request_time)='2026-06-19' AND user_agent LIKE '%Googlebot%') AS total,
  (SELECT SUM(hits) FROM (
     SELECT COUNT(*) hits FROM seo_logs.crawl
     WHERE DATE(request_time)='2026-06-19' AND user_agent LIKE '%Googlebot%'
     GROUP BY REGEXP_EXTRACT(path, r'^/([^/?]+)'))) AS section_sum;

Expected Output: total equals section_sum. A gap means some paths returned NULL from the extract — inspect and tighten the parse.

Why the Partition Filter Is the Whole Game

Every query in this report leads with a date filter, and that is not stylistic — in a warehouse that bills by bytes scanned, the partition filter is the single control that separates a report costing cents from one costing dollars. BigQuery stores each day's rows in a separate partition, so a query that filters on the partition column reads only the days it names; a query that omits the filter reads the entire table, every day ever loaded, and bills for all of it. On a crawl table holding years of history, the difference is three or four orders of magnitude in bytes scanned, which is why the partition filter is the first thing every query in this guide includes and the last thing you should ever forget.

The discipline this demands is worth making automatic. Rather than relying on remembering the filter, enforce it structurally: set require_partition_filter on the table so BigQuery rejects any query that lacks a partition predicate, turning a forgotten filter from an expensive surprise into a query that simply refuses to run. Layer a project-level maximum_bytes_billed ceiling on top, and a query that would somehow scan the whole table fails loudly rather than billing you. These two guardrails mean the crawl report stays cheap by construction, not by vigilance. Understanding that the partition filter is the mechanism behind the cost — not an incidental part of the WHERE clause but the lever that determines what you pay — is what turns BigQuery from a potentially expensive place to keep logs into a genuinely economical one for crawl analysis.

Scheduling the Report and Alerting on It

A crawl report you run by hand is a report you will eventually stop running, so the mature form of this analysis is a scheduled query that produces the numbers automatically and an alert that tells you when they move. BigQuery's scheduled queries run on a cron-like cadence, writing each run's output to a small results table, so yesterday's Googlebot status distribution, crawl-by-section, and daily trend are computed every morning without anyone touching a console. The dashboards and alerts then read the cheap results table rather than re-scanning raw partitions.

The alerting layer is what turns the report from a record into an early-warning system. Because the scheduled query writes a compact daily summary, a monitoring rule can watch that summary for the signals that matter — a sudden drop in verified Googlebot volume, a spike in the crawler's error rate, a section's crawl falling to near zero — and page you when one crosses a threshold. This is the warehouse equivalent of the log-based alerting the alerting on a Googlebot drop with LogQL guide builds, expressed as a query over the summary table rather than a stream. The value is the same: a crawl problem surfaces the day it starts rather than at your next manual review, and because the alert reads a pre-aggregated summary rather than raw logs, it is cheap to evaluate as often as you like. Scheduling the report and alerting on its output is what makes warehouse-based crawl monitoring continuous rather than periodic.

Reporting Across Multiple Crawlers

The report as built focuses on Googlebot, but the same partitioned table holds every crawler's traffic, and extending the report to compare crawlers reveals differences that single-crawler analysis misses. Bingbot, Googlebot's smartphone and desktop variants, and the AI crawlers each crawl your site with their own priorities and patterns, and a report that pivots crawl distribution by crawler shows, for instance, that one engine concentrates on your product pages while another spreads across the archive, or that a section well-crawled by Google is ignored by Bing.

The extension is a grouping change rather than a new query: where the Googlebot report filters to one user-agent, a multi-crawler report groups by a normalized crawler identity, producing a column per crawler across the same status, section, and trend breakdowns. This comparative view is exactly what a warehouse is good at — the data for every crawler is already in the partitioned table, so the multi-crawler report costs no more to run than the single-crawler one, just an added GROUP BY. The insight it produces is strategic: understanding how different engines crawl your site differently informs where to focus structural improvements, since a fix that helps Googlebot may or may not help Bing, and a section neglected by every crawler is a different problem than one crawled by some but not others. Building the report to compare crawlers rather than examine one in isolation is what makes the warehouse's breadth pay off, turning a Googlebot report into a full picture of how every engine sees your site.

From Report to Standing Monitor

A crawl report you run when you think of it catches problems late; the same report scheduled and alerted on catches them the day they start, and making that transition from manual report to standing monitor is what turns BigQuery crawl analysis from occasional insight into continuous protection. The report's queries — status distribution, crawl by section, daily trend — are exactly the signals a monitor watches, so scheduling them to run automatically and write a small summary table, then alerting when the summary shows a problem, converts the report into an early-warning system with no extra analytical work.

The transition is a matter of automation, not new analysis. The scheduled query runs the report on a cadence and stores each run's output; an alert rule watches the stored summary for the signals that matter — a drop in Googlebot volume, a spike in the crawler's error rate, a section's crawl collapsing — and notifies the team when one crosses a threshold. Because the alert reads the pre-aggregated summary rather than re-scanning raw partitions, it is cheap to evaluate frequently, so the monitor can be responsive without being expensive. This is the same shift from periodic to continuous that mature crawl monitoring makes everywhere: the manual report teaches you what to watch, and the automation makes sure you never stop watching. Turning the Googlebot crawl report into a scheduled, alerted standing monitor is what makes warehouse-scale crawl analysis protective rather than merely informative, catching the crawl problems it would otherwise only reveal at the next manual review.

From Insight to Continuous Protection

The Googlebot crawl report delivers its full value when it stops being something you run and becomes something that watches for you. Scheduled and alerted on, its queries turn from occasional insight into continuous protection, catching a crawl drop or error spike the day it starts rather than at the next manual review. That shift from periodic to continuous is what makes warehouse crawl analysis genuinely protective.

Common Mistakes

  • Omitting the partition filter. The report scans the whole table and bills for it. Always include a literal DATE(request_time) range.
  • Trusting the user-agent as verification. It counts spoofed bots. Use it for trends, but verify IPs before making crawl-budget decisions.

Frequently Asked Questions

How do I schedule this report to run daily?
Save the query as a BigQuery scheduled query targeting a report table, or run it from cron with bq query. Parameterize the date with @run_date so each run reports the prior day and scans only that partition.

Can I export the report to a dashboard?
Yes. Point Looker Studio (or any BI tool) at the crawl table or a small daily summary table you materialize from these queries. Reporting off a pre-aggregated summary keeps dashboard refreshes from re-scanning raw partitions.

Part of the BigQuery SEO Log Analysis at Scale guide.