# 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.

```sql
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:

```sql
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`.

```sql
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_id`s 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.

```sql
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

```sql
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

```sql
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

```sql
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)

```sql
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

```sql
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

```sql
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.