◾Smartico DWH \ Gamification views

Dimensional tables

dwh_ext_xxx.dm_ach_level

The table contains information about gamification levels.

Primary key: level_id

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
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

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.

Primary key: ach_id

Column	Type	Description
label_id	INT64	Label identifier
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. This table 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

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_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

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

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_shop_item

The table contains information about shop items.

Primary key: item_id

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

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

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

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

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

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

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

The table represents custom sections (menu items) created in the gamification widget.

Primary key: section_id

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

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

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_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

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

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_ach_points_change_log

Represents all changes in the gamification points. 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

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. Examples: 1 - Campaign, 2 - Mission Task Completed, 3 - Mission Completed, 5 - Store Purchase, etc.
source_reference_id	INT64	ID of the specific Sub Source of the Source Type. The lookup table depends on the source_type_id
source_entity_id	INT64	ID of the entity that triggered transaction, the lookup table depends on the source_type_id
points_collected	INT64	Amount of points in transaction, could be negative in case of deduction
user_points_ever	INT64	Amount of points user collected ever after the transaction
user_points_balance	INT64	Current points balance after transaction
user_ext_id	STRING	External ID of the user
crm_brand_id	INT64	ID of the brand in the Smartico system

Shows the source of the points credit and debit in the last 3 days

SELECT S.source_type_name,
					SUM(CASE WHEN points_collected > 0 THEN points_collected ELSE 0 END) as points_credit,
					SUM(CASE WHEN points_collected < 0 THEN -1 * points_collected ELSE 0 END) as points_debit
				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

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

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
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

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
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

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

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

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
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

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_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

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
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

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

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
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

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

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

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