# Data integration ## Possible types of integrations Smartico can be integrated with your product in 3 possible ways: * 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. * By sending server-to-server events to Smartico **REST API** about end-user actions and updates in the end-user profile. 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?** * 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 organisation. * **REST API** is good when you have resources in your R\&D team and want to have full control of the data flow. Important that from our expirience its the most time consuming approach for integration, as your team will need to understand semantic of the API and how to structure data properly ### 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. {% hint style="info" %} **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. {% endhint %} ## 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. {% code title="Example of SQL query" %} ```sql SELECT * FROM user_profile WHERE dt_update > previous_query_dt_utc ``` {% endcode %} {% hint style="info" %} **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. {% endhint %} **Users view fields** The following fields are required and should be exposed from the user's view.
Column nameData typeDescription
dt_updatetimestampColumn should be updated with current date/time in UTC timezone whenever any of the user profile fields are changed. Indexed, not nullable
ext_brand_idstring or intID of the brand in integrated product
user_ext_idstring or intUnique identified of the user
core_account_statusstringStatus 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

Use the condition inside of the segment used in marketing campaigns to directly exclude accounts that are not active in order to not target them
core_registration_datetimestampThe 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.

One of the most important properties, not only you can segment based on the registration date, for example if you want to run a campaign only for ''veteran'' players who registered more than 2 years ago, but we also use the registration date as the condition to trigger to the ''profile ready''
core_user_languagestringLanguage of the user in ISO 639-1 format (EN,BG,IT). Possible values: EN, SV, DE, FR, FI, NB, IT, ES, BG, KO...

Note that there are custom codes are used for the regional variations.

Can be used in content variations for communications, based on the language of the player you'll be able to send different communication assets
core_wallet_currencystringCurrency of the user wallet. Note that once set this value cannot be changed for user.
Has multiple uses, can be used in tag to replace the value {{state.core_wallet_currency}} when sending communications about deposit and withdrawals as well as bonus prizes in mini-games for example
The following columns are optional, but we found them useful for marketing purposes in most of the integrated systems.
Column nameData typeDescriptionUse case
core_usernamestringUsername or nicknameCan be used in the greetings as tag like "Hello {{state.core_username}}"
Also used in the leaderboards in the gamification
user_birthdatetimestampBirth date of userThe passed value should be a timestamp in UTC with milliseconds precision.
The date value should be in the past.
Can be used to build birthday campaign for example: If birth date is 'less or equal than 2 days' > send a reminder email saying that if they log in on their birthday they will receive a bonus if birth date is 'today' > trigger a campaign on the log in to give a birthday bonus to the player with a personalized popup
user_countrystring2 symbols, user registration country, e.g. UAIf the operator is targeting multiple countries it can be used to segment players based on their countries and build country specific campaigns
user_emailstringIn 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 systemUsed in communication over Email
user_first_namestringfree textCan be used as a dynamic tag in communications in order to refer to the user with their first name {{state.user_first_name}}
user_last_namestringfree textcan be used as a dynamic tag in communications in order to refer to the user with their first name {{state.user_first_name}}
user_phonestringIn case phone is submitted via API, it should be encrypted on Integrated systemUsed in communication over SMS
core_affiliate_idnumericAffiliate ID from affiliate system (in case it's numeric value)Users register using a specific Affiliate ID/link containing ID and this ID is used to separate players from affiliation and organic registration, example value 4322
core_affiliate_strstringAffiliate ID from affiliate systemSimilar to the "ID" value, but in case your affiliate identified is not numeric, but string, like "john24"
core_is_email_disabled_by_platformbooleanUser is opted out from mail communication on the Product sideIf you have communication preferences on the website where the player can unsubscribe to marketing communications, you can automatically exclude from marketing campaign all players who opted out in order to not even attempt sending a communication
core_is_sms_disabled_by_platformbooleanUser is opted out from sms communication on the Product sideIf you have communication preferences on the website where the player can unsubscribe to marketing communications, you can automatically exclude from marketing campaign all players who opted out in order to not even attempt sending a communication
core_user_genderstringPossible values: MALE, FEMALE, F, M, UNKNOWNSegment based on the gender or can be added in content variation setting to personalize pronouns in communication assets based on the player's gender
core_kyc_statusstringStatus of the user in KYC processRun a campaign on players who didn't complete their KYC process giving them a bonus when they verify their documents
### **Deposits** 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 [**immutable**](#user-content-fn-1)[^1] 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\_finalized field. {% code title="Example of SQL query" %} ```sql SELECT * FROM deposits WHERE id > previous_max_deposit_id ``` {% endcode %} 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. {% hint style="warning" %} Provided transactions should be immutable, if you want to report rollbacks, please see how they can be reported in the [#handling-rollbacks](#handling-rollbacks "mention") {% endhint %} 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 going 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 (optional in case there is only on brand in the table) | | 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 | | deposit\_amount | numeric | Deposit amount in the player currency | Optional fields | Column name | Data type | Description | | -------------------- | --------- | ---------------------------------------------------------------------------------------------------------- | | 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 | | deposit\_sub\_method | string | E.g. MasterCard | | deposit\_platform | string | E.g. Web, Mobile, App, CashDesk etc | | deposit\_utm\_value | string | Some attribution parameter, in case deposit is followed by any marketign campaign and you want to track it | ### **Withdrawals** The view represents completed withdrawals, the state in withdrawals process when money are moved from the user wallet outside. {% hint style="warning" %} Provided transactions should be immutable, if you want to report rollbacks, please see how they can be reported in the [#handling-rollbacks](#handling-rollbacks "mention") {% endhint %} 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 going 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 (optional in case there is only on brand in the table) | | 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 | | withdrawal\_amount | numeric | Withdrawal amount in the player currency | Optional fields | Column name | Data type | Description | | ------------------ | --------- | -------------------------------------------- | | withdrawal\_method | string | Name of used withdrawal method, e.g. PaySafe | ### Casino Bets & wins Casino bets & wins can be presented in one table with a column indicating if the transaction of type bet or of type win. Or these transactions can be presented in separate tables. {% hint style="warning" %} Provided transactions should be immutable, if you want to report rollbacks, please see how they can be reported in the [#handling-rollbacks](#handling-rollbacks "mention") {% endhint %} Fields with "\*" are needed only for "win" transactions 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" | | game\_id | string | The unique ID of the game | 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\_category\_id | string | The unique ID of the game's category | | game\_provider | string | E.g. "Pragmatic" | | game\_provider\_id | string | The unique ID of the game's provider | ### Sport bets, selections and settlements Smartico has built-in support for other types of transactions, such as failed bets, issued bonuses, requested withdrawals, etc, as well as for other verticals like Sports betting, FinTech, Retail, etc. For example, for the "**Sport**" domain we are indicating 4 types of transactions: * Sport bet placement (sport\_bet\_open) * Sport selections placement (sport\_bet\_selection\_open) * Sport bet settlement (sport\_bet\_settled) * Sport selections settlement (sport\_bet\_selection\_settled) The idea is that 1. the `sport_bet_open` event is sent when the overall bet is placed. Think of it as the main container for the bet. 2. Then, the `sport_bet_selection_open` event is for each individual leg or selection within that bet. So, for something like a combo bet or a parlay (a 'Multi'), you'd send the one `sport_bet_open` event for the whole bet and then a separate `sport_bet_selection_open` event for *each* selection that makes up that combo.\ It's not just for combo bets, though. Any bet structure where there are distinct selections within the main bet would follow this pattern. 3. Same goes for `sport_bet_settled` , it need to be send when all "selections" inside the bet are resolved. 4. Following `sport_bet_settled` you will need to send `sport_bet_selection_settled` for each selection inside the bet {% hint style="warning" %} Provided transactions should be immutable, if you want to report rollbacks, please see how they can be reported in the [#handling-rollbacks](#handling-rollbacks "mention") {% endhint %} The DB structure that represents these types of transactions may vary on different providers. What we have found as the most common concept is presented in the diagrams below.
## Kafka or RabbitMQ For streaming solution Operator will need to provide connection details to their broker. After that Smartico is building adaptor to get events from the broker and re-format to Smartico events according to required by Smartico semantic. Usually, we see 3 topics that are covering the main part of integration. **User profile** - information about user profiles, provided whenever user is registered or there are any updated in the profile. Example of event structure ```json { "dt_update": 1678886400000, // Example: March 15, 2023 12:00:00 PM UTC in milliseconds "ext_brand_id": "YourBrandId", // could be int, string, uuid "user_ext_id": "f0e6e2e1-4797-4b75-9d62-ad964a4f05af", // could be int, string, uuid "core_account_status": "ACTIVE", // see an explanation above "core_registration_date": 1640995200000, // required field! Example: January 1, 2022 12:00:00 AM UTC "core_user_language": "EN", // 2 symbols ISO "core_wallet_currency": "EUR", // 3 symbols ISO "core_username": "JohnDoe77", // optional "user_birthdate": 788918400000, // Example: January 1, 1995 12:00:00 AM UTC, optional "user_country": "US", // 2 symbols ISO "user_email": "john.doe@example.com", // will be encrypted on arrival "user_first_name": "John", // optional "user_last_name": "Doe", // optional "user_phone": "+15551234567", // will be normalized using user_country to align from natial format and encrypted "core_is_email_disabled_by_platform": false, // optional, but usually exists "core_is_sms_disabled_by_platform": false, // optional, but usually exists "core_user_gender": "MALE", // very optional "core_kyc_status": "VERIFIED" // optional } ``` {% hint style="info" %} Note: these are the most essential fields that we usually see on the platforms. It is ok that your platform will not have some of them, but will have many more. Expose everything and provide documentation with explanation, we will map and aks questions whenever is needed {% endhint %} **Payments** - information about deposits and withdrawals Example of event structure for deposit. Pay attention to the "type" field, we usually see that in same topic Platform is reporting failed deposits, cancelled deposits, different statuses of withdrawals (requested, approved, completed, rejected, cancelled etc) ```json { "dt_finalized": 1678886400000, // required field! Example: March 15, 2023 12:00:00 PM UTC in milliseconds. This is the timestamp when the deposit was finalized. "ext_brand_id": "YourBrandId", // Could be int, string, uuid "user_ext_id": "f0e6e2e1-4797-4b75-9d62-ad964a4f05af", // Could be int, string, uuid "transaction_id": "dep_tx_123456789", // required field! Unique identifier for this specific transaction. Could be int, string, uuid. "type": "deposit", // int,string. required field to understand type of transaction "amount": 100.00, // required field! The amount of the deposit in the core_wallet_currency "core_wallet_currency": "EUR", // 3 symbols ISO. The currency of user. "payment_method": "CreditCard", // Optional. The method used for the deposit (e.g., "CreditCard", "PayPal", "BankTransfer" or any other string). "deposit_status": "COMPLETED", // Optional. The status of the deposit (e.g., "COMPLETED", "PENDING", "FAILED"). "deposit_code": "PROMO15", // Optional, "provider_name": "ABO-GW24", // Optional } ``` **Casino bets/wins** - information about bet and win transactions. Depending on your system architecture, there could be 2 types of events - bet and win, in other systems we see only one type of transaction "win" that is sent for each bet whenever its resolved, even with 0 winning. Example of event ```json { "ext_brand_id": "YourBrandId", // Could be int, string, uuid "user_ext_id": "f0e6e2e1-4797-4b75-9d62-ad964a4f05af", // Could be int, string, uuid "transaction_id": "bet_tx_123456789", // required field! Unique identifier for this specific transaction. Could be int, string, uuid. "type": "win", // indicaator of transaction type - bet or win "bet_date": 1678886400000, "bet_amount": "1.00", // required field "win_amount": "0.00", // required field for "win" type of event "game_name": "20 Super Hot", // required field "game_category": "Slots", // optional, but usually exists "game_provider": "EGT", // optional, but usually exists "bet_amount_real": "1.00", // the split of bet/win amounts to real and bonus money present in most of the platforms "bet_amount_bonus": "0.00", "win_amount_real": "0.00", "win_amount_bonus": "0.00", "game_vendor": "Amusnet", // optional, but usually exists "casino_game_ext_id": "00000004-0000-5000-9000-000000000012", // ID of game in the catalog. int, string or uuid "casino_game_type_ext_id": "00000000-0000-0000-0000-000000000001", // ID of game type in the catalog. int, string or uuid "casino_game_provider_ext_id": "00000005-dc86-47af-8013-c46b4c5105af" // ID of the game provider in the catalog. int, string or uuid } ``` {% hint style="danger" %} Important to note that all topics should be partitioned by `user id`. This ensures events for each user are processed in the correct order. {% endhint %} ## REST API You can find attached a document giving a general idea of API-based integration. {% file src="" %} Note: this document is only given as **an example** and **shouldn't be used as a final guide for integration**; please contact your Success Manager for the exact document listing all events and properties specific to your product. ## Excluding from reporting In some cases you may want to exclude specific transactions from the Smartico reporting. For example you may have special types of deposits which you want to report to Smartico and to be able to build campaigns/automations rules/missions/tournaments on them, but you don't want them to be counted in the Data Studio or in the Deposit related reports. For this need you can expose through the DB/Kafka/RabbitMQ or pass over API special flag. ```json core_is_exclude_from_report: true ``` This flag is supported in the following events: * acc\_deposit\_approved * acc\_withdrawal\_approved * acc\_withdrawal\_completed * casino\_bet * casino\_win * casino\_bet\_win * sport\_bet\_open * sport\_bet\_settled ## Handling Rollbacks Smartico’s Data Integration supports rollback tracking for all monetary transaction types. Rollback events are used to indicate that a previously reported transaction has been reversed.\ To support this, new boolean properties have been added per transaction category | **Transaction Type** | **Rollback Property** | | --------------------------- | --------------------- | | Deposits, Withdrawals | `acc_is_rollback` | | Casino Bets, Wins, Bet-Wins | `casino_is_rollback` | | Sports Bet Settlement | `sport_is_rollback` | When any of these properties is set to **true**, the associated **amount** must be reported as a **negative value**, representing the reverse financial flow of the original transaction. This ensures consistent interpretation across integrations and analytics layers. Example of transactions **Orinal deposit transaction** ```json { "eid": "18436984-b98f-48bd-b646-bd2472650a40", "event_date": 1760359229163, "ext_brand_id": "your_brand_ext_id", "user_ext_id": "test98635568", "event_type": "acc_deposit_approved", "payload": { "acc_last_transaction_id": "tid-485144", "acc_last_deposit_amount": 100, "acc_last_deposit_date": 1655462961795 } } ``` **Rollback of the deposit**: The 100 deposit is later reversed. ```json { "eid": "18436984-b98f-48bd-b646-bd2472650a49", "event_date": 1760359239163, "ext_brand_id": "your_brand_ext_id", "user_ext_id": "test98635568", "event_type": "acc_deposit_approved", "payload": { "acc_last_transaction_id": "tid-485144", "acc_last_deposit_amount": -100, "acc_last_deposit_date": 1655462961795, "acc_is_rollback": true } } ``` Correctly reporting rollbacks is crucial for maintaining data integrity across the system, which ensures: * **Accurate Aggregations**: Correct calculations for features like [dynamic rewards](https://help.smartico.ai/welcome/products/general-concepts/dynamic-rewards) (e.g., the sum of deposits). * **Precise Segmentation**: Correct user matching for [behavioral segments](https://help.smartico.ai/welcome/products/crm-automation/segmentation/behavioral-segments) based on transactional history. * **Clean Reporting**: The exclusion of reversed transactions from reports, providing a clearer view of user activity. {% hint style="info" %} Note: the progress realtime campaigns, missions, automation rules, tournaments and jackpots is not reverted based on the rollback transactions {% endhint %} ## Data Integration QA To ensure your data integration with Smartico is working correctly, you will need to **simulate user activities** (also referred to as **events**) such as registration, profile updates, deposits, withdrawals, and bets. Each simulated activity must then be verified in the Smartico system to confirm that the updates and transactions are being delivered as expected. ### Verification Process The verification steps are the same for each type of activity: 1. **Generate the activity in your platform.**\ For example, register a new user, update a profile, make a deposit, place a bet, etc. 2. **Log in to the Smartico BackOffice.**\ Navigate to the **CRM** tab and search for the user by their **User ext ID** (the identifier of the user in your platform).\ \&#xNAN;*Note: It may take up to one minute for a newly created user to appear.*
3. **Open the user profile** and go to the **Events** tab.
1. **Filter for the relevant event.** Examples include: * **Core: update profile** – first-time registration or any profile update * **Acc: deposit approved** – successful deposits * **Acc: withdrawal approved** – successful withdrawals * **Casino: Bet**, **Casino: Win**, or **Casino: Bet-Win** (if bet and win are stitched together) * **Sport: Bet open**, **Sport: Bet settled** – for sports bet placement and settlement * **Sport: Selection open**, **Sport: Selection settled** – for sports selections placement and settlement You may also filter by **Transaction ID** to quickly locate a specific event when many are present. *** ### Event Details Once you identify the relevant event, expand it to review details:
* **Left side:** Displays the raw payload delivered to Smartico. * **Right side:** Displays the properties that were updated in Smartico based on the event payload. This side-by-side view allows you to confirm that all expected data points were both delivered and applied correctly. ### Validation through the segments Another important perspective is to validate data match using the segmentation approach 1. Create a user state based segment of "All users" check its matching to what you have in DB. Note that possible data lag of 5-10 minutes, so newly registered users won't be included yet
2. Create a behavioral segment, e.g. "Users who did deposits during July", validate number of users with your source
You can player with conditions configurations of segments to validate that data is matching to your sources. ### Data flow monitoring We have two reports focused on the events delivery In the **Reports \ Technical \ Events analysis** you can monitor events delay (median and 90th percentile)
In the **Data Studio \ Label: events** count you can monitor the volume of events and spikes
## FAQ & General notes **What databases are supported for the data integration** We support MySql, PostgreSQL, MSSQL, Oracle, Clickhouse, Snowflake, BigQuery, Redshift, MariaDB. Its important, that DB instance should allow at least 2 parallel connections. One will be fully dedicated to production data processing and one will be used whenever is needed for investigations. **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. **When working with the replica database, how often does Smartico load data?** Short answer - we are trying to load as often as possible. The general logic is following: 1. we are loading recently updated user profiles, so we are sure that all users are reflected on Smartico side and its safe to load transaction data 2. we are loading deposits, after that withdrawals, then casino bets/wins and finally sport transactions 3. as soon as we are done, we are starting from point 1 In ideal scenario the whole cycle should complete multiple times during a minute, this way we are achieving close to real-time data integration. Depending on your needs and architecture, this logic can be adjusted in the following way: * if such approach is producing high load on the system, we can do a "wait" time after completing the cycle * if you have tables that are updating periodically, e.g. once in a day, we can scan this table only on specific hours, so we don't track changes in such tables outside of hours when its not updating **What is preferable message format, partitioning, lag tracking approaches in streaming solution like Kafka/RabbitMQ?** We prefer JSON for serialization. When it comes to partitioning, it's really important to do it based on the user ID. This helps us keep the order of events straight for each user, which is crucial. We always commit the offset for each partition, even if there's an error with a message (we'll log the error and move on). It would be really helpful though, if you also set up monitoring for your Kafka cluster. Things you'd want to watch out for are if there aren't any consumers reading from a topic or if the lag starts to get really big (say, 100,000 events or more). This helps us catch potential issues quickly. **Do you support schema registry with Avro or Protobuf for Kafka integrations?** For data integration via streaming solutions like Kafka, our preferred message format is JSON. If your organization already has a Kafka implementation that uses Avro, we can support it. However, for any new Kafka integration developed for Smartico, we require the use of JSON for serialization. **What are required fields in messages for streaming solution?** Every message should include * Original timestamp of the business event (date of deposit, bet, update of profile etc) * ID of user (int, string, uuid) * Brand ID (int, string, uuid) could be required in case your system is multi-tenant and same topic can contain data for users of different brands * Unique ID of transaction (int, string, uuid) And for every type of transaction there could be additional "required" fields specific for transaction, e.g. * Deposit - amount is mandatory * Bet - bet amount, game name and game id * Win - bet amount, win amount, game name and game id * Completed withdrawal - amount * User profile - registration date, but in 99% of systems language code, user currency, account status, registration country. You can refer to the table above in this document **How the initial data load is happening?** In case of DB integration - we will load from your DB all users and all historical deposits and withdrawals. For casino bets/wins, we suggest to load data only "from now on". In case of API or Kafka integration - you will need to push all user profiles, and historical deposits / widthdawals. Its important to note that we are not doing initial data import via CSV or in the way different from the main integration, this way we can be sure that data flow is always the same, no mater if we are loading historical data or processing ongoing data. It's also importat for integrated system to re-push data in future on demand, this could be necessary in case of business wan't to introduce more data field in user profile. **Is Smartico handling de-duplication of data?** Depending on the size (amount of transactions) of your operation we can enabled "short window deduplication" in the size of 2 minutes, this means that if distance between accidentally sent duplicated events and distance in time between them is not more then 2 minutes, the duplicated event will be skipped. Note that for operations with big amount of bets, such option is not available **How Smartico is handling realtime aggregations, like "Total deposit amount"?** While we have a logic of running aggregations (total deposit, total widthrawal, bets count etc), from our expirience we are strongly recommending to have such aggregations done on platform side. We found out that running aggregations on data coming in realtime could be very fragile and may miss some business aspects that are not known on Smartico side. Possible reasons to be not accurate * The deposit event was sent twice - as result total deposit amount and count are not correct * Deposit was not sent or sent in wrong format - as result its skipped and totals are not correct * There was internal process of rolling back deposit that was not reflected to Smartico - total are not correct. What we recommend as an approach: * if your system can do realtime aggregations together with sending event to us, this will be best solution. In such case with event bet/deposit/withdrawal you can provide aggregations that are changed and we will reflect them * If you have analytical DB (e.g. BigQuery, Snowflake or any other), in which you can calculate totals on scheduled basis, - we can connect to your DB and reload aggregations automatically whenever they are calculated [^1]: Once the row is exposed and visible through the view, it shouldn't be changed under any conditions