Most popular questions

Showing 1 - 5 of 92 Items

We’d like to see the path of the different urls or pages a user viewed in a single session based on the automatic page load event in the JS SDK. You can also replace the page_url with page_title or to your own custom property. Tip: Use the sunburst visualization. Change the limit to get more paths.

select page_path, count(distinct session_id) as sessions
from
(select  session_id, group_concat (page_url,',') as page_path
from
(select  session_id, page_url,event_time_ts  as ts
from cooladata
where date_range(context) 
and filters(context) and event_name = ‘page_load’ and page_url is not null 
order by ts)
group by 1)
group by 1
order by 2 desc
limit 20
Time Series Analysis
Path Analysis

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

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
Time Series Analysis
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