◾Data integration

Possible types of integrations

Smartico can be integrated with your product in 3 possible ways:

• By sending server-to-server events to Smartico REST API about end-user actions and updates in the end-user profile.

• By providing access to a replica of your DB with a set of views that will represent the need for synchronization data, The Smartico system will connect to your DB and track changes in the views.

• By providing access to your event streaming platform, e.g. Kafka or RabbitMQ. With documentation related to the needed events and data.

In all types of integrations, your data is encrypted during transfer, and PII-sensitive properties like email, phone numbers, etc, are encrypted and stored in the isolated DB.

Which type of integration is better?

• REST API is good when you have resources in your R&D team and want to have full control of the data flow

• Integration on replica DB is the fastest and easiest way, as it doesn't require any resources from your R&D., except defining SQL views to access data.

• Kafka is good when it's already incorporated in your organization.

Events and user profile properties

Depending on your product type, you will need different sets of events and user profile properties to be sent to the Smartico system.

Based on our experience, we have defined events and user profile properties that are most common for the business verticals like Casino, Sports, Lotter, and Trading - you will find them in the integration documents below.

The properties in the documents result from our attempt to unify all the integrations we faced so far. While they cover the majority of possible Marketing and Gamification needs, there is always a need to define custom data entities for your product. Custom events and user profile properties can be added by your request either during initial integration or later when you face new use cases.

Important

The data source should provide deduplication of the events in case of REST API/Kafka type of integration and table records in case of Replica-based integration.

This means that all "transactional" data should be represented only once and be immutable. All user profile updates should be provided only if there are changes at least in one property of the user profile.

All types of events/records should have an "update date" that represents the actual date of the business event.

Not following this approach may lead to inconsistent performance of the system and wrong execution of the campaigns, missions, tournaments, and automation rules.

Replica DB

To support SQL replica-based integration with Smartico, the integrated platform should provide a replication database with "views" listed below. Smartico will use those views to track the changes in the data and send them to the Smartico CRM system as events. There are two main types of "views" that should be provided.

Users view

Users view should list all users in the integrated product and should have indexed column dt_update The column should be updated with the current date/time in the UTC timezone whenever any of the user profile fields are changed. Smartico will query this view to get user profiles that have been updated since the last query.

Example of SQL query

SELECT * FROM user_profile WHERE dt_update > previous_query_dt_utc

Note: In most systems, the data about the user is distributed among multiple tables, where every table has its own dt_update field.

Building a view that presents the join of all such tables and has an efficient index on dt_update could be quite problematic; in such case, you can expose to us all such tables, and each table will be tracked independently.

Users view fields

The following fields are required and should be exposed from the user's view.

Column name	Data type	Description
dt_update	timestamp	Column should be updated with current date/time in UTC timezone whenever any of the user profile fields are changed. Indexed, not nullable
ext_brand_id	string or int	ID of the brand in integrated product
user_ext_id	string or int	Unique identified of the user
core_account_status	string	Status of account in the external system. If the account is not ACTIVE, Smartico system will not execute any marketing activities for such an account. The default value for a newly created account is Active.Possible values: ACTIVE, BLOCKED, SUSPENDED, BANNED, SELF_EXCLUDED, DEACTIVATED, PENDING, LOCKED, APPROVED
core_registration_date	timestamp	The date/time when user is registeredThe passed value should be a timestamp in UTC with milliseconds precision. The date value should be in the past.
core_user_language	string	Language of the user in ISO 639-1 format (EN,BG,IT)Possible values: EN, SV, DE, FR, FI, NB, IT, ES, BG, KO...
core_wallet_currency	string	Currency of the user wallet. Note that once set this value cannot be changed for user

The following columns are optional, but we found them useful for marketing purposes in most of the integrated systems.

