Attribution Modeling

Showing 1 - 6 of 6 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 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

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