Data Model Reference (Enterprise Single Tenant)

The tables, views and columns of the analytics dataset of a dedicated instance, for data analysts and BI teams.

Last updated 15 days ago

This article is for administrators of a dedicated instance.

A dedicated instance can include a copy of its analytics data in Google BigQuery. This reference describes the mates_analytics dataset: its tables, views, columns and relationships. It is for data analysts and BI teams of organizations on such an instance.

This feature is available on dedicated instances that include the BigQuery export.

With the dataset, you can:

Build custom dashboards in your BI tool (Looker, Tableau, Power BI, Metabase, and others).
Run your own SQL queries for deep-dive analysis.
Export data for compliance, audit or data science work.
Combine Allmates data with your data warehouse.

Key properties

Property	Value
GCP project	The dedicated project of your instance. Your account team gives its ID.
Dataset	`mates_analytics`
Location	Depends on your instance. Your account team gives it.
Refresh	Refreshed every hour
Schema style	Star schema: dimension views and fact views

In this guide, {YOUR_GCP_PROJECT} is a placeholder for your project ID.

Access and connection

Your account team sets up access to the dataset.

From the BigQuery console

Open the BigQuery console in Google Cloud.
Select your project, {YOUR_GCP_PROJECT}.
Open the mates_analytics dataset.
Browse the tables and views in the left panel.

From a BI tool

Most BI tools connect to BigQuery with a native connector (Looker, Tableau, Power BI, Metabase) or with a JDBC or ODBC driver. Use these connection settings:

Project ID: {YOUR_GCP_PROJECT}
Dataset: mates_analytics
Location: the location of your instance
Authentication: the identity that has access to the dataset

Filter on your organization

The dataset holds the data of every organization on your instance. Almost every table and view has an organization_id column. The exceptions are the user and provider objects (tbl_user_latest, dim_user, dim_provider) and tbl_model_daily. Filter on organization_id in every query:

The query reads only the data of the organization you analyze.
Most physical tables use organization_id as their first clustering column, so the filter reduces the data that BigQuery scans.

SELECT *
FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_message_tokens`
WHERE organization_id = 'YOUR_ORG_ID'
  AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

To find your organization_id, list the organizations and their URL paths:

SELECT organization_id, display_name, url_path
FROM `{YOUR_GCP_PROJECT}.mates_analytics.dim_organization`

The url_path value is the part of the app address after /o/.

Data model structure

The dataset contains three types of objects.

Physical tables (`tbl_*`)

16 tables store the latest state of each record of the app.

They are refreshed every hour.
They hold the data with few transformations.
Most of them have a last_synced_at column (time of the last update of the row).

Naming convention: tbl_{entity}_latest, for example tbl_organization_latest, tbl_message_latest and tbl_agent_latest. One table uses another suffix: tbl_model_daily (one row per model per day).

Dimension views (`dim_*`)

11 views sit on top of the physical tables. They add:

Date columns, for example created_date and updated_date.
Extracted identifiers, for example connection_id in dim_model.
A stable interface for joins.

Naming convention: dim_{entity}, for example dim_organization, dim_member and dim_agent.

Best practice: join the facts to the dimension views, not to the physical tables.

Fact views (`fact_*`)

8 fact objects hold the business metrics. They are the main entry point for analytics. Each fact has a defined grain (what one row represents).

Naming convention: fact_{metric}, for example:

fact_message_tokens: token usage per Mate message.
fact_conversation: metrics per conversation.
fact_tool_calls: one row per tool call.

Best practice: start your queries from the fact views. They already hold the computed metrics.

Key tables reference

Core entities

View	Description	Grain
`dim_organization`	Organizations	One row per organization
`dim_workspace`	Workspaces of each organization	One row per workspace
`dim_project`	Conversations (called projects in the data)	One row per conversation
`dim_user`	User accounts	One row per user
`dim_member`	Organization memberships	One row per user and organization
`dim_agent`	Mates	One row per Mate
`dim_model`	Model configurations	One row per model
`dim_connection`	Connections to model providers	One row per connection
`dim_tool_instance`	Tools of each organization	One row per tool

Fact views

View	Description	Grain	Key columns
`fact_message_tokens`	Token usage per message	One row per Mate message	`input_tokens`, `output_tokens`, `total_tokens`
`fact_message`	All messages, with lineage between messages	One row per message (member and Mate)	`sender_type`, `message_type`, `messages_since_last_human`, `chain_depth_category`
`fact_conversation`	Metrics per conversation	One row per conversation	`total_message_count`, `unique_human_participants`, `agent_to_human_ratio`
`fact_tool_calls`	Tool calls	One row per tool call	`tool_name`, `status`, `is_successful`, `duration_seconds`
`fact_attachment`	Files added to conversations	One row per file	`file_size_bytes`, `token_count`, `page_count`

Entity relationships

Hierarchy

Organization
- Workspace
  - Project (conversation)
    - Message
    - Attachment
    - Tool call
- Member (user in the organization)
- Agent (Mate)
- Model
- Connection
- Tool instance

Key foreign keys

From	Column	To
`dim_workspace`	`organization_id`	`dim_organization.organization_id`
`dim_project`	`workspace_id`	`dim_workspace.workspace_id`
`dim_member`	`organization_id`	`dim_organization.organization_id`
`dim_member`	`user_id`	`dim_user.user_id`
`dim_agent`	`organization_id`	`dim_organization.organization_id`
`dim_agent`	`model_id`	`dim_model.model_id`
`fact_message`	`project_id`	`dim_project.project_id`
`fact_message`	`sender_member_id`	`dim_member.member_id`
`fact_tool_calls`	`tool_instance_id`	`dim_tool_instance.tool_instance_id`
`fact_tool_calls`	`agent_id`	`dim_agent.agent_id`

Common queries

Replace {YOUR_GCP_PROJECT} with your project ID and YOUR_ORG_ID with your organization_id.

1. Token usage by organization (last 30 days)

Use case: follow AI consumption across the organizations of your instance.

SELECT
  o.display_name AS organization_name,
  COUNT(*) AS message_count,
  SUM(f.input_tokens) AS total_input_tokens,
  SUM(f.output_tokens) AS total_output_tokens,
  SUM(f.total_tokens) AS total_tokens
FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_message_tokens` f
JOIN `{YOUR_GCP_PROJECT}.mates_analytics.dim_organization` o USING (organization_id)
WHERE f.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY o.display_name
ORDER BY total_tokens DESC

