◾Levels based on players behavior

This page gives different ideas for using behavioral levels.

Scenario 1 — The Rolling 30-Day Activity Window

What it does: Measures a player's total deposits or wagering over the last 30 calendar days. Every time the system rebuilds (e.g., every Monday), it looks back exactly 30 days from today and places each player on the level their recent activity qualifies them for.

Why use it: This is the most common and versatile setup. It keeps your VIP tiers aligned with current player value — a high-roller who naturally goes quiet drifts down, freeing up tier capacity and budget for players who are actively contributing. Simultaneously, a previously inactive player who starts depositing heavily will quickly rise through the tiers.

Best for: Operators who want a clean, intuitive loyalty program where "if you play, you get rewarded; if you stop, you ease back down."

Rebuild frequency: Weekly (every Monday) or daily.

💡 Currency note: All deposit and wagering amounts are automatically converted to your label's default currency so that thresholds are the same for every player regardless of their wallet currency.

Reading the chart: The blue line is the player's rolling 30-day deposit total, recalculated every Monday. Coloured dots show the level assigned at each rebuild. When the line crosses a dashed threshold from below, the player is immediately promoted. When old large deposits fall outside the 30-day window, the line drops and the level adjusts downward. Recovery is instant — a renewed deposit spree the following week pushes the line back up.

Analysis with DWH (deposits): One row per player is built in the inner CTE by summing all approved deposit amounts within the last 30 days. Rollbacks (acc_is_rollback = TRUE) are excluded — only net-settled deposits count. Amounts in the player's wallet currency are divided by user_to_label_cur_rate to convert to the label's default currency before summing. The outer query groups into buckets so you can see how many players fall into each deposit range — use this distribution to decide where to draw your tier thresholds.

-- dwh_ext_{LABEL_ID}.tr_acc_deposit_approved
-- Shows total deposits per player over the last 30 days, bucketed for threshold analysis.
WITH deposits AS (
  SELECT
    user_id,
    SUM(acc_last_deposit_amount / user_to_label_cur_rate) AS total_deposits_30d
  FROM dwh_ext_{LABEL_ID}.tr_acc_deposit_approved
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND acc_is_rollback IS NOT TRUE
    AND user_to_label_cur_rate IS NOT NULL
    AND user_to_label_cur_rate > 0
  GROUP BY user_id
)
SELECT
  CASE
    WHEN total_deposits_30d <  50    THEN '01 | < 50'
    WHEN total_deposits_30d <  100   THEN '02 | 50–100'
    WHEN total_deposits_30d <  250   THEN '03 | 100–250'
    WHEN total_deposits_30d <  500   THEN '04 | 250–500'
    WHEN total_deposits_30d <  1000  THEN '05 | 500–1,000'
    WHEN total_deposits_30d <  2500  THEN '06 | 1,000–2,500'
    WHEN total_deposits_30d <  5000  THEN '07 | 2,500–5,000'
    WHEN total_deposits_30d <  10000 THEN '08 | 5,000–10,000'
    ELSE                                  '09 | 10,000+'
  END                       AS bucket,
  COUNT(*)                  AS player_count,
  ROUND(AVG(total_deposits_30d), 2) AS avg_in_bucket,
  ROUND(MIN(total_deposits_30d), 2) AS min_in_bucket,
  ROUND(MAX(total_deposits_30d), 2) AS max_in_bucket
FROM deposits
WHERE total_deposits_30d > 0
GROUP BY 1
ORDER BY 1

Analysis with DWH (casino wagering): Uses casino_last_bet_amount_real which carries only real-money stake (bonus-funded bets are in casino_last_bet_amount_bonus). Each row in tr_casino_bet_win is one settled bet — the CTE sums all bets per player within the window. Currency normalization and rollback exclusion follow the same pattern as the deposit query.

