BigQuery Schema Reference

Power users and operators sometimes need to query analytics data directly — for one-off analyses that don't fit the prepackaged dashboards, for exports into other systems, for retention audits. This doc is the schema reference for those queries.

Project, Dataset, Location

Concern	Default	Env var
GCP project	karmaflow-prod	ANALYTICS_BQ_PROJECT_ID / GCP_PROJECT_ID
Dataset	karmaflow_analytics	ANALYTICS_BQ_DATASET
Location	US (multi-region)	ANALYTICS_BQ_LOCATION

All tenants share the dataset. Every query against these tables must include WHERE tenant_id = ? — the query layer enforces this server-side, but if you're hitting BQ directly from the console you have to do it yourself or you'll see cross-tenant data.

Tables

events

The main event firehose. Every browser event, server-side event, and derived event from Event Definitions lands here.

event_id        STRING        REQUIRED  -- UUID v4, dedupe key
tenant_id       STRING        REQUIRED  -- always filter on this
site_id         STRING        REQUIRED
event_ts        TIMESTAMP     REQUIRED  -- the event's own time (partition key)
received_ts     TIMESTAMP     REQUIRED  -- when the collector received the event
event_name      STRING        REQUIRED  -- e.g. 'page_view', 'click', 'add_to_cart'
event_kind      STRING        REQUIRED  -- 'auto' | 'custom' | 'tag' | 's2s'
visitor_id      STRING                  -- per-origin UUID from localStorage
session_id      STRING                  -- 30-min idle UUID from localStorage
user_id         STRING                  -- set after karma('identify', ...)
page_url        STRING
page_path       STRING
page_title      STRING
referrer        STRING
utm_source      STRING
utm_medium      STRING
utm_campaign    STRING
utm_term        STRING
utm_content     STRING
device_type     STRING                  -- 'mobile' | 'tablet' | 'desktop'
os              STRING                  -- (reserved; usually null at MVP)
browser         STRING                  -- (reserved; usually null at MVP)
country         STRING                  -- 2-letter ISO code from IP geo
region          STRING
city            STRING                  -- only populated if site has city-geo enabled
ip_hash         STRING                  -- SHA-256(ip + tenantSalt)[0..32]
user_agent      STRING
properties      JSON                    -- arbitrary event-specific JSON
consent_state   RECORD                  -- nested struct, see below

consent_state is a nested RECORD:

consent_state.analytics  BOOL
consent_state.marketing  BOOL
consent_state.dnt        BOOL
consent_state.gpc        BOOL

Partitioning: DATE(event_ts) daily partition. Clustering: (tenant_id, site_id, event_name) — almost every query you'll write benefits from this layout if you include tenant_id and site_id in your WHERE.

identity_map

visitor_id ↔ user_id resolution table. Populated by the identity-tick MERGE from identify-kind events in events.

tenant_id          STRING     REQUIRED
site_id            STRING     REQUIRED
visitor_id         STRING     REQUIRED
user_id            STRING     REQUIRED
first_mapped_at    TIMESTAMP
last_mapped_at     TIMESTAMP

Partitioning: none (small table, queries are clustering-pruned). Clustering: (tenant_id, site_id, visitor_id).

A single visitor_id can map to multiple user_ids over time — e.g. a shared computer where two different users sign in. Queries that need "the current user for this visitor" should ORDER BY last_mapped_at DESC LIMIT 1.

events_dlq

Dead-letter for rows that failed streaming insert.

received_ts    TIMESTAMP    REQUIRED
tenant_id      STRING                   -- might be null on truly malformed rows
site_id        STRING
reason         STRING                   -- JSON-stringified BQ partial-error object, or 'unknown'
raw_row        JSON                     -- the original row that was rejected

Partitioning: DATE(received_ts) daily. Clustering: none.

Rows here are NOT in the main events table. To recover them, see the DLQ Recovery section in Fleet Dashboard & Troubleshooting.

Useful Queries

Daily active visitors per site

SELECT
  DATE(event_ts) AS day,
  COUNT(DISTINCT visitor_id) AS dav
FROM `karmaflow-prod.karmaflow_analytics.events`
WHERE tenant_id = @tenant
  AND site_id   = @site
  AND event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY day
ORDER BY day;

Top pages by visits, last 7 days

SELECT
  page_path,
  COUNT(*) AS pageviews,
  COUNT(DISTINCT visitor_id) AS visitors