2. Daily active users

Use case: follow member engagement over time.

SELECT
  event_date,
  COUNT(DISTINCT sender_member_id) AS active_users,
  COUNT(*) AS total_messages,
  ROUND(COUNT(*) / COUNT(DISTINCT sender_member_id), 1) AS messages_per_user
FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_message`
WHERE organization_id = 'YOUR_ORG_ID'
  AND sender_type = 'human'
  AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY event_date
ORDER BY event_date

3. Most active conversations

Use case: find the conversations with the most activity.

SELECT
  project_id,
  conversation_name,
  total_message_count,
  unique_human_participants,
  unique_agents_solicited,
  TIMESTAMP_DIFF(last_message_at, first_message_at, HOUR) AS duration_hours,
  activity_status,
  engagement_category
FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_conversation`
WHERE organization_id = 'YOUR_ORG_ID'
  AND activity_status IN ('ACTIVE_NOW', 'ACTIVE_TODAY')
ORDER BY total_message_count DESC
LIMIT 20

4. Tool performance

Use case: monitor the reliability and speed of tools.

SELECT
  tool_name,
  COUNT(*) AS call_count,
  COUNTIF(is_successful) AS success_count,
  ROUND(COUNTIF(is_successful) / COUNT(*) * 100, 1) AS success_rate_pct,
  ROUND(AVG(duration_seconds), 2) AS avg_duration_sec
FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_tool_calls`
WHERE organization_id = 'YOUR_ORG_ID'
  AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY tool_name
ORDER BY call_count DESC

5. Chains of Mate messages

Use case: see how Mates reply to each other after a member message.

SELECT
  project_id,
  COUNT(*) AS total_agent_messages,
  COUNTIF(is_agent_response) AS direct_responses,
  COUNTIF(is_in_agent_chain) AS chain_messages,
  MAX(messages_since_last_human) AS max_chain_depth,
  ROUND(AVG(messages_since_last_human), 1) AS avg_chain_depth
FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_message`
WHERE organization_id = 'YOUR_ORG_ID'
  AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND sender_type = 'agent'
GROUP BY project_id
HAVING COUNT(*) > 10
ORDER BY max_chain_depth DESC
LIMIT 20

6. File upload trends

Use case: follow file usage and volume. In fact_attachment, event_date is a timestamp, so the filter converts the date.