WITH wagering AS (
  SELECT
    user_id,
    SUM(casino_last_bet_amount_real / user_to_label_cur_rate) AS total_wagering_30d
  FROM dwh_ext_{LABEL_ID}.tr_casino_bet_win
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND casino_is_rollback IS NOT TRUE
    AND casino_last_bet_amount_real > 0
    AND user_to_label_cur_rate IS NOT NULL
    AND user_to_label_cur_rate > 0
  GROUP BY user_id
)
SELECT
  CASE
    WHEN total_wagering_30d <  100   THEN '01 | < 100'
    WHEN total_wagering_30d <  500   THEN '02 | 100–500'
    WHEN total_wagering_30d <  1000  THEN '03 | 500–1,000'
    WHEN total_wagering_30d <  5000  THEN '04 | 1,000–5,000'
    WHEN total_wagering_30d <  10000 THEN '05 | 5,000–10,000'
    WHEN total_wagering_30d <  25000 THEN '06 | 10,000–25,000'
    ELSE                                  '07 | 25,000+'
  END                       AS bucket,
  COUNT(*)                  AS player_count,
  ROUND(AVG(total_wagering_30d), 2) AS avg_in_bucket
FROM wagering
WHERE total_wagering_30d > 0
GROUP BY 1
ORDER BY 1

Scenario 2 — Dual-Metric Gate (Deposits AND Wagering)

What it does: Requires players to meet both a deposit threshold and a wagering threshold to qualify for each tier. For example: Silver = deposited at least €200 AND wagered at least €500 in the last 30 days. Note: You can control whether to include bonus money bets in the level counters or not based on your vision.

Why use it: Prevents "depositor but no player" abuse (someone who deposits large amounts but immediately withdraws) and "bonus hunter" patterns (someone who plays heavily on bonus money). The dual gate ensures your top-tier players are genuinely both financially committed and actively playing.

Best for: Operators with a bonus-heavy player base, or markets where deposit/withdrawal cycling is common.

Reading the chart: Three players, each shown with their deposit bar and wagering bar side by side for Gold tier. Green = clears the threshold; Red = blocked. Player A deposited enough but wagered just below the Gold wagering threshold — stays Silver. Player B wagered massively but didn't deposit enough — also stays Silver. Only Player C clears both gates and reaches Gold. This is the dual-gate mechanic in action.

Analysis with DWH: Deposits and wagering are aggregated independently in two CTEs over the same 30-day window, then joined on user_id with a FULL OUTER JOIN so players who only deposited or only wagered still appear. The CASE in the outer query simulates the dual-gate level assignment — a player only reaches a tier if both their deposit counter AND their wagering counter clear that tier's thresholds simultaneously. Edit the threshold numbers directly in the CASE statement to model different configurations before committing to the platform.

-- Combines both counters in one query so you can see both values per player
-- and simulate which tier each player would land on under a dual-gate setup.
WITH deposits AS (
  SELECT
    user_id,
    SUM(acc_last_deposit_amount / user_to_label_cur_rate) AS dep_30d
  FROM dwh_ext_{LABEL_ID}.tr_acc_deposit_approved
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND acc_is_rollback IS NOT TRUE
    AND user_to_label_cur_rate > 0
  GROUP BY user_id
),
wagering AS (
  SELECT
    user_id,
    SUM(casino_last_bet_amount_real / user_to_label_cur_rate) AS wag_30d
  FROM dwh_ext_{LABEL_ID}.tr_casino_bet_win
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND casino_is_rollback IS NOT TRUE
    AND casino_last_bet_amount_real > 0
    AND user_to_label_cur_rate > 0
  GROUP BY user_id
),
combined AS (
  SELECT
    COALESCE(d.user_id, w.user_id)  AS user_id,
    COALESCE(d.dep_30d, 0)          AS dep_30d,
    COALESCE(w.wag_30d, 0)          AS wag_30d
  FROM deposits d
  FULL OUTER JOIN wagering w ON d.user_id = w.user_id
)
SELECT
  -- Replace these threshold values with your actual tier thresholds
  CASE
    WHEN dep_30d >= 2500 AND wag_30d >= 10000 THEN 'Diamond'
    WHEN dep_30d >= 1000 AND wag_30d >= 4000  THEN 'Platinum'
    WHEN dep_30d >= 500  AND wag_30d >= 1500  THEN 'Gold'
    WHEN dep_30d >= 200  AND wag_30d >= 500   THEN 'Silver'
    ELSE 'Bronze'
  END           AS simulated_level,
  COUNT(*)      AS player_count,
  ROUND(AVG(dep_30d), 2)  AS avg_deposits,
  ROUND(AVG(wag_30d), 2)  AS avg_wagering
