Channel Performance

Showing 1 - 5 of 23 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 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

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

In this query, we'll break down each campaign results by quantiles (4 quarters)

select           referrer_campaign,
count(*) as      PURCHASES,
count(distinct user_id,exact) as users,
min(mt) as min,
max(case when p_rank<=0.25 then mt end) as p25,
max(case when p_rank<=0.50 then mt end) as p50,
max(case when p_rank<=0.75 then mt end) as p75,
max(mt) as max
from (
select user_id,mt,referrer_campaign,
 PERCENT_RANK() OVER (partition by referrer_campaign ORDER BY mt) p_rank
 from
 (select user_id,referrer_campaign,cart_price as mt
from cooladata 
where date_range(doc.date_range)
and event_name= "Checkout_Success"and doc.slicers))
group by referrer_campaign
order by referrer_campaign 
Marketing Analytics
Behavioral
ROI