Channel Performance

Showing 1 - 10 of 16 Items

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

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 which of your campaigns draw in the most users.

SELECT install_source, 
       cnt1 , 
              Concat(String(100*pct) , '%') AS percent_from_total 
FROM   ( 
                SELECT   install_source, 
                         cnt1 , 
                         Ratio_to_report(cnt1) OVER () AS pct 
                FROM     ( 
                                  SELECT   install_source, 
                                           Count(*) AS cnt1 
                                  FROM     ( 
                                                    SELECT   install_source, 
                                                             customer_user_id 
                                                    FROM     accounts 
                                                    GROUP BY install_source, 
                                                             customer_user_id ) 
                                  GROUP BY install_source 
                                  ORDER BY cnt1 DESC ) 
                ORDER BY cnt1 DESC ) limit 5
Marketing Analytics
Behavioral
Path Analysis

breakdown 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
Segmentations

breakdown 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 
Customer Lifetime Value Analysis
Segmentations

breakdown 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 
Customer Lifetime Value Analysis
Segmentations

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
Customer Lifetime Value Analysis
kpi
Behavioral

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
Customer Lifetime Value Analysis
kpi
Behavioral

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 us

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
Customer Lifetime Value Analysis
kpi
Behavioral