Crypto Casino Analytics System Development

Crypto Casino Analytics System Development

Casino analytics is a tool for operational decision-making: understanding player behavior, optimizing bonus programs, detecting fraud, planning liquidity. Without quality analytics, a casino is flying blind.

Key Metrics

GGR (Gross Gaming Revenue) = total bets − total winnings. Basic casino income metric.

NGR (Net Gaming Revenue) = GGR − bonuses − rakeback − promo costs. Actual revenue.

RTP (Return to Player) = total winnings / total bets × 100%. Theoretically depends on the game; actually — volatility indicator for the period.

Player Lifetime Value (LTV) — predicted total NGR from a player over their lifetime.

Churn Rate — percentage of players who stopped playing in a period.

Average Session Duration, Bet Frequency, Average Bet Size — behavioral metrics.

Analytical Data Warehouse

For casino analytics, it's optimal to use a star schema in ClickHouse or BigQuery:

-- Fact table: each bet
CREATE TABLE fact_bets (
    bet_id          String,
    user_id         String,
    game_id         String,
    session_id      String,
    bet_time        DateTime,
    amount          Decimal(24, 8),
    currency        LowCardinality(String),
    winnings        Decimal(24, 8),
    ggr             Decimal(24, 8),  -- amount - winnings
    is_free_bet     Bool,
    bonus_used      Nullable(String),
    game_category   LowCardinality(String),
    country         LowCardinality(String),
    device_type     LowCardinality(String),
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(bet_time)
ORDER BY (user_id, bet_time);

-- Dimension: users
CREATE TABLE dim_users (
    user_id         String,
    registration_date Date,
    country         LowCardinality(String),
    acquisition_channel LowCardinality(String),
    vip_level       LowCardinality(String),
    first_deposit_date Nullable(Date),
    total_deposits  Decimal(24, 8),
    total_withdrawals Decimal(24, 8),
)
ENGINE = ReplacingMergeTree()
ORDER BY user_id;

ETL Pipeline

class CasinoAnalyticsETL:
    async def run_hourly_aggregation(self):
        """Update analytical aggregations every hour"""
        now = datetime.utcnow()
        hour_start = now.replace(minute=0, second=0, microsecond=0)

        # Load raw data from operational DB
        bets = await self.bet_repo.get_settled_bets_since(hour_start - timedelta(hours=1))

        # Transform and load into ClickHouse
        rows = [self.transform_bet(bet) for bet in bets]

        if rows:
            await self.clickhouse.insert('fact_bets', rows)

        # Update materialized views
        await self.update_materialized_views()

    def transform_bet(self, bet: Bet) -> dict:
        return {
            "bet_id": str(bet.id),
            "user_id": str(bet.user_id),
            "game_id": bet.game_id,
            "bet_time": bet.settled_at,
            "amount": float(bet.amount),
            "currency": bet.currency,
            "winnings": float(bet.winnings),
            "ggr": float(bet.amount - bet.winnings),
            "is_free_bet": bet.is_free_bet,
            "game_category": bet.game_category,
            "country": bet.user_country,
            "device_type": bet.device_type,
        }

Analytical Queries

Cohort retention analysis:

SELECT
    registration_cohort,
    days_since_registration,
    count(DISTINCT user_id) AS active_users,
    sum(ggr) AS cohort_ggr
FROM (
    SELECT
        b.user_id,
        toStartOfWeek(u.registration_date) AS registration_cohort,
        dateDiff('day', u.registration_date, b.bet_time) AS days_since_registration,
        b.ggr
    FROM fact_bets b
    JOIN dim_users u ON b.user_id = u.user_id
    WHERE b.bet_time >= today() - 180
)
GROUP BY registration_cohort, days_since_registration
ORDER BY registration_cohort, days_since_registration;

RTP analysis by games:

SELECT
    game_id,
    game_category,
    count() AS bet_count,
    sum(amount) AS total_wagered,
    sum(winnings) AS total_paid,
    sum(ggr) AS total_ggr,
    sum(winnings) / sum(amount) AS actual_rtp,
    countIf(ggr < 0) AS losing_rounds,
    countIf(ggr >= 0) AS winning_rounds
FROM fact_bets
WHERE bet_time BETWEEN '2024-01-01' AND '2024-02-01'
  AND NOT is_free_bet
GROUP BY game_id, game_category
ORDER BY total_wagered DESC;

Fraud Detection Analytics

-- Abnormal win rate (possible cheating)
SELECT
    user_id,
    count() AS bet_count,
    sum(winnings) / sum(amount) AS rtp,
    sum(ggr) AS user_ggr,

Casino analytics provides a complete picture of performance and helps make data-driven decisions.