Smartico Help Center
Admin loginSmartico.aiTheAffiliatePlatform.com
  • Welcome
  • Products
    • 🔆CRM Automation
      • ◾Create Journey Flow
      • ◾Event Driven Journey
      • ◾Automation Rules
      • ◾Campaigns vs Automation Rules
      • ◾Segmentation
        • ▪️User State Segments
        • ▪️Behavioral Segments
        • ▪️Imported Segments
      • ◾Activities of Flows
        • ▪️Activity: Email
        • ▪️Activity: SMS, Viber and WhatsApp
        • ▪️Activity: Telegram Bot
        • ▪️Activity: Custom IM
        • ▪️Activity: WebHook
        • ▪️Activity: Limit
        • ▪️Activity: Add Points
      • ◾Communication channels
        • ▪️Email
        • ▪️Liquid Email
        • ▪️SMS, Telegram, Viber, and WhatsApp
        • ▪️Popups
        • ▪️Inbox
        • ▪️Push notifications
        • ▪️Content Variations
        • ▪️Opt-out & Communication Statuses
        • ▪️Gamification activities in the communications
      • ◾A/B Testing
    • 🎮Gamification Blocks
      • Setup steps and checklist
      • Points
      • Gems & Diamonds
      • Levels
        • Level Map
        • Levels Tips & Hints
      • Missions
        • Mission Types & Statuses
        • How to set up a Mission
      • Tournaments
        • Tournament Setup
      • Badges
      • Store
      • Example setup
      • Gamification UI skinning
      • Terms to protect
    • 🧠AI Models
      • ◾AI Enhancer
      • ◾Best Time Model
      • ◾Churn & LTV prediction
      • ◾Favorite product
      • ◾RFM Analysis
      • ◾Sport Recommendations
    • 🎲Mini Games
      • ◾Introduction to Mini Games
      • ◾Game template setup
      • ◾Custom skins for Mini-games
      • ◾Mini-games on the landing pages
      • ◾Using images instead of prize names in Spin the Wheel
      • 🏴‍☠️Treasure Hunt
      • ⚽MatchX Game
        • Creating Rounds
        • Resolution of a Round
        • Leaderboard
        • Game Statuses
        • FAQ: MatchX game
        • Loading MatchX & Quiz games on website
      • ❓Quiz Game
      • 🪂Prize Drop
    • 🗺️Lootbox 2.0
    • 🎁Missions Lootbox
    • 🏆Jackpots
    • 🎟️Raffles
    • 💡General concepts
      • ◾Bonuses
      • ◾Dynamic Rewards
      • ◾Custom Sections
      • ◾Attribution value
      • ◾Label tags
      • ◾Multi-currency usage report
      • ◾Multi-brands support
      • ◾Override translations on Label/Brand Level
      • ◾User markers (tags)
      • ◾Deep-links
      • ◾User roles in Back Office
      • ◾Email Gateways Guide
      • ◾"Client action" event
      • ◾Custom fields/attributes
    • 💻UI Widgets
    • ❔FAQs
      • FAQ: Gamification
      • FAQ: Data Studio & Reports
      • FAQ: CRM Automation
      • FAQ: Front-end integration
      • FAQ: Other topics
      • FAQ: Bonuses
  • Use cases
    • 🔆CRM Automation
      • ◾Cross-sell. Match users on different brands
      • ◾Mini-games usage in marketing campaigns
      • ◾Optimize Communication by excluding disengaged players
      • ◾Automated Sports Campaigns
  • Technical guides
    • ◾Integration process
    • ◾Front-end integration
      • ▪️Extended integration
      • ▪️Push configurations
      • ▪️Acquisition mode
    • ◾Data integration
    • ◾Bonus API integration
    • ◾Secured Messaging Gateways (Email/SMS/IM)
    • ◾Reverse integration
    • ◾Smartico Data Warеhouse
      • ◾Smartico DWH \ Affiliation views
      • ◾Smartico DWH \ CRM views
      • ◾Smartico DWH \ Gamification views
    • ◾Games catalog API
    • ◾Custom push gateways
    • Branded Links
  • More
    • 📝Release notes
      • 🌷May 2025
      • 🌷April 2025
      • 🌷March 2025
      • ❄️February 2025
      • ❄️January 2025
      • ⛄December 2024
      • 🍁November 2024
      • 🍁October 2024
      • 🍁September 2024
      • 🌞August 2024
      • 🌞July 2024
      • 🌞June 2024
      • 🌷May 2024
      • 🌷April 2024
      • 🌷March 2024
      • ❄️February 2024
      • ❄️January 2024
      • ⛄December 2023
      • 🍁November 2023
      • 🍁October 2023
      • 🍁September 2023
      • 🌞August 2023
      • 🌞July 2023
      • 🌞June 2023
      • 🌷May 2023
      • 🌷April 2023
      • 🌷March 2023
      • ❄️February 2023
      • ❄️January 2023
      • ⛄December 2022
      • 🍁November 2022
      • 🍁October 2022
      • 🍁September 2022
      • 🌞June 2022
      • ❄️January 2022
      • 🍁October 2021
      • 🌞July 2021
      • 🌷April 2021
      • ❄️February 2021
    • ⚙️Support Scope
    • 👍Request Demo
