Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement