◾Smartico DWH \ Affiliation views

Affiliation dimensional & fact tables

This is an example of the query that calculates 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_affiliate

The table represent information about affiliate

Column	Type	Note
affiliate_id	INT64	ID of the affiliate
create_date	TIMESTAMP	Date when affiliate is created
parent_affiliate_id	INT64	ID of the parent affiliate
manager_id	INT64	ID of the manager
payment_method_id	INT64	ID of payment method
ext_affiliate_id	STRING	ID of affiliate in the external system

dwh_ext_xxx.aff_visits

The table has all visits we record per label read the notes below to understand the data

Partitioned by: create_date

Table structure:

Column	Type	Note
create_date	TIMESTAMP	visits date
label_id	INTEGER	operator
country	STRING
city	STRING
browser_agent	STRING
req_url	STRING
redirect_url	STRING
aff_id	INTEGER	affilaite_id
tracker	STRING	affiliate system tracker, can repear if visits comes from the same origin
link_id	INTEGER
params_json	JSON	HTTP GET params + values
unique_link_id	INTEGER
source_id	NUMERIC	CPL fee generated during operation_date
campaign_id	NUMERIC	CPA fee generated during operation_date
params	JSON	HTTP GET params
suspect_bot	NUMERIC	internal
ip	NUMERIC
brand_id	NUMERIC
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	boolean	when false, means the visit had tracker in cookie
is_agent_bot	boolean	true if we assume the agent is BOT

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 affilaite 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.

Partitioned by: operation_date

Table structure:

Column	Type	Note
ext_customer_id	STRING	Player ID the way it's reported to affiliate system
brand_id	INTEGER	Brand ID in affiliate system
country	STRING	ISO2 country code of the player
affiliate_id	INTEGER	Affiliate ID in case player belongs to one
registration_id	INTEGER	TAP registration id
operation_date	TIMESTAMP	financial day
update_date	TIMESTAMP	last update date
first_deposit	NUMERIC	first deposit amount
deposits	NUMERIC	deposits amount reported during operation_date
withdrawals	NUMERIC	withdrawals amount reported during operation_date
chargebacks	NUMERIC	chargebacks amount reported during operation_date
bonuses	NUMERIC	bonuses amount reported during operation_date
deductions	NUMERIC	deductions amount reported during operation_date
bets	NUMERIC	bets amount reported during operation_date
wins	NUMERIC	wins amount reported during operation_date
operations	NUMERIC	operations count reported during operation_date
ggr	NUMERIC	GGR amount reported during operation_date
cpl_fee	NUMERIC	CPL fee generated during operation_date
cpa_fee	NUMERIC	CPA fee generated during operation_date
rev_share_fee	NUMERIC	RevShare fee generated during operation_date
sub_affiliate_fee	NUMERIC	Total sub affiliate fee generated during operation_date for all affiliate levels
net_pnl	NUMERIC	NET PnL calculated during operation_date
deposit_count	NUMERIC	deposits count reported during operation_date
withdrawal_count	NUMERIC	withdrawals count reported during operation_date
q_cpa	NUMERIC	if value is 1, it means that at this operation_date player was qualified for CPA
q_cpl	NUMERIC	if value is 1, it means that at this operation_date player was qualified for CPL

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.

Table structure:

Field name	Type	Description
label_id	INTEGER	your operation id
registration_id	INTEGER	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	INTEGER	brand id inside affialite system
affiliate_id	INTEGER	affiliate id
tracker	STRING	the tracker that as used to assign player/user to affiliate
link_id	INTEGER	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	FLOAT	Total deposits ever
withdrawals	FLOAT	Total withdrawals ever
cpa_fee	FLOAT	CPA fee generated
revshare_fee	FLOAT	Rev-Share fee generated
total_bonus	FLOAT	Toatal amount of bonuses
first_deposit_amount	FLOAT	FTD amount
first_deposit_date	TIMESTAMP	FTD
deposits_count	INTEGER	times affiliate system counted deposits (might not be accurate)
player_currency	STRING	ISO3 player currency
volume	FLOAT	Total money wagered
operations	INTEGER	Tatal amount of games/bets
profit	FLOAT	Total amount of wins (not used often)
manual_fee	FLOAT	-
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	FLOAT	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	BOOLEAN	Yes/No flag indicated if rev-share qualification passed
net_win	FLOAT	Total GGR generated by player/user
deductions	FLOAT	Total deductions done
calc_pl	FLOAT	-
cpa_deal_id	INTEGER	Reference to deal id
rev_share_deal_id	INTEGER	Reference to deal id
raw_data	JSON	extra data accepted during record creation
sub_affiliate_fee	FLOAT	Total amount of fees generated for all sub-affiliates in the network of the affiliate player belongs to
no_carry_over_credit	FLOAT	-
no_carry_over_original_credit	FLOAT	-
cpl_deal_id	INTEGER	Reference to deal id
cpl_qualification_date	TIMESTAMP	Date when CPL component was triggered
cpl_fee	FLOAT	CPL fee generated
calc_roi	FLOAT	-
calc_commissions	FLOAT	-
deal_root_original_id	INTEGER	Reference to deal id
do_not_give_cpl	BOOLEAN	true = will block CPL component
do_not_give_cpa	BOOLEAN	true = will block CPA component
chargebacks	FLOAT	Total amount of ChargeBacks (affects net deposit)
calc_net_pl_old	FLOAT	-
calc_net_deposits	FLOAT	Total Net Deposit ever
registration_status_id	INTEGER	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	INTEGER	-