Retention Analysis

Showing 1 - 10 of 25 Items

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

 

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

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

You can use the windows functions to determine the right order of countries by the sum of their revenues. If you want more than 10 countries (top N) then replace 11 with N+1 (for instance for top 20 replace 11 with 21). Otherwise all the rest of the countries are marked as “other” and their revenues is summed under “Other”

 

If you want to segment by another property (not country) replace country with the name of the property.

SELECT country ,  revenues
FROM
  ( SELECT group_id ,
           CASE  WHEN group_id < 11 THEN country
               ELSE "Other"  END AS country ,
           sum(revenues) revenues
   FROM
     ( SELECT country ,  revenues ,
              if(row_number<11, row_number, 11) group_id
      FROM
        ( SELECT country , ROW_NUMBER() OVER() row_number,   revenues
         FROM
           ( SELECT ip_country as country ,  sum(amount) as revenues
            FROM cooladata 
	WHERE date_range (context) and filters (context) and event_name = ‘purchase’
            GROUP BY 1
            ORDER BY 2 DESC ) ) )
   GROUP BY 1,   2
   ORDER BY group_id ASC)
Retention Analysis
kpi

 

Bounce rate refers to the percentage of single page visits (or sessions). That is, the number of visits in which a person leaves your website from the landing page without browsing any further. To calculate bounce rate, count the sessions in which the user viewed only one unique url and compare them to the total amount of sessions. Keep in mind that the query counts unique page_url which is a property automatically sent in the page_load sent from the CoolaData JS tracker. It could be replaced with your own custom property or with page_title.  

select sum(case when unique_pages =1 then 1 else 0 end) as single_page_sessions, count(*) as sessions,
(sum(case when unique_pages =1 then 1 else 0 end)/count(*))*100 as bounce_rate
from
(select session_id, count(distinct page_url) as unique_pages
from cooladata
where date_range(context)
and filters(context) 
group by session_id)
Retention Analysis
Behavioral

Counting the number of unique URLs or pages a user viewed in a single session is crucial for understanding the amount of interest users show on your site. Combine this with the session duration and drill down to the users most popular paths allows you to understand how users behave on the site.

select unique_pages, count(distinct session_id) as sessions
from
(select session_id, count(distinct page_url) as unique_pages
from cooladata
where date_range(context)
and filters(context) 
group by session_id)
group by 1
order by 1
Retention Analysis
Behavioral

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 can caluclate your Daily Active Users and your Monthly Active users using the moving time window

SELECT 
   b.date as date,b.dau as dau,count(distinct a.user_id , exact) as mau
FROM 
( SELECT 1 as a,date(event_time_ts) as date,
                max(date(date_add(event_time_ts,-30,'DAY'))) date_30,
         count ( distinct user_id , exact ) dau
         FROM cooladata
        WHERE date_range(context)
GROUP BY 2
 ) AS b 
join 
( SELECT 1 as a ,date(event_time_ts) as date,  
user_id
        FROM  cooladata 
WHERE date_range ( between date(date_add(date_range_start,-30,'DAY')) and date(date_range_end))  group by 2,3
 ) AS a 
 ON  b.a = a.a 
 where b.date_30<a.date and a.date<=b.date
 group by 1,2
 order by 1
Retention Analysis
kpi
Behavioral

You want to know the ratio between your monthly and daily active users

select dau/mau as value from 
(SELECT 1 as dummy , AVG(_users) AS  dau
FROM (
            SELECT _day, COUNT(internal_user_id) AS _users 
            FROM (
                        SELECT date(event_time_ts) AS _day, internal_user_id 
                        FROM cooladata
                        WHERE date_range(doc.date_range)  
                       and doc.slicers
                        GROUP BY _day, internal_user_id 
            )
            GROUP BY _day 
)) 
as dau_t
join 
(SELECT 1 as dummy,AVG(_users) AS mau
FROM (
    SELECT _month, COUNT(internal_user_id) AS _users 
    FROM (
        SELECT SUBSTR(DATE(event_time_ts), 1, 7) AS _month, internal_user_id 
        FROM cooladata
      where date_range(doc.date_range) 
       and doc.slicers
        GROUP BY _month, internal_user_id 
    )
    GROUP BY _month 
))
 as mau_t 
 on dau_t.dummy=mau_t.dummy
Retention Analysis
Behavioral

You want to know what percent of your players are paying players.

select  concat(string( 100*( paying_users/total_users)),'%')   as  paying_percentage , paying_users     from 
(select  COUNT(distinct customer_user_id) AS  total_users,
              sum( is_paying ) as paying_users from accounts   ) 
Retention Analysis
Behavioral
Segmentations