Column name	Data type	Description
core_username	string	Username or nickname
user_birthdate	timestamp	Birth date of userThe passed value should be a timestamp in UTC with milliseconds precision. The date value should be in the past.
user_country	string	2 symbols, user registration country, e.g. UA
user_email	string	In case Smartico Data Guard is setup, the mail is encrypted and cannot be accessed from Smartico CRM. In case mail is submitted via API, it should be encrypted on Integrated system
user_first_name	string
user_last_name	string
user_phone	string	In case mail is submitted via API, it should be encrypted on Integrated system
core_affiliate_id	numeric	Affiliate ID from affiliate system (in case it's numeric value)
core_affiliate_str	string	Affiliate ID from affiliate system
core_is_email_disabled_by_platform	boolean	User is opted out from mail communication on the Product side
core_is_sms_disabled_by_platform	boolean	User is opted out from sms communication on the Product side
core_user_gender	string	Possible values: MALE, FEMALE, F, M, UNKNOWN

Transactional views

Transactional views that represent deposits, casino/sports bets, etc., are "transactional", which means that every record in the view should be immutable and should have an indexed "dt_finalized" column.

Smartico will use this "dt_finalized" column to find new records and send them to the Smartico CRM system.

SELECT * FROM deposits WHERE dt_finalized > previous_max_dt_finalized

Please note that every transaction should be and don't change once the transaction is made visible in the view. If this is not preserved same transaction will be reported multiple times to the Smartico CRM system.

In a lot of the systems transaction tables also have always incrementing unique IDs, if it is such, it can be also used by integration instead of the dt_update field.

Example of SQL query

SELECT * FROM deposits WHERE id > previous_max_deposit_id

All views (users and transactional) should also have two columns that uniquely identify users in the system - ext_brand_id and user_ext_id. Both columns could be the type of string or integer. Example of user identification: ext_brand_id = "SuperCasino", user_ext_id = "john77" or ext_brand_id = 5 and user_ext_id = 123

Deposits view

The view should represent final deposits, there shouldn't be attempts, failed, or canceled deposits.

Rows in the view should be immutable

Required fields

Column name	Data type	Description
dt_finalized	timestamp	Column should be updated with current date/time in UTC timezone whenever the transaction is finilized (not goin to change in future). Once transaction is visible in the view it should not be changed(!).
ext_brand_id	string or int	ID of the brand in integrated product
user_ext_id	string or int	Unique identified of the user
transaction_id	string	Transaction ID - unique transaction ID of the operation. Can be skipped, If the table has unique ID as listed above
transaction_date	timestamp	The date of deposit. The passed value should be a timestamp in UTC with milliseconds precision.
deposit_amount	numeric	Deposit amount in the player currency

Optional fields

Column name	Data type	Description
bonus_balance	numeric	Bonus money balance in the user currency right after deposit was done
real_balance	numeric	Real money balance in the user currency right after deposit was done
total_balance	numeric	Total money balance in the user currency right after deposit was done (real + bonus)
deposit_code	string	If user used some deposit or vaucher code for the bonus
deposit_method	string	Name of used deposit method, e.g. PaySafe

Bets & wins view

The view should represent the final bets together with corresponding wins, and rows in the view should be immutable.

Read about "The challenge of connecting Bet & Win casino transactions" below

It's important that the bet and win of one round are represented in one row. This is a challenge for most operations because, in most cases, bets and wins are separate transactions, and win fact comes with a delay.

Our suggestion is to make a join of bets and wins and delay data by a few minutes.

    SELECT 
	b.bet_id as id,
	b.ext_brand_id,
	b.user_ext_id,
	b.bet_date,
	b.bet_amount_real,
	b.bet_amount_bonus,
	b.bet_amount_real + b.bet_amount_bonus as bet_amount,
	b.game_name,
	b.game_category_name,
	b.game_provider_name,
	COALESCE(w.win_amount_real, 0) as win_amount_real,
	COALESCE(w.win_amount_bonus, 0) as win_amount_bonus,
	COALESCE(w.win_amount_real, 0) + COALESCE(w.win_amount_bonus, 0) as win_amount
   FROM bets b
   LEFT JOIN wins w on b.bet_id = w.win_id
   WHERE b.bet_date < now() at time zone 'UTC' - interval '3 minute'

This approach gives high confidence in getting win resolutions and minimally delays data routing to the smartico system. From our experience, delaying data by 1 minute is giving 99.5% of the proper bet coverage.

If it's not possible to connect bet & win in one row from performance considerations, Smartico can handle bets and wins from separate tables and join them using some common ID (transaction ID or round ID)

Required fields

Column name	Data type	Description
dt (or id)	timestamp or int	Column should be updated with current date/time in UTC timezone whenever the transaction became visible through the view. Once transaction is visible in the view it should not be changed(!). Or instead of date, there should be incremental ID
ext_brand_id	string or int	ID of the brand in integrated product
user_ext_id	string or int	Unique identified of the user
bet_id	string	Unique transaction ID of the operation. Can be skipped, If the table has unique ID as listed above
bet_date	timestamp	The date of bet. The passed value should be a timestamp in UTC with milliseconds precision.
bet_amount	numeric	Total bet amount (real + bonus). In the user currency
win_amount	numeric	Total win amount (real + bonus). In the user currency
game_name	string	Name of the game. E.g. "Golden Slot 2"

Optional fields

Column name	Data type	Description
bet_amount_real	numeric	Real money part of bet. In user currency
bet_amount_bonus	numeric	Bonus money part of bet. In user currency
win_amount_real	numeric	Real money part of win. In user currency
win_amount_bonus	numeric	Bonus money part of win. In user currency
game_category	string	E.g. "Slots"
game_provider	string	E.g. "Pragmatic"

Other views

Smartico has built-in support for other types of transactions, like failed bets, issued bonuses, requested withdrawals, etc, as well as for other verticals like Sports betting, FinTech, Retail, etc.

Please request relevant documentation from your account manager based on your setup and operational needs.

REST API

You can find attached a document giving a general idea of API-based integration.

18MB

smartico.ai REST API.pdf

pdf

Note: this document is only given as an example and shouldn't be used as a final guide for integration; please contact your account manager for the exact document listing all events and properties specific to your product.

The challenge of connecting Bet & Win casino transactions

It's a common issue in all the platforms to relate a particular win to the original bet because these two types of transactions are represented separately in most platforms.

Why can it be beneficial for the Smartico operation?

Smartico can act in both modes - when bet & win are coming independently and bet and win are connected and coming as one event/transaction.

The benefit when they are connected is that the CRM team can build Retention and Loyalty mechanics based on both - bet & win amounts in the hands, e.g.

• Mission - make a winning bet with a bet of 10 EUR

• Mission - make 5 bets with a winning ratio above 10

• Tournament - users are ranked by the winning ratio

• etc.

The issue with connecting bet & win is based on 3 common facts:

• not every game provider is reporting "lost" in case the user didn't win anything

• there is no strict time in which game providers should report the "win" fact

• in some cases, there could be multiple wins related to one bet

In Smartico, we took the approach of waiting for the win fact for 1 minute and, based on all the reported "win" facts, building a sum of the winning amount and creating a final "bet-win" event that contains both - bet information and win information.

For example:

1. User Bet 1 EUR at 12:34:55

2. At 12:34:57, we got a "win" fact of 0.5 EUR

3. At 12:35:33, we got the last "win" fact of 1.2 EUR

4. At 12:35:57, Smartic will create a "bet-win" transaction with a bet amount of 1 EUR and a win amount of 1.7 EUR

This approach gives high confidence in getting win resolutions and minimally delays data routing to the smartico system. From our experience, delaying data by 1 minute is giving 99.5% of the proper bet coverage.

If the CRM team want's to get higher confidence, the delay can be increased from 1 minute to any desired value

FAQ

What databases are supported for the data integration

We support MySql, PostgreSQL, MSSQL, Oracle, Clickhouse, Snowflake, BigQuery.

What security measures are applied on the connectivity level between Smartico and the Operator data source

We support secured and non-secured connections and all existing mechanics of authorizations with RDMS or streaming solutions. We also provide fixed IPs that should be whitelisted to access your data sources. It is important to note that we are not supporting integrations via VPNs.