1

Stripe + Segment – average revenue by first page viewed

Question

What is the average revenue according to the first page viewed?

Sources

Stripe (full schema here)

Analytics.js (docs here)

Results

Query

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