FROM `karmaflow-prod.karmaflow_analytics.events`
WHERE tenant_id = @tenant
  AND site_id   = @site
  AND event_name = 'page_view'
  AND event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY page_path
ORDER BY pageviews DESC
LIMIT 50;

Conversion rate by UTM source

WITH visitors AS (
  SELECT
    visitor_id,
    ANY_VALUE(utm_source IGNORE NULLS) AS first_utm
  FROM `karmaflow-prod.karmaflow_analytics.events`
  WHERE tenant_id = @tenant AND site_id = @site
    AND event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY visitor_id
),
converts AS (
  SELECT DISTINCT visitor_id
  FROM `karmaflow-prod.karmaflow_analytics.events`
  WHERE tenant_id = @tenant AND site_id = @site
    AND event_name = 'purchase'
    AND event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
)
SELECT
  v.first_utm,
  COUNT(*) AS visitors,
  COUNT(c.visitor_id) AS converters,
  SAFE_DIVIDE(COUNT(c.visitor_id), COUNT(*)) AS cvr
FROM visitors v
LEFT JOIN converts c USING (visitor_id)
GROUP BY first_utm
ORDER BY visitors DESC;

Identity-stitched user counts (vs visitor counts)

SELECT
  COUNT(DISTINCT COALESCE(idm.user_id, e.visitor_id)) AS unique_users,
  COUNT(DISTINCT e.visitor_id) AS unique_visitors,
  SAFE_DIVIDE(COUNT(DISTINCT idm.user_id), COUNT(DISTINCT e.visitor_id)) AS identified_rate
FROM `karmaflow-prod.karmaflow_analytics.events` e
LEFT JOIN `karmaflow-prod.karmaflow_analytics.identity_map` idm
  ON e.tenant_id  = idm.tenant_id
  AND e.site_id   = idm.site_id
  AND e.visitor_id = idm.visitor_id
WHERE e.tenant_id = @tenant
  AND e.site_id   = @site
  AND e.event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

Web Vitals p75 per page

SELECT
  page_path,
  JSON_VALUE(properties, '$.metric') AS metric,
  APPROX_QUANTILES(CAST(JSON_VALUE(properties, '$.value') AS FLOAT64), 100)[OFFSET(75)] AS p75,
  COUNT(*) AS samples
FROM `karmaflow-prod.karmaflow_analytics.events`
WHERE tenant_id = @tenant
  AND site_id   = @site
  AND event_name = 'web_vitals'
  AND event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY page_path, metric
HAVING samples >= 50
ORDER BY p75 DESC;

DLQ rows last 24h, grouped by reason

SELECT
  tenant_id,
  site_id,
  SUBSTR(reason, 0, 200) AS short_reason,
  COUNT(*) AS n
FROM `karmaflow-prod.karmaflow_analytics.events_dlq`
WHERE received_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
GROUP BY tenant_id, site_id, short_reason
ORDER BY n DESC;

Query Cost Notes

Streaming inserts and storage are the dominant ongoing costs; queries are typically pennies. But:

• The biggest cost lever is partition pruning. Always include an event_ts predicate that lets BQ skip partitions (event_ts >= <date>).
• Clustering pruning kicks in when you also filter on tenant_id and site_id early in the WHERE. Same for event_name if the query is per-event-type.
• SELECT * reads every column. For large date ranges, list only the columns you need — BQ is columnar, so this is a real win.
• properties is a JSON column. JSON_VALUE(properties, '$.foo') is cheap; reading the whole column then JSON_EXTRACT-ing many keys is not.

Schema Migrations

When the code grows new event fields:

1. The change is added to eventsSchema in src/services/analytics/bigQueryService.js.
2. On the next ensureEventsTable() call (startup or via the bootstrap endpoint), nothing happens — BQ doesn't support automatic schema-add via the streaming insert API.
3. To actually add the column to the table, run bq update --schema=... or use the BQ console. The bootstrap endpoint includes a --apply-schema-additions mode that does this safely.
4. Once the column exists, code that emits it succeeds. Code emitting it before the column exists lands in DLQ with No such field.

So: deploy a schema addition by adding the column in BQ first, then deploying the code that emits it, never the other way around.

When NOT to Query BigQuery Directly

The dashboard endpoints (/api/analytics/sites/:siteId/*) are rate-limited, cached, identity-aware, and consent-aware. Direct BQ queries from outside the codebase don't get any of that. Use the API for anything that's user-facing; reserve direct BQ queries for one-off analyses, ops investigations, or exports.