# Smartico DWH \ Affiliation views ## Dimensional tables ### dwh\_ext\_xxx.dm\_aff\_brand Primary key: **brand\_id** | Column | Type | Description | | ------------------ | --------- | -------------------------------- | | label\_id | INT64 | Label ID | | brand\_id | INT64 | Brand ID | | brand\_name | STRING | Brand name | | brand\_description | STRING | Brand description | | custom\_domain | STRING | Custom domain, if set | | is\_active | BOOL | Indicates if the brand is active | | create\_date | TIMESTAMP | Create date | | update\_date | TIMESTAMP | Update date | ### dwh\_ext\_xxx.dm\_affiliate The table represent information about affiliate Primary key: **affiliate\_id** | Column | Type | Description | | --------------------- | --------- | ------------------------------------------------------ | | label\_id | INT64 | Operator ID | | create\_date | TIMESTAMP | Date when affiliate is created | | update\_date | TIMESTAMP | | | affiliate\_id | INT64 | ID of the affiliate | | parent\_affiliate\_id | INT64 | ID of the parent affiliate | | manager\_id | INT64 | ID of the manager | | payment\_method\_id | INT64 | ID of payment method | | affiliate\_label\_id | INT64 | | | affiliate\_name | STRING | | | ext\_affiliate\_id | STRING | ID of affiliate in the external system | | affiliate\_username | STRING | The name of the user that is related to this affiliate | Total commission split by components (CPA, CPL, RevShare) for all affiliates lifetime ```sql WITH X as ( SELECT A.affiliate_id, A.parent_affiliate_id, COALESCE(SUM(cpa_fee),0) as cpa_fee, COALESCE(SUM(cpl_fee),0) as cpl_fee, COALESCE(SUM(rev_share_fee),0) as rev_share_fee, COALESCE(SUM(sub_affiliate_fee),0) as sub_affiliate_fee FROM dwh_ext_XXX.aff_fin_history_daily F INNER JOIN dwh_ext_XXX.dm_affiliate A ON F.affiliate_id = A.affiliate_id WHERE operation_date > '2000-01-01 00:00:00 UTC' GROUP BY A.affiliate_id, A.parent_affiliate_id ), SUB_SUMS as ( SELECT parent_affiliate_id, SUM(X.sub_affiliate_fee) as sub_affiliate_fee FROM X GROUP BY parent_affiliate_id HAVING SUM(X.sub_affiliate_fee) > 0 ), FINAL as ( select TOP_AFFS.* EXCEPT(sub_affiliate_fee, parent_affiliate_id), SUB_SUMS.sub_affiliate_fee, TOP_AFFS.cpa_fee + TOP_AFFS.rev_share_fee + TOP_AFFS.cpl_fee + COALESCE(SUB_SUMS.sub_affiliate_fee,0) as total_commission from X as TOP_AFFS left join SUB_SUMS ON TOP_AFFS.affiliate_id = SUB_SUMS.parent_affiliate_id ) SELECT * FROM FINAL ORDER BY total_commission DESC ```

Example of the query results

