Skip to content

Instantly share code, notes, and snippets.

@rex-lin

rex-lin/First Open Due to Push iOS

Created April 26, 2017 19:12
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rex-lin/1279ffea0862b12703d75bda7de883a0 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rex-lin/1279ffea0862b12703d75bda7de883a0 to your computer and use it in GitHub Desktop.
Download ZIP
First Open Due to Push iOS
Raw
First Open Due to Push iOS
-- adjuststed for daylights savings
drop table analysts.push_sent_appboy_ios;
drop table analysts.push_open_appboy_ios;
drop table analysts.push_open_amplitude_ios;
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_ios
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 ''
-- ios
and app_id = '09a5faef-7499-4712-af62-b7ea3f3c63bb'
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_ios
FROM appboy."event"
where (time + (0*INTERVAL '1 hour'))::date between '2017-03-15' and '2017-04-25'
and event_name = '$push_opened'
and external_user_id not like ''
and app_id = '09a5faef-7499-4712-af62-b7ea3f3c63bb'
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_ios
from events150416
where event_time::date between '2017-03-15' and '2017-04-25'
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_ios a
left join analysts.push_sent_appboy_ios b on (a.user_id = b.external_user_id and a.date=b.date)
left join analysts.push_open_appboy_ios 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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment