> For the complete documentation index, see [llms.txt](https://help.smartico.ai/welcome/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-system-views.md).

# Smartico DWH \ System views

## Dimensional tables

### dwh\_ext\_xxx.dm\_tag

List of tags used for organizing and categorizing entities.

Primary key: **tag\_id**

| Column       | Type      | Description                                 |
| ------------ | --------- | ------------------------------------------- |
| label\_id    | INT64     | Label identifier                            |
| create\_date | TIMESTAMP | Date and time when the tag was created      |
| update\_date | TIMESTAMP | Date and time when the tag was last updated |
| tag\_id      | INT64     | Unique identifier for the tag               |
| tag\_name    | STRING    | Name of the tag                             |
| is\_deleted  | BOOL      | Indicates if the tag has been deleted       |

### dwh\_ext\_xxx.dm\_tag\_entity

Association table linking tags to entities.

Primary key: **id**

| Column           | Type      | Description                                                                                                                                                                                                                                                                                       |
| ---------------- | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| id               | INT64     | Unique identifier for the tag-entity association                                                                                                                                                                                                                                                  |
| tag\_id          | INT64     | Reference to dm\_tag                                                                                                                                                                                                                                                                              |
| entity\_id       | INT64     | ID of the tagged entity; which object it is depends on entity\_type\_id                                                                                                                                                                                                                           |
| entity\_type\_id | INT64     | Type of the tagged entity (enum ActivityEntityType, no lookup table in the DWH). Main ids: 1 = Campaign, 2 = Segment, 7 = Mission, 10 = Bonus Template, 11 = Popup, 12 = Push, 13 = Email, 14 = SMS, 15 = Inbox, 16 = Shop Item, 26 = Tournament, 110 = Mini-game template, 112 = Automation Rule |
| is\_deleted      | BOOL      | Indicates if the association has been deleted                                                                                                                                                                                                                                                     |
| label\_id        | INT64     | Label identifier                                                                                                                                                                                                                                                                                  |
| create\_date     | TIMESTAMP | Date and time when the association was created                                                                                                                                                                                                                                                    |
| update\_date     | TIMESTAMP | Date and time when the association was last updated                                                                                                                                                                                                                                               |

### dwh\_ext\_xxx.dm\_casino\_game\_name

Names of casino games, as reported by the operator in the player's last casino bet or game properties.

Primary key: **smr\_game\_id**

| Column        | Type      | Description |
| ------------- | --------- | ----------- |
| smr\_game\_id | INT64     |             |
| game\_name    | STRING    |             |
| label\_id     | INT64     |             |
| update\_date  | TIMESTAMP |             |
| create\_date  | TIMESTAMP |             |

### dwh\_ext\_xxx.dm\_casino\_provider\_name

Contains information about casino game providers.

Primary key: **smr\_provider\_id**

| Column            | Type      | Description |
| ----------------- | --------- | ----------- |
| smr\_provider\_id | INT64     |             |
| provider\_name    | STRING    |             |
| label\_id         | INT64     |             |
| update\_date      | TIMESTAMP |             |
| create\_date      | TIMESTAMP |             |

### dwh\_ext\_xxx.dm\_casino\_game\_type

Contains information about casino game types.

Primary key: **smr\_game\_type\_id**

| Column              | Type      | Description |
| ------------------- | --------- | ----------- |
| smr\_game\_type\_id | INT64     |             |
| game\_type          | STRING    |             |
| label\_id           | INT64     |             |
| update\_date        | TIMESTAMP |             |
| create\_date        | TIMESTAMP |             |

### dwh\_ext\_xxx.dm\_sport\_type

Names of sport types (football, tennis, ...), as reported by the operator in the player's last sport bet or selection properties.

Primary key: **smr\_sport\_type\_id**

| Column               | Type      | Description |
| -------------------- | --------- | ----------- |
| smr\_sport\_type\_id | INT64     |             |
| sport\_type          | STRING    |             |
| label\_id            | INT64     |             |
| update\_date         | TIMESTAMP |             |
| create\_date         | TIMESTAMP |             |

### dwh\_ext\_xxx.dm\_sport\_league

Names of sport leagues, as reported by the operator in the player's last sport bet or selection properties.

Primary key: **smr\_sport\_league\_id**

| Column                 | Type      | Description |
| ---------------------- | --------- | ----------- |
| smr\_sport\_league\_id | INT64     |             |
| sport\_league          | STRING    |             |
| label\_id              | INT64     |             |
| update\_date           | TIMESTAMP |             |
| create\_date           | TIMESTAMP |             |

### dwh\_ext\_xxx.dm\_bo\_users

List of BackOffice users with their details.

Primary key: **user\_id**

**Joins:**

* user\_id → [dm\_ach](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach)
* user\_id → [dm\_ach\_task](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach_task)
* user\_id → [dm\_ach\_activity](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach_activity)
* user\_id → [dm\_avatar](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_avatar)
* user\_id → [dm\_saw\_template](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_saw_template)
* user\_id → [dm\_tournament](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_tournament)
* user\_id → [dm\_ach\_custom\_sections](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach_custom_sections)
* user\_id → [dm\_bnr\_banner](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_bnr_banner)
* user\_id → [dm\_bnr\_placement](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_bnr_placement)
* user\_id → [dm\_shop\_item](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_shop_item)
* user\_id → [dm\_segment](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_segment)

| Column          | Type      | Description                                                             |
| --------------- | --------- | ----------------------------------------------------------------------- |
| user\_id        | INT64     | ID of the backoffice user                                               |
| username        | STRING    | Username of the backoffice user                                         |
| bo\_user\_email | STRING    | Email of the backoffice user                                            |
| is\_active      | BOOL      | Indicates if the user is active (enabled for login and not deactivated) |
| home\_label\_id | INT64     | Home label ID of the user                                               |
| update\_date    | TIMESTAMP | Date and time when the user was last updated                            |
| create\_date    | TIMESTAMP | Date and time when the user was created                                 |

### dwh\_ext\_xxx.dm\_brand

Contains information about CRM brands.

Primary key: **crm\_brand\_id**

**Joins:**

* crm\_brand\_id → [g\_ach\_points\_change\_log](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_points_change_log)
* crm\_brand\_id → [g\_shop\_transactions](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_shop_transactions)
* crm\_brand\_id → [g\_ach\_levels\_changed](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_levels_changed)
* crm\_brand\_id → [g\_minigames](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_minigames)
* crm\_brand\_id → [g\_gems\_diamonds\_change\_log](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_gems_diamonds_change_log)
* crm\_brand\_id → [g\_ach\_claimed](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_claimed)
* crm\_brand\_id → [g\_ach\_completed](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_completed)
* crm\_brand\_id → [g\_ach\_optins](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_optins)
* crm\_brand\_id → [g\_tournament\_analytics](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_tournament_analytics)
* crm\_brand\_id → [g\_tournament\_winners](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_tournament_winners)
* crm\_brand\_id → [g\_ux](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ux)
* crm\_brand\_id → [jp\_bet](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.jp_bet)
* crm\_brand\_id → [j\_automation\_rule\_progress](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_automation_rule_progress)
* crm\_brand\_id → [j\_bonuses](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_bonuses)
* crm\_brand\_id → [j\_communication](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_communication)
* crm\_brand\_id → [j\_engagements](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_engagements)
* crm\_brand\_id → [j\_webhooks\_facts](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_webhooks_facts)

| Column           | Type      | Description                                   |
| ---------------- | --------- | --------------------------------------------- |
| label\_id        | INT64     | Label identifier                              |
| create\_date     | TIMESTAMP | Date and time when the brand was created      |
| update\_date     | TIMESTAMP | Date and time when the brand was last updated |
| crm\_brand\_id   | INT64     | ID of the brand in the Smartico system        |
| crm\_brand\_name | STRING    | Name of the brand                             |
| ext\_brand\_id   | STRING    | External brand identifier                     |
| is\_qa           | BOOL      | Indicates if this is a QA/test brand          |
| is\_deleted      | BOOL      | Indicates if the brand has been deleted       |

### dwh\_ext\_xxx.dm\_event\_type

List of event types used for triggering real-time campaigns and tracking user activities.

Primary key: **event\_type\_id**

Partitioned by: **create\_date**

**Joins:**

* event\_type\_id → [dm\_ach\_task](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach_task)
* event\_type\_id → [dm\_automation\_rule](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_automation_rule)
* event\_type\_id → [dm\_audience](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_audience)
* event\_type\_id → [j\_engagements](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_engagements)
* event\_type\_id → [j\_events\_stats\_daily](#dwh_ext_xxx.j_events_stats_daily)
* event\_type\_id → [j\_events\_stats\_hourly](#dwh_ext_xxx.j_events_stats_hourly)

| Column              | Type      | Description                                                                           |
| ------------------- | --------- | ------------------------------------------------------------------------------------- |
| event\_type\_id     | INT64     | Unique identifier for the event type                                                  |
| event\_type\_name   | STRING    | Internal name of the event type                                                       |
| event\_type\_uiname | STRING    | UI display name of the event type                                                     |
| product\_id         | INT64     | ID of the product this event type belongs to                                          |
| tracks\_mau         | BOOL      | Indicates if this event type tracks Monthly Active Users                              |
| has\_tr\_table      | BOOL      | Indicates if this event type has corresponding transactional table (with tr\_ prefix) |
| create\_date        | TIMESTAMP | Date and time when the event type was created                                         |
| update\_date        | TIMESTAMP | Date and time when the event type was last updated                                    |

### dwh\_ext\_xxx.dm\_product

Contains information about products and integrations setup on the label. E.g. label usually has one integration product and many Smartico specific products that are dependent on what is activated on the label. Example of integration product: Int: MyCasinoPlatform Example of Smartico specific product: Tournaments, Jackpots, Store, AI Churn, etc..

Primary key: **product\_id**

**Joins:**

* product\_id → [j\_bonuses](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_bonuses)
* product\_id → [j\_webhooks\_facts](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_webhooks_facts)

| Column                   | Type      | Description                                                                  |
| ------------------------ | --------- | ---------------------------------------------------------------------------- |
| product\_id              | INT64     | ID                                                                           |
| product\_name            | STRING    | Name                                                                         |
| is\_integration\_product | BOOL      | Indicator if the product of type integration or internal product of Smartico |
| integration\_type        | STRING    | Type of integration, e.g. NodeJS, Kafka, RabbitMQ, API etc.                  |
| create\_date             | TIMESTAMP |                                                                              |
| update\_date             | TIMESTAMP |                                                                              |

## Fact tables

### dwh\_ext\_xxx.r\_deposits

Row-level deposit events in label (operator) currency. Used by daily aggregates and financial analytics. Partitioned by event\_time; restrict queries with a date or timestamp predicate on event\_time.

Primary key: **smartico\_event\_id**

Partitioned by: **event\_time**

Data availability: **all time by event\_time**

**Joins:**

* crm\_brand\_id → [dm\_brand](#dwh_ext_xxx.dm_brand)
* user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details)

| Column                                 | Type      | Description                                                                   |
| -------------------------------------- | --------- | ----------------------------------------------------------------------------- |
| smartico\_event\_id                    | STRING    | Unique Smartico event identifier for the deposit row                          |
| user\_id                               | INT64     | Smartico user ID                                                              |
| crm\_brand\_id                         | INT64     | Brand ID in the Smartico system                                               |
| event\_time                            | TIMESTAMP | Time of the deposit event                                                     |
| amount\_label\_currency                | FLOAT64   | Deposit amount in label default currency                                      |
| amount\_user\_currency                 | FLOAT64   | Deposit amount in the user wallet currency                                    |
| external\_transaction\_id              | STRING    | Deposit transaction identifier from the operator or payment system            |
| acc\_last\_deposit\_payment\_method    | STRING    | Payment method label for the account at deposit time (operator-specific)      |
| acc\_last\_deposit\_payment\_submethod | STRING    | Payment sub-method or variant (e.g. provider-specific detail) at deposit time |
| insert\_time                           | TIMESTAMP | When this row was loaded into the warehouse table                             |

### dwh\_ext\_xxx.r\_withdrawals

Row-level withdrawal events in label (operator) currency. Used by daily aggregates and financial analytics. Partitioned by event\_time; restrict queries with a date or timestamp predicate on event\_time.

Primary key: **smartico\_event\_id**

Partitioned by: **event\_time**

Data availability: **all time by event\_time**

**Joins:**

* crm\_brand\_id → [dm\_brand](#dwh_ext_xxx.dm_brand)
* user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details)

| Column                    | Type      | Description                                                           |
| ------------------------- | --------- | --------------------------------------------------------------------- |
| smartico\_event\_id       | STRING    | Unique Smartico event identifier for the withdrawal row               |
| user\_id                  | INT64     | Smartico user ID                                                      |
| crm\_brand\_id            | INT64     | Brand ID in the Smartico system                                       |
| event\_time               | TIMESTAMP | Time of the withdrawal event                                          |
| amount\_label\_currency   | FLOAT64   | Withdrawal amount in label default currency                           |
| amount\_user\_currency    | FLOAT64   | Withdrawal amount in the user wallet currency                         |
| external\_transaction\_id | STRING    | Withdrawal transaction identifier from the operator or payment system |
| insert\_time              | TIMESTAMP | When this row was loaded into the warehouse table                     |

### dwh\_ext\_xxx.j\_events\_stats\_daily

Events stats daily, aggregated by event type and user. Updated every hour. Can be joined with dm\_event\_type table that contains event\_type\_name. Note that event\_type\_name from dm\_event\_type corresponds to the table with prefix 'tr\_', for example 'tr\_acc\_deposit\_approved'.

Primary key: **event\_date, event\_type\_id, user\_id**

Partitioned by: **event\_date**

**Joins:**

* user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details)
* event\_type\_id → [dm\_event\_type](#dwh_ext_xxx.dm_event_type)

| Column          | Type      | Description                    |
| --------------- | --------- | ------------------------------ |
| label\_id       | INT64     | Label ID                       |
| event\_date     | TIMESTAMP | Event date, truncated to day   |
| event\_type\_id | INT64     | Event type ID                  |
| user\_id        | INT64     | Smartico user ID               |
| cnt             | INT64     | Count of events during the day |

### dwh\_ext\_xxx.j\_events\_stats\_hourly

Events stats hourly, aggregated by event type and user. Updated every hour. Can be joined with dm\_event\_type table that contains event\_type\_name. Note that event\_type\_name from dm\_event\_type corresponds to the table with prefix 'tr\_', for example 'tr\_acc\_deposit\_approved'.

Primary key: **event\_date, event\_type\_id, user\_id**

Partitioned by: **event\_date**

**Joins:**

* user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details)
* event\_type\_id → [dm\_event\_type](#dwh_ext_xxx.dm_event_type)

| Column          | Type      | Description                     |
| --------------- | --------- | ------------------------------- |
| label\_id       | INT64     | Operator/Label ID               |
| event\_date     | TIMESTAMP | Event date, truncated to hour   |
| event\_type\_id | INT64     | Event type ID                   |
| user\_id        | INT64     | Smartico user ID                |
| cnt             | INT64     | Count of events during the hour |

### dwh\_ext\_xxx.j\_llm\_tokens\_usage

AI assistant tokens usage and credit operations. token\_op\_type = 1 means usage, token\_op\_type = 2 means credit. 'tokens' is the cost-aligned BILLABLE total (what bills the label). The raw\_\* columns are the underlying provider token counts (for analytics / re-pricing); 'model' is the model id that produced the usage, or 'BigQuery' for data-scan rows.

Primary key: **label\_id, bo\_user\_id, dt, token\_op\_type**

Partitioned by: **dt**

**Joins:**

* bo\_user\_id → [dm\_bo\_users](#dwh_ext_xxx.dm_bo_users)

| Column          | Type      | Description                                                                      |
| --------------- | --------- | -------------------------------------------------------------------------------- |
| label\_id       | INT64     | Label ID                                                                         |
| bo\_user\_id    | INT64     | BackOffice user ID (-1 if unknown)                                               |
| dt              | TIMESTAMP | Operation timestamp                                                              |
| tokens          | INT64     | Billable (cost-aligned) tokens in the operation                                  |
| token\_op\_type | INT64     | Operation type: 1 usage, 2 credit                                                |
| service         | STRING    | Name of service that used tokens, e.g. "ide-main-agent", "transactions-vegalite" |


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-system-views.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.