Product Analytics

Showing 1 - 10 of 17 Items

 

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

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

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

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’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
Product Analytics
Behavioral
Path Analysis

For example, what is the median session duration in minutes, breakdown by user device?

SELECT avg(median) AS median_session_duration_mins, session_os
FROM
  (SELECT percentile_cont(0.5) OVER (PARTITION BY session_os ORDER BY p_duration) AS median, session_os
   FROM
     (SELECT path_duration() /1000/60 AS p_duration, session_os
      FROM cooladata
      WHERE date_range (doc.date_range)  
         ) )
GROUP BY session_os 
Product Analytics
Behavioral
pivot table

You want to measure the average duration of something, and you want to display it in human readable form (HH:MM:SS). The following query part can be used to transform a duration in seconds (herebey duration_seconds) to the proper format.

concat(lpad(string(integer(duration_seconds/3600)),2,"0"),
       ":",lpad(string(integer(duration_seconds%3600/60)),2,"0"),
       ":",lpad(string(integer(duration_seconds%60)),2,"0")) 
as avg_duration
Product Analytics
Behavioral

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 total amount of times a user logged in to your game

select  avg(sessions)  as sessions from accounts 
Product Analytics
Retention