# Smartico DWH \ Gamification views ## Dimensional tables ### dwh\_ext\_xxx.dm\_ach\_level Contains information about gamification levels. Primary key: **level\_id** **Joins:** * level\_id → [g\_ach\_levels\_changed](#dwh_ext_xxx.g_ach_levels_changed) * level\_id → [g\_ach\_levels\_changed](#dwh_ext_xxx.g_ach_levels_changed) | Column | Type | Description | | --------------------------- | --------- | --------------------------------------------------------------------------- | | level\_id | INT64 | PK, ID of level | | name | STRING | Name of the level | | description | STRING | Description of the level | | image\_url | STRING | Image of the level | | ordinal\_number | INT64 | Order among active levels (1..N); null for the 'manual' type of logic | | required\_points | INT64 | Points required to achieve this level. Relevant only in case of basic logic | | required\_level\_counter\_1 | FLOAT64 | Value required for the 1st counter. E.g. deposit amount for defined period | | required\_level\_counter\_2 | FLOAT64 | Value required for 2nd counter | | create\_date | TIMESTAMP | Date and time when the level was created | | update\_date | TIMESTAMP | Date and time when the level was last updated | | level\_status\_id | INT64 | Status: 1 - draft, 2 - active, 3 - archived | | int\_param1 | STRING | Internal parameter 1 | | label\_id | INT64 | Label identifier | ### dwh\_ext\_xxx.dm\_ach\_points\_change\_source Lookup table with possible sources for the points transactions. Primary key: **source\_type\_id** **Joins:** * source\_type\_id → [g\_ach\_points\_change\_log](#dwh_ext_xxx.g_ach_points_change_log) * source\_type\_id → [g\_gems\_diamonds\_change\_log](#dwh_ext_xxx.g_gems_diamonds_change_log) * source\_type\_id → [raf\_tickets](#dwh_ext_xxx.raf_tickets) | Column | Type | Description | | ------------------ | --------- | --------------------------------------------------- | | source\_type\_id | INT64 | ID of the source type | | create\_date | TIMESTAMP | Date and time when the source type was created | | update\_date | TIMESTAMP | Date and time when the source type was last updated | | source\_type\_name | STRING | Name of the source | ### dwh\_ext\_xxx.dm\_ach The table contains information about missions and badges. The field ach\_type\_id indicates the type of achievement: 1 - Mission, 2 - Badge Primary key: **ach\_id** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_bo_users) * ach\_id → [g\_ach\_optins](#dwh_ext_xxx.g_ach_optins) * ach\_id → [g\_ach\_claimed](#dwh_ext_xxx.g_ach_claimed) * ach\_id → [g\_ach\_completed](#dwh_ext_xxx.g_ach_completed) * ach\_id → [dm\_ach\_task](#dwh_ext_xxx.dm_ach_task) * ach\_id → [dm\_ach\_activity](#dwh_ext_xxx.dm_ach_activity) | Column | Type | Description | | ------------------------- | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | create\_date | TIMESTAMP | Date and time when the mission/badge was created | | update\_date | TIMESTAMP | Date and time when the mission/badge was last updated | | ach\_id | INT64 | ID of mission/badge | | ach\_type\_id | INT64 | Type of achievement: 1 - Mission, 2 - Badge | | ach\_actual\_type\_id | INT64 | Status: 1 - Draft, 2 - Generally available, 3 - Locked, 4 - Archived, 5 - Featured manually, 6 - Featured by AI, 7 - Recurring, 8 - Recurring upon completion | | requires\_prize\_claim | BOOL | Indication if mission requires claiming of prize | | internal\_name | STRING | Name of mission/badge | | requires\_optin | BOOL | Indicator if mission requires explicit opt-in | | limit\_in\_time\_ms | INT64 | Time in milliseconds for the time limited missions | | create\_by | INT64 | ID of backoffice user that created entity | | active\_from | TIMESTAMP | Date/time, mission is active from | | active\_till | TIMESTAMP | Date/time, mission is active till | | repeat\_opt\_in\_required | BOOL | Require opt-in after every recurrence | | recurring\_quantity | INT64 | For recurring upon completion missions, defines maximum number of times players can complete this mission | | has\_user\_state\_params | BOOL | Indicates if the mission has task(s) that are dependent on dynamic parameters. Used in Dynamic Missions. | ### dwh\_ext\_xxx.dm\_ach\_activity Activities given by completing missions/badges. Contains detailed information about specific activity instances that are rewarded when players complete missions/badges, including various reward types like bonuses, points, gems, and diamonds. Primary key: **ach\_activity\_id** **Joins:** * ach\_id → [dm\_ach](#dwh_ext_xxx.dm_ach) * activity\_type\_id → [dm\_activity\_type](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_activity_type) * label\_bonus\_template\_id → [dm\_bonus\_template](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_bonus_template) * audience\_id → [dm\_audience](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_audience) * another\_achievement\_id → [dm\_ach](#dwh_ext_xxx.dm_ach) * saw\_template\_id → [dm\_saw\_template](#dwh_ext_xxx.dm_saw_template) | Column | Type | Description | | -------------------------- | --------- | -------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label identifier for the mission/badge | | create\_date | TIMESTAMP | Date and time when the activity was created | | update\_date | TIMESTAMP | Date and time when the activity was last updated | | create\_by | INT64 | User ID who created the activity | | is\_deleted | BOOL | Flag indicating if the activity has been deleted | | ach\_activity\_id | INT64 | Unique identifier for the achievement activity | | ach\_id | INT64 | Achievement ID this activity belongs to | | activity\_type\_id | INT64 | Type of activity being performed. Check for dm\_activity\_type for reference | | redeem\_automatically | BOOL | Flag indicating if rewards should be automatically redeemed | | saw\_template\_id | INT64 | Mini-game template ID if this activity involves mini-game. activity\_type\_id = 16 and 24 | | saw\_attempts\_count | INT64 | Number of Mini-game attempts granted by this activity, in case of mini-game activity. activity\_type\_id = 16 and 24 | | label\_bonus\_template\_id | INT64 | Bonus template ID for bonus rewards given by this activity, in case of bonus activity. activity\_type\_id = 100 | | bonus\_amount | FLOAT64 | Amount of bonus money awarded by this activity, in case of bonus activity. activity\_type\_id = 100 | | points | INT64 | Number of points awarded by this activity, in case of points activity. activity\_type\_id = 12 | | gems | INT64 | Number of gems awarded by this activity, in case of gems activity. activity\_type\_id = 25 | | diamonds | INT64 | Number of diamonds awarded by this activity, in case of diamonds activity. activity\_type\_id = 25 | | audience\_id | INT64 | Audience ID if this activity is to specific campaign. activity\_type\_id = 15 | | another\_achievement\_id | INT64 | Mission/Badge ID that is given as a reward for this activity. activity\_type\_id = 14 | ### dwh\_ext\_xxx.dm\_ach\_clans The table contains the configuration of clans (a "**Clan**" is a group of users that compete together in clan-based tournaments). Use this table to slice clan-based tournament performance, entry-fee economics, and clan rank trajectory by clan configuration without joining back to operational PG. Primary key: **clan\_id** **Joins:** * clan\_id → [g\_tournament\_winners](#dwh_ext_xxx.g_tournament_winners) * clan\_id → [g\_clan\_ranks](#dwh_ext_xxx.g_clan_ranks) | Column | Type | Description | | ------------------------------ | --------- | ---------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label identifier | | create\_date | TIMESTAMP | Date and time when the clan was created | | update\_date | TIMESTAMP | Date and time when the clan configuration was last updated. Use this column to detect configuration changes over time. | | clan\_id | INT64 | Primary identifier for the clan | | clan\_name | STRING | Name of the clan | | clan\_description | STRING | Description of the clan | | clan\_image\_url | STRING | Image URL of the clan | | clan\_status\_id | INT64 | Status: 1 - Active, 2 - Archived, 3 - Draft | | capacity\_limit | INT64 | Maximum number of members allowed in the clan | | entry\_fee\_currency\_type\_id | INT64 | Currency used to pay the entry fee: 0 - Points, 1 - Gems, 2 - Diamonds, 3 - Free | | entry\_fee\_amount | FLOAT64 | Entry fee amount charged when joining the clan | | rank\_points | INT64 | F1-style cumulative rating points; updated by the periodic clan rank recalculation | | entry\_segment\_id | INT64 | ID of segment for entry restriction | | visibility\_segment\_id | INT64 | ID of segment for visibility restriction | ### dwh\_ext\_xxx.dm\_ach\_task Tasks that belong to missions or Stages that belong to badges. Each mission/badge can have multiple tasks/stages that players need to complete. Tasks define what actions players need to perform, how many times, and what rewards they receive. Primary key: **task\_id** **Joins:** * ach\_id → [dm\_ach](#dwh_ext_xxx.dm_ach) * event\_type\_id → [dm\_event\_type](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_event_type) * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_bo_users) | Column | Type | Description | | --------------------------------- | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | task\_id | INT64 | Unique identifier for the task | | label\_id | INT64 | Label identifier | | ach\_id | INT64 | ID of the mission/badge this task belongs to | | task\_name | STRING | Public name of the task | | task\_type\_id | INT64 | Type of the task: 1 - CompleteAchievement (task needed to complete the mission), 2 - UnlockAchievement (task needed to unlock the mission) | | task\_logic\_type\_id | INT64 | Logic type of the task: 1 - ONE\_TIME (complete action once), 2 - MULTI (complete action a few times), 3 - MULTI\_PLUS\_UNIQUE (complete action a few times with unique attribute), 4 - MULTI\_PLUS\_AGG (complete action checking SUM of attribute) | | event\_type\_id | INT64 | Event type that triggers the task completion | | execution\_count | INT64 | Number of times the action needs to be performed to complete the task (relevant for task\_logic\_type\_id is MULTI or MULTI\_PLUS\_UNIQUE) | | execution\_period\_sec | INT64 | Time period in seconds within which the execution\_count must be reached in case of MULTI, MULTI\_PLUS\_AGG or MULTI\_PLUS\_UNIQUE task logic type | | points\_reward | INT64 | Number of points rewarded upon task completion | | progress\_increment | INT64 | How much the mission progress bar advances when this task is completed | | serialize\_value\_property\_id | INT64 | Property ID used for counting unique values or SUM aggregation in MULTI\_PLUS\_UNIQUE and MULTI\_PLUS\_AGG task types | | task\_complete\_time\_restriction | INT64 | How often action will be counted as unique progress fact: NULL - not limited, 2 - once per hour, 3 - once per day, 4 - once per week, 5 - once per month | | create\_by | INT64 | ID of backoffice user that created the task | | create\_date | TIMESTAMP | Date and time when the task was created | | update\_date | TIMESTAMP | Date and time when the task was last updated | | is\_deleted | BOOL | Flag indicating if the task has been soft-deleted | | has\_user\_state\_params | BOOL | Indicates if the task conditions depend on dynamic user state parameters. Used in Dynamic Missions | | affects\_current\_balance | BOOL | Whether points reward is added to the current balance | | affects\_leaderboard | BOOL | Whether points reward counts toward leaderboard progress | | affects\_level | BOOL | Whether points reward counts toward level progress | ### dwh\_ext\_xxx.dm\_saw\_template The table contains information about mini-game templates. Primary key: **saw\_template\_id** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_bo_users) * segment\_id → [dm\_segment](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_segment) * saw\_skin\_id → [dm\_saw\_skin](#dwh_ext_xxx.dm_saw_skin) * saw\_template\_id → [dm\_saw\_prize](#dwh_ext_xxx.dm_saw_prize) * saw\_template\_id → [g\_minigames](#dwh_ext_xxx.g_minigames) * saw\_template\_id → [dm\_ach\_activity](#dwh_ext_xxx.dm_ach_activity) | Column | Type | Description | | -------------------- | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | label\_id | INT64 | Label identifier | | create\_date | TIMESTAMP | Date and time when the template was created | | update\_date | TIMESTAMP | Date and time when the template was last updated | | saw\_template\_id | INT64 | ID of the game template | | template\_name | STRING | Name of template | | saw\_game\_type\_id | INT64 | Type of the game: 1 - Spin The Wheel, 2 - Scratch & Catch, 3 - Match X, 4 - Gift box, 5 - Prize Drop, 6 - Quiz, 7 - Lootbox Weekdays, 8 - Lootbox Calendar days, 9 - Treasure hunt, 10 - Voyager | | saw\_buyin\_type\_id | INT64 | Type of the buy-in: 1 - Free of charge, 2 - Gamification points, 3 - Spin attempts | | is\_visitor\_mode | BOOL | If true, indicates that game is targeting visitors, otherwise only registered users | | saw\_skin\_id | INT64 | ID of the skin used by this template | | create\_by | INT64 | ID of backoffice user that created entity | | segment\_id | INT64 | ID of segment to which this template is restricted | ### dwh\_ext\_xxx.dm\_saw\_skin Contains information about mini-game skins. Primary key: **saw\_skin\_id** **Joins:** * saw\_skin\_id → [dm\_saw\_template](#dwh_ext_xxx.dm_saw_template) | Column | Type | Description | | --------------- | --------- | ---------------- | | label\_id | INT64 | | | create\_date | TIMESTAMP | Created at | | update\_date | TIMESTAMP | Updated at | | saw\_skin\_id | INT64 | ID of the skin | | saw\_skin\_name | STRING | Name of the skin | ### dwh\_ext\_xxx.dm\_shop\_item The table contains information about shop items. Primary key: **item\_id** **Joins:** * item\_id → [g\_shop\_transactions](#dwh_ext_xxx.g_shop_transactions) * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_bo_users) | Column | Type | Description | | ----------------------- | --------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label identifier | | create\_date | TIMESTAMP | Date and time when the item was created | | update\_date | TIMESTAMP | Date and time when the item was last updated | | item\_id | INT64 | ID of shop item | | item\_name | STRING | Name of item | | item\_type\_id | INT64 | Type of item: 1 - Bonus, 2 - Tangible, 3 - Mini-game attempt(s), 4 - Level upgrade, 5 - Prize Drop, 6 - Raffle Ticket, 7 - Gems/Diamonds | | item\_status\_id | INT64 | Status: 1 - Draft, 2 - Active, 3 - Archived | | reference\_item\_id | INT64 | ID of the referenced item depending on item\_type\_id: label\_bonus\_template\_id from dm\_bonus\_template, saw\_template\_id from dm\_saw\_template, or raffle\_id from dm\_raffle | | price\_type\_id | INT64 | Type of currency for the purchase: 0 - Points, 1 - Gems, 2 - Diamonds | | price | INT64 | The price of the item in the respective currency defined in the price\_type\_id field | | price\_before\_discount | INT64 | The price of the item before discount | | create\_by | INT64 | ID of backoffice user that created entity | ### dwh\_ext\_xxx.dm\_saw\_prize The table contains information about mini-game prizes. Primary key: **saw\_prize\_id** **Joins:** * saw\_template\_id → [dm\_saw\_template](#dwh_ext_xxx.dm_saw_template) * saw\_prize\_id → [g\_minigames](#dwh_ext_xxx.g_minigames) | Column | Type | Description | | ----------------- | --------- | ------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label identifier | | create\_date | TIMESTAMP | Date and time when the prize was created | | update\_date | TIMESTAMP | Date and time when the prize was last updated | | saw\_prize\_id | INT64 | ID of prize | | saw\_prize\_name | STRING | Name of prize | | saw\_template\_id | INT64 | ID of the mini-game template to which the prize belongs, see dm\_saw\_template | | prize\_type\_id | INT64 | Type of the prize: 1 - No Prize, 2 - Points, 3 - Bonus, 4 - Tangible, 5 - Spin in the mini-game, 6 - Jackpot, 7 - Level upgrade | | prize\_value | FLOAT64 | Value of prize in case it is a type of "gamification points" | | is\_surcharge | BOOL | If the prize is a surcharge (will be given when no other prizes are left) | ### dwh\_ext\_xxx.dm\_jp\_template Definitions of Jackpot templates, can be used in connection to jp\_bet fact table. Primary key: **jp\_template\_id** **Joins:** * jp\_template\_id → [jp\_bet](#dwh_ext_xxx.jp_bet) | Column | Type | Description | | ---------------------------------------- | --------- | ----------------------------------------------------------------------------------------- | | label\_id | INT64 | Label identifier | | create\_date | TIMESTAMP | Date and time when the jackpot template was created | | update\_date | TIMESTAMP | Date and time when the jackpot template was last updated | | jp\_template\_id | INT64 | ID of the jackpot template | | internal\_name | STRING | Name of the jackpot template | | jp\_type\_id | INT64 | Type: 1 - Multi-user, 2 - Personal | | initial\_amount | FLOAT64 | Seed amount | | min\_amount | FLOAT64 | Min amount at which jackpot can explode | | max\_amount | FLOAT64 | Max amount at which jackpot can explode | | contribution\_type | INT64 | Type of contribution: 1 - fixed amount, 2 - percentage from bet | | contribution\_value | FLOAT64 | Value of contribution in case of fixed amount | | contribution\_player\_percentage | FLOAT64 | Percentage of contribution taken from player. If 0, contribution is from marketing budget | | jp\_currency | STRING | Currency of jackpot | | jp\_status\_id | INT64 | Status: 1 - Active, 2 - Draft, 3 - Archived | | contribution\_money\_type | INT64 | Money type: 1 - Both real and bonus bets, 2 - Only real money | | next\_seed\_contribution\_perc | FLOAT64 | Percentage of contribution that will be held for next seed | | auto\_optin | BOOL | If users will be automatically opted-in with first bet | | collect\_next\_seed\_from\_contributions | BOOL | Indicates if next seed should be built from contributions | | create\_by | INT64 | ID of backoffice user that created entity | ### dwh\_ext\_xxx.dm\_tournament The table contains information about tournaments templates. Note that in most setups, tournaments are repetitive; for example, you can set up a tournament that runs every day. To handle such a concept, we have an entity called "**Instance**", also sometimes called "**Run**". The instance is created from the template and represents a unique tournament with start and end dates. See the **dm\_tournament\_instance** table below for details Primary key: **id** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_bo_users) * entry\_segment\_id → [dm\_segment](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_segment) * visibility\_segment\_id → [dm\_segment](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_segment) * id → [dm\_tournament\_instance](#dwh_ext_xxx.dm_tournament_instance) * id → [g\_tournament\_winners](#dwh_ext_xxx.g_tournament_winners) | Column | Type | Description | | ------------------------------ | --------- | ----------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label identifier | | create\_date | TIMESTAMP | Date and time when the tournament was created | | update\_date | TIMESTAMP | Date and time when the tournament was last updated | | id | INT64 | Primary identifier for the tournament | | name | STRING | Name of the tournament | | logic\_version\_id | INT64 | Logic version: 1 - Based on properties, 2 - Based on formula builder | | registration\_type\_id | INT64 | Registration type: 1 - Auto (all users), 2 - Free opt-in, 3 - Buy-in with Points, 4 - Optin & Manual Approval, 5 - Requires Qualification | | duration\_ms | INT64 | Duration of the tournament in milliseconds | | players\_min\_count | INT64 | Minimum number of players required for the tournament | | players\_max\_count | INT64 | Maximum number of players allowed, optional | | tournament\_type\_id | INT64 | Type of tournament: 1 - Scheduled | | tournament\_status\_id | INT64 | Status: 1 - Draft, 2 - Published, 3 - Paused, 4 - Archived | | last\_tournament\_create\_date | TIMESTAMP | Timestamp of when the last tournament instance was created | | show\_before\_start\_ms | INT64 | Time in milliseconds to show the tournament before start | | allow\_late\_registration | BOOL | Indicates if late registration is allowed | | create\_by | INT64 | ID of backoffice user that created entity | | score\_logic\_uiname | STRING | UI name of the score logic used | | entry\_segment\_id | INT64 | ID of segment for entry restriction | | visibility\_segment\_id | INT64 | ID of segment for visibility restriction | | is\_clan\_based | BOOL | True if the tournament is clan-based (players compete as clan members and a clan-level leaderboard is produced) | ### dwh\_ext\_xxx.dm\_tournament\_instance The table contains information about tournament instances. Instances are created from tournament templates and represent unique tournaments with specific start and end dates. Primary key: **tournament\_instance\_id** **Joins:** * tournament\_id → [dm\_tournament](#dwh_ext_xxx.dm_tournament) * tournament\_instance\_id → [g\_tournament\_winners](#dwh_ext_xxx.g_tournament_winners) | Column | Type | Description | | ------------------------- | --------- | ---------------------------------------------------------------------------------------- | | label\_id | INT64 | Label identifier | | create\_date | TIMESTAMP | Date and time when the instance was created | | update\_date | TIMESTAMP | Date and time when the instance was last updated | | tournament\_id | INT64 | Reference to dm\_tournament | | tournament\_instance\_id | INT64 | PK - unique identifier for the instance | | start\_date | TIMESTAMP | The date when tournament will start | | end\_date | TIMESTAMP | The date when tournament is planned to be finished | | registration\_start\_date | TIMESTAMP | The date from which registration to the tournament is open (could be before start\_date) | ### dwh\_ext\_xxx.dm\_raffle List of raffles Primary key: **raffle\_id** **Joins:** * raffle\_id → [raf\_tickets](#dwh_ext_xxx.raf_tickets) * raffle\_id → [raf\_won\_prizes](#dwh_ext_xxx.raf_won_prizes) | Column | Type | Description | | ------------------------ | --------- | --------------------------------------------------------- | | raffle\_id | INT64 | Unique identifier | | label\_id | INT64 | | | raf\_status\_id | INT64 | Status of the raffle: 1 - active, 2 - draft, 3 - archived | | internal\_name | STRING | Internal name | | create\_date | TIMESTAMP | Date and time when the raffle was created | | update\_date | TIMESTAMP | Date and time when the raffle definition was last updated | | start\_date | TIMESTAMP | Date and time from which the raffle is active | | end\_date | TIMESTAMP | Date and time when the raffle ended | | max\_tickets\_count | INT64 | Maximum number of tickets that can be given | | indicative\_ticket\_cost | FLOAT64 | Indicative cost of the ticket | ### dwh\_ext\_xxx.dm\_ach\_custom\_sections Represents custom sections (menu items) created in the gamification widget. Primary key: **section\_id** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_bo_users) * segment\_id → [dm\_segment](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_segment) | Column | Type | Description | | --------------------- | --------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | section\_id | INT64 | PK | | section\_type\_id | INT64 | Type: 1 - HTML Page, 2 - Missions, 3 - Tournaments, 4 - Liquid section, 5 - Mini-games, 6 - Missions Lootbox, 7 - Match-X & Quiz, 9 - Button/Link, 10 - Lootbox Weekly, 11 - Lootbox By Days, 12 - Treasure hunt, 13 - Raffle | | section\_name\_int | STRING | Internal name of section | | section\_name\_public | STRING | Public name of section (name in the menu) | | section\_status\_id | INT64 | Status: 1 - Active, 2 - Draft, 3 - Archived | | active\_from\_date | TIMESTAMP | If section is restricted by date/time, the 'from' value | | active\_till\_date | TIMESTAMP | If section is restricted by date/time, the 'till' value | | segment\_id | INT64 | ID of the users segment to which section is restricted | | label\_id | INT64 | Label identifier | | create\_date | TIMESTAMP | Date/time section was created | | update\_date | TIMESTAMP | Date/time section was updated last | | create\_by | INT64 | ID of the BackOffice user who created the section | ### dwh\_ext\_xxx.dm\_avatar Avatars definitions Primary key: **avatar\_real\_id** **Joins:** * create\_by → [dm\_bo\_users](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_bo_users) * avatar\_real\_id → [g\_avatar\_customized](#dwh_ext_xxx.g_avatar_customized) | Column | Type | Description | | ------------------------ | --------- | ---------------------------------------------- | | label\_id | INT64 | Label identifier | | create\_date | TIMESTAMP | Date and time when the avatar was created | | update\_date | TIMESTAMP | Date and time when the avatar was last updated | | avatar\_real\_id | INT64 | Primary key | | internal\_name | STRING | Name (internal) | | description | STRING | Description (public, EN) | | avatar\_url | STRING | URL | | is\_active | BOOL | Is active | | is\_default | BOOL | Is default | | hide\_until\_achieved | BOOL | Hide until achieved | | priority | INT64 | Priority | | avatar\_source\_type\_id | INT64 | Avatar source type ID | | active\_from\_date | TIMESTAMP | Active from date | | active\_till\_date | TIMESTAMP | Active till date | | create\_by | INT64 | Created by | ### dwh\_ext\_xxx.dm\_avatars\_prompt Avatars customization LLM prompts and their cost configuration. Primary key: **prompt\_id** | Column | Type | Description | | ------------------------ | --------- | ------------------------------------------------- | | prompt\_id | INT64 | Primary key | | label\_id | INT64 | Label identifier | | prompt\_name | STRING | Name | | cost\_currency\_type\_id | INT64 | Cost currency: 1 - points, 2 - gems, 3 - diamonds | | cost\_value | INT64 | Cost value | | create\_date | TIMESTAMP | Created at | | update\_date | TIMESTAMP | Updated at | | create\_by | INT64 | Created by | | update\_by | INT64 | Updated by | | is\_active | BOOL | Is active | | llm\_prompt | STRING | LLM prompt text | ## Fact tables ### dwh\_ext\_xxx.g\_ach\_completed Represents the history of all completed missions & badges. Partitioned by: create\_date, query to the table should always use create\_date in the WHERE clause. Primary key: **ach\_completed\_id** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * ach\_id → [dm\_ach](#dwh_ext_xxx.dm_ach) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) * ach\_completed\_id → [g\_ach\_claimed](#dwh_ext_xxx.g_ach_claimed) | Column | Type | Description | | ------------------ | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | ach\_id | INT64 | ID of the mission/badge | | create\_date | TIMESTAMP | The time when mission/badge completed | | is\_recurring | BOOL | Indicate if the original mission was recurring | | user\_id | INT64 | User ID in the Smartico system | | user\_ext\_id | STRING | External ID of the user | | crm\_brand\_id | INT64 | ID of the brand in the Smartico system | | ach\_completed\_id | INT64 | Unique ID of the completion fact (except for when the mission type is "Recurring upon completion". Unique ID for "Recurring upon completion" contains the same value because it's being used for tracking/counting of "Max completion count" from the mission configuration) | Returns a count of users who completed missions in the last 3 days ```sql SELECT A.internal_name, A.ach_id, count(*) as completed_users_count FROM dwh_ext_XXX.g_ach_completed M INNER JOIN dwh_ext_XXX.dm_ach A ON M.ach_id = A.ach_id WHERE M.create_date > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY) GROUP BY A.internal_name, A.ach_id ``` ### dwh\_ext\_xxx.g\_ach\_optins Represents the history of all opt-ins in the missions that require explicit opt-ins to participate. Partitioned by: create\_date, query to the table should always use create\_date in the WHERE clause. Primary key: **ach\_id, user\_id, create\_date** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) * ach\_id → [dm\_ach](#dwh_ext_xxx.dm_ach) | Column | Type | Description | | ------------------- | --------- | ------------------------------------------------------------------------------ | | label\_id | INT64 | Label ID | | ach\_id | INT64 | ID of the mission | | create\_date | TIMESTAMP | The time when user opted in the mission | | user\_id | INT64 | User ID in the Smartico system | | next\_reset\_date | TIMESTAMP | For recurring mission when the mission progress is going to be reset. Optional | | ach\_unlock\_date | TIMESTAMP | When user unlocked the mission for which he is opting-in. Optional | | limit\_in\_time\_ms | INT64 | For the missions limited in time, how much time is left to complete mission | | user\_ext\_id | STRING | External ID of the user | | crm\_brand\_id | INT64 | ID of the brand in the Smartico system | Returns missions active in the past 30 days with counts of opted in and completed users ```sql SELECT A.internal_name, A.ach_id, COUNT(DISTINCT O.user_id) AS opted_in_users_count, COUNT(DISTINCT C.user_ext_id) AS completed_users_count FROM dwh_ext_XXX.dm_ach A LEFT JOIN dwh_ext_XXX.g_ach_optins O ON A.ach_id = O.ach_id AND O.create_date > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) LEFT JOIN dwh_ext_XXX.g_ach_completed C ON A.ach_id = C.ach_id WHERE A.requires_optin = TRUE AND (C.create_date > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) OR O.create_date > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)) GROUP BY A.internal_name, A.ach_id LIMIT 1000 ``` ### dwh\_ext\_xxx.g\_ach\_claimed Represents facts of prizes claiming for the missions for which a claim is required. Partitioned by: create\_date, query to the table should always use create\_date in the WHERE clause. Primary key: **ach\_completed\_id** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) * ach\_id → [dm\_ach](#dwh_ext_xxx.dm_ach) * ach\_completed\_id → [g\_ach\_completed](#dwh_ext_xxx.g_ach_completed) | Column | Type | Description | | ------------------ | --------- | ----------------------------------------------------------------- | | label\_id | INT64 | Label ID | | ach\_id | INT64 | ID of the mission | | create\_date | TIMESTAMP | The time when user claimed the mission | | user\_id | INT64 | User ID in the Smartico system | | user\_ext\_id | STRING | External ID of the user | | crm\_brand\_id | INT64 | ID of the brand in the Smartico system | | ach\_completed\_id | INT64 | Reference to g\_ach\_completed that represents completed missions | Read more about "claiming" in the [Missions guide](/welcome/products/gamification-basics/missions.md#manual-reward-claiming-in-missions). ### dwh\_ext\_xxx.g\_avatar\_customized Represents facts of avatar customizations (LLM-generated avatars). One row per customization event. Partitioned by: dt, query to the table should always use dt in the WHERE clause. Primary key: **N/A** Partitioned by: **dt** Data availability: **last 1080 days by dt** **Joins:** * prompt\_id → [dm\_avatars\_prompt](#dwh_ext_xxx.dm_avatars_prompt) * real\_avatar\_id → [dm\_avatar](#dwh_ext_xxx.dm_avatar) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) | Column | Type | Description | | ------------------------ | --------- | --------------------------------------------------- | | label\_id | INT64 | Label ID | | user\_id | INT64 | User ID in the Smartico system | | dt | TIMESTAMP | When the customization occurred | | real\_avatar\_id | INT64 | Resulting avatar ID (dm\_avatar.avatar\_real\_id) | | prompt\_id | INT64 | Avatar prompt used (dm\_avatars\_prompt.prompt\_id) | | cost\_currency\_type\_id | INT64 | Cost currency: 0 - points, 1 - gems, 2 - diamonds | | cost\_value | INT64 | Cost charged for the customization | | time\_took\_ms | INT64 | Duration of the request in milliseconds | ### dwh\_ext\_xxx.g\_ach\_points\_change\_log Represents all changes in the gamification points (wallet balance, leaderboard, and level points). Partitioned by: `create_date` — every query MUST include `create_date` in the WHERE clause. **IMPORTANT — Column semantics (changed \~April 2026)** * `points_collected` — the ACTUAL change applied to the wallet (`balance_after` − `balance_before`). May be 0 or less than requested when the wallet did not have sufficient balance (e.g. SET to 0). For leaderboard and level point events, this field reflects only the wallet delta and may be NULL/0 even when points were counted toward the leaderboard or level (see `affects_leaderboard` / `affects_level`). * `points_requested` — the ORIGINAL amount that was requested (equivalent to the old meaning of `points_collected` before April 2026). Use this column for "how many points were intended to be awarded/deducted". Use `affects_current_balance`, `affects_leaderboard`, and `affects_level` to understand which scoring dimensions were touched. Primary key: **N/A** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * source\_type\_id → [dm\_ach\_points\_change\_source](#dwh_ext_xxx.dm_ach_points_change_source) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) | Column | Type | Description | | ----------------------------- | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | create\_date | TIMESTAMP | The time of transaction | | user\_ext\_id | STRING | External ID of the user | | user\_id | INT64 | User ID in the Smartico system | | crm\_brand\_id | INT64 | ID of the brand in the Smartico system | | source\_type\_id | INT64 | Type of the source of the change. Join to dm\_ach\_points\_change\_source for names. Examples: 1=Campaign, 2=Mission Task, 3=Mission Completed, 5=Store Purchase, 6=Manual Adjustment, 12=Automation Rule, 18=API, 23=Clan (entry fee deduction when a user joins a clan). | | source\_reference\_id | INT64 | ID of the specific sub-source within the source type (e.g. task\_id when source\_type\_id=2) | | source\_entity\_id | INT64 | ID of the entity that triggered the transaction (e.g. ach\_id for mission, campaign\_id for campaign, clan\_id etc.) | | source\_root\_id | INT64 | Root entity in a trigger chain (e.g. automation rule that triggered a campaign) | | operation\_type | INT64 | Direction of the operation: 1 = ADD, -1 = DEDUCT | | points\_requested | INT64 | The originally requested point amount. This is what was asked to be added or deducted, before any wallet-cap logic is applied. Equivalent to the pre-April-2026 meaning of points\_collected. | | points\_collected | INT64 | The ACTUAL net change applied to the wallet balance (user\_points\_balance − user\_points\_balance\_before). Positive = earned, negative = spent. May differ from points\_requested when a deduction was capped by available balance, or when the event only affects leaderboard/level (not the spendable wallet). Introduced as actual-amount tracking in April 2026. | | affects\_current\_balance | BOOL | True if this transaction changed the user's spendable points\_balance. False for events that only affect leaderboard or level scoring. | | affects\_leaderboard | BOOL | True if these points count toward the user's leaderboard score | | affects\_level | BOOL | True if these points count toward the user's level (points\_ever) | | user\_points\_balance\_before | INT64 | User's spendable points\_balance immediately before this transaction | | user\_points\_balance | INT64 | User's spendable points\_balance immediately after this transaction | | user\_points\_ever | INT64 | User's lifetime points\_ever after this transaction (used for level thresholds) | Points flow by source in the last 3 days — using points\_requested (intended amounts, consistent across all time) ```sql SELECT S.source_type_name, SUM(CASE WHEN L.points_requested > 0 THEN L.points_requested ELSE 0 END) as points_credit_requested, SUM(CASE WHEN L.points_requested < 0 THEN -1 * L.points_requested ELSE 0 END) as points_debit_requested, SUM(CASE WHEN L.points_collected > 0 THEN L.points_collected ELSE 0 END) as points_credit_actual, SUM(CASE WHEN L.points_collected < 0 THEN -1 * L.points_collected ELSE 0 END) as points_debit_actual FROM dwh_ext_XXX.g_ach_points_change_log L INNER JOIN dwh_ext_XXX.dm_ach_points_change_source S ON L.source_type_id = S.source_type_id WHERE L.create_date > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY) GROUP BY S.source_type_name ``` Wallet-only transactions in the last 7 days (excludes pure leaderboard/level events) ```sql SELECT L.create_date, L.user_ext_id, S.source_type_name, L.points_requested, L.points_collected, L.user_points_balance_before, L.user_points_balance FROM dwh_ext_XXX.g_ach_points_change_log L INNER JOIN dwh_ext_XXX.dm_ach_points_change_source S ON L.source_type_id = S.source_type_id WHERE L.create_date > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND L.affects_current_balance = TRUE ORDER BY L.create_date DESC LIMIT 1000 ``` ### dwh\_ext\_xxx.g\_clan\_ranks Periodic rank-recalculation snapshots for clans. Each row captures a clan's F1-style cumulative rating points and its global rank at a single recalculation run, enabling time-series analysis of clan standing and rank trajectory. Partitioned by: `calc_date` (DAY) — every query MUST include `calc_date` in the WHERE clause. Populated from the periodic clan rank recalculation job. 1 = best clan in the label. Primary key: **calc\_date + label\_id + clan\_id** Partitioned by: **calc\_date** Data availability: **last 1080 days by calc\_date** **Joins:** * clan\_id → [dm\_ach\_clans](#dwh_ext_xxx.dm_ach_clans) | Column | Type | Description | | -------------- | --------- | ------------------------------------------------------------------------- | | calc\_date | TIMESTAMP | When the rank recalculation ran (partition key) | | label\_id | INT64 | Label ID | | clan\_id | INT64 | Clan ID | | rank\_points | INT64 | F1-style cumulative rating points for the clan at the time of calculation | | members\_count | INT64 | Active member count at the time of calculation | | global\_rank | INT64 | DENSE\_RANK position across all clans in this label (1 = best) | Clan rank trajectory over time for a single label ```sql SELECT calc_date, clan_id, rank_points, members_count, global_rank FROM dwh_ext_XXX.g_clan_ranks WHERE label_id = XXX AND calc_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) ORDER BY clan_id, calc_date ``` ### dwh\_ext\_xxx.g\_gems\_diamonds\_change\_log Represents all changes in the gems and diamonds. Partitioned by: create\_date, query to the table should always use create\_date in the WHERE clause. Primary key: **N/A** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * source\_type\_id → [dm\_ach\_points\_change\_source](#dwh_ext_xxx.dm_ach_points_change_source) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) | Column | Type | Description | | ------------------ | --------- | --------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | create\_date | TIMESTAMP | The time of transaction | | source\_type\_id | INT64 | Type of the source of the change, refer to dm\_ach\_points\_change\_source | | source\_entity\_id | INT64 | ID of the entity that triggered transaction, the lookup table depends on the source\_type\_id | | amount | INT64 | Amount of transaction, could be negative in case of deduction | | balance | INT64 | Balance value after transaction | | type | INT64 | Type of updated balance: 1 - gems, 2 - diamonds | | user\_ext\_id | STRING | External ID of the user | | user\_id | INT64 | User ID in the Smartico system | | crm\_brand\_id | INT64 | ID of the brand in the Smartico system | ### dwh\_ext\_xxx.g\_minigames Represents mini-game gameplay results. Partitioned by: acknowledge\_date, query to the table should always use acknowledge\_date in the WHERE clause. Primary key: **spin\_id** Partitioned by: **acknowledge\_date** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * saw\_template\_id → [dm\_saw\_template](#dwh_ext_xxx.dm_saw_template) * saw\_prize\_id → [dm\_saw\_prize](#dwh_ext_xxx.dm_saw_prize) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) | Column | Type | Description | | ------------------- | --------- | ------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | create\_date | TIMESTAMP | The time of transaction, when the game was played | | saw\_template\_id | INT64 | ID of mini-game template, refer to dm\_saw\_template | | saw\_prize\_id | INT64 | ID of the won prize, refer to dm\_saw\_prize | | prize\_type\_id | INT64 | Type of the prize: 1 - No Prize, 2 - Points, 3 - Bonus, 4 - Tangible, 5 - Spin in the mini-game, 6 - Jackpot, 7 - Level upgrade | | user\_ext\_id | STRING | External ID of the user | | user\_id | INT64 | User ID in the Smartico system | | crm\_brand\_id | INT64 | ID of the brand in the Smartico system | | buyin\_cost\_points | INT64 | The price of a mini-game attempt paid by the user for the mini-games that cost gamification points | | prize\_amount | FLOAT64 | Winning amount, in case the prize is the type of Jackpot or Points | | pool\_after | INT64 | The number of the items of this prize that is left in the stock after the user wins the prize identified by saw\_prize\_id | | spin\_id | INT64 | Unique ID of the game play fact | | saw\_prize\_note | STRING | Reserved for future use | | acknowledge\_date | TIMESTAMP | The time when the prize was claimed (issued to the player) | ### dwh\_ext\_xxx.g\_tournament\_analytics The table contains information about various tournaments and relevant analytics events. Each tournament can be represented in the table multiple times, where each time it reflects different tournament instances (different runs). For a more complete picture, the "**tournament\_id**" coresponds directly with the "**id**" in dwh\_ext\_xxx.dm\_tournament. Note that there is data nested under **progress**. Primary key: **event\_id** Partitioned by: **event\_time** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) * clan\_id → [dm\_ach\_clans](#dwh_ext_xxx.dm_ach_clans) | Column | Type | Description | | --------------- | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | event\_id | INT64 | ID of the associated tournament analytics event | | event\_time | TIMESTAMP | Time when the analytics event was recorded | | user\_id | INT64 | ID of the user associated with the event | | event\_type\_id | INT64 | ID representing the type of the analytics event: -1 - registration, -2 - manual/operator score adjustment, -3 - opt-in pending qualification, -4 - qualified pending opt-in, -5 - registration (auto opt-in), >0 - other events that are part of the progress | | progress | RECORD | Repeated record indicating progress details related to the event. The record has following fields: tournament\_id: INT64, tournament\_instance\_id: INT64, scores: INT64, total\_scores: INT64, rule\_id: INT64 | | crm\_brand\_id | INT64 | ID of the brand in the CRM system | | user\_ext\_id | STRING | External ID of the user associated with the analytics event | | clan\_id | INT64 | Clan the user belonged to at event time. NULL for non-clan tournaments (dm\_tournament.is\_clan\_based = false) | Comprehensive summary of each tournament instance with score distributions and rule applications. It allows for easy comparison between different tournament instances and can help identify patterns in performance and rule usage across various tournaments. The results are ordered by tournament\_id and then tournament\_instance\_id, making it easy to view all instances of a particular tournament together. ```sql WITH unnested_data AS ( SELECT tournament_id, tournament_instance_id, p.scores, p.total_scores, p.rule_id FROM dwh_ext_XXX.g_tournament_analytics, UNNEST(progress) AS p WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) ) SELECT tournament_id, tournament_instance_id, AVG(scores) AS avg_scores, MAX(scores) AS max_scores, MIN(scores) AS min_scores, AVG(total_scores) AS avg_total_scores, MAX(total_scores) AS max_total_scores, MIN(total_scores) AS min_total_scores, COUNT(DISTINCT rule_id) AS unique_rules_applied FROM unnested_data GROUP BY tournament_id, tournament_instance_id ORDER BY tournament_id, tournament_instance_id ``` ### dwh\_ext\_xxx.g\_tournament\_winners Represents all the data for the users that had won tournaments. Partitioned by: create\_date, query to the table should always use create\_date in the WHERE clause. Primary key: **tournament\_instance\_id + user\_id** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) * tournament\_id → [dm\_tournament](#dwh_ext_xxx.dm_tournament) * tournament\_instance\_id → [dm\_tournament\_instance](#dwh_ext_xxx.dm_tournament_instance) * activity\_type\_id → [dm\_activity\_type](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_activity_type) * clan\_id → [dm\_ach\_clans](#dwh_ext_xxx.dm_ach_clans) | Column | Type | Description | | ------------------------ | --------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | | create\_date | TIMESTAMP | The date of the transaction | | label\_id | INT64 | Unique id of the label | | user\_id | INT64 | Unique id of user in the Smartico system | | user\_ext\_id | STRING | Unique id of user in the integrated system | | crm\_brand\_id | INT64 | ID of the brand in the Smartico system | | tournament\_id | INT64 | The ID of the specific Tournament. Refer to the dm\_tournament | | tournament\_instance\_id | INT64 | The ID of the Instance of the Tournament. Refer to the dm\_tournament\_instance | | place | INT64 | The Place in which the Winner ended up on (player's individual place) | | activity\_type\_id | INT64 | Type of activity being performed. Check for dm\_activity\_type for reference | | activity\_detailed\_json | JSON | Holds the additional information as a continuous JSON string. Specifically the data on the Bonus Template and the Value, associated with the Tournament win | | clan\_id | INT64 | Clan the user belonged to at prize-distribution time. NULL for non-clan tournaments (dm\_tournament.is\_clan\_based = false). | | clan\_place | INT64 | Clan's finishing rank in this tournament instance (DENSE\_RANK over clan total scores). NULL for non-clan tournaments. | | player\_rank\_in\_clan | INT64 | Player's rank within their clan for this tournament instance (DENSE\_RANK over player scores within the clan). NULL for non-clan tournaments. | | clan\_total\_score | INT64 | Clan's aggregate score in this tournament instance. NULL for non-clan tournaments. | | clan\_member\_count | INT64 | Number of clan members who participated in this tournament instance. NULL for non-clan tournaments. | Shows the list of 1st place Winners of all the Tournaments ```sql SELECT tw.user_ext_id, tw.tournament_id, tw.tournament_instance_id, dt.name as tournament_name, dt.tournament_status_id, tw.place FROM dwh_ext_XXX.g_tournament_winners tw INNER JOIN dwh_ext_XXX.dm_tournament dt ON tw.tournament_id = dt.id WHERE tw.place = 1 ``` ### dwh\_ext\_xxx.g\_shop\_transactions Represents all purchases in the store. Partitioned by: create\_date, query to the table should always use create\_date in the WHERE clause. Primary key: **transaction\_id** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * shop\_item\_id → [dm\_shop\_item](#dwh_ext_xxx.dm_shop_item) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) | Column | Type | Description | | --------------- | --------- | ------------------------------------------------ | | label\_id | INT64 | Label ID | | transaction\_id | INT64 | Unique ID of transaction | | create\_date | TIMESTAMP | The time of transaction | | shop\_item\_id | INT64 | ID of the shop item, refer to dm\_shop\_item | | points\_amount | INT64 | The price of the item in the gamification points | | user\_ext\_id | STRING | External ID of the user | | user\_id | INT64 | User ID in the Smartico system | | crm\_brand\_id | INT64 | ID of the brand in the Smartico system | ### dwh\_ext\_xxx.g\_ux Represents the UI interactions of users with the Gamification widget. Partitioned by: create\_date, query to the table should always use create\_date in the WHERE clause. Primary key: **N/A** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#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 | | ------------------- | --------- | ----------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Unique id of the label | | crm\_brand\_id | INT64 | Unique id of the brand. Relevant only for multi-brand setup | | create\_date | TIMESTAMP | Date that represents exactly when the user performed an action (opened a modal or navigated to a some menu section) | | user\_ext\_id | STRING | Unique id of user in the integrated system | | user\_id | INT64 | Unique id of user in the Smartico system | | interface\_type | INT64 | Device type of the current user session: 0 - Mobile, 1 - Desktop | | screen\_name\_id | INT64 | Identifier of the screen the user has navigated to | | screen\_subname\_id | INT64 | Identifier of the category/section of the screen the user has navigated to | | custom\_section\_id | INT64 | Represents custom section id if the current screen is opened in the context of the custom section | | entity\_id | INT64 | Represents the ID of the entity the user has opened on the current screen. For example: a specific item in the store or a particular tournament | | os\_type\_id | INT64 | OS type: 1 - Windows, 2 - MacOS, 3 - iOS, 4 - Android, 5 - Linux, 6 - Other | | device\_type\_id | INT64 | Device type: 1 - MOBILE, 2 - DESKTOP, 3 - MOBILE\_NATIVE | | browser\_type\_id | INT64 | Browser type: 1 - Edge, 2 - Chrome, 3 - Firefox, 4 - Safari, 5 - Opera, 6 - Other | **ScreenNameIds:** | ID | Name of screen | | -- | ---------------------------------------------------------------------------------------------------------- | | 1 | Overview / Home | | 2 | Missions. screen\_subname\_id: 0 - overview, 2 - available, 3 - locked, 4 - completed, 5 - missed | | 3 | Leaderboards. screen\_subname\_id: 1 - daily, 2 - weekly, 3 - monthly | | 4 | Tournaments list. screen\_subname\_id: 0 - overview, 3 - in progress, 4 - finished, 5 - my tournaments | | 5 | Tournament lobby (details). screen\_subname\_id: 1 - rules, 2 - Prizes, 3 - Leaderboard, 4 - Related games | | 6 | Levels (grid presentation) | | 7 | Levels (map presentation) | | 8 | Badges | | 9 | Bonuses. screen\_subname\_id: 2 - pending, 3 - redeemed | | 10 | Spin a Wheel | | 11 | Store | | 12 | Store (purchase history) | | 13 | Inbox. screen\_subname\_id: 1 - All message, 2 - Favorite | | 14 | Match X | | 15 | Quiz | | 16 | Jackpots | | 17 | Lootbox | | 18 | Custom section | | 19 | Inbox preview | | 20 | Lootbox, weekdays | | 21 | Lootbox, calendar days | | 22 | Treasure hunt game | | 23 | Liquid section | | 31 | Mission item modal; entity\_id = ID of mission | | 32 | Level item modal; entity\_id = ID of level | | 33 | Badge item modal; entity\_id = ID of badge | | 34 | Store item modal; entity\_id = ID of store item | | 35 | Bonus item modal | | 36 | Jackpot item modal; entity\_id = ID of jackpot template | | 37 | Username change modal | | 38 | Avatar change modal | ### dwh\_ext\_xxx.g\_ach\_levels\_changed The table represents the log of level upgrades/downgrades for each user. Primary key: **level\_change\_id** Partitioned by: **fact\_date** Data availability: **last 1080 days by fact\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * from\_level\_id → [dm\_ach\_level](#dwh_ext_xxx.dm_ach_level) * to\_level\_id → [dm\_ach\_level](#dwh_ext_xxx.dm_ach_level) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) | Column | Type | Description | | ------------------------------------ | --------- | -------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | fact\_date | TIMESTAMP | The time of transaction | | level\_change\_id | INT64 | Unique ID of the level change fact | | from\_level\_id | INT64 | ID of the level before transaction | | to\_level\_id | INT64 | ID of the level after transaction | | points\_change\_source\_id | INT64 | If level change is triggered by points balance change, the source of points change. See points\_change\_source\_id values in post-table note | | points\_collected\_on\_level\_change | INT64 | If level change is triggered by points balance change, the amount of points added to the user at the moment of upgrade | | user\_points\_ever | INT64 | "Points ever" balance of user after level change | | user\_ext\_id | STRING | External ID of the user | | user\_id | INT64 | User ID in the Smartico system | | crm\_brand\_id | INT64 | ID of the brand in the Smartico system | **points\_change\_source\_id** possible values (when level change is triggered by points balance change): Journey = 1, AchievementTaskCompletion = 2, AchievementCompletion = 3, LevelsStructureChange = 4, StorePurchase = 5, ManualAdjustment = 6, Leaderboard = 7, Tournament = 11, AutomationRule = 12, TournamentRegistration = 13, TournamentRegistrationCancellation = 14, RefundPoints = 15, PlayMiniGame = 16, WinMiniGame = 17 ### dwh\_ext\_xxx.jp\_bet The table represents the bets participated in Jackpot. Can be used in connection with dm\_jp\_template table. Primary key: **transaction\_id\_ext + jp\_template\_id** Partitioned by: **bet\_date** Data availability: **last 1080 days by bet\_date** **Joins:** * crm\_brand\_id → [dm\_brand](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/FwlimpzjSx9FROVnX5hH#dwh_ext_xxx.dm_brand) * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) * jp\_template\_id → [dm\_jp\_template](#dwh_ext_xxx.dm_jp_template) | Column | Type | Description | | ---------------------------------------------- | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | bet\_received\_date | TIMESTAMP | Time when bet is received by Smartico | | bet\_date | TIMESTAMP | The time when bet is processed and pot calculated | | bet\_original\_date | TIMESTAMP | The original time of bet as it happened on wallet/platform side | | user\_id | INT64 | ID of user in Smartico system | | crm\_brand\_id | INT64 | ID of brand in Smartico system to which user belongs | | user\_ext\_id | STRING | External ID of the user | | transaction\_id\_ext | STRING | Unique ID of bet transaction as reported by wallet/platform | | game\_ext\_id | STRING | ID of game | | jp\_template\_id | INT64 | ID of jackpot template | | jp\_pot\_id | INT64 | ID of pot | | bet\_original\_amount\_bonus | FLOAT64 | Original amount of bonus money part of bet | | bet\_original\_amount\_real | FLOAT64 | Original amount of real money part of bet | | bet\_original\_amount\_bonus\_jp\_currency | FLOAT64 | Original amount of bonus money part of bet in Jackpot template currency | | bet\_original\_amount\_real\_jp\_currency | FLOAT64 | Original amount of real money part of bet in Jackpot template currency | | bet\_original\_currency | STRING | Original currency of bet | | contribution\_amount\_bonus\_bet\_currency | FLOAT64 | Bonus part contribution calculated from bonus money of bet in bet original currency | | contribution\_amount\_bonus\_jp\_currency | FLOAT64 | Bonus part contribution calculated from bonus money of bet in jackpot currency | | contribution\_amount\_real\_bet\_currency | FLOAT64 | Real part contribution calculated from real money of bet in bet original currency | | contribution\_amount\_real\_jp\_currency | FLOAT64 | Real part contribution calculated from real money of bet in jackpot currency | | contribution\_amount\_next\_seed\_jp\_currency | FLOAT64 | Contribution reserved for the next seed (if enabled) | | pot\_amount\_bonus\_after | FLOAT64 | Pot amount, bonus part, after bet | | pot\_amount\_real\_after | FLOAT64 | Pot amount, real part, after bet | | pot\_amount\_seed\_after | FLOAT64 | Seed of next pot | | pot\_amount\_seed\_current | FLOAT64 | Seed of current pot | | pot\_amount\_total\_after | FLOAT64 | Total pot = bonus pot + real pot + seed (current) | | winning\_position | INT64 | In case bet was winning, the position of player in winning table (for now possible value only 1, as Smartico does not support multiple winners per pot as of May 2025) | | winning\_amount\_jp\_currency | FLOAT64 | Winning amount of player in Jackpot currency | | winning\_amount\_wallet\_currency | FLOAT64 | Winning amount of player in wallet currency of player | ### dwh\_ext\_xxx.raf\_tickets The table represents tickets given to user. Primary key: **ticket\_id\_start + ticket\_id\_end** Partitioned by: **create\_date** Data availability: **last 1080 days by create\_date** **Joins:** * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) * raffle\_id → [dm\_raffle](#dwh_ext_xxx.dm_raffle) * root\_audience\_id → [dm\_audience](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_audience) * source\_type\_id → [dm\_ach\_points\_change\_source](#dwh_ext_xxx.dm_ach_points_change_source) | Column | Type | Description | | ------------------ | --------- | -------------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | user\_id | INT64 | ID of user | | create\_date | TIMESTAMP | Date/time when ticket was given | | ticket\_id\_start | INT64 | Starting ID of the ticket range | | ticket\_id\_end | INT64 | Ending ID of the ticket range | | raffle\_id | INT64 | ID of raffle for which ticket was given | | source\_type\_id | INT64 | ID of source type: 1 - Campaign, 3 - Mission, 5 - Store, 6 - Manual adjustment, 11 - Tournament, 12 - Automation rule, 17 - Mini-game, 21 - Raffle | | source\_entity\_id | INT64 | ID of entity related to the source type from where ticket was given | | root\_audience\_id | INT64 | In case source was campaign, the ID of campaign (check dm\_audience table) | | engagement\_uid | STRING | In case source was campaign, the ID of engagement (check j\_engagements table) | Example: users with number of tickets for last 30 days for raffle ID 55, with raffle name ```sql SELECT u.user_ext_id, r.internal_name AS raffle_name, SUM(t.ticket_id_end - t.ticket_id_start + 1) AS number_of_tickets FROM dwh_ext_xxx.raf_tickets AS t JOIN dwh_ext_xxx.dm_raffle AS r ON t.raffle_id = r.raffle_id JOIN dwh_ext_xxx.j_user AS u ON t.user_id = u.user_id WHERE t.raffle_id = 55 AND t.create_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) GROUP BY u.user_ext_id, r.internal_name ``` **source\_type\_id** possible values: 1 - Campaign, 3 - Mission, 5 - Store, 6 - Manual adjustment, 11 - Tournament, 12 - Automation rule, 17 - Mini-game, 21 - Raffle ### dwh\_ext\_xxx.raf\_won\_prizes The table represents the prizes won in the raffle. Partitioned by fact\_date (note the behavior related to the claimed\_date explained below). Primary key: **N/A** Partitioned by: **fact\_date** Data availability: **last 1080 days by fact\_date** **Joins:** * user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details) * raffle\_id → [dm\_raffle](#dwh_ext_xxx.dm_raffle) * ticket\_id → [raf\_tickets](#dwh_ext_xxx.raf_tickets) | Column | Type | Description | | -------------------- | --------- | ----------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | Label ID | | fact\_date | TIMESTAMP | Date/time when prize was won (or claim date for the claimable prizes at the moment of claiming) | | raf\_won\_id | INT64 | ID identifies the winning fact. Note that there can be 2 records with same ID in case of "claimable" prizes | | raffle\_id | INT64 | ID of raffle, see dm\_raffle | | draw\_id | INT64 | ID of draw | | raffle\_run\_id | INT64 | ID of the run within draw | | prize\_id | INT64 | ID of prize | | user\_id | INT64 | ID of user that won prize | | ticket\_id | INT64 | ID of ticket that won prize | | ticket\_create\_date | TIMESTAMP | Date when ticket was given to the user | | claimed\_date | TIMESTAMP | Date when prize was claimed, in case prize is set to require claiming. Nullable | Note for the prizes that require claiming: * There can be 2 records with same raf\_won\_id * 1st record indicates when the prize is won; fact\_date represents the date when draw was executed and prize defined * 2nd record indicates when user claimed the prize. It has fact\_date with same value as claimed\_date, indicating when the prize was claimed by user and issued --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-gamification-views.md?ask= ``` The question should be specific, self-contained, and written in natural language. 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.