FROM combined
WHERE dep_30d > 0 OR wag_30d > 0
GROUP BY 1
ORDER BY MIN(dep_30d) DESC

Scenario 3 — Active Betting Days (Consistency, Not Volume)

What it does: Counts the number of distinct days in the last 30 days on which the player placed at least one bet or made at least one deposit. A player who bets small amounts every single day scores higher than someone who placed one large bet on day 1.

Why use it: Pure volume metrics favor whales. Active-days metrics favor habit and consistency — the kinds of players with high long-term retention value. Players who are "one day away from losing their Silver tier" have a concrete, achievable reason to log in today.

Best for: Operators optimizing for long-term retention and daily active user metrics. Sports betting platforms where frequent small bets are the norm.

Reading the chart: players are getting different levels depending on how many days they were active (betting or depositing) in the last 30 days.

More ideas:

• Combine it with the wagering to avoid cases when players do one small bet every day and achieve high levels

• Use a fixed period, e.g., 7 levels based on the number of active days during a week

• Combine a fixed period with retention, e.g., take the maximum of active days from the current and previous weeks. This way, a player who was active for 5 days in the previous week will remain at level 5 even on the 1st day of the current week. If he was active for only 4 days this week, he will be downgraded to level 4 next week.

Analysis with DWH: tr_casino_bet_win and tr_sport_bet_open each yield one row per bet. Both are reduced to (user_id, calendar_date) pairs, then combined with UNION DISTINCT — so if a player bet on casino AND sport on the same day, that day counts once. COUNT(DISTINCT bet_date) per player gives the number of unique active days. Free bets are excluded from the sport count via sport_is_free_bet IS NOT TRUE. The result shows the full distribution from 1 to 30 days — use it to decide where to draw tier boundaries.

-- dwh_ext_{LABEL_ID}.tr_casino_bet_win + tr_sport_bet_open
-- Counts distinct calendar days with at least one bet (casino or sport) per player
-- in the last 30 days.
WITH casino_days AS (
  SELECT user_id, DATE(event_time) AS bet_date
  FROM dwh_ext_{LABEL_ID}.tr_casino_bet_win
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND casino_last_bet_amount > 0
),
sport_days AS (
  SELECT user_id, DATE(event_time) AS bet_date
  FROM dwh_ext_{LABEL_ID}.tr_sport_bet_open
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND sport_last_bet_amount > 0
    AND sport_is_free_bet IS NOT TRUE
),
all_days AS (
  SELECT user_id, bet_date FROM casino_days
  UNION DISTINCT
  SELECT user_id, bet_date FROM sport_days
),
active_days AS (
  SELECT user_id, COUNT(DISTINCT bet_date) AS days_active
  FROM all_days
  GROUP BY user_id
)
SELECT
  days_active,
  COUNT(*) AS player_count
FROM active_days
GROUP BY 1
ORDER BY 1

Variant — deposit days instead of betting days: Replace both CTEs with a single query on tr_acc_deposit_approved filtering acc_last_deposit_amount > 0 and acc_is_rollback IS NOT TRUE. Everything else stays identical.

Scenario 4 — Calendar Month Accumulation (Resets on the 1st)

What it does: Accumulates a player's deposits or wagering from the 1st of the current calendar month. On the 1st of next month, the counter resets to zero — players must re-earn their level each month.

Why use it: Creates a monthly re-engagement cycle. Players who achieved Gold last month have a strong incentive to repeat that performance this month. The visible approaching reset creates urgency that no other pattern matches.

