Customer Lifetime Value Analysis

Showing 1 - 10 of 19 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

Using the time_between function, you can calculate the time between one event and the other.

select avg(distance)/3600 as average_distance_in_hours
from
(select customer_user_id, 
time_between("install", "purchase") as distance
from cooladata
where date_range(context)
and filters(context) ) 


Add conditions to the first and second event will get you to measure the distance only on the user’s first session:
 
select avg(distance)/60 as average_distance_in_minutes
from
(select customer_user_id, 
time_between("USER_REGISTERED", "LOGGED_IN", is_new=1 ,is_new=1) as distance
from cooladata
where date_range(context)
and filters(context) ) 
Customer Lifetime Value Analysis
kpi
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

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

You want to know which of your users have spent the most money in your game over a certain period of time.

SELECT customer_user_id, cnt1 FROM ( SELECT customer_user_id, sum(amount) AS cnt1 FROM cooladata WHERE date_range(last 7 days)  GROUP BY customer_user_id ORDER BY cnt1 DESC LIMIT 10 ) ORDER BY cnt1 DESC
Customer Lifetime Value 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.

select avg(time_between_in_days) time_between_in_days  from 
(select customer_user_id ,su_date,ftd_date ,datediff(  ftd_date,su_date) as time_between_in_days from accounts)  
 
Customer Lifetime Value Analysis
Acquisition

You want to know the average amount of user LTV (Life time value)  , calculated by summary of all the deposits , divided by the distinct number of users .

select  sum(total_pay)/ count(*)  as ltv 
from  accounts
Customer Lifetime Value Analysis
Behavioral
Retention

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 know your system's profit by redacting your users's returns from their initial investments.

select

ceil(sum(position_invested_amount)-sum(position_return_amount))  as Total_profit

from

( select

/*if   (event_name="open_position" , position_invested_amount ,0)  as position_invested_amount,

if   (event_name="close_position", position_return_amount   ,0)  as position_return_amount

from cooladata

where date_range(context)

and (event_name="close_position" or event_name="open_position") */

 position_invested_amount,

 position_return_amount

from cooladata

where date_range(context)

and slicers(context)  

and (event_name="close_position" )

and position_invested_amount is not null and position_invested_amount <>0

) 
Customer Lifetime Value Analysis
Behavioral