Behavioral Segmentation

Showing 1 - 10 of 26 Items

Use a session path to take the last 2 (or any other N events) that immediately precede the session’s end. Make sure to ask for sessions with more than 5 events in the path. Use either the sunburst visualization or a regular grid view. This helps us answer questions like what were my users doing right before they quit the site?

select  last(path_to_string(path),2) as path, count(*) as count
from cooladata
where date_range(context)
	and filters(context)
	and path_count()>5
	and path_count >= 2
cluster path by session
group by path
order by count desc  
Limit 10
Behavioral Segmentation
Behavioral
Path 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

 

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

 This refers to the sum of the total number of coins spent in the game. Keep in mind the that the number shown is the turnover in the selected period. Replace the “coins_spent” with the total bet value or “cost” property and replace the event_name “spin” with the event representing the game play.

Select avg(user_turnover)
from
(Select user_id, sum( coins_spent) user_turnover
From cooladata 
Where date_range (context) and filters (context) 
And event_name = ‘spin’ 
Group by 1 )
Customer Lifetime Value Analysis
kpi

Understanding how many of your app’s players or users are actually generating income is an important KPI to monitor in the gaming monetization field. Keep in mind that this query actually counts the number of users who made a successful purchase in the selected time frame. If you want to count the number of users who made a purchase during a particular date range see segmentation of paying players. We suggest visualizing with line chart (2 Y axis).

select date(event_time_ts) as day, count(distinct user_id) as DAU, count( distinct case when event_name = "purchase" and purchase_status = ‘successthen user_id end ) as pDAU,
(count( distinct case when event_name = ‘purchase’ and purchase_status = ‘successthen user_id end )/count(distinct user_id))*100 as payers_percentage




  from cooladata 
where slicers(context) and date_range(context)
group by day
order by day   
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 want to know the ratio between your monthly and daily active users

select dau/mau as value from 
(SELECT 1 as dummy , AVG(_users) AS  dau
FROM (
            SELECT _day, COUNT(internal_user_id) AS _users 
            FROM (
                        SELECT date(event_time_ts) AS _day, internal_user_id 
                        FROM cooladata
                        WHERE date_range(doc.date_range)  
                       and doc.slicers
                        GROUP BY _day, internal_user_id 
            )
            GROUP BY _day 
)) 
as dau_t
join 
(SELECT 1 as dummy,AVG(_users) AS mau
FROM (
    SELECT _month, COUNT(internal_user_id) AS _users 
    FROM (
        SELECT SUBSTR(DATE(event_time_ts), 1, 7) AS _month, internal_user_id 
        FROM cooladata
      where date_range(doc.date_range) 
       and doc.slicers
        GROUP BY _month, internal_user_id 
    )
    GROUP BY _month 
))
 as mau_t 
 on dau_t.dummy=mau_t.dummy
Retention Analysis
Behavioral

You want to know what is the time, in days, that has past from a certain user's signup to their first deposit in a certain period of time, when both signup and FTD are in the same time period.

 

SELECT Avg(time_between_in_days) time_between_in_days 
FROM   (SELECT e1.uid, 
               e1_ts, 
               e2_ts, 
               ( e2_ts - e1_ts ) / 1000000 / 60 / 60 / 24 AS 
               time_between_in_days 
        FROM   (SELECT uid, 
                       Min(event_time_ts) AS e1_ts 
                FROM   cooladata 
                WHERE  Date_range(doc.date_range) 
                       AND doc.slicers 
                       AND event_name = "newplayer" 
                GROUP  BY 1) e1 
               JOIN (SELECT uid, 
                            Min(event_time_ts) AS e2_ts 
                     FROM   cooladata 
                     WHERE  Date_range(doc.date_range) 
                            AND doc.slicers 
                            AND event_name = "transaction" 
                     GROUP  BY 1) e2 
                 --/1000/60 as time_between_in_minutes  

                 ON e1.uid = e2.uid 
        WHERE  e1_ts < e2_ts) 
Behavioral Segmentation
Acquisition

You want to know what percent of your new users became paying users within a certain period of time

select  concat(string( 100*( payer/register) ),'%')   as registered_to_paying from 
 (select  1 as dummy, count(distinct uid) as register
from cooladata 
where date_range(doc.date_range) 
and doc.slicers
and event_name='newPlayer') a
join 
(select  1 as dummy, count(*) as payer
from accounts 
where  is_paying=1  ) b 
on a.dummy=b.dummy
Customer Lifetime Value Analysis
Retention

you want to see which have your users have completed the series of the next actions:
-action a
-action b
-action c
-action d

as well as who did not continue the process all the way and where they dropped off.

SELECT STEP_ID, STEP_NAME, USER_COUNT

FROM cooladata

WHERE date_range(last 7 days) AND 1 = 1

CLUSTER FUNNEL BY user

STARTS WITH 'action a'

FOLLOWED BY 'action b'

FOLLOWED BY 'action c'

FOLLOWED BY 'action d'

END CLUSTER
Behavioral Segmentation
Cohort Analysis
Path Analysis