◾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

• level_id → 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

• source_type_id → g_gems_diamonds_change_log

• source_type_id → 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

• ach_id → g_ach_optins

• ach_id → g_ach_claimed

• ach_id → g_ach_completed

• ach_id → dm_ach_task

• ach_id → 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	The mission/badge behavioural type as configured at creation (same enum ids as ach_status_id). FIXED at creation time — does NOT change when the entity is later archived or its status otherwise transitions. Use ach_status_id (below) to filter by CURRENT status.
ach_status_id	INT64	Current operational status: 1 - Draft, 2 - Available Unlocked, 3 - Available Locked, 4 - Archived, 5 - Featured Manually, 6 - Featured by AI, 7 - Recurring, 8 - Recurring upon completion. THIS is the field that changes on archive — filter ach_status_id != 4 (or IN (2,3,5,7,8)) to exclude archived missions.
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

• activity_type_id → dm_activity_type

• label_bonus_template_id → dm_bonus_template

• audience_id → dm_audience

• another_achievement_id → dm_ach

• saw_template_id → 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

• clan_id → 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

• event_type_id → dm_event_type

• create_by → 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

• segment_id → dm_segment

• saw_skin_id → dm_saw_skin

• saw_template_id → dm_saw_prize

• saw_template_id → g_minigames

• saw_template_id → 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_lp_template

Vibe Studio landing pages / gamification-block widgets: global templates and the concrete pages cloned & published from them. Dimension side of the LP-analytics star schema — joins to the g_lp_analytics fact table on lp_template_id.

Primary key: lp_template_id

Joins:

• lp_template_id → g_lp_analytics

• lp_original_template_id → dm_lp_template

• create_by → dm_bo_users

Column	Type	Description
lp_template_id	INT64	Integer PK of cjm.lp_templates. JOIN KEY to g_lp_analytics.lp_template_id.
label_id	INT64	Owning label
create_date	TIMESTAMP	When the template/page was created
update_date	TIMESTAMP	When the template/page was last edited
lp_template_uuid	STRING	Stable UUID; what the client sends and what the IDE session is keyed on. Server resolves it to lp_template_id for fact rows.
template_name	STRING	Human name of the template / page
is_global_template	BOOL	TRUE = reusable global template; FALSE = a concrete entity cloned from one (lp_original_template_id points at the global).
lp_original_template_id	INT64	For a concrete page: the global template it was cloned from (self-FK to lp_template_id). NULL for global templates.
lp_ui_type_id	INT64	Effective UI surface (own value, else inherited from the original): 1 - Landing, 2 - Widget (Gamification Block), 3 - Promotion, 4 - CustomGame
lp_ui_type_name	STRING	Readable lp_ui_type_id: Landing / Widget / Promotion / CustomGame
lp_template_type_id	INT64	Build/runtime template type, own value else inherited from the original
lp_template_type_code	STRING	Build template code: lp-html, lp-react, smr-react, smr-react-game
lp_template_type_name	STRING	Readable build template name
slug_name	STRING	Published sub-domain name (nullable)
lp_domain_id	INT64	ID of the domain the page publishes under (widgets & custom games use the fixed widgets domain)
is_not_in_root	BOOL	Published under sub-domain rather than main domain
last_published_at	TIMESTAMP	Last time the page was published live (NULL if never published or the env lacks the migration). Useful to bound g_lp_analytics events to post-publish traffic.
is_deleted	BOOL	TRUE = the page was deleted in the back office. Deletion is a soft flag and reversible: the row, its code and any already-published output all survive, so deleted pages KEEP their historical g_lp_analytics events and stay in this dimension. Add "AND NOT is_deleted" for a live-pages-only count; leave it off when attributing past traffic.
create_by	INT64	BO user that created it
update_by	INT64	BO user that last edited it

Top landing pages by views in the last 30 days, with UI type and build template (dimension -> fact).

SELECT
	t.lp_template_id,
	t.template_name,
	t.lp_ui_type_name,
	t.lp_template_type_code,
	COUNTIF(a.event_type_id = 1)        AS page_views,
	COUNT(DISTINCT a.session_uuid)      AS sessions,
	COUNT(DISTINCT a.visitor_id)        AS unique_visitors
FROM dwh_ext_XXX.dm_lp_template AS t
JOIN dwh_ext_XXX.g_lp_analytics AS a
	ON a.lp_template_id = t.lp_template_id