Powered by GitBook

More

  • expo.smartico.ai
  • ice.smartico.ai
  • play.smartico.ai

@ 2025 Smartico.ai

On this page
  • Dimensional tables
  • dwh_ext_xxx.dm_ach
  • dwh_ext_xxx.dm_saw_template
  • dwh_ext_xxx.dm_shop_item
  • dwh_ext_xxx.dm_saw_prize
  • dwh_ext_xxx.dm_tournament
  • dwh_ext_xxx.dm_ach_points_change_source
  • Fact tables
  • dwh_ext_xxx.g_ach_completed
  • dwh_ext_xxx.g_ach_optins
  • dwh_ext_xxx.g_ach_claimed
  • dwh_ext_xxx.g_ach_points_change_log
  • dwh_ext_xxx.g_gems_diamonds_change_log
  • dwh_ext_xxx.g_minigames
  • dwh_ext_xxx.g_tournament_analytics
  • dwh_ext_xxx.g_shop_transactions
  • dwh_ext_xxx.g_ux
  • dwh_ext_xxx.g_ach_levels_changed

Was this helpful?

  1. Technical guides
  2. Smartico Data Warеhouse

Smartico DWH \ Gamification views

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

Last updated 1 month ago

Was this helpful?

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

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

ach_actual_type_id

int64

1,Draft 2,Generally available 3,Locked 4,Archived 5,Featured manually 6,Featured by AI 7,Recurring 8,Recurring upon completion

requires_optin

bool

Indicator if mission requires explicit opt-in

limit_in_time_ms

int64

Time in milliseconds for the time limited missions

requires_prize_claim

bool

Indication if mission requires claiming of prize

create_by

int64

ID of backoffice user that created entity

bo_user_email

string

Mail of backoffice user that created entity

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

create_by

int64

ID of backoffice user that created entity

bo_user_email

string

Mail of backoffice user that created entity

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

create_by

int64

ID of backoffice user that created entity

bo_user_email

string

Mail of backoffice user that created entity

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)

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.

  1. Based on properties

  2. Based on formula builder

registration_type_id

int64

ID for the registration type of the tournament.

  1. Auto (all users will participate)

  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

ID represents the type of tournament.

  1. Scheduled

tournament_status_id

int64

ID indicating the status of the tournament.

  1. Draft

  2. Published

  3. Paused

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

create_by

int64

ID of backoffice user that created entity

bo_user_email

string

Mail of backoffice user that created entity

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.

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_gems_diamonds_change_log

The table represents all changes in the gems and diamonds

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

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

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

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.

Partitioned by: event_time

Data availability: last 180 days by create_date

Primary key: id

Column

Type

Note

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:

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

Read more about "claiming" in the .

◾
◾
    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,
CRM Views
Missions guide