← All IntegrationsIntegration
Analytics for BigQuery
BetterMeter's dashboard covers common analytics questions. For everything else — custom SQL analysis, ML model training, cross-dataset joins, and multi-year archival — export raw events to Google BigQuery and query them however you need.
BigQuery schema
Each BetterMeter event maps to a row in BigQuery with the following schema. The table is partitioned by date and clustered on domain and event_source for cost-efficient queries.
BigQuery table schema — bettermeter.events
CREATE TABLE bettermeter.events (
event_id STRING NOT NULL,
timestamp TIMESTAMP NOT NULL,
domain STRING NOT NULL,
event_name STRING, -- pageview, cli.command, mcp.tool, api.request
event_source STRING, -- web, cli, mcp, api
url STRING,
referrer STRING,
referrer_source STRING, -- google, chatgpt, claude, perplexity, direct
is_bot BOOLEAN,
bot_name STRING,
browser STRING,
os STRING,
device STRING,
country STRING,
region STRING,
visitor_hash STRING -- daily-rotating, privacy-safe hash
)
PARTITION BY DATE(timestamp)
CLUSTER BY domain, event_source;Example queries
SQL — AI traffic growth over 90 days
SELECT
DATE_TRUNC(timestamp, WEEK) AS week,
referrer_source,
COUNT(*) AS visits
FROM bettermeter.events
WHERE referrer_source IN ('chatgpt', 'claude', 'perplexity')
AND timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY week, referrer_source
ORDER BY week DESC;Export options
Scheduled Export
Daily batch via API
Real-Time Stream
Webhook → BigQuery sink
Historical Backfill
Full export via CLI
Cross-Dataset Joins
Revenue + analytics
ML Training
Feed into Vertex AI
Long-Term Archival
Years of raw events
BigQuery's serverless architecture means you pay only for queries you run. Store years of BetterMeter events for pennies per GB, then run complex analytics — AI traffic trends, bot behavior patterns, geographic shifts — without provisioning infrastructure.