WHERE a.create_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY 1, 2, 3, 4
ORDER BY page_views DESC
LIMIT 50;

Decode recent CTA clicks with the page name / UI type (fact -> dimension, the "and back" direction).

SELECT
	a.create_date,
	a.event_type_id,
	a.route_path,
	a.target_text,
	a.target_link,
	t.template_name,
	t.lp_ui_type_name
FROM dwh_ext_XXX.g_lp_analytics AS a
JOIN dwh_ext_XXX.dm_lp_template AS t
	ON t.lp_template_id = a.lp_template_id
WHERE a.event_type_id IN (6, 106)   -- PageCtaClick / WidgetCtaClick
	AND a.create_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY a.create_date DESC
LIMIT 200;

View -> CTA click-through rate per page, rolled up to the global template it was cloned from (self-join + fact join).

SELECT
	g.template_name                                                                       AS global_template_name,
	t.template_name                                                                       AS page_name,
	t.lp_ui_type_name,
	COUNTIF(a.event_type_id = 1)                                                          AS views,
	COUNTIF(a.event_type_id IN (6, 106))                                                  AS cta_clicks,
	SAFE_DIVIDE(COUNTIF(a.event_type_id IN (6, 106)), NULLIF(COUNTIF(a.event_type_id = 1), 0)) AS ctr
FROM dwh_ext_XXX.dm_lp_template AS t
LEFT JOIN dwh_ext_XXX.dm_lp_template AS g
	ON g.lp_template_id = t.lp_original_template_id
JOIN dwh_ext_XXX.g_lp_analytics AS a
	ON a.lp_template_id = t.lp_template_id
WHERE a.create_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY 1, 2, 3
ORDER BY views DESC
LIMIT 50;

dwh_ext_xxx.dm_saw_skin

Contains information about mini-game skins.

Primary key: saw_skin_id

Joins:

• saw_skin_id → 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

• create_by → 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

• saw_prize_id → 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

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

• entry_segment_id → dm_segment

• visibility_segment_id → dm_segment

• id → dm_tournament_instance

• id → g_tournament_winners

• id → g_tournaments_daily_inc

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

• tournament_instance_id → g_tournament_winners

• tournament_instance_id → g_tournaments_daily_inc

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

• raffle_id → 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

• segment_id → 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

• avatar_real_id → 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

• ach_id → dm_ach

• user_id → j_user_no_enums

• ach_completed_id → 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

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

• user_id → j_user_no_enums

• ach_id → 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

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

• user_id → j_user_no_enums

• ach_id → dm_ach

• ach_completed_id → 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.

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

• real_avatar_id → dm_avatar

• user_id → j_user_no_enums

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

• source_type_id → dm_ach_points_change_source

• user_id → j_user_no_enums

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)

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)

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_achievement_analytics

The table contains progress analytics events for missions and badges (achievements).

Each event represents a user activity that advanced one or more missions/badges. The per-mission/badge details are nested under the repeated progress record, so a single event can affect several missions/badges at once.

For a complete picture, progress.ach_id corresponds directly with ach_id in dwh_ext_xxx.dm_ach, and progress.ach_task_id corresponds with task_id in dwh_ext_xxx.dm_ach_task.

Note that there is data nested under progress - use UNNEST(progress) to access the task/mission level fields.

Partitioned by: event_time, query to the table should always use event_time in the WHERE clause.

Primary key: event_id

Partitioned by: event_time

Data availability: last 165 days by event_time

Joins:

• crm_brand_id → dm_brand

• user_id → j_user_no_enums

• event_type_id → dm_event_type

Column	Type	Description
label_id	INT64	Label ID
event_id	INT64	ID of the associated achievement 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 of the event type that triggered the progress. Positive values reference dm_event_type.event_type_id (the user activity event that advanced the mission/badge). -2 - manual completion performed by an operator
progress	RECORD	Repeated record with per-mission/badge progress for the event. The record has following fields: ach_id: INT64 (mission/badge id, see dm_ach.ach_id), ach_task_id: INT64 (task/stage id, see dm_ach_task.task_id), task_progress: INT64 (current progress value of the task), total_progress: INT64 (target value required to complete the task), is_task_completed: BOOL (true if this task/stage was completed by the event), is_ach_completed: BOOL (true if the whole mission/badge was completed by the event), task_completed_id: INT64 (id of the task completion fact), is_first_task_progress: BOOL (true if this is the first progress recorded for the task), next_reset_date: TIMESTAMP (when a recurring mission progress will reset), ach_unlock_date: TIMESTAMP (when the user unlocked the mission/badge), task_type_id: INT64 (1 - Complete Achievement, 2 - Unlock Achievement), limit_in_time_ms: INT64 (time limit to complete, for time-limited missions)
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

