> For the complete documentation index, see [llms.txt](https://help.smartico.ai/welcome/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-affiliation-views.md).
# 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 | - |
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-affiliation-views.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.