Most popular questions

Showing 1 - 10 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
Path Analysis
title; ?>

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
title; ?>

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
title; ?>

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
title; ?>

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
title; ?>

 

You can calculate the number of sessions each user had within the selected date-range, and then segment the users by this number. We added the ‘ordr’ column as a work around to show the segments in the right order. Use the Bar visualization: X axis = Number_Of_sessions and Y axis = Users

select 
   case    when session_count = 1 then "1"
	  when session_count = 2 then "2"
	  when session_count = 3 then "3"
	  when session_count = 4 then "4"
	  when session_count = 5 then "5"
	  when session_count = 6 then "6"
	  when session_count = 7 then "7"
	  when session_count = 8 then "8"
	  when session_count > 8 and session_count < 15 then "9-14"
	  when session_count > 14 and session_count < 26 then "15-25"
	  when session_count > 25 and session_count < 51 then "26-50"
	  when session_count > 50 and session_count < 101 then "51-100"
	  when session_count > 100 and session_count < 200 then "101-200"
	  when session_count > 200  then "200+"
   end as Number_Of_Sessions
,  count(distinct user_id, exact) as Users
, max(session_count) as ordr
from (  select user_id, count(distinct session_id, exact) as session_count
  from cooladata
  where date_range(context)
  and filters(context) 
  group by 1 )
where session_count is not null
group by 1
having Users is not null and Number_Of_Sessions is not null
order by ordr 
Retention Analysis
Behavioral
title; ?>

You can use this query to calculate the average time your users spent on a particular page. You can replace the screen ID with page title or url

Select screen_id, avg((next_screen_ts-event_time_ts)/1000000) as seconds_on_screen
from
(Select user_id, screen_id, event_time_ts, lead(event_time_ts) over (partition by user_id, session_id order by event_time_ts) as next_screen_ts
from
(Select screen_id, former_screen, event_time_ts, user_id, session_id, event_name
from
(select screen_id, lag(screen_id) over (partition by player_id, session_id order by event_time_ts) as former_screen, event_time_ts, user_id, session_id, event_name
from cooladata
Where date_range(context)  and filters(context)  and screen_id is not null)
where (screen_id <> former_screen) or former_screen is null))
Where next_screen_ts is not null
Group by screen_id
order by seconds_on_screen desc      
Time Series Analysis
Behavioral
title; ?>

This query is useful if you want to find how many users installed your app following a particular campaign. This ROI calculation refers to the out-of-the-box Google AdWords CoolaData Integration. We first calculate each user’s LTV using a Materialized View (scheduled aggregation table) and then we cross reference this value with the cost of the campaign that acquired the user.

select day, install_campaign, campaign as campaign_name,  advertising_channel,  users, sum(clicks) as clicks, sum(cost_nis) as cost , sum(ltv) as campaign_income, sum(number_of_purchases) as total_campaign_purchases
from
(select day, install_campaign,install_source, ltv, number_of_purchases, clicks,cost_nis, campaign, users, advertising_channel from
(select install_date, install_source,install_campaign, sum(number_of_purchases) as number_of_purchases, count(distinct internal_user_id) as users, sum(ltv) as ltv
 From   USER_AGGR
group by install_date, install_source,install_campaign) users
join
(select string(integer(campaign_id)) as campaign_id, campaign, advertising_channel, company_name, impressions, integer(clicks) clicks,  cost/1000000 as cost_nis,
(cost/1000000)/clicks as avg_cpc,  date(day) as day
from adwords_campaigns_<integration id>) adwords
on users.install_campaign=adwords.campaign_id and users.install_date=adwords.day)
group by day, install_campaign, campaign_name,  advertising_channel, users
order by day
Marketing Analytics
kpi
Behavioral
title; ?>

While running on the entire date range of the project might be a heavy query that results in a long runtime, we recommend creating a user aggregation using the Materialized View feature that allows you to always have a table available that contains all the latest and most important user fields and calculations.

USER_AGGR:
select internal_user_id, customer_user_id, 
max(case when event_name = 'install' then app_id end) as install_app, 
max(case when event_name = 'install' then event_time_ts end) as install_ts, 
date( min(case when event_name = 'install' then event_time_ts end)) as install_date,
max( case when event_name = 'install' then campaign_name end) as install_campaign,
max(case when event_name = 'install' then media_source end) as install_source, 
max(case when event_name = 'purchase' then event_time_ts end) as last_purchase, 
sum(case when event_name = 'purchase' then 1 else 0 end) as number_of_purchases, 
sum(case when event_name = ''purchase' ' then float(amount) else float(0) end) as ltv,
count(distinct session_id) as sessions, 
max(event_time_ts) as last_seen
from cooladata
where date_range(all)
group by 1,2 
Retention Analysis
kpi
Behavioral
title; ?>

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
title; ?>