Which campaign encourages my users to make more in app purchases?
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
