Transfer Service Test

queries.sql

/* What does the new service funnel look like for users who saw the landing page? */
select name, count(*) views from
(
  select message:event:domain || '.' || message:event:object name, message:event:user:uuid uuid, min(timestamp)
  from prod.event_bus_prod.eb_events
  where timestamp > '2019-04-08 22:02:00.000'
  and type like 'event.eventstream.home_services.%.viewed'
  and uuid in
  (
    select distinct message:event:user:uuid uuid from prod.event_bus_prod.eb_events
    where type = 'event.eventstream.home_services.landing.viewed'
    and timestamp > '2019-04-08 22:02:00.000'
  )
  group by 1, 2
)
group by name
order by views desc

/* How many users saw both transfer and new service flows? */
select count(*) from (
  select distinct message:event:user:uuid uuid from prod.event_bus_prod.eb_events
  where type = 'event.eventstream.home_services.landing.viewed'
  and timestamp > '2019-04-08 22:02:00.000'
  intersect
  select distinct message:event:user:uuid uuid from prod.event_bus_prod.eb_events
  where type = 'event.eventstream.home_services.transfer_call.viewed'
  and timestamp > '2019-04-08 22:02:00.000'
  intersect
  select distinct message:event:user:uuid uuid from prod.event_bus_prod.eb_events
  where type = 'event.eventstream.home_services.internet_speed.viewed'
  and timestamp > '2019-04-08 22:02:00.000'
)

/* How many users saw both transfer call and browse offers pages? */
select count(*) from (
  select distinct message:event:user:uuid uuid from prod.event_bus_prod.eb_events
  where type = 'event.eventstream.home_services.landing.viewed'
  and timestamp > '2019-04-08 22:02:00.000'
  intersect
  select distinct message:event:user:uuid uuid from prod.event_bus_prod.eb_events
  where type = 'event.eventstream.home_services.transfer_call.viewed'
  and timestamp > '2019-04-08 22:02:00.000'
  intersect
  select distinct message:event:user:uuid uuid from prod.event_bus_prod.eb_events
  where type = 'event.eventstream.home_services.browse_offers.viewed'
  and timestamp > '2019-04-08 22:02:00.000'
)

/* Of people who see the landing page, where do they go next? */
select next_page, count(*) frequency from
(
  select array_position('home_services.landing'::variant, journey) landing_index, journey[landing_index + 1] next_page from
  (
    select message:event:user:uuid uuid,
      array_agg(message:event:domain || '.' || message:event:object) within group (order by timestamp) journey
    from prod.event_bus_prod.eb_events
    where type like 'event.eventstream.%.viewed'
    and timestamp > '2019-04-08 22:02:00.000'
    group by uuid
  )
  where landing_index is not null
)
group by next_page
order by frequency desc

/* How many people click new service vs. transfer service? */
select (  
  select count(distinct message:event:user:uuid) from prod.event_bus_prod.eb_events
  where type = 'event.eventstream.home_services.new_service.clicked'
  and timestamp > '2019-04-08 22:02:00.000'
  and message:event:user:test != true
) new_service,
( select count(distinct message:event:user:uuid) from prod.event_bus_prod.eb_events
  where type = 'event.eventstream.home_services.transfer_service.clicked'
  and timestamp > '2019-04-08 22:02:00.000'
  and message:event:user:test != true
) transfer_service,
new_service + transfer_service total,
new_service * 100.0 / total new_service_percentage,
transfer_service * 100.0 / total transfer_service_percentage

/* What is the next step breakdown for users who saw the landing page? */
with transfer_option_users as
(
  select distinct message:event:user:uuid uuid from prod.event_bus_prod.eb_events
  where type = 'event.eventstream.home_services.landing.viewed'
  and timestamp > '2019-04-08 22:02:00.000'
)
select journey[next_step_index - 1] previous_page, count(*) page_views, count(*) * 100.0 / sum(count(*)) over() percentage from
(
  select journey, array_position('mover.next_step'::variant, journey) next_step_index from
  (
    select array_agg(name) within group (order by timestamp) journey from
    (
      select message:event:domain || '.' || message:event:object name, message:event:user:email email, message:event:user:test test, timestamp
      from prod.event_bus_prod.eb_events e inner join transfer_option_users u on e.message:event:user:uuid = u.uuid
      where type like 'event.eventstream.%.viewed'
      and test != true
      and timestamp >  '2019-04-08 22:02:00.000'
    )
    group by email
  )
  where next_step_index is not null
)
group by previous_page
order by page_views desc