**Notes:** 1. Provided example of calculating of sub\_affiliate\_fee should be used only in the setup with one level of sub-affiliates network (when only direct parent of affiliate is getting attribution from the comission generated by his children). In case of multi-level affiliation network setup, the sub-affailites commissions should be calculated in the different way 2. These calculations are not taking into accounts adjustments that can be done in 3 differences cases: 1. Manual adjustments 2. Adjustments based on the "Tiers" based deal structures (RevShare tiers) 3. Adjustment done beginning of the month based on "No negative cary over" calculations ### dwh\_ext\_xxx.dm\_deal Affiliate deals Primary key: **deal\_id** | Column | Type | Description | | ------------------ | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | label\_id | INT64 | Label/Operator ID | | deal\_id | INT64 | Unique identifier for the deal | | deal\_original\_id | INT64 | Original deal identifier | | affiliate\_id | INT64 | Affiliate identifier | | deal\_group\_name | STRING | Name of the deal group | | is\_default | BOOL | Whether this is the default deal | | deal\_type\_id | INT64 | Deal type: 1 - CPL, 2 - CPA, 4 - RevShare\_NetPL, 8 - RevShare\_NetDeposit. Combinations: 3 - CPL & CPA, 5 - CPL + Rev Share, Net P\&L, 6 - CPA + Rev Share, Net P\&L, 9 - CPL + Rev Share, Net Deposit, 10 - CPA + Rev Share, Net Deposit | | create\_date | TIMESTAMP | Date and time when the deal was created | | update\_date | TIMESTAMP | Date and time when the deal was last updated | ### dwh\_ext\_xxx.dm\_aff\_campaign The table contains all campaigns ever created by operation. Each time campaign is modified by your operation, the `update_date` will be set to the current timestamp. Primary key: **campaign\_id** | Column | Type | Description | | -------------------- | --------- | ----------------------------- | | label\_id | INT64 | Operator id | | create\_date | TIMESTAMP | Date when campaign is created | | update\_date | TIMESTAMP | Last update date | | campaign\_id | INT64 | ID of the campaign | | campaign\_name | STRING | Name of the campaign | | affiliate\_id | INT64 | ID of the affiliate | | campaign\_status\_id | INT64 | | ### dwh\_ext\_xxx.dm\_aff\_hash\_links Primary key: **unique\_link\_id** | Column | Type | Description | | ---------------- | --------- | ------------------- | | unique\_link\_id | INT64 | Unique link ID | | label\_id | INT64 | Label ID | | create\_date | TIMESTAMP | Create date | | update\_date | TIMESTAMP | Update date | | affiliate\_id | INT64 | Affiliate ID | | deal\_group\_id | INT64 | Deal group ID | | asset\_id | INT64 | Asset ID | | link\_id | INT64 | Link ID | | campaign\_id | INT64 | Campaign ID | | link\_hash | STRING | Link hash | | custom\_slug | STRING | Custom slug, if set | | source\_id | STRING | Source ID, if set | ## Fact tables ### dwh\_ext\_xxx.aff\_adjustments Primary key: **adjustment\_id** Partitioned by: **adjustment\_date** | Column | Type | Description | | ---------------- | --------- | --------------- | | label\_id | INT64 | Label ID | | adjustment\_id | INT64 | Adjustment ID | | affiliate\_id | INT64 | Affiliate ID | | amount | FLOAT64 | Amount | | adjustment\_date | TIMESTAMP | Adjustment date | | create\_date | TIMESTAMP | Create date | | update\_date | TIMESTAMP | Update date | | comment | STRING | Comment | | deal\_id | INT64 | Deal ID | ### dwh\_ext\_xxx.aff\_payments Primary key: **payment\_id** Partitioned by: **payment\_date** | Column | Type | Description | | ------------------- | --------- | ----------------- | | label\_id | INT64 | Label ID | | payment\_id | INT64 | Payment ID | | amount | FLOAT64 | Amount | | affiliate\_id | INT64 | Affiliate ID | | payment\_details | STRING | Payment details | | create\_date | TIMESTAMP | Create date | | update\_date | TIMESTAMP | Update date | | period\_from | TIMESTAMP | Period from | | period\_to | TIMESTAMP | Period to | | operator\_comments | STRING | Operator comments | | payment\_date | TIMESTAMP | Payment date | | payment\_method\_id | INT64 | Payment method ID | ### dwh\_ext\_xxx.aff\_fin\_history\_daily The table represents daily aggregation of all financial data system collects about a player. Each column refers to some type of financial data and reflect the SUM of all numbers affiliate system have received for some "operation\_date". To understand the final numbers for a player, you should SUM all records for a player, for example if you need to know the total deposit amount of a player, you should SUM all "deposits" + "first\_deposit" columns for a player. Primary key: **ext\_customer\_id** Partitioned by: **operation\_date** | Column | Type | Description | | ------------------- | --------- | ------------------------------------------------------------------------------------- | | operator\_id | INT64 | Operator/Label ID | | ext\_customer\_id | STRING | Player ID the way it's reported to affiliate system | | brand\_id | INT64 | Brand ID in affiliate system | | country | STRING | ISO2 country code of the player | | affiliate\_id | INT64 | Affiliate ID in case player belongs to one | | registration\_id | INT64 | TAP registration id | | operation\_date | TIMESTAMP | financial day | | update\_date | TIMESTAMP | last update date | | first\_deposit | FLOAT64 | first deposit amount | | deposits | FLOAT64 | deposits amount reported during operation\_date | | withdrawals | FLOAT64 | withdrawals amount reported during operation\_date | | chargebacks | FLOAT64 | chargebacks amount reported during operation\_date | | bonuses | FLOAT64 | bonuses amount reported during operation\_date | | deductions | FLOAT64 | deductions amount reported during operation\_date | | bets | FLOAT64 | bets amount reported during operation\_date | | wins | FLOAT64 | wins amount reported during operation\_date | | operations | FLOAT64 | operations count reported during operation\_date | | ggr | FLOAT64 | GGR amount reported during operation\_date | | cpl\_fee | FLOAT64 | CPL fee generated during operation\_date | | cpa\_fee | FLOAT64 | CPA fee generated during operation\_date | | rev\_share\_fee | FLOAT64 | RevShare fee generated during operation\_date | | sub\_affiliate\_fee | FLOAT64 | Total sub affiliate fee generated during operation\_date for **all affiliate levels** | | net\_pnl | FLOAT64 | NET PnL calculated during operation\_date | | deposit\_count | FLOAT64 | deposits count reported during operation\_date | | withdrawal\_count | FLOAT64 | withdrawals count reported during operation\_date | | q\_cpa | FLOAT64 | if value is 1, it means that at this operation\_date player was qualified for CPA | | q\_cpl | FLOAT64 | if value is 1, it means that at this operation\_date player was qualified for CPL | This is an example of the query that calculates total commission split by components (CPA, CPL, RevShare) for all affiliates lifetime. Notes: 1. Provided example of calculating of sub\_affiliate\_fee should be used only in the setup with one level of sub-affiliates network (when only direct parent of affiliate is getting attribution from the comission generated by his children). In case of multi-level affiliation network setup, the sub-affailites commissions should be calculated in the different way 2. These calculations are not taking into accounts adjustments that can be done in 3 differences cases: 1. Manual adjustments 2. Adjustments based on the "Tiers" based deal structures (RevShare tiers) 3. Adjustment done beginning of the month based on "No negative cary over" calculations ```sql WITH X as ( SELECT A.affiliate_id, A.parent_affiliate_id, COALESCE(SUM(cpa_fee),0) as cpa_fee, COALESCE(SUM(cpl_fee),0) as cpl_fee, COALESCE(SUM(rev_share_fee),0) as rev_share_fee, COALESCE(SUM(sub_affiliate_fee),0) as sub_affiliate_fee FROM dwh_ext_XXX.aff_fin_history_daily F INNER JOIN dwh_ext_XXX.dm_affiliate A ON F.affiliate_id = A.affiliate_id WHERE operation_date > '2000-01-01 00:00:00 UTC' GROUP BY A.affiliate_id, A.parent_affiliate_id ), SUB_SUMS as ( SELECT parent_affiliate_id, SUM(X.sub_affiliate_fee) as sub_affiliate_fee FROM X GROUP BY parent_affiliate_id HAVING SUM(X.sub_affiliate_fee) > 0 ), FINAL as ( select TOP_AFFS.* EXCEPT(sub_affiliate_fee, parent_affiliate_id), SUB_SUMS.sub_affiliate_fee, TOP_AFFS.cpa_fee + TOP_AFFS.rev_share_fee + TOP_AFFS.cpl_fee + COALESCE(SUB_SUMS.sub_affiliate_fee,0) as total_commission from X as TOP_AFFS left join SUB_SUMS ON TOP_AFFS.affiliate_id = SUB_SUMS.parent_affiliate_id ) SELECT * FROM FINAL ORDER BY total_commission DESC ``` ### dwh\_ext\_xxx.aff\_visits The table has all visits recorded per label. Please refer to the notes below for details on how to interpret the data. Primary key: **create\_date** Partitioned by: **create\_date** | Column | Type | Description | | ----------------- | --------- | ------------------------------------------------------------------------------------------------------------------------- | | create\_date | TIMESTAMP | visits date | | label\_id | INT64 | operator id | | country | STRING | | | city | STRING | | | browser\_agent | STRING | | | req\_url | STRING | | | redirect\_url | STRING | | | aff\_id | INT64 | affiliate\_id | | tracker | STRING | affiliate system tracker, can repeat if visits comes from the same origin | | link\_id | INT64 | | | params\_json | JSON | HTTP GET params + values | | unique\_link\_id | INT64 | | | source\_id | FLOAT64 | | | campaign\_id | FLOAT64 | | | params | JSON | HTTP GET params | | suspect\_bot | FLOAT64 | internal | | ip | FLOAT64 | | | brand\_id | FLOAT64 | | | aff\_server | STRING | if equals = 'skipme' then this click is duplicate (has same IP and User Agent in last hour as prolongable sliding window) | | headers | JSON | | | referer | STRING | | | previous\_tracker | STRING | in case we found tracker in cookie on incoming request | | unique | BOOL | when false, means the visit had tracker in cookie | | is\_agent\_bot | BOOL | true if we assume the agent is BOT | ### dwh\_ext\_xxx.aff\_registrations The table contains all registrations ever recorded by operation. Each time the system modifies any information, the `update_date` will be set to the current timestamp. By tracking the last update date on your end, you can efficiently retrieve only the records that have been modified. Primary key: **registration\_id** | Column | Type | Description | | --------------------------------- | --------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | | label\_id | INT64 | operator id | | registration\_id | INT64 | unique id of player/user | | create\_date | TIMESTAMP | technical date when record was created | | ext\_customer\_id | STRING | external id of the player/user | | country | STRING | ISO2 country code | | brand\_id | INT64 | brand id inside affiliate system | | affiliate\_id | INT64 | affiliate id | | tracker | STRING | the tracker that was used to assign player/user to affiliate | | link\_id | INT64 | reference to link that was used | | registration\_date | TIMESTAMP | registration date as was reported by PAM | | qualification\_date | TIMESTAMP | Date when player/user was CPA qualified | | deposits | FLOAT64 | Total deposits ever | | withdrawals | FLOAT64 | Total withdrawals ever | | cpa\_fee | FLOAT64 | CPA fee generated | | revshare\_fee | FLOAT64 | Rev-Share fee generated | | total\_bonus | FLOAT64 | Total amount of bonuses | | first\_deposit\_amount | FLOAT64 | FTD amount | | first\_deposit\_date | TIMESTAMP | FTD | | deposits\_count | INT64 | times affiliate system counted deposits (might not be accurate) | | player\_currency | STRING | ISO3 player currency | | volume | FLOAT64 | Total money wagered | | operations | INT64 | Total amount of games/bets | | profit | FLOAT64 | Total amount of wins (not used often) | | manual\_fee | FLOAT64 | - | | operator\_comments | STRING | Comments on the player/user | | rev\_share\_qualification\_date | TIMESTAMP | Date when rev-share component was activated | | qualification\_manual\_date | TIMESTAMP | Date when rev-share component was activated manually | | jackpot\_contribution | FLOAT64 | Total amount contributed to JP (not used much) | | update\_date | TIMESTAMP | Last update date - to be used to track changes to the user/player | | rev\_share\_qualified | BOOL | Yes/No flag indicated if rev-share qualification passed | | net\_win | FLOAT64 | Total GGR generated by player/user | | deductions | FLOAT64 | Total deductions done | | calc\_pl | FLOAT64 | - | | cpa\_deal\_id | INT64 | Reference to deal id | | rev\_share\_deal\_id | INT64 | Reference to deal id | | raw\_data | JSON | extra data accepted during record creation | | sub\_affiliate\_fee | FLOAT64 | Total amount of fees generated for all sub-affiliates in the network of the affiliate player belongs to | | no\_carry\_over\_credit | FLOAT64 | - | | no\_carry\_over\_original\_credit | FLOAT64 | - | | cpl\_deal\_id | INT64 | Reference to deal id | | cpl\_qualification\_date | TIMESTAMP | Date when CPL component was triggered | | cpl\_fee | FLOAT64 | CPL fee generated | | calc\_roi | FLOAT64 | - | | calc\_commissions | FLOAT64 | - | | deal\_root\_original\_id | INT64 | Reference to deal id | | do\_not\_give\_cpl | BOOL | true = will block CPL component | | do\_not\_give\_cpa | BOOL | true = will block CPA component | | chargebacks | FLOAT64 | Total amount of ChargeBacks (affects net deposit) | | calc\_net\_pl\_old | FLOAT64 | - | | calc\_net\_deposits | FLOAT64 | Total Net Deposit ever | | registration\_status\_id | INT64 | Valid(1), Can be changed; Duplicate(2), Final status; Fraud(3), Final status; SelfExcluded(4), Final status; Closed(5), Final status; Locked(6), Can be changed | | cpa\_tier | INT64 | - |