Smartico Help Center
Admin loginSmartico.aiTheAffiliatePlatform.com
  • Welcome
  • Products
    • 🔆CRM Automation
      • ◾Create Journey Flow
      • ◾Event Driven Journey
      • ◾Automation Rules
      • ◾Campaigns vs Automation Rules
      • ◾Segmentation
        • ▪️User State Segments
        • ▪️Behavioral Segments
        • ▪️Imported Segments
      • ◾Activities of Flows
        • ▪️Activity: Email
        • ▪️Activity: SMS, Viber and WhatsApp
        • ▪️Activity: Telegram Bot
        • ▪️Activity: Custom IM
        • ▪️Activity: WebHook
        • ▪️Activity: Limit
        • ▪️Activity: Add Points
      • ◾Communication channels
        • ▪️Email
        • ▪️Liquid Email
        • ▪️SMS, Telegram, Viber, and WhatsApp
        • ▪️Popups
        • ▪️Inbox
        • ▪️Push notifications
        • ▪️Content Variations
        • ▪️Opt-out & Communication Statuses
        • ▪️Gamification activities in the communications
      • ◾A/B Testing
    • 🎮Gamification Blocks
      • Setup steps and checklist
      • Points
      • Gems & Diamonds
      • Levels
        • Level Map
        • Levels Tips & Hints
      • Missions
        • Mission Types & Statuses
        • How to set up a Mission
      • Tournaments
        • Tournament Setup
      • Badges
      • Store
      • Example setup
      • Gamification UI skinning
      • Terms to protect
    • 🧠AI Models
      • ◾AI Enhancer
      • ◾Best Time Model
      • ◾Churn & LTV prediction
      • ◾Favorite product
      • ◾RFM Analysis
      • ◾Sport Recommendations
    • 🎲Mini Games
      • ◾Introduction to Mini Games
      • ◾Game template setup
      • ◾Custom skins for Mini-games
      • ◾Mini-games on the landing pages
      • ◾Using images instead of prize names in Spin the Wheel
      • 🏴‍☠️Treasure Hunt
      • ⚽MatchX Game
        • Creating Rounds
        • Resolution of a Round
        • Leaderboard
        • Game Statuses
        • FAQ: MatchX game
        • Loading MatchX & Quiz games on website
      • ❓Quiz Game
      • 🪂Prize Drop
    • 🗺️Lootbox 2.0
    • 🎁Missions Lootbox
    • 🏆Jackpots
    • 🎟️Raffles
    • 💡General concepts
      • ◾Bonuses
      • ◾Dynamic Rewards
      • ◾Custom Sections
      • ◾Attribution value
      • ◾Label tags
      • ◾Multi-currency usage report
      • ◾Multi-brands support
      • ◾Override translations on Label/Brand Level
      • ◾User markers (tags)
      • ◾Deep-links
      • ◾User roles in Back Office
      • ◾Email Gateways Guide
      • ◾"Client action" event
      • ◾Custom fields/attributes
    • 💻UI Widgets
    • ❔FAQs
      • FAQ: Gamification
      • FAQ: Data Studio & Reports
      • FAQ: CRM Automation
      • FAQ: Front-end integration
      • FAQ: Other topics
      • FAQ: Bonuses
  • Use cases
    • 🔆CRM Automation
      • ◾Cross-sell. Match users on different brands
      • ◾Mini-games usage in marketing campaigns
      • ◾Optimize Communication by excluding disengaged players
      • ◾Automated Sports Campaigns
  • Technical guides
    • ◾Integration process
    • ◾Front-end integration
      • ▪️Extended integration
      • ▪️Push configurations
      • ▪️Acquisition mode
    • ◾Data integration
    • ◾Bonus API integration
    • ◾Secured Messaging Gateways (Email/SMS/IM)
    • ◾Reverse integration
    • ◾Smartico Data Warеhouse
      • ◾Smartico DWH \ Affiliation views
      • ◾Smartico DWH \ CRM views
      • ◾Smartico DWH \ Gamification views
    • ◾Games catalog API
    • ◾Custom push gateways
    • Branded Links
  • More
    • 📝Release notes
      • 🌷May 2025
      • 🌷April 2025
      • 🌷March 2025
      • ❄️February 2025
      • ❄️January 2025
      • ⛄December 2024
      • 🍁November 2024
      • 🍁October 2024
      • 🍁September 2024
      • 🌞August 2024
      • 🌞July 2024
      • 🌞June 2024
      • 🌷May 2024
      • 🌷April 2024
      • 🌷March 2024
      • ❄️February 2024
      • ❄️January 2024
      • ⛄December 2023
      • 🍁November 2023
      • 🍁October 2023
      • 🍁September 2023
      • 🌞August 2023
      • 🌞July 2023
      • 🌞June 2023
      • 🌷May 2023
      • 🌷April 2023
      • 🌷March 2023
      • ❄️February 2023
      • ❄️January 2023
      • ⛄December 2022
      • 🍁November 2022
      • 🍁October 2022
      • 🍁September 2022
      • 🌞June 2022
      • ❄️January 2022
      • 🍁October 2021
      • 🌞July 2021
      • 🌷April 2021
      • ❄️February 2021
    • ⚙️Support Scope
    • 👍Request Demo
Powered by GitBook

More

  • expo.smartico.ai
  • ice.smartico.ai
  • play.smartico.ai

@ 2025 Smartico.ai

On this page
  • Affiliation dimensional & fact tables
  • dwh_ext_xxx.dm_affiliate
  • dwh_ext_xxx.aff_visits
  • dwh_ext_xxx.aff_fin_history_daily
  • dwh_ext_xxx.aff_registrations
  • dwh_ext_xxx.dm_aff_campaign

Was this helpful?

  1. Technical guides
  2. Smartico Data Warеhouse

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

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 recorded per label Please refer to the notes below for details on how to interpret the data.

Partitioned by: create_date

Table structure:

Column
Type
Note

create_date

TIMESTAMP

visits date

label_id

INTEGER

operator id

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

campaign_id

NUMERIC

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

operator 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

-

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.

Table structure:

Field name
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

Last updated 9 days ago

Was this helpful?

◾
◾
Example of the query results