Simpool integration guide
1. Integration methods
a. Simpool is a server-to-server integration based, no need for an SDK.
b. Simpool can extract the data from numerous sources:
c. Extraction user – simpool_dev, password – XXXXXXX
d. Extraction mode – pull
e. Loading frequency – daily\hourly
f. ETL method – incremental
2. Data model
Simpool’s data requirements are straight forward, but in cases you need additional column, simply add them after ‘DT’ column and we’ll include them in a json filed called ‘extra_data’ and it will be available on free querying.
a. Logins
- user_id string
- login_ts string(yyyy-mm-dd hh:mm:ss)
- install_date string(yyyy-mm-dd)
- platform string
- source string
- country string
- balance json
- dt (login date) string(yyyy-mm-dd), index
| USER_ID | 9459929 | 9458967 | 9459113 |
| LOGIN_TS | 2021-04-10 12:34:12 | 2021-04-10 05:41:12 | 2021-04-10 22:34:14 |
| INSTALL_DATE | 2021-03-10 | 2021-01-08 | 2021-02-10 |
| PLATFORM | ANDROID | IOS | IOS |
| SOURCE | applovin | adlovin | fb |
| COUNTRY | CA | MX | MX |
| BALANCE | {“hc”:4473663,”soft1″:0,”soft2″:0} | {“hc”:13025001,”soft1″:0,”soft2″:0} | {“hc”:15525001,”soft”1:0,”soft2″:0} |
| DT | 2021-04-10 | 2021-04-10 | 2021-04-10 |
b. Payments
- user_id string
- payment_ts string(yyyy-mm-dd hh:mm:ss)
- transaction_id string
- payment_currency string
- usd_amount float
- local_payment_currency_amount float
- payment_source( ( shop, personal offer, OOC, lifecharge, etc) string
- sku string
- game_currency json
- balance (before payment) -json
- level json,
- dt (payment_ts) string(yyyy-mm-dd), index
| USER_ID | 8505872 | 8726483 | 8726483 |
| PAYMENT_TS | 2022-01-23 01:21:43 | 2022-01-23 08:21:43 | 2022-01-23 01:45:43 |
| TransactionID | 4354363 | 34436 | 5657455 |
| PAYMENT_CURRENCY | CAD | MXN | MXN |
| USD_AMOUNT | 0.99 | 0.99 | 4.98 |
| LOCAL_CURRENCY_AMOUNT | 1.28 | 249 | 129 |
| PAYMENT_SOURCE | buy_time_button | static.roc_spin_end_POPUP | static.roc_POPUP |
| SKU | special_sale_300 | Flat_500% | Flat_500% |
| game_currnency | {“hc”:150000,”soft1″:0,”soft2″:0} | {“hc”:45000,”soft1″:0,”soft2″:0} | {“hc”:162000,”soft1″:0,”soft2″:0} |
| Balance | {“hc”:44663,”soft1″:0,”soft2″:0} | {“hc”:13001,soft1:0,soft2:0} | {“hc”:1552001,”soft1:0,”soft2″:0} |
| level | {“main_level”:230,”prog2″:0} | {“main_level”:1127,prog2:0} | {“main_level”:1127,”prog2″:0} |
| DT | 2022-01-23 | 2022-01-23 | 2022-01-23 |
c. Bonuses
- user_id string
- bonus_ts string(yyyy-mm-dd hh:mm:ss)
- bonus_type string
- bonus_amount json
- balance (before collection), json
- level json
- dt (bonus_ts) string(yyyy-mm-dd), index
| USER_ID | 9182624 | 9434534 | 9434687 |
| BONUS_TS | 2021-04-18 21:39.34 | 2021-04-18 21:46.56 | 2021-04-18 12:16.12 |
| BONUS_TYPE | HOURLY_SPECIAL | RACE_LUCKY_WINS | RACE_LUCKY_WINS |
| bonus_amount | {“hc”:150000,”soft1″:0,”soft2″:0} | {“hc”:4450000,”soft1″:0,”soft2″:0} | {“hc”:162000,”soft1″:0,”soft2″:0} |
| Balance | {“hc”:44763,”soft1″:0,”soft2″:0} | {“hc”:13025001,”soft1″:0,”soft2″:0} | {“hc”:155001,soft1:0,”soft2″:0} |
| level | {“main_level”:230,”prog2″:0} | {“main_level”:1127,”prog2″:0} | {“main_level”:1127,”prog2″:0} |
| DT | 2021-04-18 | 2021-04-18 | 2021-04-18 |
d. Games
- game_ts string(yyyy-mm-dd hh:mm:ss)
- gameid string
- user_id string
- object_name string
- user_skill string
- game_results json
- level(before game) json
- balance(before game) json
- extra data json
- dt (game_ts) string(yyyy-mm-dd), index
| GAME_ts | 2021-04-18 21:39.34 | 2021-04-18 21:46.56 | 2021-04-18 12:16.12 |
| gameid | 4547457342 | 234324554643 | 234345363 |
| USER_ID | 8870845 | 8870845 | 9383912 |
| object_NAME | content1 | villgae12 | race13 |
| IS_WIN | 0 | 0 | 0 |
| USER_SKILL | 0 | 0 | 0 |
| game_results | {“hc_spent”:1000,”hc_earn”:2000, “soft1_spent”:0 , “soft2_earn”:10, “soft3_spent”:0 , “soft3_earn”:15} | {“hc_spent”:1000,”hc_earn”:2000, “soft1_spent”:0 , “soft2_earn”:10, “soft3_spent”:10, “soft3_earn”:35} | {“hc_spent”:1000, “hc_earn”:2000 ,”soft1_spent”:0 , “soft2_earn”:10, “soft3_spent”:0 , “soft3_earn”:15} |
| level | {“main_level”:230,”prog2″:0,”prog2″:0} | {“main_level”:1127,”prog2″:0, “prog2”:0} | {“main_level”:1127,”prog2″:0, “prog2:0} |
| Balance | {“hc”:4473663,”soft1″:0,”soft2″:0} | {“hc”:130001,”soft1″:0,”soft2″:0} | {“hc”:15525001,”soft1″:0, “soft2”:0} |
| EXTRA_DATA | {“bonus_game”:false; “skill”:5;”is_tournoment”:false} | {“bonus_game”:false; “skill”:4;”is_tournoment”:false} | {“bonus_game”:true; “skill”:1;”is_tournoment”:false} |
| DT | 2021-04-18 | 2021-04-18 | 2021-04-18 |
e. Balance
- user_id string
- balance json ( EOD balance)
- dt string(yyyy-mm-dd),index
| USER_ID | 9182624 | 9434534 | 9434687 |
| Balance | {“hc”:4473663,”soft1″:0,”soft2″:0} | {“hc”:13025001,”soft1″:0,”soft2″:0} | {“hc”:15525001,”soft1″:0,”soft2″:0} |
| DT | 2021-04-18 | 2021-04-18 | 2021-04-18 |
*hc – hard currency
f. Exchange
- user_id string
- exchange_ts string(yyyy-mm-dd hh:mm:ss)
- exchange_trigger string
- exchange json
- level(before exchange) json
- balance(before exchange) json
- dt string(yyyy-mm-dd),index
| USER_ID | 9182624 | 9434534 | 9434687 |
| exchange_ts | 18/04/2021 21:39.34 | 18/04/2021 21:46.56 | 09/09/2021 12:16.12 |
| exchange_triger | shop | end live | shop |
| exchange | {“currency_from”:coins, “currecn_to”:gems, “from_amunt”:5000 , “to_amount”:10} |
{“currency_from”:coins, “currecn_to”:gems, “from_amunt”:10000 , “to_amount”:50} |
{“currency_from”:coins, “currecn_to”:gems, “from_amunt”:10000 , “to_amount”:50} |
| level | {“main_level”:230,”prog2″:0, “prog2”:0} |
{“main_level”:1127,”prog2″:0, “prog2”:0} |
{“main_level”:1127,”prog2″:0, “prog2”:0} |
| Balance | {“hc”:4463,soft1:0,soft2:0} | {hc:13501,soft1:0,soft2:0} | {hc:155001,soft1:0,soft2:0} |
| DT | 18/04/2021 | 18/04/2021 | 09/09/2021 |
g. Ads
- user_id string
- impression_triger string
- impression_ts string(yyyy-mm-dd hh:mm:ss)
- click_ts string(yyyy-mm-dd hh:mm:ss)
- revenues numeric
- ad_type string
- ad_provider string
- reward(json)
- level json
- balance(before ad) json
- dt string(yyyy-mm-dd),index
USER_ID |
9182624 |
9434534 |
9434687 |
impression_triger |
game end |
live renewal |
shop |
impression_ts |
18/04/2021 21:39.34 |
18/04/2021 21:46.56 |
09/09/2021 12:16.12 |
click_ts |
18/04/2021 21:39.38 |
18/04/2021 21:47.47 |
09/09/2021 12:18.12 |
revenues_USD |
0.005 |
0.002 |
0.009 |
ad_type |
rewarded |
interstitial |
banner |
ad_provider |
admob |
ironsource ironsource |
|
reward |
{hc:3,soft1:0,soft2:0} |
{hc:0,soft1:0,soft2:0} |
{hc:0,soft1:0,soft2:0} |
level |
{“main_level”:230,”prog2″:0,
“prog2”:0} |
{“main_level”:1127,”prog2″:0,
“prog2”:0} |
{“main_level”:1127,”prog2″:0,
“prog2”:0} |
balance |
{“hc”:4463,soft1:0,soft2:0} |
{hc:13501,soft1:0,soft2:0} |
{hc:155001,soft1:0,soft2:0} |
DT |
18/04/2021 |
18/04/2021 |
09/09/2021 |