iGaming Data Mart
A star schema for bets, payments and bonuses, built with dbt on DuckDB. Every report reads the same GGR, NGR and FTD, in euros, on Malta reporting days, with each player's VIP tier as it was at the time.
What the mart holds
One star schema, seven fact tables
Every fact carries the player's version key, the reporting day, the brand and the market. Select a table to see its grain.
- Table
- fct_wagering_daily
- Grain
- One row per player, game (or sport) and Malta reporting day
- About
- Real-money and bonus wagering. Casino play is dated by session start, sportsbook by settlement. 1,861,761 rows.
All 19 tables in the mart, with their grain and row counts
| Table | Type | Grain | Rows |
|---|---|---|---|
fct_wagering_daily | Fact | Player x game x reporting day | 1,861,761 |
fct_sportsbook_bets | Fact | Bet | 1,222,610 |
fct_player_activity_daily | Fact | Player x reporting day with real-money play | 1,002,694 |
fct_payments | Fact | Deposit or withdrawal attempt | 581,857 |
fct_campaign_assignments | Fact | Player x CRM campaign (offer or holdout) | 167,705 |
fct_bonuses | Fact | Bonus, from grant to settlement | 46,507 |
fct_account_events | Fact | Deposit limit, cool-off or self-exclusion | 8,134 |
dim_player | Dimension | Player x VIP tier version (SCD Type 2) | 77,858 |
dim_date | Dimension | Malta reporting day | 647 |
dim_game | Dimension | Casino game or sport | 114 |
dim_affiliate | Dimension | Affiliate and its deal (CPA, revenue share or hybrid) | 60 |
dim_campaign | Dimension | Welcome or CRM offer | 15 |
dim_provider | Dimension | Game provider, plus the in-house sportsbook | 9 |
dim_market | Dimension | Market, with currency and time zone | 7 |
dim_psp | Dimension | Payment service provider | 5 |
dim_brand | Dimension | Brand | 3 |
kpi_daily_ggr_by_product | Metrics | Day x brand x market x product | 29,841 |
kpi_daily_revenue | Metrics | Day x brand x market x product group | 15,188 |
kpi_daily_accounts | Metrics | Day x brand x market | 7,606 |
One definition per metric
GGR and NGR are dbt macros. Every model that reports them calls the same macro, so a definition changes in one place.
| Metric | Definition | Dated by |
|---|---|---|
| GGR | Real-money stakes minus real-money winnings. Bonus-funded play is left out. | Casino: session start. Sportsbook: settlement. |
| Bonus cost | Bonus money that converted to withdrawable cash. Bonuses that are lost or expire cost nothing. | The day the bonus converts |
| NGR | GGR minus bonus cost. | As GGR and bonus cost |
| Expected GGR | What the house edge predicts: stake x (1 − RTP) for casino, stake x the bet's margin for sportsbook. | As GGR |
| FTD | A player's first successful deposit, counted once per player. | The day of the deposit |
| Deposits | Successful deposits only. Failed attempts and duplicate PSP callbacks are excluded. | The day of the request |
| Active player | Placed at least one real-money bet. Counted once per player in any period. | Casino: session start. Sportsbook: placement. |
The macros
Models never write stake - win themselves. They call the macro, and the dashboards show the wording from one reference table.
-- macros/metrics.sql {% macro ggr(cash_stake, cash_win) -%} ({{ cash_stake }} - {{ cash_win }}) {%- endmacro %} {% macro ngr(ggr, bonus_cost) -%} ({{ ggr }} - {{ bonus_cost }}) {%- endmacro %} -- marts/metrics/kpi_daily_revenue.sql {{ ngr('ggr_eur', 'bonus_cost_eur') }} as ngr_eur
What NGR leaves out
Operators define NGR differently. Many also deduct gaming duty and payment fees. This project deducts bonus cost only, and says so wherever NGR appears, so nobody compares it with a figure that means something else.
Why bonus play is outside GGR
Bonus money that a player loses never cost the operator anything. Counting its stakes would inflate GGR, and then counting the bonus again in NGR would double-count it. So bonus stakes and wins are kept in their own columns, and the cost enters NGR once, when the bonus converts to cash.
One currency, one calendar
The platform logs UTC and the player's currency. The mart reports every amount in euros, on the Malta calendar day.
A late-night session in Auckland
One simulated casino session from New Zealand, followed through the mart.
- 13:41 UTC, Saturday 5 September 2026The platform's timestamp
- 01:41, Sunday 6 SeptemberThe player's clock in Auckland
- 15:41, Saturday 5 September in MaltaSo the session is reported on Saturday 5 September
- NZ$158.16 × 0.5640 = €89.20Converted at Saturday's rate, which is Friday's, carried forward
Weekend exchange rates
Reference rates are published on business days. The mart fills the 780 currency-days without a published rate with the last rate before them.
| Day | EUR per GBP | Source |
|---|---|---|
| Fri 4 Sep 2026 | 1.2323 | Published |
| Sat 5 Sep | 1.2323 | Carried forward |
| Sun 6 Sep | 1.2323 | Carried forward |
| Mon 7 Sep | 1.2268 | Published |
Rates are simulated.
Casino sessions reported on a different day than the player's own date
Canadian evenings run past midnight in Malta. New Zealand mornings start before Malta's day does. Reporting on local dates would split the same day differently in every market.
Sportsbook revenue is booked when a bet settles, not when it is placed. 52% of settled bets settle on a later Malta day than they were placed, and 7,076 bets were still open when the period ended.
Revenue stays with the tier it was earned in
The CRM logs every tier change. The mart turns the log into date ranges, so each bet joins the tier the player held on that day.
One simulated player, six versions
A VIP manager upgraded this player to Gold a week after they joined. Monthly reviews then moved them down and back up. GGR is what the player lost to the house in each version. It was negative while they were Gold, because they won more than they staked.
- Bronze16–23 Jan€1,022
- Gold24 Jan – 31 May · manual upgrade−€3,002
- SilverJune−€49
- BronzeJuly – August€1,658
- SilverSeptember€2,387
- GoldFrom 1 Octcurrent
2026. The CRM log had 78,366 rows. The dimension has 77,858 versions: 508 rows repeated the current tier or were replaced later the same day, so they don't open a version.
GGR from July to September 2026, by VIP tier
Joining revenue to each player's current tier moves it up the ladder, because the players who lost the most were promoted on 1 October. Silver would get €620K instead of the €222K its members produced while they were Silver.
86 tests, run on every build
dbt runs the tests after each model it builds. If one fails, nothing downstream of it is built.
The custom checks
- GGR in the mart matches GGR computed straight from the source tables, so no join duplicated or dropped rows.
- The KPI tables add up to the facts they summarise.
- NGR equals GGR minus bonus cost on every row.
- Every amount finds an exchange rate for its currency and day.
- Each player's VIP versions follow each other with no gap or overlap.
- Each player has exactly one current VIP version.
- Each player has at most one FTD, and it is a successful deposit.
- No player is active, or deposits, before the day they registered.
Problems in the source data
The simulator writes data the way real platforms do, problems included. The staging models fix them.
| 1,746 | PSP callbacks delivered twice. The first delivery is kept. |
| 120,997 | Payment statuses sent in capitals by one PSP. Normalised. |
| 780 | Currency-days with no published rate. The last rate is carried forward. |
| 508 | VIP log rows that change nothing. They don't open a version. |
| 7,076 | Bets still open at the end. Left out of GGR until they settle. |
The tests caught a bug in my simulator. On a player's first day, play could be timestamped before they registered. For a Canadian player who registered late in the evening, that put their first bets on an earlier Malta day than their registration. The activity check failed, and I fixed the simulator.
Runs on a laptop, written to move
DuckDB builds and tests the whole mart locally. The SQL is kept easy to move to BigQuery.
Engine differences in four macros
Time-zone conversion, the date series, day of week and the float type differ between DuckDB and BigQuery. Each lives in one macro with a version per engine, and dbt picks the right one. Everything else uses SQL both engines accept. The BigQuery versions have not been run yet.
{% macro default__date_in_tz(ts, tz) -%} cast(timezone({{ tz }}, timezone('UTC', {{ ts }})) as date) {%- endmacro %} {% macro bigquery__date_in_tz(ts, tz) -%} date({{ ts }}, {{ tz }}) {%- endmacro %}
Rebuild it yourself
One command simulates the platform, builds the 41 models and runs the tests, in under a minute on a laptop.
# Python 3.12
pip install -r requirements.txt
python run_pipeline.py
The simulator uses a fixed seed, so every run produces the same data and the same numbers as this page.
What comes next
- In progress
Executive dashboard
GGR, NGR, FTDs, deposits and active players by brand, market and product, against target and the same day last week.
- In progress
NGR investigation
Why NGR fell in September: results luck, player behaviour, acquisition or bonus cost.
- In progress
Bonus ROI
Incremental NGR from reload offers, measured against a random holdout group.
Want to walk through the model?
I can talk through the grain decisions, the definitions and how this would run on a production warehouse.