rex-lin · April 12, 2017 23:37
diff --git a/User Profiles v2 b/User Profiles v2
 - Big Query ---
 -- export csv of this data and import into redshift

 select user_id, source, campaign, date
 from (
 select user_id, source, campaign,date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date ASC) row_num
 from (

 SELECT
  ud.value.value.string_value AS user_id,
  CASE
    WHEN user_dim.traffic_source.user_acquired_source IS NULL THEN '(direct)'
    ELSE user_dim.traffic_source.user_acquired_source
  END AS source,
    CASE
    WHEN user_dim.traffic_source.user_acquired_campaign IS NULL THEN '(direct)'
    ELSE user_dim.traffic_source.user_acquired_campaign 
  END as campaign,
  TIMESTAMP_MICROS(ed.timestamp_micros)AS date,
  user_dim.device_info.user_default_language AS locale
 FROM
  `flipagram-cheerful.com_cheerfulinc_flipagram_ANDROID.*`,
  UNNEST(user_dim.user_properties) ud,
  UNNEST(event_dim) ed
 WHERE
  ud.key = 'UserID'
  AND DATE(TIMESTAMP_MICROS(ed.timestamp_micros)) BETWEEN '2016-12-01'
  AND CURRENT_DATE()
  AND ud.value.value.string_value != 'Never Logged In'
  AND ud.value.value.string_value != 'Logged Out')
  
 ) where row_num =1

 --------------------------------
 -- redshift import of data
 drop table analysts.firebase_source_user;

 CREATE TABLE analysts.firebase_source_user
 (
 	user_id VARCHAR(25),
 	source VARCHAR(50),
 	campaign VARCHAR(50),
  event_date VARCHAR(50)
 );


 COPY analysts.firebase_source_user FROM 's3://flipagram-data-warehouse/user_source_data.csv' CREDENTIALS 'aws_access_key_id=AKIAI2GZ644LF3L4TK6A;aws_secret_access_key=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' REGION 'us-east-1' IGNOREHEADER 1 csv;

 -----------------------
 drop table analysts.retention_rex_1;

 -- user_id = user_id of the user
 -- date_created = day the user was created
 -- total_flipagrams = total flipagrams the user has created
 -- total_likes = total likes the user has given
 -- total_comments = total comments the user has given
 -- total_follows = total follows the user has given
 -- total_plays = total plays the user has given
 -- total_interactions = sum of above 5 metrics
 -- total_flipagram_lengths = sum of the length of all of a users flipagrams (seconds)
 -- days_visited = number of days the user opened flipagram mobile app in the user's first 30 days
 -- play_count = number of plays given in the first 30 days
 -- like_count = number of likes given in the first 30 days
 -- comment_count = number of comments given in the first 30 days
 -- reflip_count = number of reflips given in the first 30 days
 -- play_rec_count = number of plays received in the first 30 days
 -- like_rec_count = number of likes received in the first 30 days
 -- comment_rec_count = number of comments received in the first 30 days
 -- reflip_rec_count = number of reflips received in the first 30 days
 -- users_following = number of users followed in the first 30 days
 -- users_followed = number of followers in the first 30 days
 -- following_followed_ration = users_following/users_followed, if either number is 0, then null
 -- api_received = number of transactional pushes received in the first 30 days 
 -- adhoc_received = number of ad hoc pushes received in the first 30 days 
 -- api_received_ios = number of ios transactional pushes received in the first 30 days 
 -- adhoc_received_ios = number of ios ad hoc pushes received in the first 30 days 
 -- api_received_android = number of android transactional pushes received in the first 30 days 
 -- adhoc_received_android = number of android ad hoc pushes received in the first 30 days 
 -- api_received_d0 = number of transactional pushes received in the first 0 days 
 -- adhoc_received_d0 = number of ad hoc pushes received in the first 0 days 
 -- api_received_d1 = number of transactional pushes received in the first 1 days 
 -- adhoc_received_d1 = number of ad hoc pushes received in the first 1 days 
 -- api_received_d7 = number of transactional pushes received in the first 7 days 
 -- adhoc_received_d7 = number of ad hoc pushes received in the first 7 days 
 -- api_received_d14 = number of transactional pushes received in the first 14 days 
 -- adhoc_received_d14 = number of ad hoc pushes received in the first 14 days 
 -- api_received_d30 = number of transactional pushes received in the first 30 days (same as api_received)
 -- adhoc_received_d30 = number of ad hoc pushes received in the first 30 days (same as adhoc_received)
 -- d1 = did the user perform a retained action on d1 (n-day), retained action = play, like ,comment, reflip, create
 -- d7 = did the user perform a retained action on d7 (n-day), retained action = play, like ,comment, reflip, create
 -- w2 = did the user perform a retained action on w2 (n-day), retained action = play, like ,comment, reflip, create
 -- m1 = did the user perform a retained action on m1 (n-day), retained action = play, like ,comment, reflip, create
 -- m2 = did the user perform a retained action on m2 (n-day), retained action = play, like ,comment, reflip, create
 -- m3 = did the user perform a retained action on m3 (n-day), retained action = play, like ,comment, reflip, create

 -- need to scrub out 0 days visited users
 -- for some reason some users not showing up in amplitude



 -- users created in a date range
 with users as (
 select id as user_id, date(date_created) as date_created, locale, has_avatar
 from pf.dim_user
 where date_created between '2016-12-01' and '2017-01-31'

 ),

 -- pushes recieved in first 30 days of a users creation
 push_detailed as (
 select external_user_id as user_id, time::date as date, 
 case when is_api_campaign = 'TRUE' then campaign_name else 'Ad Hoc Push' end as campaign_name, is_api_campaign,
 case when app_id = '09a5faef-7499-4712-af62-b7ea3f3c63bb' then 'iOS' when app_id = '3097b70c-5cab-4304-b8a7-9745c7f196b0' then 'Android' end as app_id,
 sum(case when event_name = '$campaign_received' then 1 else 0 end) as push_sent

 from appboy.event a
 left join appboy.event_property b on (a.event_id=b.event_id and b.event_property_name = '$campaign_id')
 left join analysts.push_campaigns c on b.value_string=c.campaign_id
 join users d on (a.external_user_id=d.user_id and a.time::date <= d.date_created + interval '30 day')

 where time::date >= 2016-12-01
 and external_user_id is not null
 and external_user_id != ''
 group by 1,2,3,4,5
 order by 1,2 asc

 ),

 -- agg of push data
 push_data_agg as (

 select user_id, sum(case when campaign_name != 'Ad Hoc Push' then push_sent else 0 end) as api_received,
 sum(case when campaign_name = 'Ad Hoc Push' then push_sent else 0 end) as adhoc_received,
 sum(case when (campaign_name != 'Ad Hoc Push' and app_id = 'iOS') then push_sent else 0 end) as api_received_ios,
 sum(case when (campaign_name != 'Ad Hoc Push' and app_id ='Android') then push_sent else 0 end) as api_received_android,
 sum(case when (campaign_name = 'Ad Hoc Push' and app_id = 'iOS') then push_sent else 0 end) as adhoc_received_ios,
 sum(case when (campaign_name = 'Ad Hoc Push' and app_id ='Android') then push_sent else 0 end) as adhoc_received_android


 from push_detailed
 group by 1

 ),

 -- pushes recieved on d0
 push_data_time_d0 as 
 (
 select a.user_id,
 sum(case when d0.campaign_name != 'Ad Hoc Push' then d0.push_sent else 0 end) as api_received_d0,
 sum(case when d0.campaign_name = 'Ad Hoc Push' then d0.push_sent else 0 end) as adhoc_received_d0

 from users a
 left join push_detailed d0 on (a.user_id=d0.user_id and a.date_created=d0.date)

 group by 1

 ),

 -- pushes recieved through d1
 push_data_time_d1 as 
 (
 select a.user_id,
 sum(case when d1.campaign_name != 'Ad Hoc Push' then d1.push_sent else 0 end) as api_received_d1,
 sum(case when d1.campaign_name = 'Ad Hoc Push' then d1.push_sent else 0 end) as adhoc_received_d1

 from users a

 left join push_detailed d1 on (a.user_id=d1.user_id and (a.date_created + interval '1 day') >= d1.date)

 group by 1

 ),

 -- pushes recieved through d7
 push_data_time_d7 as 
 (
 select a.user_id,

 sum(case when d7.campaign_name != 'Ad Hoc Push' then d7.push_sent else 0 end) as api_received_d7,
 sum(case when d7.campaign_name = 'Ad Hoc Push' then d7.push_sent else 0 end) as adhoc_received_d7

 from users a

 left join push_detailed d7 on (a.user_id=d7.user_id and (a.date_created + interval '7 day') >= d7.date)

 group by 1

 ),

 -- pushes recieved through d14
 push_data_time_d14 as 
 (
 select a.user_id,

 sum(case when d14.campaign_name != 'Ad Hoc Push' then d14.push_sent else 0 end) as api_received_d14,
 sum(case when d14.campaign_name = 'Ad Hoc Push' then d14.push_sent else 0 end) as adhoc_received_d14

 from users a

 left join push_detailed d14 on (a.user_id=d14.user_id and (a.date_created + interval '14 day') >= d14.date)

 group by 1

 ),

 -- pushes recieved through d30
 push_data_time_d30 as 
 (
 select a.user_id,

 sum(case when d30.campaign_name != 'Ad Hoc Push' then d30.push_sent else 0 end) as api_received_d30,
 sum(case when d30.campaign_name = 'Ad Hoc Push' then d30.push_sent else 0 end) as adhoc_received_d30

 from users a

 left join push_detailed d30 on (a.user_id=d30.user_id and (a.date_created + interval '30 day') >= d30.date)

 group by 1

 ),



 ---

 -- flipagram creation metrics by user
 flipagram as (
 select created_by as user_id, date(date_created) as date,  sum(1) as flipagrams_created,
 0 as likes, 0 as comment, 0 as reflip, 0 as follow, 0 as play,round(sum(duration)/1000.00) as flipagram_lengths
 from pf.dim_flipagram
 where date_created >= '2016-12-01'
 group by 1,2
 ),

 -- likes created by user
 likes as (
 select user_id, date(date_created) as date,
 0 as flipagrams_created, sum(1) as likes, 0 as comment, 0 as reflip, 0 as follow, 0 as play, 0 as flipagram_lengths
 from pf.dim_relationship_like_by_user
 where date_created >= '2016-12-01'
 and status = 'C'
 group by 1,2
 ),

 -- comments created by user
 comment as (
 select user_id, date(date_created) as date,
 0 as flipagrams_created,0 as likes, sum(1) as comment, 0 as reflip, 0 as follow, 0 as play, 0 as flipagram_lengths
 from pf.dim_relationship_comment_by_user
 where date_created >= '2016-12-01'
 and status = 'C'
 group by 1,2
 ),

 -- reflip created by user
 reflip as (
 select user_id, date(date_created) as date, 0 as flipagrams_created,
 0 as likes, 0 as comment, sum(1) as reflip,  0 as follow, 0 as play, 0 as flipagram_lengths
 from pf.dim_relationship_reflip_by_user
 where date_created >= '2016-12-01'
 and status = 'C'
 group by 1,2
 ),

 -- plays created by user
 play as (
 select user_id, date(date_created) as date, 0 as flipagrams_created,
 0 as likes, 0 as comment, 0 as reflip, 0 as follow,  sum(1) as play, 0 as flipagram_lengths
 from pf.dim_relationship_play_by_user
 where date_created >= '2016-12-01'
 and status = 'C'
 group by 1,2
 ),

 -- follows created by user
 follow as (
 select following_user_id as user_id, date(date_created) as date,   0 as flipagrams_created,
 0 as likes, 0 as comment, 0 as reflip,sum(1) as follow, 0 as play, 0 as flipagram_lengths
 from pf.dim_relationship_follow_by_followed
 where date_created >= '2016-12-01'
 and status = 'C'
 group by 1,2
 ),

 -- combine user interactions
 interactions as (
 select user_id, date, sum(flipagrams_created) as flipagrams, sum(likes) as likes, sum(comment) as comment,
 sum(follow) as follow, sum(play) as play, sum(flipagram_lengths) as flipagram_lengths,sum(flipagrams_created)+sum(likes)+sum(comment)+sum(follow)+sum(play) as interactions
 from (
 select * from flipagram
 union
 select * from likes
 union
 select * from comment
 union
 select * from follow
 union
 select * from play
 )
 group by 1,2
 ),

 /*
 ios_users as (

 select a.user_id,
 case when paint_applied.count is not null then paint_applied.count else 0 end as paint_applied,
 case when vibe_applied.count is not null then vibe_applied.count else 0 end as vibe_applied,
 case when vibe_used.count is not null then vibe_used.count else 0 end as vibe_used,
 case when paint_used.count is not null then paint_used.count else 0 end as paint_used,
 case when shares.count is not null then shares.count else 0 end as shares

 from (
 select user_id
 from events150416
 where event_time::date >= '2016-10-01'
 group by 1) a
 left join (
 select user_id, count(1) as count
 from events150416
 where event_type = 'Paint Applied'
 and json_extract_path_text(event_properties, 'Paint Splatter Count') > 0
 and event_time::date >= '2016-10-01'
 group by 1
 ) paint_applied on a.user_id = paint_applied.user_id
 left join (
 select user_id, count(1) as count
 from events150416
 where event_type = 'Filter Applied'
 and json_extract_path_text(event_properties, 'Vibe Applied') in ('Electro', 'Boombox','Strobe','Rave')
 and event_time::date >= '2016-10-01'
 group by 1
 ) vibe_applied on a.user_id = vibe_applied.user_id
 left join (
 select user_id, count(1) as count
 from events150416
 where event_type = 'Flipagram Created'
 and json_extract_path_text(event_properties, 'Vibe Applied') in ('Electro', 'Boombox','Strobe','Rave')
 and event_time::date >= '2016-10-01'
 group by 1
 ) vibe_used on a.user_id = vibe_used.user_id
 left join (
 select user_id, count(1) as count
 from events150416
 where event_type = 'Flipagram Created'
 and json_extract_path_text(event_properties, 'Paint Splatter Count') > 0
 and event_time::date >= '2016-10-01'
 group by 1
 ) paint_used on a.user_id = paint_used.user_id
 left join (
 select user_id, count(1) as count
 from events150416
 where event_type = 'Share Completed'

 and event_time::date >= '2016-10-01'
 group by 1
 ) shares on a.user_id = shares.user_id
 ),

 */

 -- days a user visited in a time range
 days_visited as (

 select user_id, count(distinct visit_date) as days_visited
 from (
 select a.user_id, visit_date
 from ((
 select user_id, event_time::date as visit_date
 from events147385
 -- change this date range to the range of activity we are looking for
 where event_time::date between '2016-12-01' and '2017-03-31'
 group by 1,2
 )
 union 
 (
 select user_id, event_time::date as visit_date
 from events150416
 -- change this date range to the range of activity we are looking for
 where event_time::date between '2016-12-01' and '2017-03-31'
 group by 1,2
 )) a
 join users b on (a.user_id=b.user_id and (b.date_created + interval '30 day') > a.visit_date)

 )
 group by 1
 ),

 --- interactions recieved
 actions_rec as (

 select user_id, sum(case when relationship_type = 'PLAY' then 1 else 0 end) as play_rec_count, 
 sum(case when relationship_type = 'COMMENT' then 1 else 0 end) as comment_rec_count, 
 count(distinct case when relationship_type = 'REFLIP' then source_id else null end) as reflip_rec_count, 
 count( distinct case when relationship_type = 'LIKE' then source_id else null end) as like_rec_count
 from (
 select a.date_created::date as date, relationship_type, target_id as flipagram_id, b.created_by as user_id, source_id
 from pf.event_relationship_staging a
 left join pf.dim_flipagram b on a.target_id=b.id
 join users c on (b.created_by=c.user_id and (c.date_created + interval '30 day') > a.date_created)

 where relationship_type in ('PLAY','LIKE','COMMENT','REFLIP')
 and a.date_created::date between '2016-12-01' and '2017-03-31'
 and a.status = 'C'
 )
 where user_id is not null
 group by 1
 order by 1 asc

 ),

 -- interactions in a time frame
 interations_sum as (
 select user_id, sum(case when relationship_type = 'PLAY' then 1 else 0 end) as play_count, 
 sum(case when relationship_type = 'COMMENT' then 1 else 0 end) as comment_count, 
 sum(case when relationship_type = 'REFLIP' then 1 else 0 end) as reflip_count, 
 sum(case when relationship_type = 'LIKE' then 1 else 0 end) as like_count,
 sum(case when relationship_type = 'FOLLOW' then 1 else 0 end) as follow_count
 from (

 select a.date_created::date as date, relationship_type, source_id as user_id
 from pf.event_relationship_staging a
 join users b on (a.source_id=b.user_id and (b.date_created + interval '30 day') > a.date_created)
 where relationship_type in ('PLAY','LIKE','COMMENT','REFLIP','FOLLOW')
 and a.date_created::date between '2016-12-01' and '2017-03-31'
 and status = 'C'

 )
 where user_id is not null
 group by 1
 order by 1 asc
 ),

 following as
 (

 -- number of users following a user
 select target_id as user_id, count(distinct source_id) as users_following
 from pf.event_relationship_staging a
 join users b on (a.target_id=b.user_id and (b.date_created + interval '30 day') > a.date_created)
 where relationship_type in ('FOLLOW') 
 and a.date_created::date between '2016-12-01' and '2017-03-31'
 and status = 'C'
 group by 1

 ),

 followed as
 (
 -- number of users a user followed
 select source_id as user_id, count(distinct target_id) as users_followed
 from pf.event_relationship_staging a
 join users b on (a.source_id=b.user_id and (b.date_created + interval '30 day') > a.date_created)
 where relationship_type in ('FOLLOW')
 and a.date_created::date between '2016-12-01' and '2017-03-31'
 and status = 'C'
 group by 1
 ),

 flipagram_time_box as (
 select created_by as user_id,  sum(1) as flipagram_count, round(sum(duration)/1000.00) as flipagram_length_count_sec
 from pf.dim_flipagram a
 join users b on (a.created_by=b.user_id and (b.date_created + interval '30 day') > a.date_created)
 and a.date_created::date between '2016-12-01' and '2017-03-31'
 group by 1
 ),

 ios_source as (
 select user_id, visit_date, iad_campaign_name, iad_adgroup_name, iad_keyword
 from (
 select user_id, visit_date, iad_campaign_name, iad_adgroup_name, iad_keyword, rank() OVER (PARTITION BY user_id ORDER BY visit_date asc) as row
 from (
 select user_id, event_time::date as visit_date, 
 json_extract_path_text(user_properties, 'iad-campaign-name') as iad_campaign_name,
 json_extract_path_text(user_properties, 'iad-adgroup-name') as iad_adgroup_name,
 json_extract_path_text(user_properties, 'iad-keyword') as iad_keyword
 from events150416
 where event_time::date between '2017-01-01' and '2017-03-31'
 and json_extract_path_text(user_properties, 'iad-campaign-name') is not null
 and json_extract_path_text(user_properties, 'iad-campaign-name') !=''
 and json_extract_path_text(user_properties, 'iad-campaign-name') !='CampaignName'
 and json_extract_path_text(user_properties, 'iad-adgroup-name') is not null
 and json_extract_path_text(user_properties, 'iad-adgroup-name') !=''
 and json_extract_path_text(user_properties, 'iad-adgroup-name') !='AdGroupName'
 and json_extract_path_text(user_properties, 'iad-keyword') is not null
 and json_extract_path_text(user_properties, 'iad-keyword') !=''
 and json_extract_path_text(user_properties, 'iad-keyword') !='Keyword'
 and user_id is not null
 group by 1,2,3,4,5))
 where row = 1
 ),

 retention_detailed as (

 select user_id, visit_date, os
 from (
 select user_id, visit_date, os
 from ((
 select user_id, event_time::date as visit_date, 'android' as os
 from events147385
 where event_time::date between '2016-12-01' and '2017-03-31'
 and event_type in ('Flipagram View','Fliapgram Created','Like','Comment','Reflip','DM Sent','Flipagram Started','Follow')
 and user_id is not null
 and user_id != ''
 group by 1,2,3
 )
 union 
 (
 select user_id, event_time::date as visit_date, 'ios' as os
 from events150416
 where event_time::date between '2016-12-01' and '2017-03-31'
 and event_type in ('Flipagram View','Fliapgram Created','Like','Comment','Reflip','DM Sent','Flipagram Started','Follow')
 and user_id is not null
 and user_id != ''
 group by 1,2,3
 )))
 group by 1,2,3

 ),

 retention_agg as (
 select user_id, visit_date
 from retention_detailed
 group by 1,2),

 ios_user as (
 select user_id
 from retention_detailed
 where os = 'ios'
 group by 1),

 android_user as (
 select user_id
 from retention_detailed
 where os = 'android'
 group by 1),

 branch_ua as (
 select user_id, first_referring_link_channel, first_referring_link_feature,first_referring_link_campaign,first_referring_link_stage,first_referring_link_tags 
 from 
 (
 SELECT developer_identity as user_id, first_referring_link_channel, first_referring_link_feature,first_referring_link_campaign,first_referring_link_stage, first_referring_link_tags, ROW_NUMBER() OVER (PARTITION BY developer_identity ORDER BY timestamp asc) as rn

 FROM branchio.event a 
 where developer_identity is not null 
 and developer_identity != '' 
 )
 where rn = 1
 )


 -----------------
 select a.user_id,a.date_created,
 first_referring_link_channel, first_referring_link_feature,first_referring_link_campaign,first_referring_link_stage,first_referring_link_tags,
 -- totals are life time
 total_flipagrams,total_likes,total_comments,total_follows,total_plays,total_interactions,total_flipagram_lengths,
 -- counts are time boxed
 case when dv.days_visited is null then 0 else dv.days_visited end as days_visited,
 case when flipagram_count is null then 0 else flipagram_count end as flipagram_count,
 case when flipagram_length_count_sec is null then 0 else flipagram_length_count_sec end as flipagram_length_count_sec,
 case when play_count is null then 0 else play_count end as play_count,
 case when like_count is null then 0 else like_count end as like_count,
 case when comment_count is null then 0 else comment_count end as comment_count,
 case when reflip_count is null then 0 else reflip_count end as reflip_count,
 case when play_rec_count is null then 0 else play_rec_count end as play_rec_count,
 case when like_rec_count is null then 0 else like_rec_count end as like_rec_count,
 case when comment_rec_count is null then 0 else comment_rec_count end as comment_rec_count,
 case when reflip_rec_count is null then 0 else reflip_rec_count end as reflip_rec_count,
 case when users_following is null then 0 else users_following end as users_following,
 case when users_followed is null then 0 else users_followed end as users_followed,
 round((users_following*1.0)/(users_followed*1.0),2) as following_followed_ratio,

 /*
 paint_applied,
 ios.paint_used,
 ios.vibe_applied,
 ios.vibe_used,
 ios.shares,
 */
 case when api_received is null then 0 else api_received end as api_received,
 case when adhoc_received is null then 0 else adhoc_received end as adhoc_received,
 case when api_received_ios is null then 0 else api_received_ios end as api_received_ios,
 case when adhoc_received_ios is null then 0 else adhoc_received_ios end as adhoc_received_ios,
 case when api_received_android is null then 0 else api_received_android end as api_received_android,
 case when adhoc_received_android is null then 0 else adhoc_received_android end as adhoc_received_android,

 case when api_received_d0 is null then 0 else api_received_d0 end as api_received_d0,
 case when adhoc_received_d0 is null then 0 else adhoc_received_d0 end as adhoc_received_d0,
 case when api_received_d1 is null then 0 else api_received_d1 end as api_received_d1,
 case when adhoc_received_d1 is null then 0 else adhoc_received_d1 end as adhoc_received_d1,
 case when api_received_d7 is null then 0 else api_received_d7 end as api_received_d7,
 case when adhoc_received_d7 is null then 0 else adhoc_received_d7 end as adhoc_received_d7,
 case when api_received_d14 is null then 0 else api_received_d14 end as api_received_d14,
 case when adhoc_received_d14 is null then 0 else adhoc_received_d14 end as adhoc_received_d14,
 case when api_received_d30 is null then 0 else api_received_d30 end as api_received_d30,
 case when adhoc_received_d30 is null then 0 else adhoc_received_d30 end as adhoc_received_d30,


 case when d1.user_id is not null then 1 else 0 end as d1,
 case when d7.user_id is not null then 1 else 0 end as d7,
 case when w2.user_id is not null then 1 else 0 end as w2,
 case when m1.user_id is not null then 1 else 0 end as m1,
 case when m2.user_id is not null then 1 else 0 end as m2,
 case when m3.user_id is not null then 1 else 0 end as m3,
 iad_campaign_name, iad_adgroup_name, iad_keyword,
 androidsource.source as android_source, androidsource.campaign as android_campaign, case when (androidsource.campaign is not null) then 'android' end as android_ua,
 case when (iad_campaign_name is not null and iad_campaign_name != 'CampaignName') then 'iOS' end as iOS_ua,
 case when ios_usr.user_id is not null then 1 else 0 end as ios_user,
 case when android_usr.user_id is not null then 1 else 0 end as android_user


 into analysts.retention_rex_1
 from users a
 left join (
 select user_id, sum(flipagrams) as total_flipagrams,
 sum(likes) as total_likes,
 sum(comment) as total_comments,
 sum(follow) as total_follows,
 sum(play) as total_plays,
 sum(interactions) as total_interactions,
 sum(flipagram_lengths) as total_flipagram_lengths
 from interactions
 group by 1

 ) b on a.user_id=b.user_id

 -- left join ios_users ios on a.user_id=ios.user_id

 left join
 (
 select a.user_id
 from users a
 join retention_agg b on (a.user_id = b.user_id and b.visit_date = a.date_created + interval '1 day')
 group by 1
 ) d1 on a.user_id=d1.user_id

 left join
 (
 select a.user_id
 from users a
 join retention_agg b on (a.user_id = b.user_id and b.visit_date = a.date_created + interval '7 day')
 group by 1
 ) d7 on a.user_id=d7.user_id

 left join
 (
 select a.user_id
 from users a
 join retention_agg b on (a.user_id = b.user_id and b.visit_date between a.date_created + interval '8 day' and a.date_created + interval '15 day')
 group by 1
 ) w2 on a.user_id=w2.user_id

 left join
 (
 select a.user_id
 from users a
 join retention_agg b on (a.user_id = b.user_id and b.visit_date between a.date_created + interval '30 day' and a.date_created + interval '60 day')
 group by 1
 ) m1 on a.user_id=m1.user_id

 left join
 (
 select a.user_id
 from users a
 join retention_agg b on (a.user_id = b.user_id and b.visit_date between a.date_created + interval '61 day' and a.date_created + interval '90 day')
 group by 1
 ) m2 on a.user_id=m2.user_id

 left join
 (
 select a.user_id
 from users a
 join retention_agg b on (a.user_id = b.user_id and b.visit_date between a.date_created + interval '91 day' and a.date_created + interval '120 day')
 group by 1
 ) m3 on a.user_id=m3.user_id

 left join push_data_agg push on a.user_id=push.user_id
 left join push_data_time_d0 push_time_d0 on a.user_id=push_time_d0.user_id
 left join push_data_time_d1 push_time_d1 on a.user_id=push_time_d1.user_id
 left join push_data_time_d7 push_time_d7 on a.user_id=push_time_d7.user_id
 left join push_data_time_d14 push_time_d14 on a.user_id=push_time_d14.user_id
 left join push_data_time_d30 push_time_d30 on a.user_id=push_time_d30.user_id
 left join days_visited dv on a.user_id = dv.user_id
 left join actions_rec ar on a.user_id = ar.user_id
 left join interations_sum intsum on a.user_id=intsum.user_id
 left join following following on a.user_id=following.user_id
 left join followed followed on a.user_id=followed.user_id
 left join flipagram_time_box ftb on a.user_id=ftb.user_id
 left join ios_source iosource on a.user_id=iosource.user_id
 left join analysts.firebase_source_user androidsource on (a.user_id=androidsource.user_id and source != '(direct)')
 left join ios_user ios_usr on a.user_id=ios_usr.user_id
 left join android_user android_usr on a.user_id=android_usr.user_id
 left join branch_ua branch on a.user_id=branch.user_id

 ;
No results found