◾Smartico DWH \ Gamification views

This page is describing dimensional and fact tables related to the gamification module

Note that Bonus related structures are used by both - CRM & Gamification modules and described in CRM Views

Dimensional tables

dwh_ext_xxx.dm_ach

The table contains information about missions and badges.

Column	Type	Note
ach_id	int64	ID of mission/badge
internal_name	string	Name of mission/badge
ach_type_id	int64	Type of achievement, 1 - Mission, 2 - Badge
requires_optin	bool	Indicator if mission requires explicit opt-in
limit_in_time_ms	int64	Time in milliseconds for the time limited missions

dwh_ext_xxx.dm_saw_template

.The table contains information about mini-game templates

Column	Type	Note
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
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

dwh_ext_xxx.dm_shop_item

The table contains information about missions and badges.

Column	Type	Note
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
item_status_id	bool	Status: 1 - Draft 2 - Active 3 - Archived

dwh_ext_xxx.dm_saw_prize

The table contains information about missions and badges.

Column	Type	Note
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_value	bool	Value of prize in case it is a type of "gamification points".
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
is_surcharge	bool	If the prize is a surcharge (will be given when no other prizes are left)

List of all bonus templates with their label, id, type and name.

Column	Type	Note
label_id	int64	The id of the label to which this bonus templated belongs
label_bonus_template_id	int64	The id of the bonus template for the specified label
product_bonus_type_id	int64	Id that indicates the bonus type
public_name	string	Bonus template name

dwh_ext_xxx.dm_tournament

The table contains information about tournaments and tournament instances.

id	int64	Primary identifier for the tournament.
name	string	Name of the tournament.
logic_version_id	int64	ID representing the logic version of the tournament. Based on properties Based on formula builder
registration_type_id	int64	ID for the registration type of the tournament. Auto (all users will participate) Free opt-in Buy-in with Points Optin & Manual Approval 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	ID represents the type of tournament. Scheduled
tournament_status_id	int64	ID indicating the status of the tournament. Draft Published Paused Archived
last_tournament_create_date	timestamp	Timestamp of when the last tournament 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.

dwh_ext_xxx.dm_ach_points_change_source

Lookup table with possible sources for the points transactions.

Column	Type	Note
source_type_id	int64	ID
source_type_name	string	Name of the source

Fact tables

dwh_ext_xxx.g_ach_completed

The table represents the history of all completed missions & badges.

Partitioned by: create_date

Data availability: last 180 days by create_date

Primary key: ach_completed_id

Column	Type	Note
ach_id	INT64	ID of the mission/badge
create_date	TIMESTAMP	The time when mission/badge compelted
is_recurring	BOOLEAN	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

Example of the query returning a count of users 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

The table represents the history of all opt-ins in the missions that require explicit opt-ins to participate.

Partitioned by: create_date

Data availability: last 180 days by create_date

Primary key: ach_id, user_id, create_date

Column	Type	Note
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
user_ext_id	STRING	External ID of the user
crm_brand_id	INT64	ID of the brand in the Smartico system

Example of the query returning up to 1000 rows of missions that were active in the past 30 days with counts of opted in customers and customers that completed the mission in that period.

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

The table represents facts of prizes claiming for the missions for which a claim is required.

Read more about "claiming" in the Missions guide.

Partitioned by: create_date

Data availability: last 180 days by create_date

Primary key: ach_completed_id

Column	Type	Note
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

dwh_ext_xxx.g_ach_points_change_log

The table represents all changes in the gamification points

Partitioned by: create_date

Data availability: last 180 days by create_date

Primary key: NA

Column	Type	Note
create_date	TIMESTAMP	The time of transaction
source_type_id	INT64	Type of 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
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

Example of query showing the source of the points credit and deposit

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_12078.g_ach_points_change_log L 
inner join dwh_ext_12078.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_minigames

The table represents mini-game gameplay results.

Partitioned by: create_date

Data availability: last 180 days by create_date

Primary key: spin_id

Column	Type	Note
create_date	TIMESTAMP	The time of transaction
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 (there are other types of mini-games, that are free or "attempt" based)
prize_amount	NUMERIC	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

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

Note that there is data nested under progress.

Partitioned by: event_time

Data availability: last 180 days by create_date

Primary key: id

Column	Type	Note
create_date	TIMESTAMP	Timestamp of when the record was created.
update_date	TIMESTAMP	Timestamp of when the record was last updated.
id	INT64	Primary identifier for the tournament.
name	STRING	Name of the tournament, optional.
logic_version_id	INT64	ID representing the logic version of the tournament.
registration_type_id	INT64	ID for the registration type of the tournament. AUTO BUY-IN OPT-IN BUY-IN-CASH
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	ID representing the type of the tournament. ONE TIME REPETITIVE SIT & GO
tournament_status_id	INT64	ID indicating the status of the tournament. Archived Draft Paused Published
last_tournament_create_date	TIMESTAMP	Timestamp of when the last tournament was created.
show_before_start_ms	INT64	Time in milliseconds to show the tournament before start.
allow_late_registration	BOOLEAN	Indicates if late registration is allowed.
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.
progress	RECORD	Repeated record indicating progress details related to the event.
	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.

This example provides a comprehensive summary of each tournament instance, focusing on 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. This structure can be particularly useful for analyzing trends or changes in tournament performance over time or across different instances of the same tournament.

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_shop_transactions

The table represents all purchases in the store.

Partitioned by: create_date

Data availability: last 180 days by create_date

Primary key: transaction_id

Column	Type	Note
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

The table represents the UI interactions of users with the Gamification widget

Partitioned by: create_date

Data availability: last 180 days by create_date

Primary key: NA

Column	Type	Note
label_id	integer	Unique id of the label
crm_brand_id	integer	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	integer	Unique id of user in the Smartico system
interface_type	integer	Device type of the current user session 0 - Mobile 1 - Desktop
screen_name_id	integer	Identifier of the screen the user has navigated to. Possible values are shown in the table below - ScreenNameIds
screen_subname_id	integer	Identifier of the category/section of the screen the user has navigated to. Possible values are presented in the table below and depend on the screen_name_id. Relevant only for screens that have categorized tabs, like store, tournaments, etc
custom_section_id	integer	Represents custom section id if the current screen is opened in the context of the custom section.
entity_id	integer	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.

ScreenNameIds:

ID	Name of screen
1	Overview / Home
2	Missions screen_subname_id could be 0 - overview 2 - available 3 - locked 4 - completed 5 - missed
3	Leaderboards screen_subname_id could be 1 - daily 2 - weekly 3 - monthly
4	Tournaments list screen_subname_id could be 0 - overview 3 - in progress 4 - finished 5 - my tournaments
5	Tournament lobby (details of specific tournament) screen_subname_id could be 1 - rules 2 - Prizes 3 - Leaderboard 4 - Related games
6	Levels (grid presentation)
7	Levels (map presentation)
8	Badges
9	Bonuses screen_subname_id could be 2 - pending 3 - redeemed
10	Spin a Wheel
11	Store
12	Store (purchase history)
13	Inbox screen_subname_id could be 1 - All message 2 - Favorite
14	Match X
15	Quiz
16	Jackpots
17	Lootbox
18	Custom section
31	Mission item modal
32	Level item modal
33	Badge item modal
34	Store item modal
35	Bonus item modal
36	Jackpot item modal
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.

Partitioned by: fact_date

Data availability: last 180 days by fact_date

Primary key: level_change_id

Column	Type	Note
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, then this field represents the source of points change. Possible IDs:
		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,
points_collected_on_level_change	INT64	If level change is triggered by points balance change, then this field represents the amount of points added to the user at the moment of upgrade
user_points_ever	INT64	"Points ever" balance of user after level hcange
user_ext_id	STRING	External ID of the user
crm_brand_id	INT64	ID of the brand in the Smartico system