Best for: Operators who want a monthly loyalty program rhythm aligned with their bonus calendar.

Reading the chart: The purple line climbs steadily within each month as deposits accumulate. On February 1st and March 1st, the line drops sharply to zero — the monthly reset. Each month the player must climb the same thresholds again from scratch. In January and February, they reached Gold; in March, they ran out of steam at Silver. The reset creates a fresh competitive cycle every month.

More ideas:

• Make a shorter window, e.g., weekly

• Combine with retention, do weekly reset but take max(current period, previous period)

Analysis with DWH: DATE_TRUNC(CURRENT_DATE(), MONTH) resolves to midnight on the 1st of the current month — so only events from that point forward are counted. The counter grows throughout the month as players deposit. When the rebuild runs on the 1st of next month, the window shifts and all players start from zero again. Run this query at any point in the month to preview the current distribution and simulate what levels would be assigned if the rebuild ran right now.

-- dwh_ext_{LABEL_ID}.tr_acc_deposit_approved
-- Sums deposits from the 1st of the current calendar month only.
-- This mirrors exactly what the level counter will read at each rebuild.
WITH mtd_deposits AS (
  SELECT
    user_id,
    SUM(acc_last_deposit_amount / user_to_label_cur_rate) AS deposits_mtd
  FROM dwh_ext_{LABEL_ID}.tr_acc_deposit_approved
  WHERE event_time >= TIMESTAMP(DATE_TRUNC(CURRENT_DATE(), MONTH))
    AND acc_is_rollback IS NOT TRUE
    AND user_to_label_cur_rate IS NOT NULL
    AND user_to_label_cur_rate > 0
  GROUP BY user_id
)
SELECT
  CASE
    WHEN deposits_mtd <  50    THEN '01 | < 50'
    WHEN deposits_mtd <  100   THEN '02 | 50–100'
    WHEN deposits_mtd <  250   THEN '03 | 100–250'
    WHEN deposits_mtd <  500   THEN '04 | 250–500'
    WHEN deposits_mtd <  1000  THEN '05 | 500–1,000'
    WHEN deposits_mtd <  2500  THEN '06 | 1,000–2,500'
    WHEN deposits_mtd <  5000  THEN '07 | 2,500–5,000'
    ELSE                            '08 | 5,000+'
  END          AS bucket,
  COUNT(*)     AS player_count,
  ROUND(AVG(deposits_mtd), 2) AS avg_in_bucket
FROM mtd_deposits
WHERE deposits_mtd > 0
GROUP BY 1
ORDER BY 1

Variant — current week instead of month: Replace the filter with event_time >= TIMESTAMP(DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY))) to accumulate from the most recent Monday.

Scenario 5 — Additional Virtual Currency Balance (Gems / Diamonds)

What it does: Counts diamonds earned in the current calendar month and the previous calendar month, then takes the higher of the two as the counter value. A player who earned heavily in Month 1 is protected through all of Month 2 even with zero activity. Only if Month 3 also shows insufficient earnings does the level finally drop.

Why use it: Virtual currency balances fluctuate when players spend gems in the store — making raw balance unreliable as a level counter. Earned diamonds per month is a pure activity signal. The two-month maximum gives players a natural grace month before any downgrade, eliminating frustration from immediate post-month drops.

Best for: Operators with an active gamification economy (missions, achievements, tournaments) that award diamonds as a reward currency.

Reading the chart: The solid red line is the counter the engine uses — MAX(earned this month, earned last month). The dashed purple line is what the player actually earned in the current month alone. The gap between them is the grace protection. Level labels sit above each monthly dot.

Month

Earned this month

Earned last month

Counter (MAX)

Level

What happened

Oct	820	—	820	🥇 Gold	Strong earning month, reaches Gold
Nov	210	820	820	🥇 Gold	Barely active, but Oct's 820 protects — stays Gold for the full month
Dec	95	210	210	🥈 Silver	Oct's big number is now gone. Nov's 210 gives a partial save — Silver, not Bronze
Jan	40	95	95	🥉 Bronze	Both months weak — no protection left, final downgrade

