Stripe + Segment – average revenue by first page viewed
What is the average revenue according to the first page viewed?
Stripe (full schema here)
Analytics.js (docs here)
with -- Get the revenue by customer stripe_revenue as ( select charges.customer_id, users.email email, sum(amount) / 100 customer_value from stripe.charges charges right join ( select id, email from stripe.customers ) as users("id", "email") on charges.customer_id = users.id group by 1,2 ), -- Get the anonymous_id for each customer email_anonymous_ids as ( select anonymous_id, stripe_revenue.email, case when customer_value is null then 0 else customer_value end from stripe_revenue left join ( select anonymous_id, email from segment.identifies ) as identifies("anonymous_id", "email") on identifies.email = stripe_revenue.email group by 1,2,3 ), page_ordered as ( select anonymous_id, received_at, name, row_number() over (partition by anonymous_id order by received_at asc) as rn from segment.pages ), -- Get the first pageview first_page as ( select * from page_ordered where rn = 1 ), -- Join the first pageview with the revenue pages_with_revenue as ( select first_page.anonymous_id, received_at, name, customer_value from first_page left join ( select anonymous_id, customer_value from email_anonymous_ids ) as anonymous_with_revenue("anonymous_id", "customer_value") on first_page.anonymous_id = anonymous_with_revenue.anonymous_id where name is not null ) select case when name ilike '%blog%' then 'Blog' when name ilike '%integrations%' then 'Integrations Page' when name ilike '%pricing%' then 'Pricing Page' when name ilike '%signup%' then 'Signup Page' when name ilike '%home%' then 'Home Page' when name ilike '%docs%' then 'Docs Page' when name ilike '%help%' then 'Help Page' when name ilike '%academy%' then 'Academy Page' when name ilike '%jobs%' then 'Jobs Page' when name ilike '%redshift%' or name ilike '%sql%' then 'Redshift Page' else 'Other' end, avg(customer_value) from pages_with_revenue group by 1 order by 2 desc
Upvote Follow 1