Technical reference

Data Structure for Visualization

Connect Tableau, Power BI, Looker, or another reporting tool to the versioned BI views provided by Aggregate without exposing private event rows.

Use the supported views as the reporting boundary

Routine BI credentials should receive SELECT access only to bi_anonymous_events_v1, bi_anonymous_goals_v1, and, when needed, bi_anonymous_geo_events_v1. Do not grant routine reporting users access to events or the other application tables.

Supported BI contract

`bi_anonymous_events_v1`

Anonymous only Hourly grain

Each row is one released hourly cell, grouped by every dimension listed below. The current UTC hour and cells below anonymous_min_cell_count are absent. The default threshold is 5 events and the allowed range is 2–1000.

Columns exposed by bi_anonymous_events_v1
Column	Logical type	Meaning
`website_token`	String	Site key used to partition events. Website names and domains are stored outside the database.
`event_hour`	UTC datetime	Start of a completed UTC hour; anonymous event times are bucketed to this boundary.
`event_name`	String	`view` for page views, or a fixed safe name for a measured interaction.
`page_path`	Text	Sanitized path with query strings and fragments removed and identifier-like segments redacted.
`referrer_channel`	Category	`direct`, `internal`, `search`, `social`, `email`, `referral`, or `unknown`.
`device_class`	Category	`mobile`, `tablet`, `desktop`, `bot`, or `unknown`.
`viewport_bucket`	Category	`small`, `medium`, `large`, or `unknown`.
`event_count`	Integer	Number of event rows in the released cell. This is not a count of distinct people.

The row grain is the combination of the first seven columns. Sum event_count with SUM(...) when removing dimensions from a report. This totals already-released cells; it does not re-run suppression at the broader grain or recover hidden cells.

Conversion BI contract

`bi_anonymous_goals_v1`

Anonymous only Daily grain

Each row is one released daily goal cell. The view includes only anonymous events with an allowlisted goal retained in goal_event. The current UTC day and cells below anonymous_min_cell_count are absent. The default threshold is 5 goal occurrences and the allowed range is 2–1000.

Columns exposed by bi_anonymous_goals_v1
Column	Logical type	Meaning
`website_token`	String	Site key used to partition goal occurrences.
`event_day`	UTC date	A completed UTC calendar day.
`goal_event`	String	Stable allowlisted code from `config/goals.yaml`; labels are presentation metadata and are not stored in this view.
`event_count`	Integer	Number of goal occurrences in the released cell, not distinct people or unique converters.

This view deliberately excludes event name, path, referrer, device, viewport, geography, and identifiers. Do not join it to the hourly or geography views: they have different grains and independent suppression. A configured goal can still be sensitive in context, and a generic rejection warning is an allowlist result—not sensitive-data detection.

Optional BI contract

`bi_anonymous_geo_events_v1`

Anonymous only Daily grain

Each row is one released daily geography cell. The current UTC day and cells below anonymous_geo_min_cell_count are not shown individually; their counts may be released only in a qualifying other pool. The default threshold is 25 events and the allowed range is 10–1000.

Columns exposed by bi_anonymous_geo_events_v1
Column	Logical type	Meaning
`website_token`	String	Site key used to partition events.
`event_day`	UTC date	A completed UTC calendar day.
`event_name`	String	`view` for page views, or a fixed safe interaction name.
`geo_area`	Category	`continent:XX`, `country:XX`, or a suppressed pool such as `continent:other` or `country:other`.
`event_count`	Integer	Number of event rows in the released geography cell, not distinct people.

This view deliberately excludes path, referrer, device, viewport, and identifiers. Do not join it to the hourly view: the views have different grains and independent suppression. Low-volume areas may be combined into an other pool, including one otherwise-visible area when secondary suppression is needed. Events without a retained geography value are excluded, so geography totals will not match the hourly view.

Custom reporting views

Administrators can define JSON properties and SQL column names in Data model, then regenerate these views for reporting on retained raw events:

analytics_custom_events_v1: all event names in both privacy modes.
analytics_custom_pageviews_v1: page views only.
analytics_custom_goals_v1: events with a retained goal.

Modeled scalar properties become text columns. Missing values, JSON null, arrays, and objects produce SQL null. These views expose individual rows without suppression and need separately approved raw-data access. They include raw rows already marked as archived while those rows remain retained; archive aggregate cells do not contain the custom properties.

The bi_anonymous_* views keep their existing dimensions and disclosure thresholds. Deleting raw events removes their custom properties from reporting, even when archived counts remain available.

Private source tables

These tables explain how the views are built; they are not the routine BI contract. The private events table contains individual rows and may contain enhanced identifiers or properties. Even anonymous-mode rows can remain personal data in context.

`events`

This is the unified event store. Logical types are shown because the exact physical SQL type varies across PostgreSQL, MySQL, MariaDB, SQL Server, and SQLite.

