Skip to content
English - United Kingdom
Sign in
Contact us
  • There are no suggestions because the search field is empty.

The Report Generator Pipeline

How BTT's monthly SEO reports are built, auto-created, and sent to clients.

Overview

Ops Hub generates monthly client reports automatically. Each report is a JSONB snapshot stored in the reports table, rendered into HTML on demand, and delivered to the client via a magic-link URL. Reports are either auto-created by the monthly cron or manually triggered from the client detail page.

Four report types

clients.default_report_type records which type the auto-create cron will generate. Clients with type none are skipped with outcome skipped_placeholder_type.

How auto-creation works

The cron at /api/cron/auto-create-monthly-drafts runs on the 1st of each month at 06:00 UTC:

  1. Pulls all active SEO clients (package contains SEO) not assigned to Lisa.
  2. Checks whether a report already exists for the previous calendar month (idempotency).
  3. Calls the generator for each client (concurrency 8).
  4. Soft-aborts at 270 seconds; clients not reached are picked up on the next firing.
  5. Posts a per-AM summary to #bristechtonic-jarvis Slack, per-client errors to #critical-alerts.
  6. Logs each run to report_auto_create_runs.

Uses waitUntil from @vercel/functions so the lambda continues past the 30-second HTTP timeout.

How the generator works

The generator (lib/reports/generator.js) runs each report section in parallel via Promise.allSettled. A failed section becomes {error: ...} in the snapshot instead of blocking the whole report. The renderer surfaces a soft error inline. Cost telemetry (generation time and Claude API spend in GBP) rolls up to the reports row.

Key section keys in the snapshot: client_meta, ga4_overview, ga4_traffic_sources, ga4_top_pages, ga4_audience, gsc_overview, gsc_top_queries, keyword_metrics, aeo_results, ai_traffic, ai_overview_proxy, wider_picture, hubspot_context, activity, executive_summary, three_month_outlook.

AI Overviews proxy section

Google does not expose AI Overview impressions in GSC yet. The proxy approach: pull GSC impressions for queries where the site ranks at average position 2.5 or below (top-of-SERP results that AI Overviews tend to cite from). The report shows total eligible-position impressions with month-on-month delta, qualifying query count, share of total impressions, and the top 12 eligible queries. The section auto-skips when a client has zero eligible queries.

Position threshold is 2.5 (not 2.0) because GSC reports average position across the period -- a query ranking 1/1/1/1/5 averages to 1.8, clearly in scope; 2.0 cuts too many borderline signals.

AI assistants traffic hero

A hero block showing sessions attributed to AI assistant referrers (ChatGPT, Perplexity, Claude, Gemini, Microsoft Copilot, You.com, Poe, DuckAssist, Meta AI, Phind) with month-on-month percentage delta per assistant. The classifier lives in lib/reports/ai-traffic.js -- the AI_PROVIDERS array is the single source of truth. Append a new entry to track a new assistant. The block hides itself when GA4 is not connected or the site has zero AI referral traffic.

Magic-link delivery

When an AM publishes a draft report, a magic_link_token is generated and stored on the reports row. The public URL /r/[token] renders the report without authentication. The Email magic link button on the action bar opens a pre-filled mailto: so the AM sends from their own address.

Cost tracking

Each report generation records generation_cost_gbp on the reports row. The AI Costs dashboard aggregates this alongside AEO runs and AI Citations to show monthly variable AI spend per AM and per client.