Funnel Analysis

Showing 1 - 9 of 9 Items

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

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

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

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

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
Funnel Analysis
Cohort Analysis
Path Analysis

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
Funnel Analysis
Cohort Analysis
Path Analysis

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
Funnel Analysis
Cohort Analysis
Path Analysis

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
Funnel Analysis
Cohort Analysis
Path Analysis

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
Funnel Analysis
Cohort Analysis
Path Analysis