Logical columns in the private events table
Column	Logical type	Mode and purpose
`id`	Integer	Generated primary key for an individual event row.
`website_token`	String (191)	Required site key for both modes.
`event_name`	String (191)	Required event name; defaults to `view`.
`url`	Text	Required stored page path. Exposed as `page_path` by the hourly view.
`referrer`	Nullable text	Stored referrer channel; exposed as `referrer_channel`.
`privacy_mode`	String (20)	Aggregate writes `anonymous` or `enhanced`; defaults to anonymous.
`device_class`	String (20)	Coarse device category for both modes.
`viewport_bucket`	String (20)	Coarse viewport category for both modes.
`geo_area`	Nullable string (16)	Aggregate writes only canonical `continent:XX` or `country:XX` values; synthetic `other` values exist only in the geography view.
`generalized_user_agent`	Nullable string (191)	Enhanced only; forced to null in anonymous mode.
`screen_width`	Nullable integer	Enhanced only; forced to null in anonymous mode.
`visitor_id`	Nullable string (191)	Enhanced only; forced to null in anonymous mode.
`session_id`	Nullable string (191)	Enhanced only; forced to null in anonymous mode.
`consent_state`	Nullable string (20)	`granted` for enhanced rows; null in anonymous mode.
`custom_data`	Nullable JSON	Scalar event properties and the reserved `orgInternalTraffic: true` (or configured-name) marker. Anonymous mode retains only explicitly configured consent-free properties and the marker. The `bi_anonymous_*` views and archives omit this JSON.
`goal_event`	Nullable string (191)	Enabled allowlisted goal code; present in anonymous mode only when its definition permits anonymous use.
`created_at`	UTC datetime	UTC hour boundary for anonymous rows; exact server event time for enhanced rows.

`analytics_privacy_settings`

A singleton row with id = 1 supplies the live disclosure thresholds used by the three BI views.

Columns in the analytics privacy settings table
Column	Logical type	Meaning
`id`	Integer	Primary key; the views read the singleton row whose value is 1.
`anonymous_min_cell_count`	Integer	Hourly event and daily goal threshold; default 5, allowed range 2–1000.
`anonymous_geo_min_cell_count`	Integer	Daily geography threshold; default 25, allowed range 10–1000.
`updated_at`	UTC datetime	Time the settings row was last changed.

If row 1 is missing or a threshold is outside its allowed range, the affected view returns no rows. BI accounts should not receive access to this table.

There is no relational websites table. Website names, domains, and tokens live in config/websites.yaml. If reports need friendly names, maintain a controlled token-to-name mapping in the visualization layer or in a separately governed reporting dataset.

Safe query patterns

Replace the example token with a parameter supplied by your BI tool. These queries select explicit columns and label results as released counts because enhanced events and unreleased counts are absent. Goal results are occurrence counts rather than unique converters, and geography counts may instead contribute to a released other pool.

Released page views by hour

SELECT
    event_hour,
    page_path,
    SUM(event_count) AS released_page_view_count
FROM bi_anonymous_events_v1
WHERE website_token = 'replace-with-site-token'
  AND event_name = 'view'
GROUP BY event_hour, page_path
ORDER BY event_hour, page_path;

Released goals by day

SELECT
    event_day,
    goal_event,
    event_count AS released_goal_occurrence_count
FROM bi_anonymous_goals_v1
WHERE website_token = 'replace-with-site-token'
ORDER BY event_day, goal_event;

Released page views by area

SELECT
    event_day,
    geo_area,
    SUM(event_count) AS released_page_view_count
FROM bi_anonymous_geo_events_v1
WHERE website_token = 'replace-with-site-token'
  AND event_name = 'view'
GROUP BY event_day, geo_area
ORDER BY event_day, geo_area;

Use SUM(event_count), not COUNT(*). Counting rows counts released grouped cells rather than the events represented by those cells.

Connection checklist

PostgreSQL, MySQL, MariaDB, or SQL Server

Create a dedicated database principal; never reuse the application's credentials.
Grant only the minimal connection/schema access required plus SELECT on the approved view or views needed; do not grant source-table SELECT.
Use TLS and restrict the principal to the BI service's expected network location.
Review and reapply view grants after upgrades because migrations may recreate the views.
A shared view exposes every website token in it; BI filters are not tenant isolation.

SQLite

SQLite has no database users or object-level view grants.
A read-only database file still allows access to the private events table.
Have a trusted process export explicit columns from the approved views.
Protect exported files and use a server database when direct BI access is required.

Model and refresh settings

Configure event_hour as UTC; the physical timestamp may not carry a timezone marker.
Connector-specific physical types vary; model event_day as a date and event_count as a whole number.
Expect new hourly event data only after an hour completes, and goal and geography data only after a UTC day completes.
Treat missing cells as withheld or unavailable, not as confirmed zero traffic.
Remember that event_count counts event or goal occurrences, not visitors, unique people, or unique converters.
Review stored BI extracts separately; later threshold changes do not remove earlier exports.

Suppression reduces disclosure risk but does not establish k-anonymity or guarantee that released data is legally anonymous. Apply appropriate retention, access controls, and review to the visualization tool and its extracts.

Back to How It Works Get Started