BigQuery Schema Reference

Direct query reference for the events, identity_map, and events_dlq tables. Partition + clustering layout.

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
ga_client_id    STRING                  -- GA4 client id from the site's own Google tag (_ga cookie)
ga_session_id   STRING                  -- GA4 session id (_ga_<stream> cookie)
ga_measurement_id STRING                -- the GA4 stream those ids belong to, e.g. G-XXXXXXX
gclid           STRING                  -- Google Ads click id from the landing URL
gbraid          STRING                  -- Google Ads app/iOS click id
wbraid          STRING                  -- Google Ads web-to-app click id
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

The six GA and click-id columns are what lets a site visit be joined to a GA4 session, an ad click and a conversation. They are filled only when the tracker could read them: GA ids need the site's own Google tag, and click ids are left out whenever the visitor declined marketing consent (consent_state.marketing = false), in the browser and again on the server. They are appended at the end of the physical table, so in the BigQuery console they appear after consent_state.

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 a new event field:

1. The field is added to eventsSchema in src/services/analytics/bigQueryService.js, as a NULLABLE column. BigQuery lets an existing table gain nullable columns (appended at the end); a REQUIRED column cannot be added to a table that already has rows, and the code reports it rather than trying.
2. The collector adds any missing nullable column to the live events table itself, once per process, before its first insert. The bootstrap endpoint and scripts/createAnalyticsBigQuerySchema.js run the same step and report the columns they added. There is no manual step to remember.
3. Until that step has succeeded, a field the table does not have yet is dropped silently: streaming inserts use ignoreUnknownValues, so the row is kept without the field and nothing lands in events_dlq. If the collector cannot update the table (for example, its service account lacks permission), it logs a warning and retries after ten minutes.
4. BigQuery can take a few minutes to make a new column visible to streaming inserts, so the first rows after a schema change may still arrive without it.

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.