How to measure a player's daily aggregation?
Creating a daily aggregation that is appended each day will enable you to learn trends of your players and segment them and even use machine learning modules to cluster players and predict purchases and churn. Run the aggregation using the Materialized View feature daily with a
select
customer_user_id as customer_user_id,
date(event_time_ts) as activity_date ,
sum(case when event_name=’spin’ then 1 else 0 end ) as spins,
sum(case when event_name=’spin’ then total_spent end ) as total_spent,
sum(case when event_name=’spin’ then total_won end ) as total_won,
avg(case when event_name=’spin’ then total_spent end ) as avg_bet,
avg(case when event_name=’spin’ then total_won end ) as avg_win,
count(distinct case when event_name=’spin’ then game_id end ) as games_played,
max(total_spent) as highest_bet,
max(total_won) as highest_win,
max(balance) as highest_balance,
min(balance) as lowest_balance,
sum(case when event_name='spin' then total_won end )/
sum(case when event_name='spin' then total_spent end ) as RTP,
count( distinct session_id) as sessions,
sum(case when event_name = 'purchase' and purchase_status =’success’ then amount end) as money_spent,
sum(case when event_name = 'purchase' and purchase_status =’success’ then 1 else 0 end) as purchases,
count(distinct case when event_name=’spin’ then level end ) as levels
from cooladata
where date_range(yesterday)
group by 1,2