Scenario 6 — Combined Sport + Casino Wagering

What it does: Either combines casino and sport wagering into a single total counter, or uses one counter per vertical. Players must meet both thresholds, or a combined total — depending on configuration.

Why use it: Rewards cross-vertical engagement appropriately. A combined counter avoids disadvantaging players whose preferred vertical is temporarily less attractive (e.g., during an off-season in sport).

Best for: Multi-vertical operators (casino + sportsbook) who want a unified loyalty program.

Reading the chart: Each stacked bar shows a player's total wagering split by vertical (blue = casino, teal = sport). The combined bar height is what the counter sees. Player A is almost exclusively a casino player — still qualifies for Silver on combined total. Player B is almost exclusively a sport bettor — same result. Player D crosses the Gold threshold with a strong combination of both. Player E falls short of Silver despite activity in both verticals — the total simply isn't there yet.

Analysis with DWH: Casino wagering (real money only, rollbacks excluded) and sport wagering (free bets excluded) are aggregated independently over 30 days. A FULL OUTER JOIN preserves single-vertical players — a casino-only player has sport_wag = 0 and vice versa. The player_type grouping immediately shows you the vertical mix of your active player base: if 80% are casino-only, a combined counter is fairer than two independent gates. COALESCE(casino_wag, 0) + COALESCE(sport_wag, 0) is the combined total that a single-counter configuration would use.

-- dwh_ext_{LABEL_ID}.tr_casino_bet_win  (casino)
-- dwh_ext_{LABEL_ID}.tr_sport_bet_open  (sport)
-- Sums casino and sport wagering independently, then combines into a single total.
-- Shows per-player split and combined total so you can configure either a
-- single combined counter or two independent vertical counters.
WITH casino AS (
  SELECT
    user_id,
    SUM(casino_last_bet_amount_real / user_to_label_cur_rate) AS casino_wag
  FROM dwh_ext_{LABEL_ID}.tr_casino_bet_win
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND casino_is_rollback IS NOT TRUE
    AND casino_last_bet_amount_real > 0
    AND user_to_label_cur_rate > 0
  GROUP BY user_id
),
sport AS (
  SELECT
    user_id,
    SUM(sport_last_bet_amount / user_to_label_cur_rate) AS sport_wag
  FROM dwh_ext_{LABEL_ID}.tr_sport_bet_open
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND sport_is_free_bet IS NOT TRUE
    AND sport_last_bet_amount > 0
    AND user_to_label_cur_rate > 0
  GROUP BY user_id
),
combined AS (
  SELECT
    COALESCE(c.user_id, s.user_id)    AS user_id,
    COALESCE(c.casino_wag, 0)         AS casino_wag,
    COALESCE(s.sport_wag, 0)          AS sport_wag,
    COALESCE(c.casino_wag, 0) + COALESCE(s.sport_wag, 0) AS total_wag
  FROM casino c
  FULL OUTER JOIN sport s ON c.user_id = s.user_id
)
SELECT
  CASE
    WHEN casino_wag > 0 AND sport_wag > 0 THEN 'Both verticals'
    WHEN casino_wag > 0                   THEN 'Casino only'
    WHEN sport_wag  > 0                   THEN 'Sport only'
  END                       AS player_type,
  COUNT(*)                  AS player_count,
  ROUND(AVG(casino_wag), 2) AS avg_casino_wagering,
  ROUND(AVG(sport_wag),  2) AS avg_sport_wagering,
  ROUND(AVG(total_wag),  2) AS avg_combined_wagering,
  ROUND(MIN(total_wag),  2) AS min_combined,
  ROUND(MAX(total_wag),  2) AS max_combined
FROM combined
WHERE total_wag > 0
GROUP BY 1
ORDER BY 1

Scenario 7 — Recent + Lifetime Hybrid

What it does: Counter 1 = rolling 30-day deposits (recent activity gate). Counter 2 = lifetime deposits (historical commitment gate). To reach a top tier, a player must have both strong recent activity AND a significant lifetime history.

