![\"affiliate](\"https:\/\/www.scaleo.io\/blog\/wp-content\/uploads\/2023\/01\/scaleo-affiliate-software-for-igaming-industry-scaled.jpg\" "\\"-\\"")
<\/a><\/figure>\n\n\n\nThis is the reconciliation nightmare that costs casino operators real money\u2014not because the systems are \u201cbroken,\u201d but because nobody built a coherent framework for how affiliate data, platform data, PSP data, and CRM data should align.<\/p>\n\n\n\n
Operators who treat BI integration as an afterthought end up burning dozens of hours a month reconciling spreadsheets, disputing affiliate payouts, and making acquisition decisions based on incomplete (or mismatched) numbers.<\/p>\n\n\n\n
The operators who get this right build automated pipelines that flow from raw click events to executive dashboards showing: which affiliates drive profitable players, which traffic sources have the best LTV, and where commission budget should actually be allocated.<\/p>\n\n\n\n
This guide shows you how to structure affiliate data exports, integrate with your BI stack, and build reconciliation frameworks that work in production\u2014without needing a data science PhD.<\/p>\n\n\n\n
What you\u2019ll be able to answer (fast) when this is implemented?<\/h2>\n\n\n\n
Before we go technical, here\u2019s the real \u201cwhy\u201d (the questions your CFO and Head of Acquisition will ask):<\/p>\n\n\n\n
- \n
- Which affiliates are profitable after<\/strong> bonuses, fees, chargebacks, and commission?<\/li>\n\n\n\n
- Which SubID<\/a> clusters (placements\/creatives) generate retained<\/strong> players instead of one-and-done FTDs?<\/li>\n\n\n\n
- Which GEOs look \u201cgreat\u201d on deposits but are negative after withdrawals + bonus cost?<\/li>\n\n\n\n
- Which commission plans are mathematically broken (commission rate drifts vs actual NGR<\/a>)?<\/li>\n\n\n\n
- Which affiliates are repeatedly triggering disputes because your evidence chain is incomplete?<\/li>\n<\/ul>\n\n\n\n
Why casino BI integration is harder than e-commerce?<\/h2>\n\n\n\n
In e-commerce, the flow is usually: click \u2192 purchase \u2192 revenue. One event, one transaction, done.<\/p>\n\n\n\n
Casino attribution is fundamentally more complex:<\/p>\n\n\n\n
Multiple revenue-impacting events per player<\/h3>\n\n\n\n
A single player generates registration, KYC approval, deposits, wagering activity, bonus claims, withdrawals, chargebacks, and ongoing NGR over time. Each event affects commission and profitability differently.<\/p>\n\n\n\n
Time lag between attribution and revenue<\/h3>\n\n\n\n
A player clicks an affiliate link on January 5, registers on January 7, makes first deposit on January 10, and generates \u20ac500 NGR over the next 90 days. Which month does that NGR \u201cbelong\u201d to? When do you pay? What happens if the deposit is charged back on February 3?<\/p>\n\n\n\n
RevShare is not a one-time event<\/h3>\n\n\n\n
CPA is simple: one payable moment. RevShare<\/a> means you\u2019re calculating commission on player activity every month for potentially years. Your BI needs to track player lifetime value<\/a> by affiliate source indefinitely.<\/p>\n\n\n\n
You\u2019re reconciling multiple systems, not one \u201ctruth\u201d<\/h3>\n\n\n\n
Your affiliate system tracks clicks and attribution. Your gaming platform tracks wagers and NGR. Your PSP tracks deposits and withdrawals. Your CRM tracks segmentation and player lifecycle status. All four must agree on which players belong to which affiliate attribution\u2014and how revenue is defined.<\/p>\n\n\n\n
If your warehouse can\u2019t join these sources reliably, your profitability analysis is fiction.<\/p>\n\n\n\n
The core data architecture that actually works<\/h2>\n\n\n\n
Before export formats and schemas, lock the conceptual model. The simplest model that survives real-world change looks like this:<\/p>\n\n\n\n
Layer 1: Raw events (immutable)<\/h3>\n\n\n\n
Every click, registration, deposit, wager, and reversal is logged as an immutable event with timestamp, player ID, affiliate ID, click ID, amounts, and metadata. Raw events are append-only. If something changes, you add a new event\u2014you don\u2019t rewrite history.<\/p>\n\n\n\n
Layer 2: Attribution state (calculated)<\/h3>\n\n\n\n
This is where business logic lives: which affiliate gets credit, which commission rate applies, what \u201cqualified\u201d means, how reversals\/holds apply, and what the current attributed LTV is. This layer gets recalculated when rules change.<\/p>\n\n\n\n
Layer 3: Reporting aggregates (optimized)<\/h3>\n\n\n\n
Pre-calculated rollups for dashboards: daily revenue by affiliate, cohort retention curves, payout accrual vs paid, and ROI by channel. These refresh hourly\/daily and exist for speed\u2014not truth.<\/p>\n\n\n\n
Most operators accidentally mix these layers (e.g., storing calculated commission next to raw events), then can\u2019t reprocess when they discover a mapping or definition mistake. Separate the layers. Keep raw events forever. Recalculate attribution when needed. Refresh aggregates for speed.<\/p>\n\n\n\n
What data you actually need to export (minimum viable tables)<\/h2>\n\n\n\n
Below is the minimum dataset your BI team needs from your affiliate system to build reliable joins and profitability reporting.<\/p>\n\n\n\n
Clicks (raw events)<\/h3>\n\n\n\n
Field<\/th> Type<\/th> Description<\/th> Example<\/th><\/tr><\/thead> click_id<\/strong><\/td> string<\/td> Unique click identifier<\/td> clk_a7f3d9e2b4<\/td><\/tr> timestamp<\/strong><\/td> datetime (UTC)<\/td> When click occurred<\/td> 2026-01-15 14:32:18<\/td><\/tr> affiliate_id<\/strong><\/td> integer<\/td> Which affiliate<\/td> 12345<\/td><\/tr> offer_id<\/strong><\/td> string\/integer<\/td> Which offer\/promo<\/td> offer_welcome_de<\/td><\/tr> sub1\u2013sub5<\/strong><\/td> string<\/td> SubID dimensions<\/td> twitch \/ jan-stream \/ overlay<\/td><\/tr> ip_hash<\/strong><\/td> string<\/td> IP hashed for privacy<\/td> SHA256\u2026<\/td><\/tr> user_agent<\/strong><\/td> string<\/td> Browser\/device info<\/td> Mozilla\/5.0\u2026<\/td><\/tr> geo_country<\/strong><\/td> string<\/td> Server-detected country<\/td> DE<\/td><\/tr> device_type<\/strong><\/td> string<\/td> desktop\/mobile\/tablet<\/td> mobile<\/td><\/tr> referrer_url<\/strong><\/td> string<\/td> Where click came from<\/td> review-site.com\/best-casinos<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Why this matters:<\/strong> clicks are your attribution evidence chain. When a payout dispute happens (\u201cwe sent this player\u201d), you need the raw click record with timestamp and parameters proving how and when the player arrived.<\/p>\n\n\n\n
Conversions (multi-event)<\/h3>\n\n\n\n
Field<\/th> Type<\/th> Description<\/th> Example<\/th><\/tr><\/thead> conversion_id<\/strong><\/td> string<\/td> Unique conversion record ID<\/td> conv_847392<\/td><\/tr> click_id<\/strong><\/td> string<\/td> Links back to click<\/td> clk_a7f3d9e2b4<\/td><\/tr> player_id<\/strong><\/td> string<\/td> Platform player ID<\/td> p_847392<\/td><\/tr> affiliate_id<\/strong><\/td> integer<\/td> Credited affiliate<\/td> 12345<\/td><\/tr> event_type<\/strong><\/td> enum<\/td> registration\/kyc\/ftd\/deposit\/ngr<\/td> first_deposit<\/td><\/tr> event_timestamp<\/strong><\/td> datetime (UTC)<\/td> When event occurred<\/td> 2026-01-15 16:45:22<\/td><\/tr> amount<\/strong><\/td> decimal<\/td> Transaction or revenue amount<\/td> 200.00<\/td><\/tr> currency<\/strong><\/td> string<\/td> ISO currency code<\/td> EUR<\/td><\/tr> status<\/strong><\/td> enum<\/td> pending\/approved\/rejected<\/td> approved<\/td><\/tr> commission_amount<\/strong><\/td> decimal<\/td> Affiliate earnings for this event<\/td> 70.00<\/td><\/tr> commission_currency<\/strong><\/td> string<\/td> Payout currency<\/td> EUR<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Why this matters:<\/strong> this is your money table. Your BI joins this to clicks (via click_id) and to platform\/PSP\/CRM (via player_id) to analyze conversion rates, time-to-deposit, cohort quality, and commission ROI by affiliate and SubID.<\/p>\n\n\n\n
Player attribution map (click \u2192 player)<\/h3>\n\n\n\n
This table is the glue. It\u2019s where most \u201cwe can\u2019t reconcile\u201d projects either succeed or die.<\/p>\n\n\n\n
Field<\/th> Type<\/th> Description<\/th> Example<\/th><\/tr><\/thead> player_id<\/strong><\/td> string<\/td> Platform player ID<\/td> p_847392<\/td><\/tr> first_click_id<\/strong><\/td> string<\/td> Original click associated with registration<\/td> clk_a7f3d9e2b4<\/td><\/tr> attributed_affiliate_id<\/strong><\/td> integer<\/td> Affiliate credited per your rules<\/td> 12345<\/td><\/tr> attribution_model<\/strong><\/td> string<\/td> last-click \/ position-based \/ etc.<\/td> last_click<\/td><\/tr> attribution_window_days<\/strong><\/td> integer<\/td> Window used when player converted<\/td> 30<\/td><\/tr> created_at<\/strong><\/td> datetime (UTC)<\/td> When mapping was created<\/td> 2026-01-15 16:40:00<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Important nuance:<\/strong> click_id is perfect for attribution truth, but platform\/PSP systems usually join on player_id<\/strong>. You need a strict click\u2192player mapping captured at registration\/FTD so cross-system joins don\u2019t devolve into guesswork.<\/p>\n\n\n\n
Player lifetime value (aggregated)<\/h3>\n\n\n\n
Field<\/th> Type<\/th> Description<\/th> Example<\/th><\/tr><\/thead> player_id<\/strong><\/td> string<\/td> Platform player ID<\/td> p_847392<\/td><\/tr> affiliate_id<\/strong><\/td> integer<\/td> Attributed affiliate<\/td> 12345<\/td><\/tr> registration_date<\/strong><\/td> date<\/td> When player registered<\/td> 2026-01-15<\/td><\/tr> first_deposit_date<\/strong><\/td> date<\/td> When FTD occurred<\/td> 2026-01-15<\/td><\/tr> total_deposits<\/strong><\/td> decimal<\/td> Lifetime deposits<\/td> 1250.00<\/td><\/tr> total_withdrawals<\/strong><\/td> decimal<\/td> Lifetime withdrawals<\/td> 420.00<\/td><\/tr> total_ngr<\/strong><\/td> decimal<\/td> Lifetime NGR<\/td> 830.00<\/td><\/tr> total_commission_paid<\/strong><\/td> decimal<\/td> Total commission paid<\/td> 290.50<\/td><\/tr> days_active<\/strong><\/td> integer<\/td> Days between first and last activity<\/td> 87<\/td><\/tr> is_active_30d<\/strong><\/td> boolean<\/td> Active in last 30 days<\/td> true<\/td><\/tr> last_activity_date<\/strong><\/td> date<\/td> Most recent login\/wager<\/td> 2026-04-10<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Why this matters:<\/strong> it separates \u201cvolume\u201d affiliates from \u201cvalue\u201d affiliates. You can tier commission based on cohort quality instead of being held hostage by FTD counts.<\/p>\n\n\n\n
Payouts (financial reconciliation)<\/h3>\n\n\n\n
Field<\/th> Type<\/th> Description<\/th> Example<\/th><\/tr><\/thead> payout_id<\/strong><\/td> string<\/td> Unique payout batch ID<\/td> payout_202601<\/td><\/tr> affiliate_id<\/strong><\/td> integer<\/td> Affiliate paid<\/td> 12345<\/td><\/tr> period_start<\/strong><\/td> date<\/td> Commission period start<\/td> 2026-01-01<\/td><\/tr> period_end<\/strong><\/td> date<\/td> Commission period end<\/td> 2026-01-31<\/td><\/tr> total_commission<\/strong><\/td> decimal<\/td> Total owed<\/td> 4280.50<\/td><\/tr> currency<\/strong><\/td> string<\/td> Payout currency<\/td> EUR<\/td><\/tr> status<\/strong><\/td> enum<\/td> pending\/paid\/held<\/td> paid<\/td><\/tr> paid_date<\/strong><\/td> date<\/td> When actually paid<\/td> 2026-03-01<\/td><\/tr> payment_method<\/strong><\/td> string<\/td> wire\/crypto\/etc.<\/td> wire<\/td><\/tr> invoice_id<\/strong><\/td> string<\/td> Accounting reference<\/td> INV-2026-001<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Why this matters:<\/strong> finance reconciles commission accrued<\/em> (from conversions) vs commission paid<\/em> (from payouts). Discrepancies mean calculation errors, holds, or missing payments.<\/p>\n\n\n\n
Export cadence: hourly, daily, or real-time?<\/h2>\n\n\n\n
Different data have different freshness requirements:<\/p>\n\n\n\n
Export hourly<\/h3>\n\n\n\n
- \n
- Raw clicks (useful for fraud\/anomaly detection)<\/li>\n\n\n\n
- Conversion events (for \u201ctoday\u201d dashboards)<\/li>\n\n\n\n
- Pending commissions (for operational monitoring)<\/li>\n<\/ul>\n\n\n\n
Export daily (end of day UTC)<\/h3>\n\n\n\n
- \n
- Aggregates (daily revenue by affiliate, daily cohort metrics)<\/li>\n\n\n\n
- Reconciliation datasets (clicks vs conversions vs platform\/PSP totals)<\/li>\n<\/ul>\n\n\n\n
Export weekly\/monthly<\/h3>\n\n\n\n
- \n
- Historical rollups (monthly cohort retention, YoY)<\/li>\n\n\n\n
- Payout summaries (month-end accounting)<\/li>\n<\/ul>\n\n\n\n
Most operators start with daily warehouse loads, then move to hourly as BI maturity increases. Real-time streaming is optional\u2014and usually only worth it for fraud triggers and live ops dashboards.<\/p>\n\n\n\n
File formats and access methods<\/h2>\n\n\n\n
Choose the simplest method that matches your data volume and BI maturity:<\/p>\n\n\n\n
Method<\/th> Best when<\/th> Watch-outs<\/th><\/tr><\/thead> CSV exports<\/strong><\/td> Lower volumes, file-based workflows, simple pipelines<\/td> Easy to break with schema changes; painful at 1M+ rows per export<\/td><\/tr> JSON exports<\/strong><\/td> API-first ingestion, rich metadata, modern pipelines<\/td> Harder for analysts; needs robust parsing and schema control<\/td><\/tr> Direct database read access<\/strong><\/td> High volume, SQL-capable team, near real-time queries<\/td> Requires governance; use read replica and access controls<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n At scale, the common pattern is: read replica for operational queries<\/strong> + warehouse copy for historical joins<\/strong> (platform + PSP + CRM).<\/p>\n\n\n\n
The reconciliation framework (affiliate + platform + PSP)<\/h2>\n\n\n\n
This is where most BI projects stumble. You have three \u201ctruths\u201d that must align:<\/p>\n\n\n\n
- \n
- Affiliate system:<\/strong> attribution, events, commission calculation<\/li>\n\n\n\n
- Gaming platform:<\/strong> wagers, bonus cost, NGR (profitability truth)<\/li>\n\n\n\n
- PSP:<\/strong> deposits, withdrawals, settlements (cash movement truth)<\/li>\n<\/ul>\n\n\n\n
Numbers don\u2019t match because definitions and timing don\u2019t match. Here are the most common mismatch types:<\/p>\n\n\n\n
Discrepancy type<\/th> Typical cause<\/th> Fix<\/th><\/tr><\/thead> Affiliate commission > platform profitability<\/strong><\/td> Bonuses\/fees not reflected in \u201crevenue\u201d definition used for payout logic<\/td> Align to the same NGR definition and document it<\/td><\/tr> Platform revenue \u2260 PSP deposits<\/strong><\/td> Deposits pending\/failed; settlement timing; refunds\/chargebacks<\/td> Use settled transactions for PSP truth; track timing drift<\/td><\/tr> Player count mismatch<\/strong><\/td> ID format drift; duplicates; missing click\u2192player mapping<\/td> Standardize player_id and maintain a strict attribution map<\/td><\/tr> Time period mismatch<\/strong><\/td> UTC vs local time boundaries; month cutoffs differ<\/td> Store everything in UTC; localize only in reporting layer<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n The reconciliation pattern:<\/strong> use player_id<\/strong> for cross-system joins, and use click_id<\/strong> (via the attribution map) as your proof chain for affiliate credit.<\/p>\n\n\n\n
Example reconciliation query pattern<\/h3>\n\n\n\n
This example flags affiliates where actual commission rate drift suggests either a definition mismatch (NGR components) or a rule-application issue.<\/p>\n\n\n\n
-- Join affiliate conversions to platform revenue by player_id (via your attribution map)\nSELECT \n am.attributed_affiliate_id AS affiliate_id,\n COUNT(DISTINCT am.player_id) AS players,\n SUM(ac.commission_amount) AS commission_owed,\n SUM(pr.ngr_amount) AS platform_ngr,\n (SUM(ac.commission_amount) \/ NULLIF(SUM(pr.ngr_amount), 0)) AS actual_commission_rate\nFROM attribution_map am\nJOIN affiliate_conversions ac \n ON ac.player_id = am.player_id\nLEFT JOIN platform_revenue pr\n ON pr.player_id = am.player_id\n AND DATE_TRUNC('month', pr.revenue_date) = DATE_TRUNC('month', ac.event_timestamp)\nWHERE ac.event_type = 'first_deposit'\n AND ac.status = 'approved'\nGROUP BY am.attributed_affiliate_id\nHAVING ABS((SUM(ac.commission_amount) \/ NULLIF(SUM(pr.ngr_amount), 0)) - 0.35) > 0.05;<\/code><\/pre>\n\n\n\nRun this monthly before payouts. If you see drift, you\u2019re either paying on the wrong \u201crevenue\u201d definition, or your rules aren\u2019t being applied consistently across events\/currencies\/time windows.<\/p>\n\n\n\n
Privacy and compliance note (don\u2019t skip this)<\/h2>\n\n\n\n
- Gaming platform:<\/strong> wagers, bonus cost, NGR (profitability truth)<\/li>\n\n\n\n
- Affiliate system:<\/strong> attribution, events, commission calculation<\/li>\n\n\n\n
- Which SubID<\/a> clusters (placements\/creatives) generate retained<\/strong> players instead of one-and-done FTDs?<\/li>\n\n\n\n