Number of users who completed each mission/badge in the past 30 days. The progress array is unnested and filtered to completing tasks (task_type_id = 1) that completed the whole achievement (is_ach_completed = true). dm_ach is joined to resolve the mission/badge name.

SELECT
					A.internal_name,
					A.ach_id,
					COUNT(DISTINCT a.user_id) AS users_completed
				FROM dwh_ext_XXX.g_achievement_analytics a,
				UNNEST(progress) AS p
				JOIN dwh_ext_XXX.dm_ach A ON A.ach_id = p.ach_id AND A.label_id = a.label_id
				WHERE a.event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
					AND p.is_ach_completed = TRUE
					AND p.task_type_id = 1
				GROUP BY A.internal_name, A.ach_id
				ORDER BY users_completed DESC
				LIMIT 1000

Which event types drive the most mission/badge progress in the past 7 days. Only real activity events are considered (event_type_id > 0), joined to dm_event_type for the technical name.

SELECT
					e.event_type_name,
					COUNT(*) AS progress_events,
					COUNT(DISTINCT a.user_id) AS distinct_users
				FROM dwh_ext_XXX.g_achievement_analytics a
				LEFT JOIN dwh_ext_XXX.dm_event_type e ON e.event_type_id = a.event_type_id
				WHERE a.event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
					AND a.event_type_id > 0
				GROUP BY e.event_type_name
				ORDER BY progress_events 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 + clan_id

Partitioned by: calc_date

Data availability: last 1080 days by calc_date

Joins:

• clan_id → 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

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

• source_type_id → dm_ach_points_change_source

• user_id → j_user_no_enums

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

• saw_template_id → dm_saw_template

• saw_prize_id → dm_saw_prize

• user_id → j_user_no_enums

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: label_id + user_id + event_time + event_type_id + tournament_id + tournament_instance_id

Partitioned by: event_time

Data availability: last 1080 days by create_date

Joins:

• crm_brand_id → dm_brand

• user_id → j_user_no_enums

• clan_id → dm_ach_clans

Column	Type	Description
label_id	INT64	Label ID
event_id	INT64	ID of the CJ event that produced this row. Not unique. One event can generate several rows: rows of different event types (e.g. a registration and a progress row), and one row per tournament on the registration event types. In addition, a single progress row nests several tournament instances in progress, so flattening with UNNEST multiplies rows per event_id. Use event_id to attribute a registration, qualification or progress change to the activity event that caused it — not as a key. Rows with no originating event (user-initiated opt-in, operator score adjustment) instead carry a unique negative value that does not correspond to any event. To deduplicate, use (label_id, user_id, event_time, event_type_id, tournament_id, tournament_instance_id).
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.

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

• user_id → j_user_no_enums

• tournament_id → dm_tournament

• tournament_instance_id → dm_tournament_instance

• activity_type_id → dm_activity_type

• clan_id → 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

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_tournaments_daily_inc

Daily (hour-grain) aggregation of user participation progress in tournaments. One row per user per tournament instance per event hour, with cumulative scores and linked casino/sport bet activity that contributed to progress. Built from g_tournament_analytics (and related bet events). Partitioned by start_date — every query SHOULD include start_date in the WHERE clause.

Primary key: start_date + event_time + label_id + user_id + tournament_id + tournament_instance_id

Partitioned by: start_date

Data availability: last 180 days by start_date

Joins:

• crm_brand_id → dm_brand

• user_id → j_user_no_enums

• tournament_id → dm_tournament

• tournament_instance_id → dm_tournament_instance

• tournament_instance_id → g_tournament_winners