Why use it: Prevents new high-rollers from jumping straight to Diamond after two weeks of heavy play, while also preventing churned long-term players from coasting on historical deposits. Top tiers genuinely represent both loyal and currently active players.

Best for: Operators with a mature player base where true VIPs are expected to demonstrate both longevity and current engagement.

Reading the chart: Each dot is a player archetype positioned by their 30-day counter (horizontal) and lifetime counter (vertical). Level is shown by dot color. The key insight: both the "New high-roller" (strong recent, little history) and the "Churned VIP" (rich history, no recent activity) land at Bronze or Silver despite impressive numbers in one dimension — because they fail the other gate. Only the "True VIP" who scores well on both axes reaches Platinum.

Analysis with DWH: recent and lifetime CTEs query the same tr_acc_deposit_approved table with different event_time filters. The recent CTE uses a 30-day rolling window; the lifetime CTE uses a fixed far-past start date to capture all history. After joining, the CASE block simulates the dual-gate assignment — a player only advances to a tier if both their 30-day counter AND their lifetime counter clear that tier's thresholds. The two COUNTIF diagnostic columns flag players who are likely churned long-term users (large lifetime, tiny recent) and likely new high-rollers (large recent relative to lifetime) — the two edge cases this hybrid pattern is specifically designed to handle correctly.

-- dwh_ext_{LABEL_ID}.tr_acc_deposit_approved
-- Counter 1: rolling 30-day deposits (current activity)
-- Counter 2: all-time lifetime deposits (historical commitment)
-- Both are calculated from the same table — different time windows only.
WITH recent AS (
  SELECT
    user_id,
    SUM(acc_last_deposit_amount / user_to_label_cur_rate) AS dep_30d
  FROM dwh_ext_{LABEL_ID}.tr_acc_deposit_approved
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND acc_is_rollback IS NOT TRUE
    AND user_to_label_cur_rate > 0
  GROUP BY user_id
),
lifetime AS (
  SELECT
    user_id,
    SUM(acc_last_deposit_amount / user_to_label_cur_rate) AS dep_lifetime
  FROM dwh_ext_{LABEL_ID}.tr_acc_deposit_approved
  WHERE event_time >= TIMESTAMP('2000-01-01')   -- full history; adjust if needed
    AND acc_is_rollback IS NOT TRUE
    AND user_to_label_cur_rate > 0
  GROUP BY user_id
),
combined AS (
  SELECT
    COALESCE(r.user_id, l.user_id)  AS user_id,
    COALESCE(r.dep_30d, 0)          AS dep_30d,
    COALESCE(l.dep_lifetime, 0)     AS dep_lifetime
  FROM recent r
  FULL OUTER JOIN lifetime l ON r.user_id = l.user_id
)
SELECT
  -- Simulate dual-gate level assignment. Adjust thresholds as needed.
  CASE
    WHEN dep_30d >= 2500 AND dep_lifetime >= 50000  THEN 'Diamond'
    WHEN dep_30d >= 1000 AND dep_lifetime >= 15000  THEN 'Platinum'
    WHEN dep_30d >= 500  AND dep_lifetime >= 5000   THEN 'Gold'
    WHEN dep_30d >= 200  AND dep_lifetime >= 1000   THEN 'Silver'
    ELSE 'Bronze'
  END                         AS simulated_level,
  COUNT(*)                    AS player_count,
  ROUND(AVG(dep_30d), 2)      AS avg_recent_deposits,
  ROUND(AVG(dep_lifetime), 2) AS avg_lifetime_deposits,
  -- Shows how many players are blocked by recent vs lifetime being the weak gate
  COUNTIF(dep_30d < dep_lifetime * 0.05) AS likely_churned_vips,
  COUNTIF(dep_lifetime < dep_30d * 2)    AS likely_new_high_rollers
FROM combined
WHERE dep_30d > 0 OR dep_lifetime > 0
GROUP BY 1
ORDER BY MIN(dep_30d) DESC