SELECT
  DATE(event_date) AS upload_date,
  COUNT(*) AS files_uploaded,
  ROUND(SUM(file_size_mb), 2) AS total_size_mb,
  ROUND(AVG(file_size_mb), 2) AS avg_file_size_mb,
  SUM(token_count) AS total_tokens_extracted
FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_attachment`
WHERE organization_id = 'YOUR_ORG_ID'
  AND event_date >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
GROUP BY upload_date
ORDER BY upload_date

Query performance tips

1. Filter on `organization_id`

Most physical tables use organization_id as their first clustering column. Put it in the WHERE clause of every query.

-- Good: uses the clustering
SELECT * FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_message_tokens`
WHERE organization_id = 'YOUR_ORG_ID'
  AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

-- Bad: reads the data of every organization
SELECT * FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_message_tokens`
WHERE sender_display_name = 'Alice'

2. Add a date filter

Most fact views have an event_date column. A date filter reduces the rows that the query processes and returns. The message, conversation and tool tables are not partitioned by date, so a date filter alone does not reduce the data that BigQuery scans. Combine it with the organization_id filter.

3. Query the fact views, not the physical tables

The fact views already hold the joins, categories and metrics.

-- Good: uses the fact view
SELECT * FROM `{YOUR_GCP_PROJECT}.mates_analytics.fact_message_tokens`
WHERE organization_id = 'YOUR_ORG_ID'

-- Bad: rebuilds the joins by hand
SELECT m.*, mm.input_tokens, mm.output_tokens
FROM `{YOUR_GCP_PROJECT}.mates_analytics.tbl_message_latest` m
LEFT JOIN `{YOUR_GCP_PROJECT}.mates_analytics.tbl_message_metadata_latest` mm
  ON m.message_id = mm.message_id
WHERE m.organization_id = 'YOUR_ORG_ID'

4. Use the clustering columns

Each physical table has its own clustering columns. Filter on them in order.

Table	Clustering columns
`tbl_message_latest`	`organization_id`, `project_id`
`tbl_agent_latest`	`organization_id`, `status`
`tbl_model_latest`	`organization_id`, `provider`
`tbl_tools_call_latest`	`organization_id`, `status`

-- Good: uses both clustering columns
SELECT * FROM `{YOUR_GCP_PROJECT}.mates_analytics.tbl_message_latest`
WHERE organization_id = 'YOUR_ORG_ID' AND project_id = 'PROJECT_123'

-- OK: uses the first clustering column only
SELECT * FROM `{YOUR_GCP_PROJECT}.mates_analytics.tbl_message_latest`
WHERE organization_id = 'YOUR_ORG_ID'

5. Select only the columns you need

BigQuery scans every column that a query reads. SELECT * reads every column. LIMIT reduces the rows returned, not the data scanned. To explore a table, use the table preview in the BigQuery console.

Data freshness

Physical tables (tbl_*): refreshed every hour. One exception refreshes once a day: tbl_model_daily.
Dimension and fact views: they read the physical tables when you run the query. They show the data of the last refresh.

A change in the app reaches the dataset at the next hourly refresh. To check the last update of a table, read its last_synced_at column:

SELECT organization_id, MAX(last_synced_at) AS last_update
FROM `{YOUR_GCP_PROJECT}.mates_analytics.tbl_message_latest`
WHERE organization_id = 'YOUR_ORG_ID'
GROUP BY organization_id

Good to know

The numbers of tables and views change with each release. The appendix lists the objects on the date of this article.

Appendix: complete list

Physical tables (16)

tbl_organization_latest
tbl_workspace_latest
tbl_project_latest
tbl_user_latest
tbl_member_latest
tbl_agent_latest
tbl_model_latest
tbl_connection_latest
tbl_tool_instance_latest
tbl_message_latest
tbl_message_metadata_latest
tbl_chat_metadata_latest
tbl_tools_call_latest
tbl_attachment_latest
tbl_invitation_latest
tbl_model_daily

Dimension views (11)

dim_organization
dim_workspace
dim_project
dim_user
dim_member
dim_agent
dim_model
dim_connection
dim_tool_instance
dim_provider
dim_invitation

Fact objects (8)

fact_message_tokens
fact_message
fact_conversation
fact_tool_calls
fact_chat_tokens
fact_attachment
fact_message_errors
fact_message_cost

Was this helpful?

Still need help? Ask the team