Column	Type	Description
event_time	TIMESTAMP	Hour-truncated timestamp of the tournament analytics events for this user/instance
label_id	INT64	ID of Label in Smartico system
user_id	INT64	Unique user identifier in Smartico system
tournament_id	INT64	ID of the tournament. Refer to dm_tournament
tournament_instance_id	INT64	Instance ID (run) of the tournament. Refer to dm_tournament_instance
start_date	TIMESTAMP	Tournament start date
end_date	TIMESTAMP	Tournament end date
registration_start_date	TIMESTAMP	When tournament registration opened
timeline_prc	FLOAT64	Indicates where the event falls in the range start/end of the tournament instance (percent, bucketed by 5)
first_event_time	TIMESTAMP	Earliest scoring/progress event time within the event hour for this user in the tournament instance
crm_brand_id	INT64	CRM Brand identifier
user_ext_id	STRING	External user ID
user_registration_time	TIMESTAMP	First user registration time
user_qualification_time	TIMESTAMP	User qualification time
user_optin_time	TIMESTAMP	User opt-in time
total_scores	INT64	Maximum score achieved by the user (within the hour aggregation)
casino_bet_amount_label_currency	FLOAT64	Sum of user casino bets in label currency that contributed to tournament progress
casino_win_amount_label_currency	FLOAT64	Sum of user casino wins in label currency that contributed to tournament progress
sport_bet_amount_label_currency	FLOAT64	Sum of user sport bets in label currency that contributed to tournament progress
sport_win_amount_label_currency	FLOAT64	Sum of user sport wins in label currency that contributed to tournament progress
count_events	INT64	Number of non-test user events (scoring/progress events with event_type_id > 0)
registration_type_id	INT64	Type of registration for the tournament: 1 - Auto, 2 - Free opt-in, 3 - Buy-in with Points, 4 - Optin & Manual Approval, 5 - Requires Qualification, 6 - Buy-in with Gems, 7 - Buy-in with Diamonds
casino_bets_count	INT64	Count of casino bets that contributed to tournament progress
sport_bets_count	INT64	Count of sport bets that contributed to tournament progress

Users who progressed in a tournament instance with scores and linked casino/sport betting activity

SELECT
					t.user_ext_id,
					t.tournament_id,
					dt.name AS tournament_name,
					t.tournament_instance_id,
					t.event_time,
					t.total_scores,
					t.casino_bet_amount_label_currency,
					t.sport_bet_amount_label_currency,
					t.count_events
				FROM dwh_ext_XXX.g_tournaments_daily_inc t
				INNER JOIN dwh_ext_XXX.dm_tournament dt ON t.tournament_id = dt.id
				WHERE t.start_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
					AND t.total_scores > 0
				ORDER BY t.event_time DESC

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

• shop_item_id → dm_shop_item

• user_id → j_user_no_enums

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

• user_id → j_user_no_enums

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_lp_analytics

Represents UI interactions of visitors with Smartico Landing Pages (LP iframe / smr-* widget IIFE / promo / custom-games templates). Population by smartico-lp-analytics.js (loaded on-demand by smartico.js when window._smartico_lp_analytics === true). Anonymous visits are recorded (user_ext_id is nullable); the visitor_id column mirrors window._smartico_visitor_id which is reconciled to user activity via the IDENTIFY channel on retention labels. Partitioned by: create_date. Cluster: label_id, lp_template_id, event_type_id.

Primary key: N/A

Partitioned by: create_date

Data availability: last 1080 days by create_date

Joins:

• lp_template_id → dm_lp_template

• crm_brand_id → dm_brand

• user_id → j_user_no_enums

Column	Type	Description
event_id	STRING	Client-generated UUID; dedupe key for retries
create_date	TIMESTAMP	Time the event occurred on the client (clock-skew-clamped server-side, same rules as g_ux)
lp_template_id	INT64	Integer PK of cjm.lp_templates (resolved server-side from lp_template_uuid sent by the client)
event_type_id	INT64	LpAnalyticsEventType — page events 1..99, widget events 101..199. Source of truth: common/src/enum/LpAnalyticsEventType.ts
route_path	STRING	In-LP route (e.g. "#/missions", "/store"); NULL for events without a route (e.g. WidgetMounted)
session_uuid	STRING	Per-visit session id (resets on tab close), client-generated
visitor_id	STRING	Smartico tracker visitor id (window._smartico_visitor_id) — already managed by smartico.js; merge key into IDENTIFY on retention labels
user_id	INT64	Internal Smartico user id once smartico.js has identified (NULL for anonymous and for acquisition labels even post-identify)
user_ext_id	STRING	External user id (post Util.cleanUserExtId); NULL for anonymous
label_id	INT64	Resolved server-side from _smartico_label_public_key
crm_brand_id	INT64	Resolved server-side from brand_key (same path g_ux uses)
referrer	STRING	document.referrer truncated to 512 chars
utm_source	STRING	utm_source from URL on first event of session
utm_medium	STRING	utm_medium from URL on first event of session
utm_campaign	STRING	utm_campaign from URL on first event of session
utm_term	STRING	utm_term from URL on first event of session
utm_content	STRING	utm_content from URL on first event of session
device_type_id	INT64	Device type: 1 - MOBILE, 2 - DESKTOP, 3 - MOBILE_NATIVE
os_type_id	INT64	OS type: 1 - Windows, 2 - MacOS, 3 - iOS, 4 - Android, 5 - Linux, 6 - Other
browser_type_id	INT64	Browser type: 1 - Edge, 2 - Chrome, 3 - Firefox, 4 - Safari, 5 - Opera, 6 - Other
target_link	STRING	For *CtaClick events: data-track-id of the clicked element (or href for )
target_text	STRING	For *CtaClick events: trimmed innerText, capped 200 chars
from_page_view_ms	INT64	Milliseconds since the session's PageView event
cli_ip	STRING	Client IP filled by server from x-forwarded-for
bfcache_restore	BOOL	TRUE for PageView emitted on pageshow.persisted (page restored from bfcache, NOT a fresh load). Lets analysts separate genuine fresh visits from back-button restores.

Event ID ranges:

ID Range	Meaning
1..99	Page events — things that happen on a full Smartico landing / promo / custom-game page
101..199	Widget events — things that happen inside a widget (Gamification Block / Custom Game) embedded on your own website
201..299	Reserved for future surfaces

Event types (in plain language):

ID	Name	What it means
1	PageView	A visitor opened the landing page. Also fires when they return to it with the browser Back button (the bfcache_restore column is TRUE in that case, so you can separate fresh visits from back-button returns).
2	PageHidden	The visitor switched away from the page — moved to another tab, minimised the window, or locked the phone.
3	PageVisible	The visitor came back to the page after it had been hidden.
4	PageUnload	The visitor left or closed the page. This is the last event of the visit.
5	PageRouteChange	The visitor moved to a different screen inside the same page (for pages with several screens, e.g. from the home screen to "Missions").
6	PageCtaClick	The visitor clicked a button or link that you chose to track. Not automatic — you mark the element with a data-track-id attribute (see below).
7	PageFormSubmit	The visitor submitted a form on the page (e.g. a sign-up or contact form).
8	PageError	The page's code hit an unexpected error. Useful for spotting broken pages.
101	WidgetMounted	A widget you embedded on your own site finished loading and appeared on the page.
102	WidgetUnmounted	The widget was removed from the page.
103	WidgetHidden	The page hosting the widget was hidden (tab switch / minimise).
104	WidgetVisible	The host page became visible again.
105	WidgetRouteChange	The visitor moved to a different screen inside the widget.
106	WidgetCtaClick	The visitor clicked a button or link you chose to track inside the widget. Not automatic — mark it with data-track-id (see below).
107	WidgetFormSubmit	The visitor submitted a form inside the widget.

Which events fire on their own, and which you set up: Most events are automatic — page opens, screen changes, hide/show, leaving the page, and form submits all record themselves with no setup. The only events you decide on are the click events (PageCtaClick / WidgetCtaClick): add a data-track-id="my-button" attribute to any button or link you want to measure. When a visitor clicks it, target_link stores that id (or the link's URL for an <a>) and target_text stores the visible text. A client-facing version of this guide lives in lp-analytics.md.

Identity reconciliation: On retention labels, anonymous rows (NULL user_id) can be joined to subsequent identified rows via visitor_id. On acquisition labels, the IDENTIFY channel deliberately drops visitor_id (see Tracker.ts:983) — analysts should rely on URL handoff (?_smartico_visitor_id=…) for acquisition → retention stitching, not on a server-side merge.

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

• from_level_id → dm_ach_level

• to_level_id → dm_ach_level

• user_id → j_user_no_enums

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

• user_id → j_user_no_enums

• jp_template_id → 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

• raffle_id → dm_raffle

• root_audience_id → dm_audience

• source_type_id → 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

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

• raffle_id → dm_raffle

• ticket_id → 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