The Problem
A crypto casino operator with 50,000 active players saw GGR drop 15% quarter over quarter. The cause: unoptimized bonuses and floating RTP. Without solid analytics, the casino operates blindly—bonus campaigns bleed uncontrollably, churn rises.
We build analytics systems for crypto casinos that turn raw data into management decisions. Our team includes certified ClickHouse engineers with 5+ years in iGaming analytics. We've delivered 30+ projects processing up to 10 million bets daily. One client was losing $250,000 monthly due to inefficient bonuses; after implementation, the loss dropped 40%. In another case, RTP monitoring caught a smart contract bug, saving $75,000 per quarter. Start with a pilot—get your first metrics in 2 weeks.
What We Solve
- Uncontrolled Bonus Spend: Without proper attribution, bonuses inflate costs. Our system ties each bonus to a player’s NGR, so you see ROI instantly.
- Hidden RTP Drift: In crypto casinos, on-chain or off-chain RTP can deviate from the expected value. We detect anomalies that signal implementation errors or exploits.
- Slow Query Performance: Standard databases choke on millions of rows. ClickHouse delivers aggregations 10–50x faster than PostgreSQL.
How We Build It
Our architecture uses ClickHouse with a star schema model and Python-based ETL pipelines. The columnar DB gives 10–50x speedup for aggregations. Star schema separates facts (bets) from dimensions (players, games), simplifying scaling. Real-time dashboards provide a full business picture: from a single game session to global trends. A project from scratch to production takes from 4 weeks.
Key Metrics for Crypto Casino Analytics
Here are the KPIs we track:
| Metric | Formula / Description | Purpose |
|---|---|---|
| GGR | Bets − Wins | Gross gaming revenue |
| NGR | GGR − Bonuses − Rakeback | Net gaming revenue |
| RTP | (Wins / Bets) × 100% | Actual game return |
| LTV | Projected NGR over lifetime | Player value |
| Churn Rate | % of players lost in period | Retention tracking |
GGR, NGR, and RTP are the foundation. Without them, you can't evaluate bonus effectiveness, identify leaks, or plan liquidity. For example, if a slot RTP exceeds 100%, that's an immediate trigger for contract audit.
Building an Efficient Analytical Warehouse
The optimal architecture is a star schema on ClickHouse. Compared to PostgreSQL, aggregation queries run 10–50x faster thanks to columnar storage. Example structure:
-- 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), 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: players 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 Process: From Bet to Dashboard
Every hour we collect raw data from the operational DB, transform it, and load into ClickHouse. Example pipeline:
class CasinoAnalyticsETL: async def run_hourly_aggregation(self): now = datetime.utcnow() hour_start = now.replace(minute=0, second=0, microsecond=0) bets = await self.bet_repo.get_settled_bets_since(hour_start - timedelta(hours=1)) rows = [self.transform_bet(bet) for bet in bets] if rows: await self.clickhouse.insert('fact_bets', rows) 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, } Materialized views update automatically and return aggregates for dashboards in milliseconds. ETL errors are logged and processed with a retry policy.
Analytical Queries: Cohorts and RTP
Retention cohort 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 >= now() - INTERVAL 180 DAY ) GROUP BY registration_cohort, days_since_registration ORDER BY registration_cohort, days_since_registration; RTP analysis by game:
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 now() - INTERVAL 60 DAY AND now() AND NOT is_free_bet GROUP BY game_id, game_category ORDER BY total_wagered DESC; Detecting Anomalies in Real Time
Query for suspicious activity:
SELECT user_id, count() AS bet_count, sum(winnings) / sum(amount) AS rtp, sum(ggr) AS user_ggr, max(winnings) AS max_single_win FROM fact_bets WHERE bet_time >= now() - INTERVAL 7 DAY GROUP BY user_id HAVING rtp > 1.5 AND bet_count > 50 ORDER BY rtp DESC LIMIT 100; We augment this with a dynamic threshold based on a moving average of RTP. In one project, such monitoring caught a smart contract bug that gave a group of players 1.8 RTP—a $75,000 quarterly loss was fixed in 2 days.
Dashboard Tools Comparison
| Tool | Metric Type | Dashboard Load | Recommendation |
|---|---|---|---|
| Apache Superset | Financial, cohort | 1-2 sec | Complex analytics and reports |
| Grafana | Operational, real-time | <1 sec | Real-time monitoring |
| Metabase | Simple, self-service | 2-4 sec | Small teams |
What’s Included in the Analytics System
- Audit of current data and requirements—analyze sources, structure, data quality.
- Star schema and ETL design—data model tailored to your metrics.
- Pipeline development—collection, transformation, loading with monitoring.
- Materialized views—pre-aggregated for fast dashboards.
- Dashboards—3 sets: operational (real-time), financial (GGR/NGR), cohort (LTV, retention).
- Documentation—schema, dashboard descriptions, guide for adding new games.
- Team training—2-3 sessions on system usage.
- 3 months support—bug fixes, query optimization, metric additions.
Development Process
- Audit current data and requirements—1 week.
- Design star schema and ETL—1-2 weeks.
- Build pipelines and materialized views—2-3 weeks.
- Set up dashboards (3 types)—1 week.
- Test metric accuracy and performance—3-4 days.
- Team training and documentation handoff—2 days.
- 3 months support—enhancements, bug fixes, optimization.
Timeline Estimates
A basic pilot with GGR, NGR, RTP, and simple dashboards—from 2 weeks. A full system with cohort analysis, fraud detection, and integration of all games—from 4 to 8 weeks. Exact timelines are assessed individually after requirements gathering.
Contact us for a free preliminary assessment of your project. Order the development of an analytics system—get transparent metrics and control over your business.