First Open Due to Push Analysis - Android

First Open Due to Push Analysis - Android

-- adjuststed for daylights savings

drop table analysts.push_sent_appboy;
drop table analysts.push_open_appboy;
drop table analysts.push_open_amplitude;

SELECT external_user_id, (time + (0*INTERVAL '1 hour'))::date as date, min((time + (0*INTERVAL '1 hour'))) as event_time
into analysts.push_sent_appboy
FROM appboy."event"
where (time + (0*INTERVAL '1 hour'))::date between '2017-03-15' and '2017-04-25'
and event_name = '$campaign_received'
and external_user_id not like ''
-- android
and app_id = '3097b70c-5cab-4304-b8a7-9745c7f196b0'

group by 1,2
order by 1,2,3 asc;

---

SELECT external_user_id, (time + (0*INTERVAL '1 hour'))::date as date, min((time + (0*INTERVAL '1 hour'))) as event_time
into analysts.push_open_appboy
FROM appboy."event"
where (time + (0*INTERVAL '1 hour'))::date between '2017-01-01' and '2017-01-31'
and event_name = '$push_opened'
and external_user_id not like ''
and app_id = '3097b70c-5cab-4304-b8a7-9745c7f196b0'

group by 1,2
order by 1,2,3 asc;

---

select user_id, event_time::date as date, min(event_time) as event_time
into analysts.push_open_amplitude
from events147385
where event_time::date between '2017-01-01' and '2017-01-31'
and user_id not like ''
and user_id is not null
group by 1,2
order by 1,2,3 asc;

---

select date, d_user_id, round((push_first_open*1.0)/(d_user_id*1.0),3) as push_first_open_ratio
from (

select date, count(distinct user_id) as d_user_id, sum(case when push_first_open = 'push_open' then 1 else 0 end) as push_first_open
from
(
select a.user_id, a.date, a.event_time as first_open_time, b.event_time as first_push_time, c.event_time as first_push_open_time,
(EXTRACT(hour FROM b.event_time)*60*60 + EXTRACT(minutes FROM b.event_time)*60 + EXTRACT(seconds FROM b.event_time)) -
(EXTRACT(hour FROM a.event_time)*60*60 + EXTRACT(minutes FROM a.event_time)*60 + EXTRACT(seconds FROM a.event_time)) as time_diff_sent,
(EXTRACT(hour FROM c.event_time)*60*60 + EXTRACT(minutes FROM c.event_time)*60 + EXTRACT(seconds FROM c.event_time)) -
(EXTRACT(hour FROM a.event_time)*60*60 + EXTRACT(minutes FROM a.event_time)*60 + EXTRACT(seconds FROM a.event_time)) as time_diff_open,

case when (EXTRACT(hour FROM b.event_time)*60*60 + EXTRACT(minutes FROM b.event_time)*60 + EXTRACT(seconds FROM b.event_time)) -
(EXTRACT(hour FROM a.event_time)*60*60 + EXTRACT(minutes FROM a.event_time)*60 + EXTRACT(seconds FROM a.event_time)) > 0 then 'open_first' else 'push_first' end
as order,

-- attribute the first open to push
case when
((((EXTRACT(hour FROM b.event_time)*60*60 + EXTRACT(minutes FROM b.event_time)*60 + EXTRACT(seconds FROM b.event_time)) -
(EXTRACT(hour FROM a.event_time)*60*60 + EXTRACT(minutes FROM a.event_time)*60 + EXTRACT(seconds FROM a.event_time))) between -900 and 60) or

(((EXTRACT(hour FROM c.event_time)*60*60 + EXTRACT(minutes FROM c.event_time)*60 + EXTRACT(seconds FROM c.event_time)) -
(EXTRACT(hour FROM a.event_time)*60*60 + EXTRACT(minutes FROM a.event_time)*60 + EXTRACT(seconds FROM a.event_time))) between -120 and 5))
then 'push_open' else '0' end
as push_first_open



from analysts.push_open_amplitude a
left join analysts.push_sent_appboy b on (a.user_id = b.external_user_id and a.date=b.date)
left join analysts.push_open_appboy c on (a.user_id = c.external_user_id and a.date=c.date)

--where a.date='2017-01-01'

)
group by 1
order by 1 asc);