◾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

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

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	-