The Enterprise CDN Log Analysis Cookbook for Technical SEO

req_timestamp      -> Timestamp or date partition of the request (e.g. 2026-10-15 14:22:01 UTC)
req_host           -> Hostname / market domain (e.g. www.example.com)
req_path           -> URL path without query string (e.g. /products/item-12345)
query_str          -> URL query parameters (e.g. color=black&sort=price)
status_code        -> HTTP response status sent to client (200, 301, 304, 403, 500)
user_agent         -> Raw User-Agent string (e.g. Mozilla/5.0 ... Googlebot/2.1)
cache_status       -> Edge cache outcome (HIT, MISS, REVALIDATED, BYPASS)
turnaround_time_ms -> Edge response time / TTFB / TAT in milliseconds
client_asn         -> Network ASN for bot verification (e.g. 15169 for Google LLC)
trace_id           -> Unique edge request ID for end-to-end debugging

SELECT
  DATE(req_timestamp) AS crawl_date,
  req_host,
  -- 1. Map URL paths to page template buckets
  CASE
    WHEN req_path LIKE 'products/%' OR req_path LIKE 'itm/%' THEN 'PDP (Product Detail)'
    WHEN req_path LIKE 'c/%' OR req_path LIKE 'b/%'          THEN 'PLP (Category Browse)'
    WHEN req_path LIKE 's/%' OR req_path LIKE 'search/%'     THEN 'Internal Search SRP'
    WHEN req_path LIKE 'stores/%'                            THEN 'Merchant Storefront'
    WHEN req_path LIKE 'blog/%' OR req_path LIKE 'guides/%'  THEN 'Editorial / Guides'
    WHEN req_path IS NULL OR req_path = '' OR req_path = '/' THEN 'Homepage'
    ELSE 'Other'
  END AS page_template,

  -- 2. Device User-Agent type
  CASE
    WHEN LOWER(user_agent) LIKE '%mobile%' THEN 'Mobile UA'
    ELSE 'Desktop UA'
  END AS device_ua,

  -- 3. Clean vs. Parameterized URL
  CASE
    WHEN query_str IS NULL OR LENGTH(query_str) = 0 THEN 'Clean / Canonical'
    ELSE 'Parameterized'
  END AS url_type,

  -- 4. HTTP Status Class
  CASE
    WHEN CAST(status_code AS STRING) LIKE '2%' THEN '2xx Success'
    WHEN CAST(status_code AS STRING) LIKE '3%' THEN '3xx Redirect'
    WHEN CAST(status_code AS STRING) LIKE '4%' THEN '4xx Client Error'
    WHEN CAST(status_code AS STRING) LIKE '5%' THEN '5xx Server Error'
    ELSE 'Other'
  END AS status_bucket,
  status_code,

  -- 5. Bot Family Classification
  CASE
    WHEN LOWER(user_agent) LIKE '%google%' THEN 'Google'
    WHEN LOWER(user_agent) LIKE '%bing%'   THEN 'Bing'
    WHEN LOWER(user_agent) LIKE '%chatgpt-user%'
      OR LOWER(user_agent) LIKE '%gptbot%'
      OR LOWER(user_agent) LIKE '%oai-searchbot%' THEN 'OpenAI / ChatGPT'
    WHEN LOWER(user_agent) LIKE '%claude-user%'
      OR LOWER(user_agent) LIKE '%claude-searchbot%'
      OR LOWER(user_agent) LIKE '%claudebot%'     THEN 'Anthropic / Claude'
    WHEN LOWER(user_agent) LIKE '%perplexity%'    THEN 'Perplexity'
    WHEN LOWER(user_agent) LIKE '%applebot%'      THEN 'Apple'
    ELSE 'Other Bot'
  END AS bot_family,

  -- 6. Total Requests vs. Unique URLs Crawled
  COUNT(*) AS total_requests,
  COUNT(DISTINCT CONCAT('https://', req_host, '/', req_path,
    IF(LENGTH(query_str) > 0, CONCAT('?', query_str), ''))) AS unique_urls_crawled

FROM `project.dataset.cdn_bot_logs`
WHERE
  req_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND (
       LOWER(user_agent) LIKE '%googlebot%'
    OR LOWER(user_agent) LIKE '%googleother%'
    OR LOWER(user_agent) LIKE '%adsbot-google%'
    OR LOWER(user_agent) LIKE '%bingbot%'
    OR LOWER(user_agent) LIKE '%applebot%'
    OR LOWER(user_agent) LIKE '%chatgpt-user%'
    OR LOWER(user_agent) LIKE '%gptbot%'
    OR LOWER(user_agent) LIKE '%oai-searchbot%'
    OR LOWER(user_agent) LIKE '%claude-user%'
    OR LOWER(user_agent) LIKE '%claude-searchbot%'
    OR LOWER(user_agent) LIKE '%perplexitybot%'
  )
  AND LOWER(user_agent) NOT LIKE '%internal_monitoring%'
  AND LOWER(user_agent) NOT LIKE '%screaming frog%'
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
ORDER BY crawl_date DESC, total_requests DESC;

SELECT
  p.inventory_status,
  c.cache_status,
  c.status_code,
  COUNT(c.trace_id) AS googlebot_requests,
  COUNT(DISTINCT p.product_id) AS distinct_products_crawled
FROM `project.dataset.product_catalog` p
INNER JOIN `project.dataset.cdn_bot_logs` c
  ON p.product_id = REGEXP_EXTRACT(c.req_path, r'products/(\d+)')
WHERE
  c.req_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND LOWER(c.user_agent) LIKE '%googlebot%'
  AND p.stock_quantity <= 0
  AND c.status_code = 200
GROUP BY 1, 2, 3
ORDER BY googlebot_requests DESC;

WITH cdn_crawls AS (
  SELECT
    DATE(req_timestamp) AS crawl_date,
    CONCAT('https://', req_host, '/', req_path) AS clean_url,
    COUNT(DISTINCT trace_id) AS bot_requests
  FROM `project.dataset.cdn_bot_logs`
  WHERE
    req_timestamp BETWEEN '2026-10-01' AND '2026-10-15'
    AND LOWER(user_agent) LIKE '%googlebot%'
  GROUP BY 1, 2
),
submitted_urls AS (
  SELECT
    DATE(submitted_at) AS sub_date,
    canonical_url,
    page_template
  FROM `project.dataset.url_submissions`
  WHERE DATE(submitted_at) BETWEEN '2026-10-01' AND '2026-10-15'
)
SELECT
  s.page_template,
  COUNT(DISTINCT s.canonical_url) AS total_submitted_urls,
  COUNT(DISTINCT c.clean_url) AS total_crawled_urls,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT c.clean_url), COUNT(DISTINCT s.canonical_url)) * 100, 2) AS crawl_pickup_rate_pct
FROM submitted_urls s
LEFT JOIN cdn_crawls c
  ON s.canonical_url = c.clean_url
  AND c.crawl_date >= s.sub_date
GROUP BY 1
ORDER BY total_submitted_urls DESC;

SELECT
  DATE(req_timestamp) AS req_date,
  CONCAT('https://', req_host, '/', req_path) AS req_url,
  COUNT(trace_id) AS redirect_requests
FROM `project.dataset.cdn_bot_logs`
WHERE
  req_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
  AND LOWER(user_agent) LIKE '%googlebot%'
  AND status_code BETWEEN 300 AND 399
  AND req_path LIKE '%\%%' ESCAPE '\'
GROUP BY 1, 2
ORDER BY redirect_requests DESC
LIMIT 100;

# 1. Real-Time Googlebot Status Code Distribution
sum by (res_status) (
  count_over_time(
    {namespace="cdn_edge_logs", req_host="www.example.com", req_user_agent=~".*Googlebot.*"}
    | logfmt [$__auto]
  )
)

# 2. Isolate Edge Error Codes for GSC Live Inspection Tool 5xx Failures
sum by (edge_error_code) (
  count_over_time(
    {namespace="cdn_edge_logs", req_host="www.example.com", client_asn="15169", agent=~".*InspectionTool.*", res_status=~"5.."}
    | logfmt [$__auto]
  )
)

# Extract all <loc> URLs from compressed .xml.gz sitemaps with their source filename
for file in *.xml.gz; do
  gunzip -c "$file" | grep '<loc' | grep -oE 'https://[^<]+' | while read -r url; do
    echo "$url,$file" >> sitemap_urls.txt
  done
done

# Find duplicate URLs across sitemap files
cut -d',' -f1 sitemap_urls.txt | sort | uniq -d > duplicate_sitemap_urls.txt

# Split into 200MB CSV chunks with headers for warehouse upload
split -b 200M sitemap_urls.txt part_
for file in part_*; do
  echo "url,sitemap_file" > "${file}.csv"
  cat "$file" >> "${file}.csv"
  rm "$file"
done