Time Series Analysis

Showing 1 - 10 of 38 Items

You can use the Cohort CQL to calculate the average deposit amount per day.

“COHORT BY 1 DAYS EACH “ segments the users by their install day but can be changed to segment by property or any other time frame( see Cohort CQL documentation).

If you don’t have an install event, use your signup or registration event. Use the heatmap visualization below.

PV by Cohort 
SELECT cohort_name, cohort_id, cohort_size, bucket_id, SUM(deposit)/cohort_size as average_daily_revenue
FROM cooladata
WHERE date_range(context) and filters(context) 
CLUSTER COHORT BY 1 DAYS EACH
STARTS WITH event_name = 'install'
FOLLOWED BY event_name = 'purchase'
BUCKET BY 1 DAYS
GROUP BY cohort_name, cohort_id, cohort_size, bucket_id      
Customer Lifetime Value Analysis
kpi

Use our Cohort CQL to calculate a user retention rate on the last 30 days.

select bucket_id, avg(Retention) as Retention
from
(select cohort_name, cohort_id, cohort_size, bucket_id, count(distinct user_id, exact)*100/cohort_size as Retention
from cooladata
where date_range(last 30 days) 
and TRUE CLUSTER COHORT BY 1 DAYS EACH
STARTS WITH TRUE
FOLLOWED BY TRUE
BUCKET BY 1 DAYS ALL
having cohort_id < 31 and bucket_id < 31
group by cohort_name, cohort_id, cohort_size, bucket_id
  )
  group by 1
  order by 1
Retention Analysis
Cohort Analysis

We use CoolaData’s session path to and take only the first 3 events from it. We filter by is_new =1 to only get that user’s first session ever in the app/site.

.

select  first(path_to_string(path),3) as path, count(*) as count
from cooladata
where date_range(context)
	and filters(context)
	and path_count()>5
cluster path by session
group by path
order by count desc  
Limit 10


Behavioral Segmentation
Behavioral

 

You can calculate the number of sessions each user had within the selected date-range, and then segment the users by this number. We added the ‘ordr’ column as a work around to show the segments in the right order. Use the Bar visualization: X axis = Number_Of_sessions and Y axis = Users

select 
   case    when session_count = 1 then "1"
	  when session_count = 2 then "2"
	  when session_count = 3 then "3"
	  when session_count = 4 then "4"
	  when session_count = 5 then "5"
	  when session_count = 6 then "6"
	  when session_count = 7 then "7"
	  when session_count = 8 then "8"
	  when session_count > 8 and session_count < 15 then "9-14"
	  when session_count > 14 and session_count < 26 then "15-25"
	  when session_count > 25 and session_count < 51 then "26-50"
	  when session_count > 50 and session_count < 101 then "51-100"
	  when session_count > 100 and session_count < 200 then "101-200"
	  when session_count > 200  then "200+"
   end as Number_Of_Sessions
,  count(distinct user_id, exact) as Users
, max(session_count) as ordr
from (  select user_id, count(distinct session_id, exact) as session_count
  from cooladata
  where date_range(context)
  and filters(context) 
  group by 1 )
where session_count is not null
group by 1
having Users is not null and Number_Of_Sessions is not null
order by ordr 
Retention Analysis
Behavioral

You can use this query to calculate the average time your users spent on a particular page. You can replace the screen ID with page title or url

Select screen_id, avg((next_screen_ts-event_time_ts)/1000000) as seconds_on_screen
from
(Select user_id, screen_id, event_time_ts, lead(event_time_ts) over (partition by user_id, session_id order by event_time_ts) as next_screen_ts
from
(Select screen_id, former_screen, event_time_ts, user_id, session_id, event_name
from
(select screen_id, lag(screen_id) over (partition by player_id, session_id order by event_time_ts) as former_screen, event_time_ts, user_id, session_id, event_name
from cooladata
Where date_range(context)  and filters(context)  and screen_id is not null)
where (screen_id <> former_screen) or former_screen is null))
Where next_screen_ts is not null
Group by screen_id
order by seconds_on_screen desc      
Time Series Analysis
Behavioral

 

Bounce rate refers to the percentage of single page visits (or sessions). That is, the number of visits in which a person leaves your website from the landing page without browsing any further. To calculate bounce rate, count the sessions in which the user viewed only one unique url and compare them to the total amount of sessions. Keep in mind that the query counts unique page_url which is a property automatically sent in the page_load sent from the CoolaData JS tracker. It could be replaced with your own custom property or with page_title.  

select sum(case when unique_pages =1 then 1 else 0 end) as single_page_sessions, count(*) as sessions,
(sum(case when unique_pages =1 then 1 else 0 end)/count(*))*100 as bounce_rate
from
(select session_id, count(distinct page_url) as unique_pages
from cooladata
where date_range(context)
and filters(context) 
group by session_id)
Retention Analysis
Behavioral

Counting the number of unique URLs or pages a user viewed in a single session is crucial for understanding the amount of interest users show on your site. Combine this with the session duration and drill down to the users most popular paths allows you to understand how users behave on the site.

select unique_pages, count(distinct session_id) as sessions
from
(select session_id, count(distinct page_url) as unique_pages
from cooladata
where date_range(context)
and filters(context) 
group by session_id)
group by 1
order by 1
Retention Analysis
Behavioral

This query will help you calculate the revenue or purchase sum per user in the selected date range.

Select avg(revenue) as avg_revenue_per_player, avg_purchases_per_player
from
(Select user_id, sum(purchase_amount) as revenue, count(*) as purchases
From cooladata 
Where date_range (context)  and filters (context) and event_name = ‘purchase’ and purchase_status = ‘successGroup by user_id)
Customer Lifetime Value Analysis
kpi

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 =’successthen amount  end) as   money_spent, 
 sum(case when event_name = 'purchase' and purchase_status =’successthen 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      
Behavioral Segmentation
Behavioral
Cohort Analysis

You can caluclate your Daily Active Users and your Monthly Active users using the moving time window

SELECT 
   b.date as date,b.dau as dau,count(distinct a.user_id , exact) as mau
FROM 
( SELECT 1 as a,date(event_time_ts) as date,
                max(date(date_add(event_time_ts,-30,'DAY'))) date_30,
         count ( distinct user_id , exact ) dau
         FROM cooladata
        WHERE date_range(context)
GROUP BY 2
 ) AS b 
join 
( SELECT 1 as a ,date(event_time_ts) as date,  
user_id
        FROM  cooladata 
WHERE date_range ( between date(date_add(date_range_start,-30,'DAY')) and date(date_range_end))  group by 2,3
 ) AS a 
 ON  b.a = a.a 
 where b.date_30<a.date and a.date<=b.date
 group by 1,2
 order by 1
Retention Analysis
kpi
Behavioral