Financial Analytics

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

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

Select day, avg(revenue) as avg_revenue_per_player, avg_purchases_per_player
from
(Select date(event_time_ts) as day, 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 day, user_id)
Group by day
Financial Analytics
kpi
Behavioral

We usually recommend sending a boolean parameter for first time depositors as best practice. That way you can study the first time a user actually creates his first deposit, what lead him to do so, how long it took him and so on. (is_ftd =1 or 0 on purchase events)

In cases where such a parameter is not available, this can be calculated by creating a user purchase aggregation and adding a serial number to each user purchase (by timestamp).

First time deposits are all the purchases in which purchase_serial_num =1.

Select customer_user_id, user_id, event_time_ts as purchase_timestamp, purchase_amount as purchase_amount, session_id, is_new, sys_user_create_date as user_first_seen,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time_ts ASC) as purchase_serial_num
From cooladata 
Where date_range (all)  and event_name = ‘purchase’ and purchase_status = ‘success
Funnel Analysis
kpi

In this query, we'll break down each campaign results by quantiles (4 quarters)

select           referrer_campaign,
count(*) as      PURCHASES,
count(distinct user_id,exact) as users,
min(mt) as min,
max(case when p_rank<=0.25 then mt end) as p25,
max(case when p_rank<=0.50 then mt end) as p50,
max(case when p_rank<=0.75 then mt end) as p75,
max(mt) as max
from (
select user_id,mt,referrer_campaign,
 PERCENT_RANK() OVER (partition by referrer_campaign ORDER BY mt) p_rank
 from
 (select user_id,referrer_campaign,cart_price as mt
from cooladata 
where date_range(doc.date_range)
and event_name= "Checkout_Success"and doc.slicers))
group by referrer_campaign
order by referrer_campaign 
Marketing Analytics
Behavioral
ROI

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 the average amount your users' first deposits.

select avg(first_deposit_amount) as average_ftd_amount   from 
    accounts  where is_paying=1    
Financial Analytics
Acquisition

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 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